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.
In previous articles i explained Using XML Data type to Bulk insert multiple records from GridView to SQL database in Asp.Net and Bind, save, edit, update, cancel,delete and paging example in gridview in asp.net and Jquery and json to bind gridviewfrom sql server database and Create jquery scrollable gridview with fix header and Export gridview data to CSV file and Delete multiple records from gridview with checkbox selection
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.
3 comments
Click here for commentsVery good article and nicely explained.
ReplyCan we update records using SqlBulkCopy.
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..:)
ReplyHow can we do this through MVC
ReplyIf 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..