Introduction
Here I will discuss how to pass/send a data-table as parameter to a stored procedure from c# application.In other words, we can say how to pass table valued parameter as parameter in the stored procedure in SQL server. When we are sending one line of query to the stored procedure that time, we can send our defined variable, during bulk sending or multiple row sending we need to pass data-table as a stored procedure.
Reason for passing/sending data-table to stored procedure or need of table valued parameter in SQL server
Normally when we are sending single row of record to the database at that time the record is going to the database and perform the operation, then it’s coming to our server side code, lets assume we have 100 rows of records available. If we will send those records to one by one using any loop, then it will increase server hit count which makes our application too much slower. For overcoming this issue we will send those records through a data-table.
Example
For solving this issue I will take an example of the employee table. Here I will insert more than one record to the employee table using stored procedure and I will pass data-table as a parameter.
Follow the below coding steps for completing the above task
Database:
Below code represents that how we can create an employee table in SQL database
CREATE TABLE [dbo].[Employee](
[Id] int NOT NULL PRIMARY KEY IDENTITY(1,1),
[Name] varchar(max) NULL,
[Address] nvarchar(max) NULL
)
GO Once we created the table, we have to create an user defined table type which will pass in our stored procedure.
CREATE TYPE [dbo].[EmployeeType] AS TABLE(
[Id] [int] NOT NULL,
[Name] [varchar](max) NULL,
[Address] [varchar](max) NULL
)
GOBelow image shows that how you can find the user defined table type
Here, I created a stored procedure and where I passed data-table as a parameter.
CREATE PROCEDURE [dbo].[BulkInsertEmployees]
@tblEmployees EmployeeType READONLY
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Employee(Id, Name, Address)
SELECT Id, Name, Country FROM @tblEmployees
ENDC# :
Inside the below method i will use the stored procedure and send data-table as a parameter. Here I will create a dummy data-table which contains 3 employees information. I will pass these employee data-table as a parameter.
protected void Bulk_Insert(object sender, EventArgs e)
{
DataTable dtEmployee = new DataTable();
dtEmployee.Clear();
dtEmployee.Columns.Add("Id");
dtEmployee.Columns.Add("Name");
dtEmployee.Columns.Add("Address");
object[] o1 = { 1,"Sagar", "Bhubaneswar" };
dtEmployee.Rows.Add(o1);
object[] o2 = { 2,"Bidya", "Bangalore" };
dtEmployee.Rows.Add(o2);
object[] o3 = {3 "Mishra", "Pune" };
dtEmployee.Rows.Add(o3);if (dt.Rows.Count > 0)
{
string consString = ConfigurationManager.ConnectionStrings[“constr”].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlCommand cmd = new SqlCommand(“BulkInsertEmployees”))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
cmd.Parameters.AddWithValue(“@tblEmployees”, dtEmployee);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
}
SUMMARY
In the above article I created one SQL table , user defined table type , stored procedure in SQL database. In C# code I passed/sent data-table as a parameter. In the stored procedure I passed a table valued parameter in the SQL server side. From the above article we concluded that, during insert more than one record from C# to SQL, data-table is a good choice rather than using loop.
Can we pass data dictionary instead of data table to a procedure?Because data dictionary is lighter and faster as compare to datatable.
Yes, you can pass data-dictionary to a stored procedure. You only have to change your user defined table type as per your dictionary