Posted on 12/27/2020 7:52:59 AM by Admin

SqlBulkCopy class in Ado.Net

SqlBulkCopy class ,Lets you efficiently bulk load a SQL Server table with data from another source. There are other ways to load data into a SQL Server table (INSERT statements, for example), but SqlBulkCopy offers a significant performance advantage over them.

The SqlBulkCopy class can be used to write data only to SQL Server tables. However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance.

Constructors

Constructors Description
SqlBulkCopy(SqlConnection) Initializes a new instance of the SqlBulkCopy class using the specified open instance of SqlConnection.
SqlBulkCopy(SqlConnection, SqlBulkCopyOptions, SqlTransaction) Initializes a new instance of the SqlBulkCopy class using the supplied existing open instance of SqlConnection. The SqlBulkCopy instance behaves according to options supplied in the copyOptions parameter.
If a non-null SqlTransaction is supplied, the copy operations will be performed within that transaction.
SqlBulkCopy(String) Initializes and opens a new instance of SqlConnection based on the supplied connectionString.
The constructor uses the SqlConnection to initialize a new instance of the SqlBulkCopy class.
SqlBulkCopy(String, SqlBulkCopyOptions) Initializes and opens a new instance of SqlConnection based on the supplied connectionString.
The constructor uses that SqlConnection to initialize a new instance of the SqlBulkCopy class.
The SqlConnection instance behaves according to options supplied in the copyOptions parameter. .

Properties

Properties Description
BatchSize Number of rows in each batch. At the end of each batch, the rows in the batch are sent to the server.
BulkCopyTimeout Number of seconds for the operation to complete before it times out.
ColumnMappings Returns a collection of SqlBulkCopyColumnMapping items.
Column mappings define the relationships between columns in the data source and columns in the destination.
DestinationTableName Name of the destination table on the server. .
EnableStreaming Enables or disables a SqlBulkCopy object to stream data from an IDataReader object.
NotifyAfter Defines the number of rows to be processed before generating a notification event.

Methods

Some of the important methods of SqlBulkCopy Class are

Method Description
Close() Closes the SqlBulkCopy instance.
WriteToServer(DataTable) Copies all rows in the supplied DataTable to a destination table
specified by the DestinationTableName property of the SqlBulkCopy object.
WriteToServer(IDataReader) Copies all rows in the supplied IDataReader to a destination table specified by the DestinationTableName property of the SqlBulkCopy object.
WriteToServerAsync(DataTable) The asynchronous version of WriteToServer(DataTable),
which copies all rows in the supplied DataTable to a destination table
specified by the DestinationTableName property of the SqlBulkCopy object. .
WriteToServerAsync(IDataReader) The asynchronous version of WriteToServer(IDataReader),
which copies all rows in the supplied IDataReader to a destination table
specified by the DestinationTableName property of the SqlBulkCopy object.

Events

Event Description
SqlRowsCopied Occurs every time that the number of rows specified by the NotifyAfter property have been processed.

Example : copying data from one table to another in sql server

The source and destination tables may be in the same database or in different databases and these database can be on the same sql server or in different servers

Step 1 : Create a new database. Name it SourceDB. Execute the following SQL script to create Departments and Employees tables, and to populate with data.


   Create Database SourceDB
   Go
   Use SourceDB
   Go
  CREATE TABLE [dbo].[Department] (
    [Id]   INT          IDENTITY (1, 1) NOT NULL,
    [Name] VARCHAR (50) NULL,
    CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED ([Id] ASC)
  );
  CREATE TABLE [dbo].[Employee] (
    [Id]           INT          IDENTITY (1, 1) NOT NULL,
    [Name]         VARCHAR (50) NULL,
    [Salary]       INT          NULL,
    [DepartmentId] INT          NULL,
    CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Employee_Department] FOREIGN KEY ([DepartmentId]) REFERENCES [dbo].[Department] ([Id])
  );
  SET IDENTITY_INSERT [dbo].[Department] ON
  INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (1, N'HR')
  INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (2, N'Development')
  INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (3, N'Database')
  INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (4, N'Business')
  SET IDENTITY_INSERT [dbo].[Department] OFF
  SET IDENTITY_INSERT [dbo].[Employee] ON
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (1, N'sachin', 100000, 1)
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (2, N'arjun', 23000, 1)
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (3, N'vikash', 30000, 2)
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (4, N'pocha', 43000, 3)
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (5, N'Nivedita', 24000, 4)
  SET IDENTITY_INSERT [dbo].[Employee] OFF

