Passing Datatable to Stored Procedure in SQL Server

In this article, we will learn how to pass DataTable from C# to a Stored Procedure in SQL Server.

Basic steps

• Create a Table Type with the structure as same of the DataTable in SQL Server
• Make this Table Type as Input Parameter of Stored Procedure
• Finally, pass the DataTable to the Table Type Parameter of the Stored Procedure

Note: This is supported on SQL Server 2008 onward.

Now let's take a step by step example

Step 1: Create a Table Employee

CREATE TABLE Employee(
    EmpID        VARCHAR(10)
    , EmpName    VARCHAR(50)
    , Gender     CHAR(1)
    , DOJ        DATETIME
)

Step 2: Create a Table Type

CREATE TYPE EmpTableType AS TABLE (
    EmpID        VARCHAR(10)
    , EmpName    VARCHAR(50)
    , Gender     CHAR(1)
    , DOJ        DATETIME
)

Step 3: Create a Stored Procedure that would take Table Type as parameter that we created in previous step.

CREATE PROCEDURE usp_GetEmpDetils(
    @EmpDet EmpTableType READONLY
) AS
BEGIN   
    INSERT INTO Employee
    SELECT * FROM @EmpDet
    SELECT * FROM Employee

END

Step 4: ADO.Net Code passing DataTable to the TableType Parameter in Stored Procedure

DataTable EmpTable = new DataTable();
EmpTable.Columns.Add("EmpID");
EmpTable.Columns.Add("EmpName");
EmpTable.Columns.Add("Gender");
EmpTable.Columns.Add("DOJ");
DataRow EmpRow = EmpTable.NewRow();
EmpRow["EmpID"] = "EMP0001";
EmpRow["EmpName"] = "Sandeep Mittal";
EmpRow["Gender"] = "M";
EmpRow["DOJ"] = "01/01/2010";       
EmpTable.Rows.Add(EmpRow);
EmpTable.AcceptChanges();

SqlConnection connection = new SqlConnection("data source=ServerName;database=DBName;uid=UserID;pwd=Password");
SqlCommand selectCommand = new SqlCommand("usp_GetEmpDetils", connection);
selectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam = selectCommand.Parameters.AddWithValue("@EmpDet", EmpTable);
tvpParam.SqlDbType = SqlDbType.Structured;
connection.Open();
grid.DataSource = selectCommand.ExecuteReader();
grid.DataBind();
connection.Close();