SQLGeography in SQL Server 2012 – Polygon must start on correct position? No!

Posted on 2012/12/03 by Daniel Wertheim • Posted in Ado.Net, SQL-Server • Tagged Spatial, sqlserver • 1 Comment

This is a follow up on my post that I wrote this weekend: “SQLGeography in SQL Server 2012 – Polygon must start on correct position?“, you probably want to go and skim it through and then come back. But basically after my first tests I got the feeling that the starting position was the issue, but it turns out it’s not. It’s the “left hand rule”. Let my try and explain with two pictures.

sqlgeography-polygon-clockwise

If you draw the polygon walking “clockwise” and imagine your self looking to the left. Then you are looking away from the shape being constructed, hence we will get the area outside of the shape.

If you instead walk counter-clockwise, you will be looking into the shape, hence we will get the polygon we desired. At least for my use-case.

sqlgeography-polygon-counter-clockwise

Sorry,

//Daniel

SQLGeography in SQL Server 2012 – Polygon must start on correct position?

Posted on 2012/12/01 by Daniel Wertheim • Posted in Ado.Net, SQL-Server, Transact Sql • Tagged longitude latitude, Spatial, sql server • 3 Comments

I’ve been working with some simple spatial data lately. In one use-case, the user marks a polygon by using an iPad or iPhone in field and walks around storing points (coordinates), which after at least three points, forms an area (the first coordinates are also used as the last to close it). I’m using the builtin spatial funtionality in SQL Server 2012. According to SqlGeography.STIsValid and SqlGeography.IsValidDetailed, the surface was a valid polygon, but when using e.g SqlGeography.STContains and checking if a point was contained by the polygon, it said yes for one that wasn’t, and no for one that was. My first thought was that I had mixed up the values or mapped Longitude, Latitude wrong. But it worked correctly some times. Before coming to the error let’s set up a use-case

EDIT#1: It might actually be the case that it’s not the start point that is the issue, but the direction. If you draw it counter-clockwise it works. I’m no GIS guy and I probably miss a bunch of things here, but as a dev, I expect Microsoft to try and take care of this kind of stuff or at least make the API give a hint about it.

EDIT#2: I’ve written a followup on this post, explaining the issue and the “left hand rule”. Again feel free to educate me.

Use-case “Castle of Wertheim”

There’s a town in Germany, called Wertheim. In that town, there is a castle and I’ve used Bing Maps to draw a polygon around it.

Wertheim, Castle, polygon

The coordinates has been exported and made appropriate for use with SqlGeograpy.STPolyFromText, like this:

POLYGON((Longitude Latitude, ..., Longitude Latitude))

Now let’s just have a quick look at how the polygon will be treated in SQL Server 2012. Declare a geography variable representing the shape I drew on Bing maps.

declare @g geography
set @g = geography::STPolyFromText('POLYGON((9.520413279533372 49.75894717649063, 9.520086050033555 49.758562511155105, 9.519426226615892 49.75856770935565, 9.519155323505387 49.758881333091104, 9.51935380697249 49.75917069683788, 9.519538879394517 49.75930238276165, 9.519844651222215 49.75922614358621, 9.520131647586808 49.759229609005885, 9.5204722881317 49.759349165832695, 9.520630538463578 49.75934396771592, 9.52069222927092 49.759262530480356, 9.520689547061906 49.75921401461543, 9.520770013332353 49.759172429549714, 9.520866572856889 49.75913777530104, 9.520893394947038 49.75907713030626, 9.52084511518477 49.75901648523564, 9.520732462406144 49.75899222718616, 9.520528614521012 49.75899569262254, 9.520413279533372 49.75894717649063))', 4236)

If we just select it, Management Studio will render the surface for us:

select @g

By default it outputs this view:

ManagementStudio01

Click on the “Spatial result” tab and you will see the rendered surface:

ManagementStudio02

Something went wrong indeed. As it turns out, it seems that where you start drawing your polygon (your start position) is of great importance. So, for me. The user draw a valid polygon but he just started out on the wrong point.

Fixing it

Lets use some other functions. First: SqlGeography.ReorientObject and then: SqlGeography.STAsText. ReorientObject and in our case make it start on the correct point. By dumping it out as text we can easily take the first Longitude and Laitude pair and map them on Bing Maps. Note! You need to switch places, otherwise you will end up in Africa somewhere. Latitude should come first. As we can see the correct start point is the one furthest to the left (west).

select @g.ReorientObject().STAsText()

ouputs:

POLYGON ((9.5191553235053874 49.758881333091104, 9.5194262266158916 49.758567709355653, 9.5200860500335551 49.758562511155105, 9.520413279533372 49.758947176490629, 9.5205286145210124 49.758995692622541, 9.5207324624061442 49.758992227186162, 9.52084511518477 49.759016485235641, 9.5208933949470378 49.75907713030626, 9.5208665728568889 49.759137775301042, 9.5207700133323527 49.759172429549714, 9.520689547061906 49.759214014615431, 9.52069222927092 49.759262530480356, 9.5206305384635783 49.759343967715921, 9.5204722881317 49.759349165832695, 9.5201316475868083 49.759229609005885, 9.5198446512222148 49.759226143586211, 9.519538879394517 49.759302382761653, 9.51935380697249 49.759170696837877, 9.5191553235053874 49.758881333091104))

which gives us a first point with, Latitude, Longitude (note that places are switched):

49.758881333091104 9.5191553235053874

and on the map:

Wertheim, Castle, polygon-Correct start

So ReorientObjects looks nifty. It kind of inverts the shape. From docs:

