C#, DataTable, SQL, sql server table valued parameter performance, table valued parameter in sql server

How to pass data-table as parameter to a stored procedure in MSSQL from C# Application

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
)
GO
Below image shows that how you can find the user defined table type
table valued parameter in sql server
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
END
C# :
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.

About Bidyasagar Mishra

2 thoughts on “How to pass data-table as parameter to a stored procedure in MSSQL from C# Application

Leave a Reply

Your email address will not be published. Required fields are marked *