Important Properties and Event of SqlBulkCopy Class

Some of the important properties and events of the SqlBulkCopy class are:

1. BatchSize property
2. NotifyAfter property
3. SqlRowsCopied event

BatchSize Property in SqlBulkCopy

The 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 that once the reader has read 50 rows, they will be sent to the database as a single batch to perform the bulk copy operation.

The NotifyAfter property determines how many rows will be processed before raising SqlRowsCopied event. In the example below, the NotifyAfter property is set to 100. This means that once every 100 rows are copied to the destination table, the SqlRowsCopied event is raised.

The SqlRowsCopied event is used for reporting the progress of the data transfer. The event is raised every time the number of rows specified by the NotifyAfter property is processed.

The following console application demonstrates how to bulk load data in batches of 50 rows, and notifies after copying 100 rows to the destination table:

• Step 1: Execute the following SQL script to create Products 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 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 the App.config file.

<connectionStrings>
<add name="CS" connectionString="server=.;database=TestDB;integrated security=true"/>
</connectionStrings>

• Step 4: Open program.cs file. 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 the SqlRowsCopied event and then passing the name of the event handler method to the 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....");
  }