Returns a geography instance with interchanged interior regions and exterior regions

But this isn’t something we can use if the user did draw it correctly. Lucky us there’s another function we can use: SqlGeography.EnvelopeAngle, from docs:

For instances with angles greater than 90 degrees, 180 degrees will be returned.

So if we try:

select case when @g.EnvelopeAngle() > 90 then @g.ReorientObject() else @g end

we will now see it render correctly:

ManagementStudio03

Fixing it in C#

I needed this to be fixed in C#, since the user-drawn polygons are stored using ADO.Net. I put together a small factory that will assist me with it:

public static class GeographyFactory
{
    public static SqlGeography CreatePolygon(Coordinates[] coordinates, int srid)
    {
        var list = coordinates.Distinct().ToList();

        var b = new SqlGeographyBuilder();
        b.SetSrid(srid);
        b.BeginGeography(OpenGisGeographyType.Polygon);
        b.BeginFigure(list[0].Latitude, list[0].Longitude);

        for (var i = 1; i < list.Count; i++)
            b.AddLine(list[i].Latitude, list[i].Longitude);

        b.AddLine(list[0].Latitude, list[0].Longitude);
        b.EndFigure();
        b.EndGeography();

        return b.ConstructedGeography.EnvelopeAngle() > 90 
            ? b.ConstructedGeography.ReorientObject() 
            : b.ConstructedGeography;
    }
}

In theory I guess you could re-order your coordinates instead and start with the one lying most to the left (west).

//Daniel

Microsoft.SqlServer.Types and SQL Server 2012, inconsistency

Posted on 2012/11/14 by Daniel Wertheim • Posted in Ado.Net, SQL-Server, Transact Sql • Tagged Ado.Net, microsoft sqlserver, sqlserver2012, t-sql • Leave a comment

Been working a bit with SQL Server 2012 and spatial features using the Geography data type. I’m using it from Ado.Net hence I make use of the Microsoft.SqlServer.Types library. Didn’t take long to get annoyed about the API in there. Let’s have a look. There´s a type SqlGeography that you can use as a parameter against SQL Server. First I created a simple polygon consisting of some coordinates (don’t know where on earth since it’s taken from MSDN).

Due to lack of buidling a polygon from actual instances SqlGeography representing points we need to do it the textual way:

var polygon = new SqlChars("POLYGON((
    -122.358 47.653, 
    -122.348 47.649, 
    -122.348 47.658, 
    -122.358 47.658, 
    -122.358 47.653))".ToCharArray());

Now we could create a standard parameter for use in our SQL:

var coordsP = (SqlParameter)cmd.CreateParameter();
coordsP.ParameterName = "pCoords";
coordsP.Direction = ParameterDirection.Input;
coordsP.SqlDbType = SqlDbType.Udt;
coordsP.UdtTypeName = "geography";
coordsP.Value = SqlGeography.STPolyFromText(polygon, 4326);

There´s a method SqlGeography.Point(latitude, longitude, srid) that you could use to create a simple point, it would look something like this:

var point = SqlGeography.Point(
    47.653, //Latitude 
    -122.358, //Longitude
    4326); //SRID

Lets have a look at the data we inserted using the polygon created above:

select
    Coords.STAsText() Polygon,
    Coords.STPointN(1).STAsText() Point1,
    Coords.STPointN(1).Lat Point1Latitud,
    Coords.STPointN(1).Long Point1Longitude
from LocationSpatials

outputs:

Polygon
--------------          
POLYGON ((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))

Point1
--------------
POINT (-122.358 47.653)

Point1Latitud
--------------
47,653

Point1Longitude
--------------
-122,358

Tell me you spot it! These kind of inconsistencies drives me crazy! If I in code define a point with Latitude first and Longitude as the second argument, I sure as hell would expect that to be the layout of the STAsText methods as well. Also, isn’t it cutym to have it Latitude, Longitude and not Longitude, Latitude? I hope I’m wrong, otherwise the person doing the STAsText method must have been drunk.

//Daniel

Truly amazing! SQLCE truncates parameters

Posted on 2012/05/24 by Daniel Wertheim • Posted in Ado.Net, SQLCE • Tagged Ado.Net, sqlce4 • Leave a comment

Recently I got a bug report in SisoDb where a document got truncated hence when getting deserialized back the deserialization process blow up. After a short investigation I found this truly amazing bug in SQLCe4. If you have a field being “nvarchar(4000)” and you use a command parameters that has a value being 4001 chars where you set the “dbParam.Size” property to 4001 chars; the size will be 4000 and then it just truncates the value upon insert. SQL Server on the other hand, behaves correctly. It throws an exception warning about truncation.

Short sample

I’ve put together a small sample showing the behavior in this Gist: https://gist.github.com/2784218 I really do hope it’s me missing something.

class Program
{
	static void Main(string[] args)
	{
		FatSql(); //Fails
		Console.WriteLine("***** ***** ***** ***** *****");
		Ce(); //Gives 4000 chars and not 4001, hence, truncation

		Console.ReadLine();
	}

	static void FatSql()
	{
		try
		{
			using (var cn = new SqlConnection("data source=.;initial catalog=;integrated security=true;"))
			{
				cn.Open();
				cn.ExecuteSql("if DB_ID('TrulyAmazing') is null begin create database TrulyAmazing; end");
				cn.ExecuteSql("if OBJECT_ID(N'TrulyAmazing.dbo.Foo', N'U') is null begin create table TrulyAmazing.dbo.Foo(SomeValue nvarchar(4000));end");
				cn.ExecuteSql("truncate table TrulyAmazing.dbo.Foo;");
				cn.ExecuteSql("insert into TrulyAmazing.dbo.Foo values (@p0);", new string('a', 4001));
				Console.WriteLine(cn.ExecuteScalarString("select top 1 SomeValue from TrulyAmazing.dbo.Foo;").Length);
			}
		}
		catch (Exception ex)
		{
			Console.WriteLine(ex.Message);
		}
	}

