NIck NIck - 4 months ago 30
SQL Question

Pass table valued parameter using ADO.Net

How to pass table valued parameter to stored procedure using ADO.Net?

Answer

1 Create type in sql server:

    CREATE TYPE [dbo].[MyDataType] As Table
(
    ID INT,
    Name NVARCHAR(50)
)

2 Create Procedure

CREATE PROCEDURE [dbo].[MyProcedure]
(
@myData As [dbo].[MyDataType] Readonly
)
AS

Begin

    Select * FROM @myData
End

3 Create datatable in C#

DataTable myDataTable = new DataTable("MyDataType");
            myDataTable.Columns.Add("Name", typeof(string));
            myDataTable.Columns.Add("Id", typeof(Int32));
            myDataTable.Rows.Add("XYZ", 1);
            myDataTable.Rows.Add("ABC", 2);

4 Create sql parameter

SqlParameter parameter = new SqlParameter();
            parameter.ParameterName = "@myData";
            parameter.SqlDbType = System.Data.SqlDbType.Structured;
            parameter.Value = myDataTable;
            command.Parameters.Add(parameter); 
Comments