Tim Van Wassenhove

Passionate geek, interested in Technology. Proud father of two

13 Jul 2011

Using User-Defined Table Type with Identity column in ADO.NET

A while ago i wanted to use a User-Defined Table Type to pass in a set of records. Nothing special about this except that the first column of the UDTT was an Identity column:

CREATE TYPE [Star].[example] AS TABLE(  
  [Ordinal] [int] IDENTITY(1,1) NOT NULL,  
  [Name] [nvarchar](200) NOT NULL,

After finding a lot of posts saying that this is not supported a colleague of mine, Stephen Horsfield, found a way to do it as following:

var sqlMetaData = new[] 
  new SqlMetaData("Ordinal", SqlDbType.Int, true, false, SortOrder.Unspecified, -1),   
  new SqlMetaData("Name", SqlDbType.NVarChar, 200)

sqlRecords = new HashSet<SqlDataRecord>(usersToInclude.Select(user =>
  var record = new SqlDataRecord(sqlMetaData);   
  record.SetString(1, user.Name);   
  return record;