TODO:
Have you ever wanted to pass a DataTable from your C# application to a Stored Procedure in SQL Server?
SOLUTION:
Step 1: Create the User-Definied Table Type in SQL Server
CREATE TYPE [dbo].[MyUserDefinedType] AS TABLE(
[Id] [int] NULL,
[FieldOne] [int] NULL,
[FieldTwo] [int] NULL
)
Step 2: Create the DataTable and call the Stored Procedure
using (DataTable myDataTable = new DataTable())
{
myDataTable.Columns.Add(“FieldOne”, System.Type.GetType("System.Int32"));
myDataTable.Columns.Add(“FieldTwo, System.Type.GetType("System.Int32"));
myDataTable.Columns.Add(“FieldThree, System.Type.GetType("System.Int32"));
DataRow dataRow = null;
for (int i = 0; i < 100; i++)
{
dataRow = myDataTable.NewRow();
dataRow[“FieldOne] = 1;
dataRow[“FieldTwo”] = 2;
dataRow[“FieldThree] = 3;
myDataTable.Rows.Add(dataRow);
}
using (SqlConnection connection = new SqlConnection(“connectionstring”))
{
using (SqlCommand command = connection.CreateCommand())
{
connection.Open();
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 90;
command.CommandText = “MyStoredProcedureName;
command.Parameters.AddWithValue(“@IncomingDataTable”, myDataTable);
command.ExecuteNonQuery();
}
}
}
Step 3: Create the Table
CREATE TABLE MyTable(FieldOne int NULL, FieldTwo NULL, FieldThree NULL)
Step 4: Create the Stored Procedure
CREATE PROCEDURE [dbo].[MyStoredProcedure]
@MyUserDefinedType MyUserDefinedType READONLY
AS
BEGIN
INSERT INTO dbo.MyTable
([FieldOne], [FieldTwo], [FieldThree])
SELECT [FieldOne], [FieldTwo], [FieldThree] FROM @MyUserDefinedType
END
NOTES:
Be sure that the DataTable columns are added in the SAME order as they are created in the SQL Server User Type.