some of the important properties and Events of SqlBulkCopy class are
1. BatchSize property
2. NotifyAfter property and
3. SqlRowsCopied event
BatchSize property - Specifies the number of rows in a batch that will be copied to the destination table. The BatchSize property becomes important to specify as the performance of data transfer depends on it, you should be very cautious while defining the batch size, The default batch size is 1. In the example below, BatchSize is set to 50. This means once the reader has read 50 rows they will be sent to the database as a single batch to perform the bulk copy operation.
NotifyAfter property - Determine how many rows will be processed before raising SqlRowsCopied event. In the example below, NotifyAfter property is set to 100. This means once every 100 rows are copied to the destination table SqlRowsCopied event is raised.
SqlRowsCopied event - This event is used for reporting the progress of the data transfer, the event is raised every time the number of rows specified by NotifyAfter property are processed.
The following console application demonstrates how to bulk load data in batches of 50 rows, And Notifies after copying 100 rows to destination table.
Step 1 : Execute the following SQL script to create Product's Source table and populate it with test data.
Create Table tblProductSource
(
[Id] int primary key,
[Name] nvarchar(50),
[Description] nvarchar(250)
)
GO
Declare @Id int
Set @Id = 1
While(@Id <= 300)
Begin
Insert into Products_Source values
(@Id, 'Product - ' + CAST(@Id as nvarchar(20)),
'Product - ' + CAST(@Id as nvarchar(20)) + ' Description')
Print @Id
Set @Id = @Id + 1
End
GO
Step 2 : Create Product's destination table
Create Table tblProductDestination
(
[Id] int primary key,
[Name] nvarchar(50),
[Description] nvarchar(250)
)
GO
Step 3 : Create a new console application. Name it DemoForNotifyAfter. Include the database connection string in App.config file.
<connectionStrings>
<add name="CS" connectionString="server=.;database=TestDB;integrated security=true"/>
Step 4 : Open program.cs file then copy and paste the following code .
using System;
using System.Configuration;
using System.Data.SqlClient;
namespace DemoForNotifyAfter
{
class Program
{
static void Main()
{
string cs = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;
using (SqlConnection sourceCon = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("Select * from tblProductSource", sourceCon);
sourceCon.Open();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
using (SqlConnection destinationCon = new SqlConnection(cs))
{
using (SqlBulkCopy bc = new SqlBulkCopy(destinationCon))
{
bc.BatchSize = 50;
bc.NotifyAfter = 5000;
bc.SqlRowsCopied += new SqlRowsCopiedEventHandler(bc_SqlRowsCopied);
bc.DestinationTableName = "tblProductDestination";
destinationCon.Open();
bc.WriteToServer(rdr);
}
}
}
}
}
static void bc_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
Console.WriteLine(e.RowsCopied + " loaded....");
}
}
}
Note : we can also define the inline event handler method Instead of creating a separate event handler method for SqlRowsCopied event and then passing the name of the event handler method to SqlRowsCopiedEventHandler delegate with the help of an anonymous function, that will reduce the amount of code we need to write as shown below.
bc.SqlRowsCopied += (sender, eventArgs) =>
{
Console.WriteLine(eventArgs.RowsCopied + " loaded....");
};
This means the following method is no longer required
static void bc_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
Console.WriteLine(e.RowsCopied + " loaded....");
}