	static void Ce()
	{
		const string dbPath = @"c:\Temp\TrulyAmazing.sdf";

		if (!File.Exists(dbPath))
			using (var e = new SqlCeEngine(string.Concat("data source=", dbPath)))
				e.CreateDatabase();

		using (var cn = new SqlCeConnection(string.Concat("data source=", dbPath)))
		{
			cn.Open();
			if(string.IsNullOrWhiteSpace(cn.ExecuteScalarString("select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME='Foo';")))
				cn.ExecuteSql("create table Foo(SomeValue nvarchar(4000))");
			
			cn.ExecuteSql("insert into Foo values (@p0);", new string('a', 4001));
			Console.WriteLine(cn.ExecuteScalarString("select top 1 SomeValue from Foo;").Length);
		}
	}
}

public static class AdoExtensions
{
	public static void ExecuteSql(this IDbConnection cn, string sql, params object[] parameters)
	{
		using(var cmd = cn.CreateCommand())
		{
			cmd.CommandType = CommandType.Text;
			cmd.CommandText = sql;

			for (var i = 0; i < parameters.Length; i++)
			{
				var parameter = parameters[i];
				var dbParam = cmd.CreateParameter();
				dbParam.ParameterName = string.Concat("@p", i);

				if (parameter is string)
				{
					dbParam.DbType = DbType.String;
					dbParam.Size = parameter.ToString().Length;
				}

				dbParam.Value = parameter;

				cmd.Parameters.Add(dbParam);
			}

			cmd.ExecuteNonQuery();
		}
	}

	public static string ExecuteScalarString(this IDbConnection cn, string sql)
	{
		using (var cmd = cn.CreateCommand())
		{
			cmd.CommandType = CommandType.Text;
			cmd.CommandText = sql;

			var value = cmd.ExecuteScalar();

			if(value == null || value == DBNull.Value)
				return null;

			return value.ToString();
		}
	}
}

//Daniel

C# – Custom datareader for SqlBulkCopy

Posted on 2010/11/10 by Daniel Wertheim • Posted in Ado.Net • Tagged Ado.Net, c#, custom datareader, sqlbulkcopy • 1 Comment

When prototyping SisoDb I used datatables under the covers when consuming the SqlBulkCopy class to insert data. This lead to that I had the source entities in memory as well as the datatables. Since the SqlBulkCopy class can work with readers I created a very simple datareader implementation over my entities instead. I gained a little performance boost and got rid of unnecessary creation of in-memory datatables. At first the API of a datareader seems kind of tedious to implement, but in the context of using it in conjunction with the SqlBulkCopy-class, there really isn’t that many methods that need to be implemented:

public override int GetOrdinal(string name)
public override IEnumerator GetEnumerator()
public override bool Read()
public override void Close()
public override bool HasRows
public override int FieldCount
public override int RecordsAffected
public override bool IsClosed

My model contains two entities but three sets: Structure, StructureIndex; sets: Structures, Indexes, Uniques. The root is the Structure which then contains the other sets. For me the simplest solution was to create one reader for each entity. Each custom reader extended a custom base-class: SingleResultReaderBase<T>.

To keep track of tablename, columnnames, ordinals etc. I put together a verry simple StorageSchema base class and then one specific implementation for each entity (Structure, StructureIndex).

That’s pretty much it. I will show you the code for how I handled the root entity. The complete code for the other entities can be found in the trunk of http://sisodb.codeplex.com

SingleResultReaderBase<T>

internal abstract class SingleResultReaderBase<T> : DbDataReader where T : class 
{
	protected internal StorageSchemaBase StorageSchema { get; private set; }

	protected IEnumerable<T> Items { get; private set; }

	protected IEnumerator<T> Enumerator { get; private set; }

	protected SingleResultReaderBase(StorageSchemaBase storageSchema, IEnumerable<T> items)
	{
		StorageSchema = storageSchema;
		Items = items;
		Enumerator = Items.GetEnumerator();
	}

	public override bool IsClosed
	{
		get { return Enumerator == null; }
	}

	public override int RecordsAffected
	{
		get { return Items.Count(); }
	}

	public override int FieldCount
	{
		get { return StorageSchema.FieldCount(); }
	}

	public override bool HasRows
	{
		get { return Items != null && Items.Count() > 0; }
	}

	public override void Close()
	{
		Enumerator = null;
		Items = null;
	}
	
	public override bool Read()
	{
		return Enumerator.MoveNext();
	}

	public override IEnumerator GetEnumerator()
	{
		return Enumerator;
	}

	public override int GetOrdinal(string name)
	{
		return StorageSchema.FieldsByName[name].Index;
	}

	public override object this[int ordinal]
	{
		get { throw new NotSupportedException(); }
	}

	public override object this[string name]
	{
		get { throw new NotSupportedException(); }
	}

	public override int Depth
	{
		get { throw new NotSupportedException(); }
	}

	public override string GetName(int ordinal)
	{
		throw new NotSupportedException();
	}

	public override DataTable GetSchemaTable()
	{
		throw new NotSupportedException();
	}

	public override bool NextResult()
	{
		throw new NotSupportedException();
	}

	public override bool GetBoolean(int ordinal)
	{
		throw new NotSupportedException();
	}

