Articles → ADO.NET → Pass Datatable In Stored Procedure C#
Pass Datatable In Stored Procedure C#
Create Table, Type And Procedure
- Table – A table to insert data.
Click to Enlarge
- Type – In simple words counterpart of data table in sql server.
CREATE TYPE DataTable AS TABLE(
EmployeeName Varchar(50),
Employee_Address Varchar(100)
)
- Procedure – To insert data in the table.
Create Procedure InsertEmployee (@EmployeeList DataTable READONLY) As Insert Into Employee
Select
EmployeeName,
Employee_Address
From
@EmployeeList
Calling Stored Procedure From C# Code
DataTable dataTable = new DataTable("Employee");
dataTable.Columns.Add("EmployeeName", typeof(string));
dataTable.Columns.Add("EmployeeAddress", typeof(string));
DataRow row = dataTable.NewRow();
row["EmployeeName"] = "gyansangrah";
row["EmployeeAddress"] = "India";
dataTable.Rows.Add(row);
using(SqlConnection connection = new SqlConnection(@"conn_string")) {
connection.Open();
using(SqlCommand command = new SqlCommand("InsertEmployee", connection)) {
SqlParameter param = new SqlParameter("@EmployeeList", SqlDbType.Structured);
param.Value = dataTable;
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(param);
command.ExecuteNonQuery();
}
}
Output
Click to Enlarge