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();
}
}
}
0 comments:
Post a Comment
Note: only a member of this blog may post a comment.