Step 2 : Create another new database. Name it DestinationDB. Execute the SQL script to create Departments and Employees tables. Here we have just created the structure of the tables and no data. We will be moving data from SourceDB tables to DestinationDB tables.


   Create Database DestinationDB
   Go
   Use DestinationDB
   Go
   CREATE TABLE [dbo].[Department] (
    [Id]   INT          IDENTITY (1, 1) NOT NULL,
    [Name] VARCHAR (50) NULL,
    CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED ([Id] ASC)
    );
   CREATE TABLE [dbo].[Employee] (
    [Id]           INT          IDENTITY (1, 1) NOT NULL,
    [Name]         VARCHAR (50) NULL,
    [Salary]       INT          NULL,
    [DepartmentId] INT          NULL,
    CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Employee_Department] FOREIGN KEY ([DepartmentId]) REFERENCES [dbo].[Department] ([Id])
   );

Step 3 : Create a new empty asp.net web application project. Name it DemoForSqlBulkCopy.

 Create a web application
Create a web application
 Create Empty webform project
Create Empty webform project

Step 4 :Add a webform to the project.

Add webform
Add webform

Step 5 : Include the following 2 connection strings for the Source and Destination databases in the web.config file of the project


   <connectionStrings>
   <add name="SourceCS"connectionString="server=.;database=SourceDB;integrated security=true"/>
   <add name="DestinationCS"connectionString="server=.;database=DestinationDB;integrated security=true"/>
   </connectionStrings>

.

Step 4 :Drag and Drop a Button control into the designer of web forms , and double click on it to generate Button Click event handler, then Copy and paste the following code in the button click event handler method in the code-behind file.


    string sourceCS = 
    ConfigurationManager.ConnectionStrings["SourceCS"].ConnectionString;
   string destinationCS = ConfigurationManager.ConnectionStrings["DestinationCS"].ConnectionString;
   using (SqlConnection sourceCon = new SqlConnection(sourceCS))
   {
    SqlCommand cmd = new SqlCommand("Select * from Departments", sourceCon);
    sourceCon.Open();
    using (SqlDataReader rdr = cmd.ExecuteReader())
    {
        using (SqlConnection destinationCon = new SqlConnection(destinationCS))
        {
            using (SqlBulkCopy bc = new SqlBulkCopy(destinationCon))
            {
                bc.DestinationTableName = "Departments";
                destinationCon.Open();
                bc.WriteToServer(rdr);
            }
        }
    }
    cmd = new SqlCommand("Select * from Employees", sourceCon);
    using (SqlDataReader rdr = cmd.ExecuteReader())
    {
        using (SqlConnection destinationCon = new SqlConnection(destinationCS))
        {
            using (SqlBulkCopy bc = new SqlBulkCopy(destinationCon))
            {
                bc.DestinationTableName = "Employees";
                destinationCon.Open();
                bc.WriteToServer(rdr);
            }
        }
     }
   }

As the column names in the source and destination tables are the same column mappings were not required, But it is not necessary that column names in both the table would always be the same, in that case, we use column mappings property of SqlBulkCopy class.

SqlBulkCopy.ColumnMappings Property

Column mappings define the relationships between columns in the data source and columns in the destination. If the data source and the destination table have the same number of columns, and the ordinal position of each source column within the data source matches the ordinal position of the corresponding destination column, the ColumnMappings collection is unnecessary.

However, if the column counts differ, or the ordinal positions are not consistent, you must use ColumnMappings to make sure that data is copied into the correct columns.

Let's change the DepartmentId column of Employee table of DestinationDb to Dept_Id and Name column to EmployeeName, to understand the column mappings better.


    Use DestinationDB
    Go
   sp_rename 'Employee.Name, 'EmployeeName', 'COLUMN';
   sp_rename 'Employee.DepartmentId', 'Dep_Id', 'COLUMN';

