SqlBulkCopy to bulk insert multiple records from GridView to SQL database in Asp.Net C#,VB

Introduction: In this article I am going to explain how to save bulk data selected from gridview to sql server database table at once using SqlBulkCopy in Asp.Net using both C# and VB language.
SqlBulkCopy to insert multiple records from GridView to SQL database in Asp.Net C#,VB

Description: While working on asp.net project I got the requirement to insert multiple selected records from gridview into database. One approach was to loop through gridview records and save checked record one by one to database. But it will degrade performance when there are thousands of records involved. 

Here SqlBulkCopy comes for rescue. SqlBulkCopy enables us to efficiently bulk load a SQL Server table with data from another source. The SqlBulkCopy class can be used to write data only to SQL Server tables. However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance.

Implementation: Let’s create a demo page to insert data in bulk to sql table.

For demonstration purpose I have populated employee data from "tbEmployees" table in gridview. Now suppose we want to shortlist some employees and insert those shortlisted employees in another table say "tbShortlistedEmployees" at once . So I have loaded selected employee data into datatable and using SqBulkCopy inserted those records in bulk to destination sql table.
  • So first of all create "tbEmployees" table and insert dummy data into it using following script
GO
CREATE TABLE tbEmployees
(
                EmpId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
                EmpCode VARCHAR(10),
                EmpName VARCHAR(100)               
)

GO
INSERT INTO tbEmployees (EmpCode, EmpName)
VALUES
('EMP0001', 'Rahul'),
('EMP0002', 'Sonam'),
('EMP0003', 'Sahil'),
('EMP0004', 'Raunak'),
('EMP0005', 'Shikhir'),
('EMP0006', 'Anjan'),
('EMP0007', 'Rajesh'),
('EMP0008', 'Supreet'),
('EMP0009', 'Simran');

  • Now create another table tbShortlistedEmployees to store shortlisted employees

 CREATE TABLE tbShortlistedEmployees
(
                EmpId INT,
                EmpCode VARCHAR(10),
                EmpName VARCHAR(100)               
) 
  • Now create a stored procedure to fetch and bind employee records in GridView

CREATE PROCEDURE spGetEmployees
AS
BEGIN
  SELECT * FROM tbEmployees               
END 
  • Create another stored procedure to bind shortlisted employee records in another GridView


CREATE PROCEDURE spGetShortlistedEmployees
AS
BEGIN
  SELECT * FROM tbShortlistedEmployees        
END 
  • Database work is over. Now in web.config file create a connectionstring as:

  <connectionStrings>
    <add name="conStr" connectionString="Data Source=LALIT-PC;Initial Catalog=MyDataBase;Integrated Security=True"/>
  </connectionStrings>

HTML Source

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <fieldset style="width:500px;">
                <legend>Bulk insert multiple records to sql table</legend>
                <table>
                    <tr>
                        <td style="vertical-align: top">List of Employees
            <asp:GridView ID="grdEmployeeDetails" runat="server" AutoGenerateColumns="false" CellPadding="4" CellSpacing="4">
                <AlternatingRowStyle BackColor="White" />
                <HeaderStyle BackColor="#507C7D1" Font-Bold="True" ForeColor="White" HorizontalAlign="Left"></HeaderStyle>
                <RowStyle BackColor="#EFF3FB" />
                <Columns>
                    <asp:TemplateField>
                        <ItemTemplate>
                            <asp:CheckBox ID="cbSelect" runat="server" />
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:BoundField DataField="EmpId" HeaderText="EmpId" ItemStyle-Width="30" />
                    <asp:BoundField DataField="EmpName" HeaderText="Name" ItemStyle-Width="90" />
                    <asp:BoundField DataField="EmpCode" HeaderText="Code" ItemStyle-Width="60" />
                </Columns>
            </asp:GridView>
                        </td>
                        <td></td>
                        <td style="vertical-align: top">Shortlisted Employees
                        <asp:GridView ID="grdShortlistedEmployees" runat="server" AutoGenerateColumns="false" CellPadding="4" CellSpacing="4">
                            <AlternatingRowStyle BackColor="White" />
                            <HeaderStyle BackColor="#507C7D1" Font-Bold="True" ForeColor="White" HorizontalAlign="Left"></HeaderStyle>
                            <RowStyle BackColor="#EFF3FB" />
                            <Columns>
                                <asp:BoundField DataField="EmpId" HeaderText="EmpId" ItemStyle-Width="30" />
                                <asp:BoundField DataField="EmpName" HeaderText="Name" ItemStyle-Width="90" />
                                <asp:BoundField DataField="EmpCode" HeaderText="Code" ItemStyle-Width="60" />
                            </Columns>
                        </asp:GridView>
                        </td>
                    </tr>
                </table>
                <br />
                <asp:Button ID="btnBulkInsert" Text="Bulk Insert" OnClick="btnBulkInsert_Click" runat="server" />
                <asp:Label ID="lblMsg" runat="server"></asp:Label>
            </fieldset>
        </div>
    </form>
