Description:-
In
this example we explain that how to use sqlBulkCopy to insert bulk data to
database in asp.net. sqlBulkcopy is used to copies all the rows or data in the supplied
datatable to a destination table specified by the DestinationTableName property
of the sqlBulkCopy object.
As
the name suggest sqlbulkcopy does bulk insert from one place to another place
like from datatable to sql server database.
Transferring
data from one place to another is a common practice in a terms of software
developments and also there are many ways to transfer data from one place to
another but which one is the best suits for you.
You
can simply insert record one by one but it is the most time consuming process
when there is a large number of (bunch of data) data.so best way is to use
sqlBulkCopy to insert data to database. Here below is the diagram that display
the time of normal insert vs sqlbulcopy.
10
Records
|
1,000
Records
|
10,000
Records
|
|
SqlBulkCopy
|
4 ms
|
16 ms
|
180 ms
|
One INSERT statement
Per Import Record |
5 ms
|
333 ms
|
2,978 ms
|
sqlbulk.aspx:-
<%@
Page Language="C#" AutoEventWireup="true" CodeBehind="sqlBulk.aspx.cs" Inherits="WebApplication1.sqlBulk" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>SqlBulkCopy: Bulk Copy data from DataTable
(DataSet) to SQL Server Table using C# and VB.Net</title>
</head>
<body>
<form
id="form1"
runat="server">
<asp:GridView ID="GridView1"
runat="server"
AutoGenerateColumns="false">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Topic" HeaderText="Topic" ItemStyle-Width="30" />
<asp:BoundField DataField="FirstName" HeaderText="First Name" ItemStyle-Width="150" />
<asp:BoundField DataField="LastName" HeaderText="Last Name" ItemStyle-Width="150" />
<asp:BoundField DataField="Email" HeaderText="Email" ItemStyle-Width="150" />
</Columns>
</asp:GridView>
<br
/>
<asp:Button ID="Button1"
Text="Bulk
Insert" OnClick="Bulk_Insert" runat="server" />
</form>
</body>
</html>
sqlbulk.aspx.cs:-
using System;
using
System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using
System.Web.UI.WebControls;
using System.Data;
using
System.Data.SqlClient;
namespace WebApplication1
{
public partial
class sqlBulk
: System.Web.UI.Page
{
protected
void Page_Load(object
sender, EventArgs e)
{
if
(!this.IsPostBack)
{
DataSet
ds = new DataSet();
ds.ReadXml(Server.MapPath("Lead.xml"));
GridView1.DataSource =
ds.Tables[0];
GridView1.DataBind();
}
}
protected
void Bulk_Insert(object
sender, EventArgs e)
{
DataTable
dt = new DataTable();
dt.Columns.AddRange(new DataColumn[4]
{ new DataColumn("Topic", typeof(string)),
new DataColumn("FisrtName", typeof(string)),
new DataColumn("LastName",typeof(string)),
new
DataColumn("Email",
typeof(string))});
foreach
(GridViewRow row in
GridView1.Rows)
{
if
((row.FindControl("CheckBox1") as CheckBox).Checked)
{
string
Topic = row.Cells[1].Text;
string
FirstName = row.Cells[2].Text;
string
LastName = row.Cells[3].Text;
string Email =
row.Cells[3].Text;
dt.Rows.Add(Topic,
FirstName, LastName, Email);
}
}
if
(dt.Rows.Count > 0)
{
string
consString = @"Data Source=RIGEL\SQL2008R2;Initial
Catalog=AX2012R2_Contoso_SPICA;Integrated Security=True";
using
(SqlConnection con = new SqlConnection(consString))
{
using
(SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "dbo.Lead";
//[OPTIONAL]: Map the DataTable columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("Topic", "Topic");
sqlBulkCopy.ColumnMappings.Add("FisrtName",
"FisrtName");
sqlBulkCopy.ColumnMappings.Add("LastName",
"LastName");
sqlBulkCopy.ColumnMappings.Add("Email", "Email");
con.Open();
sqlBulkCopy.WriteToServer(dt);
con.Close();
}
}
}
}
}
}
Lead.xml:-
<?xml version="1.0" encoding="utf-8" ?>
<Leads>
<Lead>
<Topic>Lead 1</Topic>
<FirstName>pintu patel</FirstName>
<Lastname>United States</Lastname>
<Email> pintu @gmail.com</Email>
</Lead>
<Lead>
<Topic>Lead 2</Topic>
<FirstName>kirit patel</FirstName>
<Lastname>United States</Lastname>
<Email> kirit@gmail.com </Email>
</Lead>
<Lead>
<Topic>Lead 3</Topic>
<FirstName>rahul gajera</FirstName>
<Lastname>United States</Lastname>
<Email> rahul@gmail.com </Email>
</Lead>
<Lead>
<Topic>Lead 4</Topic>
<FirstName>ketan patel</FirstName>
<Lastname>United States</Lastname>
<Email> ketan@gmail.com </Email>
</Lead>
<Lead>
0 comments:
Post a Comment