	public override byte GetByte(int ordinal)
	{
		throw new NotSupportedException();
	}

	public override long GetBytes(int ordinal, long dataOffset, byte[] buffer, int bufferOffset, int length)
	{
		throw new NotSupportedException();
	}

	public override char GetChar(int ordinal)
	{
		throw new NotSupportedException();
	}

	public override long GetChars(int ordinal, long dataOffset, char[] buffer, int bufferOffset, int length)
	{
		throw new NotSupportedException();
	}

	public override Guid GetGuid(int ordinal)
	{
		throw new NotSupportedException();
	}

	public override short GetInt16(int ordinal)
	{
		throw new NotSupportedException();
	}

	public override int GetInt32(int ordinal)
	{
		throw new NotSupportedException();
	}

	public override long GetInt64(int ordinal)
	{
		throw new NotSupportedException();
	}

	public override DateTime GetDateTime(int ordinal)
	{
		throw new NotSupportedException();
	}

	public override string GetString(int ordinal)
	{
		throw new NotSupportedException();
	}

	public override int GetValues(object[] values)
	{
		throw new NotSupportedException();
	}

	public override bool IsDBNull(int ordinal)
	{
		throw new NotSupportedException();
	}

	public override decimal GetDecimal(int ordinal)
	{
		throw new NotSupportedException();
	}

	public override double GetDouble(int ordinal)
	{
		throw new NotSupportedException();
	}

	public override float GetFloat(int ordinal)
	{
		throw new NotSupportedException();
	}

	public override string GetDataTypeName(int ordinal)
	{
		throw new NotSupportedException();
	}

	public override Type GetFieldType(int ordinal)
	{
		throw new NotSupportedException();
	}
}

StructuresReader

internal class StructuresReader : SingleResultReaderBase<IStructure>
{
	internal StructuresReader(StructureStorageSchema storageSchema, IEnumerable<IStructure> items)
		: base(storageSchema, items)
	{
	}

	public override object GetValue(int ordinal)
	{
		var schemaField = StorageSchema.FieldsByIndex[ordinal];
		if(schemaField.Name == StructureStorageSchema.Fields.Id.Name)
		{
			if (Enumerator.Current.Id.IdType == IdTypes.Identity)
				return int.Parse(Enumerator.Current.Id.Value);

			if (Enumerator.Current.Id.IdType == IdTypes.Guid)
				return Guid.Parse(Enumerator.Current.Id.Value);

			throw new NotSupportedException();
		}

		if(schemaField.Name == StructureStorageSchema.Fields.Json.Name)
			return Enumerator.Current.Json;

		throw new NotSupportedException();
	}
}

StorageSchema

[Serializable]
internal abstract class StorageSchemaBase
{
	internal readonly Dictionary<int, SchemaField> FieldsByIndex;
	internal readonly Dictionary<string, SchemaField> FieldsByName;

	internal string Name { get; private set; }

	protected StorageSchemaBase(string name)
	{
		FieldsByIndex = new Dictionary<int, SchemaField>();
		FieldsByName = new Dictionary<string, SchemaField>();
		Name = name;
		
		InitializeFields();
	}

	protected abstract void InitializeFields();

	public int FieldCount()
	{
		return FieldsByIndex.Count;
	}
}

[Serializable]
internal class StructureStorageSchema
	: StorageSchemaBase
{
	internal static class Fields
	{
		internal static readonly SchemaField Id = new SchemaField(0, "Id");
		internal static readonly SchemaField Json = new SchemaField(1, "Json");

		internal static SchemaField[] GetOrderedFields()
		{
			return new[] { Id, Json };
		}
	}

	internal StructureStorageSchema(IStructureSchema structureSchema) 
		: base(structureSchema.GetStructureTableName())
	{
	}

	protected override void InitializeFields()
	{
		foreach (var field in Fields.GetOrderedFields())
		{
			FieldsByIndex.Add(field.Index, field);
			FieldsByName.Add(field.Name, field);
		}
	}
}

Then to consume the reader you would have code looking something like this:

using(var structuresReader = new StructuresReader(structureStorageSchema, structures))
{
	using (var structuresBulkInserter = new SqlBulkCopy(_connection,
		SqlBulkCopyOptions.Default | SqlBulkCopyOptions.KeepIdentity, _transatcion))
	{
		structuresBulkInserter.BatchSize = structuresReader.RecordsAffected;
		structuresBulkInserter.DestinationTableName = structuresReader.StorageSchema.Name;
		structuresBulkInserter.NotifyAfter = 0;

		foreach (var field in structuresReader.StorageSchema.FieldsByIndex.Values)
			structuresBulkInserter.ColumnMappings.Add(field.Name, field.Name);

		structuresBulkInserter.WriteToServer(structuresReader);
		structuresBulkInserter.Close();
	}
}

C# – Batch identity inserts

Posted on 2010/10/24 by Daniel Wertheim • Posted in Ado.Net • Tagged Ado.Net, Identity bulk insert, SisoDb • 2 Comments

Updated! Now contains a second version which will return all id’s if you are uncertain of how table locks and row locks will be handled.

NOTE!

You really should think twice before using this kind of code. What you should do instead is to at least try to use the SqlBulCopy class instead. Of course you will get problems since it will not give you the id’s back, but there are solutions to that problem. E.g. you can create a solution where you in the database preserve a range of identities before you perform the insert. By doing this you can assign your parents and their children the correct id-values and use the KeepIdentity option of the SqlBulkCopy class.