Note: I have used System Store procedure sp_rename which is used to rename a column in SQL server.

To define column mappings we use Add() method of ColumnMappings , which takes source column name and destination column name as parameters.


    bc.ColumnMappings.Add("Source Column","Destination Column");


   protected void Button1_Click(object sender, EventArgs e)
    {
      string sourceCS = ConfigurationManager.ConnectionStrings["SourceCS"].ConnectionString;
      string destinationCS =  ConfigurationManager.ConnectionStrings["DestinationCS"].ConnectionString;
      using (SqlConnection sourceCon = new SqlConnection(sourceCS))
       {
       SqlCommand cmd = new SqlCommand("Select * from Employee", sourceCon);
        sourceCon.Open();
         using (SqlDataReader rdr = cmd.ExecuteReader())
         {
          using (SqlConnection destinationCon = new SqlConnection(destinationCS))
           {
              using (SqlBulkCopy bc = new SqlBulkCopy(destinationCon))
                {
                 bc.DestinationTableName = "Employee";
                 bc.ColumnMappings.Add("Id","Id");
                 bc.ColumnMappings.Add("Name", "EmployeeName"); // notice here
                 bc.ColumnMappings.Add("Salary", "Salary");
                 bc.ColumnMappings.Add("DepartmentId", "Dep_Id"); //notice here
                 destinationCon.Open();
                 bc.WriteToServer(rdr);
                        }
                    }
                }
            }
        }

The complete source code looks like below

Aspx.cs (code behind file)


   public partial class SqlBulkExample : System.Web.UI.Page
    {
        string sourceCS =ConfigurationManager.ConnectionStrings["SourceCS"].ConnectionString;
        string destinationCS = ConfigurationManager.ConnectionStrings["DestinationCS"].ConnectionString;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindSourceGrid();
                BindDestinationGrid();
            }
        }
        private void BindSourceGrid()
        {
            DataSet ds = new DataSet();
            using (SqlConnection con = new SqlConnection(sourceCS))
                {
                    SqlDataAdapter da = new SqlDataAdapter("select * from Employee", con);
                    da.Fill(ds, "Employee");     
                }
            GridView1.DataSource = ds;
            GridView1.DataBind();      
        }
        private void BindDestinationGrid()
        {
            DataSet ds = new DataSet();
            using (SqlConnection con = new SqlConnection(destinationCS))
            {
                SqlDataAdapter da = new SqlDataAdapter("select * from Employee", con);
                da.Fill(ds, "Employee");
            }
            GridView2.DataSource = ds;
            GridView2.DataBind();
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
          using (SqlConnection sourceCon = new SqlConnection(sourceCS))
            {
                SqlCommand cmd = new SqlCommand("Select * from Employee", sourceCon);
                sourceCon.Open();
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    using (SqlConnection destinationCon = new SqlConnection(destinationCS))
                    {
                        using (SqlBulkCopy bc = new SqlBulkCopy(destinationCon))
                        {
                            bc.DestinationTableName = "Employee";
                            bc.ColumnMappings.Add("Id","Id");
                            bc.ColumnMappings.Add("Name", "EmployeeName");
                            bc.ColumnMappings.Add("Salary", "Salary");
                            bc.ColumnMappings.Add("DepartmentId", "Dep_Id");

                            destinationCon.Open();
                            bc.WriteToServer(rdr);
                        }
                    }
                }
            }
            BindDestinationGrid();
        }
     }

Aspx page


    <form id="form1" runat="server">
    <div>
        <h1>source Table</h1>
        <asp:GridView ID="GridView1" runat="server"></asp:GridView>
        <br />
         <asp:Button ID="Button1" runat="server" Text="Copy Data to destinationDB" OnClick="Button1_Click" />
          <h1>Destination Table</h1>
        <asp:GridView ID="GridView2" runat="server"></asp:GridView>   
    </div>
    </form>

After running the application , you will get below results.

DataSet and DataAdapter   Example
DataSet example