Articles → ADO.NET → Pass datatable in stored procedure C#

Pass datatable in stored procedure C#






Create table, type and procedure







Click to Enlarge



CREATE TYPE DataTable AS TABLE(
       EmployeeName Varchar(50),
       Employee_Address Varchar(100)
)




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


Posted By  -  Karan Gupta
 
Posted On  -  Sunday, December 20, 2015

Query/Feedback


Your Email Id  
 
Subject 
 
Query/FeedbackCharacters remaining 250