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