I’m currently building support for identity inserts in SisoDb where I need to let the database generate the ids and then assign them to the entities. To get better performance I want to batch up the inserts and sending them in a large chunk to the Sql-server and I want to make use of parameters to be protected from sql-injection attacks. You can of course use SqlBulkCopy but it will not let you get the generated identities so that you can assign them to your entities.

This is a draft of the solution so you might have to tweak it a bit.

Tables

For this example I will use a classic example of Cars and Owners where each car can have one owner. The tables look like this:

Person {#Id, PersonNo, Name}
Car {#Id, RelOwnerId, RegNo, Make, Model}

Person -- <1:N> -- Car

The Id-fields are defined as identities.

Create records for insert

I have created a IdentityInsertRecord that holds the parent record values as well as several rows of child record values.

var records = new List<IdentityInsertRecord>();

var parentValues = new List<string> { "800503", "Hans Wertheim" };
var childValues = new List<string[]>
                        {
                            new[] { "ABC111", "Volvo", "S60" },
                            new[] { "ABC112", "Volvo", "V70" },
                            new[] { "ABC113", "Volvo", "XC90" },
                        };
records.Add(new IdentityInsertRecord(parentValues, childValues));

parentValues = new List<string> { "800504", "Daniel Wertheim" };
childValues = new List<string[]>
                        {
                            new[] { "ABC211", "Audi", "A3" },
                            new[] { "ABC212", "Audi", "A4" },
                            new[] { "ABC213", "Audi", "A6" },
                        };
records.Add(new IdentityInsertRecord(parentValues, childValues));

parentValues = new List<string> { "800505", "Anton Wertheim" };
childValues = new List<string[]>
                        {
                            new[] { "ABC311", "BMW", "316" },
                            new[] { "ABC412", "BMW", "320" },
                            new[] { "ABC513", "BMW", "323" },
                        };
records.Add(new IdentityInsertRecord(parentValues, childValues));

The code above defines three parent records (Persons) that each owns three cars.

Generate the Sql

To generate the Sql I have created a SqlBulkIdentityInsertsGenerator class that you will have to provide with some metadata, describing the names of the parent table, child table and their columns as well as the column in the child table holding the referencing value.

var generator = new SqlBulkIdentityInsertsGenerator
{
    ParentTableName = "Person",
    ParentColumnNames = { "PersonNo", "Name" },
    ChildTableName = "Car",
    ChildColumnNames = { "RegNo", "Make", "Model" },
    NameOfRelationalColumn = "RelOwnerId"
};

After that, all you have to do is let the generator generate the sql needed to insert the rows.

var sql = generator.GenerateSql(records);

Insert the rows and extract the Id’s

In this post I will just show you this manually, which means, I have taken the generated sql and executed it via Management studio.

VERSION 1

declare @currentParentId int;
declare @firstParentId int;
declare @lastParentId int;
declare @firstChildId int;
declare @lastChildId int;
exec sp_executesql N'insert into [dbo].[Person] ([PersonNo],[Name]) values (@p0,@p1);set @pOut=scope_identity();',N'@p0 varchar(100),@p1 varchar(100), @pOut int out', @p0='800503',@p1='Hans Wertheim',@pOut=@currentParentId out;
set @firstParentId=@currentParentId;
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);set @pOut=scope_identity();',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100), @pOut int out', @p0 = @currentParentId,@p1 = 'ABC111',@p2 = 'Volvo',@p3 = 'S60',@pOut=@firstChildId out;
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100)', @p0 = @currentParentId,@p1 = 'ABC112',@p2 = 'Volvo',@p3 = 'V70';
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100)', @p0 = @currentParentId,@p1 = 'ABC113',@p2 = 'Volvo',@p3 = 'XC90';
exec sp_executesql N'insert into [dbo].[Person] ([PersonNo],[Name]) values (@p0,@p1);set @pOut=scope_identity();',N'@p0 varchar(100),@p1 varchar(100), @pOut int out', @p0='800504',@p1='Daniel Wertheim',@pOut=@currentParentId out;
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100)', @p0 = @currentParentId,@p1 = 'ABC211',@p2 = 'Audi',@p3 = 'A3';
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100)', @p0 = @currentParentId,@p1 = 'ABC212',@p2 = 'Audi',@p3 = 'A4';
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100)', @p0 = @currentParentId,@p1 = 'ABC213',@p2 = 'Audi',@p3 = 'A6';
exec sp_executesql N'insert into [dbo].[Person] ([PersonNo],[Name]) values (@p0,@p1);set @pOut=scope_identity();',N'@p0 varchar(100),@p1 varchar(100), @pOut int out', @p0='800505',@p1='Anton Wertheim',@pOut=@currentParentId out;
set @lastParentId=@currentParentId;
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100)', @p0 = @currentParentId,@p1 = 'ABC311',@p2 = 'BMW',@p3 = '316';
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100)', @p0 = @currentParentId,@p1 = 'ABC412',@p2 = 'BMW',@p3 = '320';
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);set @pOut=scope_identity();',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100), @pOut int out', @p0 = @currentParentId,@p1 = 'ABC513',@p2 = 'BMW',@p3 = '323',@pOut=@lastChildId out;
select @firstParentId FirstParentId, @lastParentId LastParentId, @firstChildId FirstChildId, @lastChildId LastChildId;

The output becomes:

FirstParentId	LastParentId	FirstChildId	LastChildId
7	9	19	27

VERSION 2

declare @currentParentId int;
declare @currentChildId int;
declare @ids table(entityId int, entityType char(1));
exec sp_executesql N'insert into [dbo].[Person] ([PersonNo],[Name]) values (@p0,@p1);set @pOut=scope_identity();',N'@p0 varchar(100),@p1 varchar(100), @pOut int out', @p0='800503',@p1='Hans Wertheim',@pOut=@currentParentId out;
insert into @ids (entityId, entityType) values (@currentParentId, 'p');
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);set @pOut=scope_identity();',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100), @pOut int out', @p0 = @currentParentId,@p1 = 'ABC111',@p2 = 'Volvo',@p3 = 'S60',@pOut=@currentChildId out;
insert into @ids (entityId, entityType) values (@currentChildId, 'c');
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);set @pOut=scope_identity();',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100), @pOut int out', @p0 = @currentParentId,@p1 = 'ABC112',@p2 = 'Volvo',@p3 = 'V70',@pOut=@currentChildId out;
insert into @ids (entityId, entityType) values (@currentChildId, 'c');
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);set @pOut=scope_identity();',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100), @pOut int out', @p0 = @currentParentId,@p1 = 'ABC113',@p2 = 'Volvo',@p3 = 'XC90',@pOut=@currentChildId out;
insert into @ids (entityId, entityType) values (@currentChildId, 'c');
exec sp_executesql N'insert into [dbo].[Person] ([PersonNo],[Name]) values (@p0,@p1);set @pOut=scope_identity();',N'@p0 varchar(100),@p1 varchar(100), @pOut int out', @p0='800504',@p1='Daniel Wertheim',@pOut=@currentParentId out;
insert into @ids (entityId, entityType) values (@currentParentId, 'p');
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);set @pOut=scope_identity();',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100), @pOut int out', @p0 = @currentParentId,@p1 = 'ABC211',@p2 = 'Audi',@p3 = 'A3',@pOut=@currentChildId out;
insert into @ids (entityId, entityType) values (@currentChildId, 'c');
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);set @pOut=scope_identity();',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100), @pOut int out', @p0 = @currentParentId,@p1 = 'ABC212',@p2 = 'Audi',@p3 = 'A4',@pOut=@currentChildId out;
insert into @ids (entityId, entityType) values (@currentChildId, 'c');
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);set @pOut=scope_identity();',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100), @pOut int out', @p0 = @currentParentId,@p1 = 'ABC213',@p2 = 'Audi',@p3 = 'A6',@pOut=@currentChildId out;
insert into @ids (entityId, entityType) values (@currentChildId, 'c');
exec sp_executesql N'insert into [dbo].[Person] ([PersonNo],[Name]) values (@p0,@p1);set @pOut=scope_identity();',N'@p0 varchar(100),@p1 varchar(100), @pOut int out', @p0='800505',@p1='Anton Wertheim',@pOut=@currentParentId out;
insert into @ids (entityId, entityType) values (@currentParentId, 'p');
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);set @pOut=scope_identity();',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100), @pOut int out', @p0 = @currentParentId,@p1 = 'ABC311',@p2 = 'BMW',@p3 = '316',@pOut=@currentChildId out;
insert into @ids (entityId, entityType) values (@currentChildId, 'c');
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);set @pOut=scope_identity();',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100), @pOut int out', @p0 = @currentParentId,@p1 = 'ABC412',@p2 = 'BMW',@p3 = '320',@pOut=@currentChildId out;
insert into @ids (entityId, entityType) values (@currentChildId, 'c');
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);set @pOut=scope_identity();',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100), @pOut int out', @p0 = @currentParentId,@p1 = 'ABC513',@p2 = 'BMW',@p3 = '323',@pOut=@currentChildId out;
insert into @ids (entityId, entityType) values (@currentChildId, 'c');
select entityId, entityType from @ids order by entityType, entityId;

All you have left to do is to loop your entities in the correct order and assign the values.

Have fun.

The code

VERSION 1

[Serializable]
public class IdentityInsertRecord
{
    public IList<string> ParentValues { get; private set; }

    public IList<string[]> ChildValues { get; private set; }

    public IdentityInsertRecord(IEnumerable<string> parentValues, IEnumerable<string[]> childValues)
    {
        ParentValues = new List<string>(parentValues);
        ChildValues = new List<string[]>(childValues);
    }
}

[Serializable]
public class SqlFormats
{
    public string ParentSqlFormatWithOut { get; private set; }

    public string ChildSqlFormat { get; private set; }

    public string ChildSqlFormatWithOut { get; private set; }

    public SqlFormats(string parentSqlFormatWithOut, string childSqlFormat, string childSqlFormatWithOut)
    {
        ParentSqlFormatWithOut = parentSqlFormatWithOut;

        ChildSqlFormat = childSqlFormat;
        ChildSqlFormatWithOut = childSqlFormatWithOut;
    }
}

public class SqlBulkIdentityInsertsGenerator
{
    public string ParentTableName { get; set; }

    public List<string> ParentColumnNames { get; private set; }

    public string ChildTableName { get; set; }

    public List<string> ChildColumnNames { get; private set; }

    public string NameOfRelationalColumn { get; set; }

    public SqlBulkIdentityInsertsGenerator()
    {
        ParentTableName = string.Empty;
        ParentColumnNames = new List<string>();

        ChildTableName = string.Empty;
        ChildColumnNames = new List<string>();
        NameOfRelationalColumn = string.Empty;
    }

