Part 20 sqlbulkcopy notifyafter example
In this video we will discuss the use
1. BatchSize property
2. NotifyAfter property
3. SqlRowsCopied event
Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
/ @aarvikitchen5572
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....");
}
Text version of the video
http://csharp-video-tutorials.blogspo...
Slides
http://csharp-video-tutorials.blogspo...
All ADO .NET Text Articles
http://csharp-video-tutorials.blogspo...
All ADO .NET Slides
http://csharp-video-tutorials.blogspo...
All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenka...
All Dot Net and SQL Server Tutorials in Arabic
/ kudvenkatarabic
On this page of the site you can watch the video online Part 20 sqlbulkcopy notifyafter example with a duration of hours minute second in good quality, which was uploaded by the user kudvenkat 27 September 2014, share the link with friends and acquaintances, this video has already been watched 45,977 times on youtube and it was liked by 187 viewers. Enjoy your viewing!
