Posted on 12/27/2020 8:23:08 AM by Admin

Important Properties and Event of SqlBulkCopy Class

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 is very important as the performance of data transfer depends on it. 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 - Defines the number of rows to 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 raised every time the number of rows specified by NotifyAfter property are processed. This event is useful for reporting the progress of the data transfer.

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 : Copy and paste the following code in Program.cs file.


  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 : Instead of creating a separate event handler method for SqlRowsCopied event and then passing the name of the event handler method to SqlRowsCopiedEventHandler delegate, we can reduce the amount of code we have to write with the help of an anonymous function 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....");
   }