Bulk insert multiple records from GridView to SQL database using XML Data type in Asp.Net C#,VB

Introduction: In this article I am going to explain how to save bulk data selected from gridview to sql database table at once in Asp.Net using XML data type available in SQL

So basically you will learn the following through this article
  • How to insert thousands of records at once using a single database call i.e. single connection to database.
  • Using xml data type in sql
Bulk insert multiple records from GridView to SQL database using XML Data type in Asp.Net C#,VB
In previous articles i explained Using SqlBulkCopy to bulk insert multiple records from gridview to sql database in asp.net  and Load more records on demand in asp.net gridview on button click from sql server table and  Export gridview data to pdf file in asp.net and Maintain scroll position of gridview/repeater inside div/panel using jquery on postback in asp.net ajax updatepanel and Custom paging in repeater control in asp.net


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 because for saving each record it needs to connect to database. So there will be thousands of database calls to insert large number of records one by one. 

But luckily there is xml data type available in sql. So we can create xml of selected records from code behind file of asp.net and pass that xml to xml type parameter in stored procedure. Then we can insert all records from XML to sql table.

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 without loop through selected records. 
So I have created xml of selected employee using StringBuilder and passed that xml to stored procedure as a parameter.


Now 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 bind employee records in GridView
GO
CREATE PROCEDURE spGetEmployees
AS
BEGIN
  SELECT * FROM tbEmployees               
END 
  • Create another stored procedure to save shortlisted employees in table using XML
GO
CREATE PROCEDURE spSaveShortlistedEmployees
(
                @EmployeeXml XML
)
AS
BEGIN
   INSERT INTO tbShortlistedEmployees (EmpId,EmpCode,EmpName)           
   SELECT  p.value('@EmpId','INT'), p.value('@EmpName','VARCHAR(100)'),         
   p.value('@EmpCode','VARCHAR(20)')  FROM @EmployeeXml.nodes('/ROOT/Employee')n(p);
  
   RETURN @@ROWCOUNT;  
END
  • Create one more stored procedure to bind shortlisted employee records in another GridView
GO
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 XML data type

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

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))
        {
            using (SqlCommand cmd = new SqlCommand("spGetEmployees", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    if (dr.HasRows)
                    {
                        grdEmployeeDetails.DataSource = dr;
                        grdEmployeeDetails.DataBind();
                    }
                }
            }
        }
    }

    private string CreateEmployeeXML()
    {
        StringBuilder sb = new StringBuilder();
        //Loop through each row of gridview
        foreach (GridViewRow row in grdEmployeeDetails.Rows)
        {
            //Create XML of the rows selected.
            if (((CheckBox)row.FindControl("cbSelect")).Checked)
            {
                Int32 empId = Convert.ToInt32(row.Cells[1].Text);
                string empName = row.Cells[2].Text;
                string empCode = row.Cells[3].Text;
                sb.Append(String.Format("<Employee EmpId='{0}' EmpName='{1}' EmpCode='{2}'/>", empId, empName, empCode));
            }
        }
        return String.Format("<ROOT>{0}</ROOT>", sb.ToString());
    }

    protected void btnBulkInsert_Click(object sender, EventArgs e)
    {
        string conString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(conString))
        {
            using (SqlCommand cmd = new SqlCommand("spSaveShortlistedEmployees", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                //Get xml of employee details
                string EmployeeXML = CreateEmployeeXML();
                //Pass employee data in xml format to stored procedure
                cmd.Parameters.AddWithValue("@EmployeeXml", EmployeeXML);
                con.Open();
                //To get return value from stored procedure add a parameter
                cmd.Parameters.Add("@ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                cmd.ExecuteNonQuery();
                //Get return value i.e. number of records inserted from stored procedure using @@ROWCOUNT.
                int NoOfRecordsInserted = (int)cmd.Parameters["@ReturnValue"].Value;
                lblMsg.Text = NoOfRecordsInserted + " records inserted successfully";
                lblMsg.ForeColor = Color.Green;
                //Bind Inserted records in another gridview
                BindShortlistedEmployees();
            }
        }
    }

    //Bind the records that were inserted in bulk
    protected void BindShortlistedEmployees()
    {
        string conString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(conString))
        {
            using (SqlCommand cmd = new SqlCommand("spGetShortlistedEmployees", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    if (dr.HasRows)
                    {
                        grdShortlistedEmployees.DataSource = dr;
                        grdShortlistedEmployees.DataBind();
                    }
                }
            }
        }
    }

