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();

I am a Software Engineer and passionate programmer. Love working in SQL Server. Like participating in online technical communities like asp.net, MSDN, technical discussions and helping my friends, colleagues and others in their technical problems

Previous
Next Post »