</body>
</html>

Asp.Net C# Code to bulk insert data into sql server table using SqlBulkCopy

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web.UI.WebControls;

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindEmployees();
        }
    }
    protected void BindEmployees()
    {
        string conString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(conString))
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand("spGetEmployees", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            adp.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                grdEmployeeDetails.DataSource = dt;
                grdEmployeeDetails.DataBind();
            }
            else
            {
                grdEmployeeDetails.DataSource = null;
                grdEmployeeDetails.DataBind();
            }
        }
    }

    protected void btnBulkInsert_Click(object sender, EventArgs e)
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("Id", typeof(Int32));
        dt.Columns.Add("Name", typeof(string));
        dt.Columns.Add("Code", typeof(string));
        try
        {

            foreach (GridViewRow row in grdEmployeeDetails.Rows)
            {
                if (((CheckBox)row.FindControl("cbSelect")).Checked)
                {
                    Int32 empid = Convert.ToInt32(row.Cells[1].Text);
                    string name = row.Cells[2].Text;
                    string code = row.Cells[3].Text;
                    dt.Rows.Add(empid, name, code);
                }
            }
            //Check if datatable has any row
            if (dt.Rows.Count > 0)
            {
                string conString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
                using (SqlConnection con = new SqlConnection(conString))
                {
                    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                    {
                        //Set the database table name in which records will be inserted in bulk
                        sqlBulkCopy.DestinationTableName = "dbo.tbShortlistedEmployees";

                        //Map the DataTable columns with that of the database table. Optional if database table column and datatable columns names are same
                        sqlBulkCopy.ColumnMappings.Add("Id", "EmpId");
                        sqlBulkCopy.ColumnMappings.Add("Name", "EmpName");
                        sqlBulkCopy.ColumnMappings.Add("Code", "EmpCode");
                        con.Open();
                        sqlBulkCopy.WriteToServer(dt);
                        con.Close();
                        lblMsg.Text = dt.Rows.Count + " records inserted successfully";
                        lblMsg.ForeColor = Color.Green;
                        BindShortlistedEmployees();
                    }
                }
            }
        }
        catch (Exception ex)
        {
            lblMsg.Text = "Error: " + ex.Message.ToString();
            lblMsg.ForeColor = Color.Red;
        }
    }

    protected void BindShortlistedEmployees()
    {
        string conString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(conString))
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand("spGetShortlistedEmployees", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            adp.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                grdShortlistedEmployees.DataSource = dt;
                grdShortlistedEmployees.DataBind();
            }
            else
            {
                grdShortlistedEmployees.DataSource = null;
                grdShortlistedEmployees.DataBind();
            }
        }
    }


Asp.Net VB Code to bulk insert data into sql server table using SqlBulkCopy

