SqlBulkCopy Batch Size Property
Table 2:
In code behind add the following code
Batch Size defines the Number of rows in each batch. At the end of each batch, the rows in the batch are sent to the server.
Let's see with an example:
We are creating 2 tables
Table 1:
create table EmployeeBatchList
(
BatchID int,
BatchName varchar(100),
BatchDescription varchar(100)
)
Declare @Count int
set @Count=1
while @Count<5000
begin
insert into EmployeeBatchList(BatchID,BatchName,BatchDescription)
values(@Count,'BatchName - ' + cast(@Count as varchar(20)),'BatchDescription - '
+ cast(@Count as varchar(20)))
set @Count=@Count+1
end
create table ClientBatchList
(
ClientID int,
ClientName varchar(100),
ClientDescription varchar(100)
)
select * from EmployeeBatchList
Select * from ClientBatchList
In the aspx file put the following code
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SqlBulkCopyFromOneTableToAnother.aspx.cs" Inherits="ADONET.SqlBulkCopyFromOneTableToAnother" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="btnLoad" runat="server" OnClick="SaveData_OnClick" Text="Save Data" /><br /><br />
<asp:Label ID="lblMessage" runat="server" ForeColor="Green"></asp:Label>
</div>
</form>
</body>
</html>
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace ADONET
{
public partial class SqlBulkCopyFromOneTableToAnother : System.Web.UI.Page
{
string strMessage = string.Empty;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void SaveData_OnClick(object sender, EventArgs e)
{
String CS = ConfigurationManager.ConnectionStrings["EmplyoeeDB"].ConnectionString;
using (SqlConnection sqlcon = new SqlConnection(CS))
{
SqlCommand sqlCommand = new SqlCommand("select * from
EmployeeBatchList", sqlcon);
SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand);
DataSet ds = new DataSet();
adapter.Fill(ds, "EmployeeBatchList");
sqlcon.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlcon))
{
bulkCopy.BatchSize = 500;
bulkCopy.NotifyAfter = 200;
bulkCopy.SqlRowsCopied +=
bulkCopy_SqlRowsCopied;
bulkCopy.DestinationTableName = "ClientBatchList";
bulkCopy.ColumnMappings.Add("BatchID", "ClientID");
bulkCopy.ColumnMappings.Add("BatchName", "ClientName");
bulkCopy.ColumnMappings.Add("BatchDescription", "ClientDescription");
bulkCopy.WriteToServer(ds.Tables[0]);
}
}
}
void bulkCopy_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
strMessage += e.RowsCopied + " Rows loaded<br/>";
lblMessage.Text = strMessage;
}
}
}
Lets Execute our application we will get the following output
Click on Save Data
Select * from ClientBatchList
Posts
0 comments:
Post a Comment