passing datatable to stored procedure

In this post, I have explained how DataTable from C# is passed to a stored procedure in Sql Server. 

1. Create a TableType with the structure as same of the DataTable in Sql Server 
2. Make this TableType as input parameter of stored procedure. 
3. Finally, pass the DataTable to the TableType parameter of the stored procedure

Note : This is supported on Sql Server 2008 onwards.

Step 1: First 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 Stored Procedure that will take Table Type that we created in Step 2 as parameter 

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