Imports System.Data.SqlClient
Imports System.Drawing
Imports System.Data
    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Not IsPostBack Then
            BindEmployees()
        End If
    End Sub


    Protected Sub BindEmployees()
        Dim conString As String = ConfigurationManager.ConnectionStrings("conStr").ConnectionString
        Using con As New SqlConnection(conString)
            Dim dt As New DataTable()
            Dim cmd As New SqlCommand("spGetEmployees", con)
            cmd.CommandType = CommandType.StoredProcedure
            Dim adp As New SqlDataAdapter(cmd)
            adp.Fill(dt)
            If dt.Rows.Count > 0 Then
                grdEmployeeDetails.DataSource = dt
                grdEmployeeDetails.DataBind()
            Else
                grdEmployeeDetails.DataSource = Nothing
                grdEmployeeDetails.DataBind()
            End If
        End Using
    End Sub

    Protected Sub btnBulkInsert_Click(sender As Object, e As EventArgs)
        Dim dt As New DataTable()
        dt.Columns.Add("Id", GetType(Int32))
        dt.Columns.Add("Name", GetType(String))
        dt.Columns.Add("Code", GetType(String))
        Try

            For Each row As GridViewRow In grdEmployeeDetails.Rows
                If DirectCast(row.FindControl("cbSelect"), CheckBox).Checked Then
                    Dim empid As Int32 = Convert.ToInt32(row.Cells(1).Text)
                    Dim name As String = row.Cells(2).Text
                    Dim code As String = row.Cells(3).Text
                    dt.Rows.Add(empid, name, code)
                End If
            Next
            'Check if datatable has any row
            If dt.Rows.Count > 0 Then
                Dim conString As String = ConfigurationManager.ConnectionStrings("conStr").ConnectionString
                Using con As New SqlConnection(conString)
                    Using sqlBulkCopy As New SqlBulkCopy(con)
                        'Set the database table name in which records will be inserted in bulk
                        sqlBulkCopy.DestinationTableName = "dbo.tbShortlistedEmployees"

                        'Map the DataTable columns with that of the database table. Optional if database table column and datatable columns names are same
                        sqlBulkCopy.ColumnMappings.Add("Id", "EmpId")
                        sqlBulkCopy.ColumnMappings.Add("Name", "EmpName")
                        sqlBulkCopy.ColumnMappings.Add("Code", "EmpCode")
                        con.Open()
                        sqlBulkCopy.WriteToServer(dt)
                        con.Close()
                        lblMsg.Text = dt.Rows.Count & " records inserted successfully"
                        lblMsg.ForeColor = Color.Green
                        BindShortlistedEmployees()
                    End Using
                End Using
            End If
        Catch ex As Exception
            lblMsg.Text = "Error: " & ex.Message.ToString()
            lblMsg.ForeColor = Color.Red
        End Try
    End Sub

    Protected Sub BindShortlistedEmployees()
        Dim conString As String = ConfigurationManager.ConnectionStrings("conStr").ConnectionString
        Using con As New SqlConnection(conString)
            Dim dt As New DataTable()
            Dim cmd As New SqlCommand("spGetShortlistedEmployees", con)
            cmd.CommandType = CommandType.StoredProcedure
            Dim adp As New SqlDataAdapter(cmd)
            adp.Fill(dt)
            If dt.Rows.Count > 0 Then
                grdShortlistedEmployees.DataSource = dt
                grdShortlistedEmployees.DataBind()
            Else
                grdShortlistedEmployees.DataSource = Nothing
                grdShortlistedEmployees.DataBind()
            End If
        End Using
    End Sub


Now over to you:
A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates.
Previous
Next Post »

3 comments

Click here for comments
December 05, 2015 ×

Very good article and nicely explained.
Can we update records using SqlBulkCopy.

Reply
avatar
December 12, 2015 ×

Thanks jagadeesh for your valuable comment.. Yes we can update using SqlBulkCopy..In mu upcoming article i will explain that..So stay connected and keep reading for more useful updates..:)

Reply
avatar
Ankur
admin
September 08, 2019 ×

How can we do this through MVC

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..