SqlBulkCopy class of ADO.NET

Last Updated: September 14, 2019

In cases where you need to inserts lots of records into the database, eg 1 million records, then ADO.NET has a class called SqlBulkCopy which does this work very quickly.

The SqlBulkCopy has a method called WriteToServer() that takes a ‘SqlDataReader’ object, and through this method the bulk inserts are done quickly.

Suppose there is a database table called AccountData which has 1 million records, and I want to copy all it’s records to my another table called Account.

The definition of ‘AccountData’ and ‘Account’ tables are given below:


CREATE TABLE [dbo].[AccountData] (
    [Id]    INT          IDENTITY (1, 1) NOT NULL Primary key,
    [PersonName]  VARCHAR (50) NOT NULL,
    [TotalCash] MONEY        NOT NULL
);

CREATE TABLE [dbo].[Account] (
    [Id]    INT          IDENTITY (1, 1) NOT NULL Primary key,
    [Name]  VARCHAR (50) NOT NULL,
    [Money] MONEY        NOT NULL
);

Action Method

Go to the Controller to add TransferData actions whose code is given below:

public IActionResult TransferData()
{
    return View();
}

[HttpPost]
[ActionName("TransferData")]
public IActionResult TransferData_Post()
{
    string connectionString = Configuration["ConnectionStrings:DefaultConnection"];

    SqlConnection connection = new SqlConnection(connectionString);
    string sql = $"Select * From AccountData";
    SqlCommand command = new SqlCommand(sql, connection);

    connection.Open();
    SqlDataReader dataReader = command.ExecuteReader();

    // create a SqlBulkCopy object
    SqlBulkCopy sqlBulk = new SqlBulkCopy(connection);

    //Give your Destination table name
    sqlBulk.DestinationTableName = "Account";

    //Mappings
    sqlBulk.ColumnMappings.Add("PersonName", "Name");
    sqlBulk.ColumnMappings.Add("TotalCash", "Money");

    //Copy rows to destination table
    sqlBulk.WriteToServer(dataReader);

    return View();
}

You can see that first I got a SqlDataReader object for reading the data from ‘AccountData’ table:

SqlDataReader dataReader = command.ExecuteReader();

Next I created SqlBulkCopy object by passing the SqlConnection object to it:

SqlBulkCopy sqlBulk = new SqlBulkCopy(connection);

To tell SqlBulkCopy object how the data copy should be done, I need to provide the destination table name like:

sqlBulk.DestinationTableName = "Account";

I also have to provide the column mappings, by using the ColumnMappings.Add() method. This method takes 2 parameters:

Source table column’s name as first parameter
Destination table column’s name as second parameter

This mappings code is:

sqlBulk.ColumnMappings.Add("PersonName", "Name");
sqlBulk.ColumnMappings.Add("TotalCash", "Money");

Finally I use the WriteToServer() method to start the copy operation, like:

sqlBulk.WriteToServer(dataReader);

View

Create the View called TransferData in the ‘Views/Home’ folder and add the following code to it:

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>ADO - Transfer Data</title>
    <link rel="stylesheet" asp-href-include="lib/bootstrap/dist/css/*.min.css" />
</head>
<body>
    <div class="container-fluid">
        <h1>Transfer data from SqlBulkCopy class</h1>
        <form method="post">
            <button type="submit" class="btn btn-sm btn-primary">Transfer Data</button>
        </form>
    </div>
</body>
</html>

When the Transfer Data button is clicked then the TransferData action will be called and
the SqlBulkCopy code will execute.

Testing SqlBulkCopy

Add some records to the ‘AccountData’ table and then run your project and go to the URL – ‘http://localhost:61126/Home/TransferData’. Finally click the Transfer Data button.
Now open the ‘Account’ table and you will see new records from the ‘AccountData’ are copied to it.

You can download the source code using the below link:

Download

Conclusion

In this tutorial you learned to use the SqlBulkCopy class of ADO.NET. Use it whenever you are building your next code.

Also see – How to use Try Catch Block to Catch Exceptions