    private SqlFormats GenerateSqlFormats()
    {
        //TODO: Check ParentTableName, ChildTableName, ...., ....

        var parentColumnNamesString = string.Join(",", ParentColumnNames.Select(n => "[" + n + "]"));
        var childColumnNamesString = string.Join(",", ChildColumnNames.Select(n => "[" + n + "]"));

        var parentParams = new List<string>();
        var numOfInsertableParentColumns = ParentColumnNames.Count();
        for (var c = 0; c < numOfInsertableParentColumns; c++)
            parentParams.Add("@p" + c);

        var childParams = new List<string>();
        var numOfInsertableChildColumns = ChildColumnNames.Count();
        for (var c = 0; c < numOfInsertableChildColumns; c++)
            childParams.Add("@p" + (c + 1));

        var parentInsert = string.Format("insert into [dbo].[{0}] ({1}) values ({2});",
                                            ParentTableName,
                                            parentColumnNamesString,
                                            string.Join(",", parentParams));

        var childInsert = string.Format("insert into [dbo].[{0}] ([{1}],{2}) values ({3});",
                                        ChildTableName,
                                        NameOfRelationalColumn,
                                        childColumnNamesString,
                                        "@p0," + string.Join(",", childParams));

        var parentParamsString = string.Join(",", parentParams.Select(p => p + " varchar(100)"));
        var childParamsString = "@p0 int," + string.Join(",", childParams.Select(p => p + " varchar(100)"));

        var parentSqlFormatWithOut = string.Format("exec sp_executesql N'{0}set @pOut=scope_identity();',N'{1}, @pOut int out', {2};",
                                            parentInsert,
                                            parentParamsString,
                                            "{0}");

        var childSqlFormat = string.Format("exec sp_executesql N'{0}',N'{1}', {2};",
                                            childInsert,
                                            childParamsString,
                                            "{0}");

        var childSqlFormatWithOut = string.Format("exec sp_executesql N'{0}set @pOut=scope_identity();',N'{1}, @pOut int out', {2};",
                                            childInsert,
                                            childParamsString,
                                            "{0}");

        return new SqlFormats(parentSqlFormatWithOut, childSqlFormat, childSqlFormatWithOut);
    }

    public string GenerateSql(IList<IdentityInsertRecord> records)
    {
        var sqlFormats = GenerateSqlFormats();
        var sql = new StringBuilder();

        sql.AppendLine("declare @currentParentId int;");
        sql.AppendLine("declare @firstParentId int;");
        sql.AppendLine("declare @lastParentId int;");
        sql.AppendLine("declare @firstChildId int;");
        sql.AppendLine("declare @lastChildId int;");

        for (var c = 0; c < records.Count; c++)
        {
            var record = records[c];
            var isFirstRecord = c == 0;
            var isLastRecord = c == records.Count - 1;

            var parentParameters = new List<string>();
            foreach (var parentValue in record.ParentValues)
                parentParameters.Add(string.Format("@p{0}='{1}'", parentParameters.Count, parentValue));

            sql.AppendLine(string.Format(sqlFormats.ParentSqlFormatWithOut,
                                                string.Join(",", parentParameters) + ",@pOut=@currentParentId out"));

            if (isFirstRecord)
                sql.AppendLine("set @firstParentId=@currentParentId;");
            else if (isLastRecord)
                sql.AppendLine("set @lastParentId=@currentParentId;");
                
            for (var cChild = 0; cChild < record.ChildValues.Count; cChild++)
            {
                var childRow = record.ChildValues[cChild];
                var isFirstChildRow = cChild == 0;
                var isLastChildRow = cChild == record.ChildValues.Count - 1;

                var childParameters = new List<string>();
                childParameters.Add(string.Format("@p{0} = {1}", childParameters.Count, "@currentParentId"));

                foreach (var childValue in childRow)
                    childParameters.Add(string.Format("@p{0} = '{1}'", childParameters.Count, childValue));

                if (isFirstRecord && isFirstChildRow)
                    sql.AppendLine(string.Format(sqlFormats.ChildSqlFormatWithOut,
                                                    string.Join(",", childParameters) + ",@pOut=@firstChildId out"));
                else if(isLastRecord && isLastChildRow)
                    sql.AppendLine(string.Format(sqlFormats.ChildSqlFormatWithOut,
                                                    string.Join(",", childParameters) + ",@pOut=@lastChildId out"));
                else
                    sql.AppendLine(string.Format(sqlFormats.ChildSqlFormat, string.Join(",", childParameters)));
            }

            if (isLastRecord)
                sql.AppendLine(
                    "select @firstParentId FirstParentId, @lastParentId LastParentId, @firstChildId FirstChildId, @lastChildId LastChildId;");
        }

        return sql.ToString();
    }
}

VERSION 2

[Serializable]
public class IdentityInsertRecord
{
    public IList<string> ParentValues { get; private set; }

    public IList<string[]> ChildValues { get; private set; }

    public IdentityInsertRecord(IEnumerable<string> parentValues, IEnumerable<string[]> childValues)
    {
        ParentValues = new List<string>(parentValues);
        ChildValues = new List<string[]>(childValues);
    }
}

[Serializable]
public class SqlFormats
{
    public string ParentSqlFormatWithOut { get; private set; }

    public string ChildSqlFormat { get; private set; }

    public string ChildSqlFormatWithOut { get; private set; }

    public SqlFormats(string parentSqlFormatWithOut, string childSqlFormat, string childSqlFormatWithOut)
    {
        ParentSqlFormatWithOut = parentSqlFormatWithOut;

        ChildSqlFormat = childSqlFormat;
        ChildSqlFormatWithOut = childSqlFormatWithOut;
    }
}

public class SqlBulkIdentityInsertsGenerator
{
    public string ParentTableName { get; set; }

    public List<string> ParentColumnNames { get; private set; }

    public string ChildTableName { get; set; }

