SqlBulkCopy class of ADO.NET

Last Updated: August 19, 2020

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

This tutorial is a part of ADO.NET series.

1. Learn ADO.NET by building CRUD features in ASP.NET Core Application
2. Create Records using ADO.NET in ASP.NET Core Application
3. Read Records using ADO.NET in ASP.NET Core Application
4. Update Records using ADO.NET in ASP.NET Core Application
5. Delete Records using ADO.NET in ASP.NET Core Application
6. How to use Try Catch Block to Catch Exceptions
7. SqlBulkCopy class of ADO.NET

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. Then I will use SqlBulkCopy class.

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
);

Important – Learn ASP.NET Core Convention-Based URL Routing

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 the 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";

If you want to make highest quality codes in ASP.NET Core then make sure you know Dependency Injection in ASP.NET Core in full details (A to Z).

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/css/bootstrap.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. 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