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
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.
Now first of all create tbEmployees table and insert dummy data into it using following script
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.
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.
1 comments:
Click here for commentsnice idea.Can we use JSON or AJAX to store multiple value?
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..