    public List<string> ChildColumnNames { get; private set; }

    public string NameOfRelationalColumn { get; set; }

    public SqlBulkIdentityInsertsGenerator()
    {
        ParentTableName = string.Empty;
        ParentColumnNames = new List<string>();

        ChildTableName = string.Empty;
        ChildColumnNames = new List<string>();
        NameOfRelationalColumn = string.Empty;
    }

    private SqlFormats GenerateSqlFormats()
    {
        //TODO: Check ParentTableName, ChildTableName, ...., ....

        var parentColumnNamesString = string.Join(",", ParentColumnNames.Select(n => "[" + n + "]"));
        var childColumnNamesString = string.Join(",", ChildColumnNames.Select(n => "[" + n + "]"));

        var parentParams = new List<string>();
        var numOfInsertableParentColumns = ParentColumnNames.Count();
        for (var c = 0; c < numOfInsertableParentColumns; c++)
            parentParams.Add("@p" + c);

        var childParams = new List<string>();
        var numOfInsertableChildColumns = ChildColumnNames.Count();
        for (var c = 0; c < numOfInsertableChildColumns; c++)
            childParams.Add("@p" + (c + 1));

        var parentInsert = string.Format("insert into [dbo].[{0}] ({1}) values ({2});",
                                            ParentTableName,
                                            parentColumnNamesString,
                                            string.Join(",", parentParams));

        var childInsert = string.Format("insert into [dbo].[{0}] ([{1}],{2}) values ({3});",
                                        ChildTableName,
                                        NameOfRelationalColumn,
                                        childColumnNamesString,
                                        "@p0," + string.Join(",", childParams));

        var parentParamsString = string.Join(",", parentParams.Select(p => p + " varchar(100)"));
        var childParamsString = "@p0 int," + string.Join(",", childParams.Select(p => p + " varchar(100)"));

        var parentSqlFormatWithOut = string.Format("exec sp_executesql N'{0}set @pOut=scope_identity();',N'{1}, @pOut int out', {2};",
                                            parentInsert,
                                            parentParamsString,
                                            "{0}");

        var childSqlFormat = string.Format("exec sp_executesql N'{0}',N'{1}', {2};",
                                            childInsert,
                                            childParamsString,
                                            "{0}");

        var childSqlFormatWithOut = string.Format("exec sp_executesql N'{0}set @pOut=scope_identity();',N'{1}, @pOut int out', {2};",
                                            childInsert,
                                            childParamsString,
                                            "{0}");

        return new SqlFormats(parentSqlFormatWithOut, childSqlFormat, childSqlFormatWithOut);
    }

    public string GenerateSql(IList<IdentityInsertRecord> records)
    {
        var sqlFormats = GenerateSqlFormats();
        var sql = new StringBuilder();

        sql.AppendLine("declare @currentParentId int;");
        sql.AppendLine("declare @currentChildId int;");
        sql.AppendLine("declare @ids table(entityId int, entityType char(1));");

        for (var c = 0; c < records.Count; c++)
        {
            var record = records[c];
            var isLastRecord = c == records.Count - 1;

            var parentParameters = new List<string>();
            foreach (var parentValue in record.ParentValues)
                parentParameters.Add(string.Format("@p{0}='{1}'", parentParameters.Count, parentValue));

            sql.AppendLine(string.Format(sqlFormats.ParentSqlFormatWithOut,
                                                string.Join(",", parentParameters) + ",@pOut=@currentParentId out"));

            sql.AppendLine("insert into @ids (entityId, entityType) values (@currentParentId, 'p');");
                
            foreach (var childRow in record.ChildValues)
            {
                var childParameters = new List<string>();
                childParameters.Add(string.Format("@p{0} = {1}", childParameters.Count, "@currentParentId"));

                foreach (var childValue in childRow)
                    childParameters.Add(string.Format("@p{0} = '{1}'", childParameters.Count, childValue));

                sql.AppendLine(string.Format(sqlFormats.ChildSqlFormatWithOut,
                                                string.Join(",", childParameters) + ",@pOut=@currentChildId out"));
                sql.AppendLine("insert into @ids (entityId, entityType) values (@currentChildId, 'c');");
            }

            if (isLastRecord)
                sql.AppendLine(
                    "select entityId, entityType from @ids order by entityType, entityId;");
        }

        return sql.ToString();
    }
}

//Daniel

Daniel Wertheim

Love what you do – Continue to do it – Be eager to learn more about it.

Category Archives: Ado.Net

SQLGeography in SQL Server 2012 – Polygon must start on correct position? No!

SQLGeography in SQL Server 2012 – Polygon must start on correct position?

Use-case “Castle of Wertheim”

Fixing it

Fixing it in C#

Microsoft.SqlServer.Types and SQL Server 2012, inconsistency

Truly amazing! SQLCE truncates parameters

Short sample

C# – Custom datareader for SqlBulkCopy

C# – Batch identity inserts

NOTE!

Tables

Create records for insert

Generate the Sql

Insert the rows and extract the Id’s

VERSION 1

VERSION 2

The code

VERSION 1

VERSION 2

Follow “Daniel Wertheim”

Share this:

Like this:

Use-case “Castle of Wertheim”

Fixing it

Fixing it in C#

Share this:

Like this:

Share this:

Like this:

Short sample

Share this:

Like this:

Share this:

Like this:

NOTE!

Tables

Create records for insert

Generate the Sql

Insert the rows and extract the Id’s

VERSION 1

VERSION 2

The code

VERSION 1

VERSION 2

Share this:

Like this:

Follow “Daniel Wertheim”