Asp.Net VB Code to bulk insert data into sql server table using XML Data type

Imports System.Data.SqlClient
Imports System.Drawing
Imports System.Data
Imports System.Text

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)
            Using cmd As New SqlCommand("spGetEmployees", con)
                cmd.CommandType = CommandType.StoredProcedure
                con.Open()
                Using dr As SqlDataReader = cmd.ExecuteReader()
                    If dr.HasRows Then
                        grdEmployeeDetails.DataSource = dr
                        grdEmployeeDetails.DataBind()
                    End If
                End Using
            End Using
        End Using
    End Sub

    Private Function CreateEmployeeXML() As String
        Dim sb As New StringBuilder()
        'Loop through each row of gridview
        For Each row As GridViewRow In grdEmployeeDetails.Rows
            'Create XML of the rows selected.
            If DirectCast(row.FindControl("cbSelect"), CheckBox).Checked Then
                Dim empId As Int32 = Convert.ToInt32(row.Cells(1).Text)
                Dim empName As String = row.Cells(2).Text
                Dim empCode As String = row.Cells(3).Text
                sb.Append([String].Format("<Employee EmpId='{0}' EmpName='{1}' EmpCode='{2}'/>", empId, empName, empCode))
            End If
        Next
        Return [String].Format("<ROOT>{0}</ROOT>", sb.ToString())
    End Function

    Protected Sub btnBulkInsert_Click(sender As Object, e As EventArgs)
        Dim conString As String = ConfigurationManager.ConnectionStrings("conStr").ConnectionString
        Using con As New SqlConnection(conString)
            Using cmd As New SqlCommand("spSaveShortlistedEmployees", con)
                cmd.CommandType = CommandType.StoredProcedure
                'Get xml of employee details
                Dim EmployeeXML As String = CreateEmployeeXML()
                'Pass employee data in xml format to stored procedure
                cmd.Parameters.AddWithValue("@EmployeeXml", EmployeeXML)
                con.Open()
                'To get return value from stored procedure add a parameter
                cmd.Parameters.Add("@ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue
                cmd.ExecuteNonQuery()
                'Get return value i.e. number of records inserted from stored procedure using @@ROWCOUNT.
                Dim NoOfRecordsInserted As Integer = CInt(cmd.Parameters("@ReturnValue").Value)
                lblMsg.Text = NoOfRecordsInserted & " records inserted successfully"
                lblMsg.ForeColor = Color.Green
                'Bind Inserted records in another gridview
                BindShortlistedEmployees()
            End Using
        End Using
    End Sub

    'Bind the records that were inserted in bulk
    Protected Sub BindShortlistedEmployees()
        Dim conString As String = ConfigurationManager.ConnectionStrings("conStr").ConnectionString
        Using con As New SqlConnection(conString)
            Using cmd As New SqlCommand("spGetShortlistedEmployees", con)
                cmd.CommandType = CommandType.StoredProcedure
                con.Open()
                Using dr As SqlDataReader = cmd.ExecuteReader()
                    If dr.HasRows Then
                        grdShortlistedEmployees.DataSource = dr
                        grdShortlistedEmployees.DataBind()
                    End If
                End Using
            End Using
        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 »

1 comments:

Click here for comments
Pravin
admin
December 07, 2015 ×

nice idea.Can we use JSON or AJAX to store multiple value?

Congrats bro Pravin you got PERTAMAX...! hehehehe...
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..