Best way for bulk data insert to SQL server using C#

Introduction

In this article, I will discuss bulk data insert to SQL server. I will pass a data table as a parameter to a stored procedure from the c# application and get that parameter value in the procedure using the user-defined table type. In other words, we can say how to pass table-valued parameters as parameters in the stored procedure in the SQL server. 

Reason for inserting bulk data to SQL table

Normally when we are inserting 5-10 rows of records into the database table from the C# application, that time we are sending the records through a for or for-each loop. Then the records inserted into the database table one by one. In this process, one row of records assigns as a parameter to the procedure from the for or for-each loop. Then it goes to the database inserts the record and comes back to the back end code for taking the next row from the for or for-each loop until the loop is complete. This process does not take so much time, it’s complete within few seconds. let’s assume we have millions of rows of records available. If we will follow this traditional process for inserting records to the table from the C# application then it will take minutes or hours of time. For inserting one row of records each time it will go to the server and come back to the back end code, which will increase the execution time. For overcoming the more execution time we will use bulk data insert to SQL server.

Bulk data insert to SQL server

In the below example, I will describe how we can implement the bulk data insert to SQL server.
Example
Here, I will take an example of the employee table. I will insert more than one record to the employee table using the stored procedure. I will pass the data table as a parameter to the stored procedure. You can use this below example for inserting millions of records to SQL server from your C# application by which you can insert millions of records to your application within few seconds.
Follow the below coding steps for completing the above task
Database code:
The below code represents that how we can create an employee table in the 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 you created the table, you have to create a user-defined table type. It will pass in our stored procedure, for consuming the data table records from the C# application.
CREATE TYPE [dbo].[EmployeeType] AS TABLE(
      [Id] [int] NOT NULL,
      [Name] [varchar](max) NULL,
      [Address] [varchar](max) NULL
)
GO
The below image shows that where you can find the user-defined table type which is created by you.

Bulk data insert to SQL server

Here I created one stored procedure, I passed the created user-defined table type 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# code:
In the below method, I will create some dummy records as employee records. I will insert those records into the employee table. Here I will add those records into a data table. I will pass that data table as a parameter through the stored procedure. 
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 (dtEmployee.Rows.Count > 0)
        {
            //here you will add your database connection string
            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;
                    //passed data table as a parameter 
                    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 the C# code, I passed the data table as a parameter. In the stored procedure I passed a user-defined table type as a parameter on the SQL stored procedure. 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 for/for-each loop. I hope this article will help you for inserting millions of records into the SQL table from your C# application. If you find helpful information from this article, please share it with your colleagues and the development community. Happy to help you 😊.

2 thoughts on “Best way for bulk data insert to SQL server using C#”

Leave a Comment

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