Articles → ADO.NET → Pass Datatable In Stored Procedure C#

Pass Datatable In Stored Procedure C#






Create Table, Type And Procedure




  1. Table – A table to insert data.
  2. Picture showing the design view of the sample table in SQL server
    Click to Enlarge

  3. Type – In simple words counterpart of data table in sql server.
  4. CREATE TYPE DataTable AS TABLE(
      EmployeeName Varchar(50), 
      Employee_Address Varchar(100)
    )


  5. Procedure – To insert data in the table.
  6. 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




Picture showing the data inserted in the sample table
Click to Enlarge


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

Query/Feedback


Your Email Id  
 
Subject 
 
Query/FeedbackCharacters remaining 250