User Defined Table Types Dal

User defined table types can be passed on Ado.Net  with the use of DataTables

 

Dal code
  1. public static int AddBulkData(Guid itemGuid, List<SettingsPacket> data)
  2. {
  3.     int result = 0;
  4.  
  5.     DataTable settingsPacketsTable = new DataTable("dbo.udtTypeName");
  6.  
  7.     /*
  8.         [Col1] INT,
  9.         [ColGuid] UNIQUEIDENTIFIER,
  10.         [ColData]                 VARBINARY (MAX),
  11.         [ColBit]        BIT
  12.      */
  13.  
  14.     settingsPacketsTable.Columns.Add("Col1", typeof(byte));
  15.     settingsPacketsTable.Columns.Add("ColGuid", typeof(Guid));
  16.     settingsPacketsTable.Columns.Add("ColData", typeof(byte[]));
  17.     settingsPacketsTable.Columns.Add("ColBit", typeof(bool));
  18.  
  19.     foreach (SettingsPacket packet in data)
  20.     {
  21.         DataRow row = settingsPacketsTable.NewRow();
  22.         row["Col1"] = packet.SettingStatus;
  23.         row["ColGuid"] = itemGuid;
  24.         row["ColData"] = packet.Data;
  25.         row["ColBit"] = packet.IsGet;
  26.  
  27.         settingsPacketsTable.Rows.Add(row);
  28.     }
  29.  
  30.     using (ISqlDbConnection connection = SqlDbConnectionProvider.GetConnection("ConnStringName"))
  31.     using (ISqlCommandWrapper cmd = connection.CreateCommand("SprocName"))
  32.     {
  33.         cmd.AddInParameter("@UdtParamName", "dbo.udtTypeName", settingsPacketsTable);
  34.         cmd.AddReturnParameter("@Return", SqlDbType.Int, 4);
  35.  
  36.         connection.ExecuteNonQuery(cmd);
  37.  
  38.         result = (int)cmd.GetParameterValue("@Return");
  39.     }
  40.  
  41.     return result;
  42. }

 

Things to remember:

– The order of data table columns should match the order of the udtType,

– Ado.Net will not lookup udt columns by name

Advertisements

About vijayvepa

I'm a software consultant for Software Specialists Inc. currently working at Philips Respironics, Pittsburgh

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: