Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Part 20 - sqlbulkcopy notifyafter example

Suggested Videos
Part 17 - Strongly typed datasets
Part 18 - Load xml data into sql server table using sqlbulkcopy
Part 19 - Copying data from one table to another table using SqlBulkCopy



In this video we will discuss the use 
1. BatchSize property
2. NotifyAfter property
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 10000. This means once the reader has read 10000 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 5000. This means once every 5000 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.

Let us now understand these properties with an example.

Step 1 : Execute the following SQL script to create Products_Source table and populate it with test data.
Create Table Products_Source
(
 [Id] int primary key,
 [Name] nvarchar(50),
 [Description] nvarchar(250)
)
GO

Declare @Id int
Set @Id = 1

While(@Id <= 300000)
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 Products_Destination table
Create Table Products_Destination
(
 [Id] int primary key,
 [Name] nvarchar(50),
 [Description] nvarchar(250)
)
GO

Step 3 : Create a new console application. Name it Demo. Include the database connection string in App.config file
<connectionStrings>
  <add name="CS"
        connectionString="server=.;database=Sample;integrated security=SSPI"/>
</connectionStrings>

Step 4 : Copy and paste the following code in Program.cs file.
using System;
using System.Configuration;
using System.Data.SqlClient;

namespace Demo
{
    class Program
    {
        static void Main()
        {
            string cs = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;
            using (SqlConnection sourceCon = new SqlConnection(cs))
            {
                SqlCommand cmd = new 
                                       SqlCommand("Select * from Products_Source", sourceCon);
                sourceCon.Open();
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    using (SqlConnection destinationCon = new SqlConnection(cs))
                    {
                        using (SqlBulkCopy bc = new SqlBulkCopy(destinationCon))
                        {
                            bc.BatchSize = 10000;
                            bc.NotifyAfter = 5000;
                            bc.SqlRowsCopied += 
                                     new SqlRowsCopiedEventHandler(bc_SqlRowsCopied);
                            bc.DestinationTableName = "Products_Destination";
                            destinationCon.Open();
                            bc.WriteToServer(rdr);
                        }
                    }
                }
            }
        }

        static void bc_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
        {
            Console.WriteLine(e.RowsCopied + " loaded....");
        }
    }
}

Please 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....");
}

No comments:

Post a Comment

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.