Learn to use ADO.NET SqlBulkCopy class with Example

ADO.NET SqlBulkCopy class let you to insert lots of records into the database in a very fast manner. Example – 1 million records can be insserted in a few seconds time.

This tutorial is a part of ADO.NET series.

• 1. Learn ADO.NET by building CRUD Operations in ASP.NET Core
• 2. ADO.NET – Create Records in ASP.NET Core
• 3. ADO.NET – Read Records in ASP.NET Core
• 4. ADO.NET – Update Records in ASP.NET Core
• 5. ADO.NET – Delete Records in ASP.NET Core
• 6. How to do ADO.NET SqlTransaction with Try Catch Block
• 7. Learn to use ADO.NET SqlBulkCopy class with Example

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 we want to copy all it’s records to another table called Account. Then we 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
);

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 we got a SqlDataReader object for reading the data from the AccountData table:

SqlDataReader dataReader = command.ExecuteReader();

Next we reated SqlBulkCopy object by passing the SqlConnection object to it:

SqlBulkCopy sqlBulk = new SqlBulkCopy(connection);

To tell SqlBulkCopy object how the data should be copied, we need to provide the destination table name like:

sqlBulk.DestinationTableName = "Account";

We 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 we use the WriteToServer() method to start the copy operation, like:

sqlBulk.WriteToServer(dataReader);

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

<h1>Transfer data from SqlBulkCopy class</h1>
<form method="post">
    <button type="submit" class="btn btn-sm btn-primary">Transfer Data</button>
</form>

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

Add some records to the AccountData table and then run your project and go to the URL – /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.

Can we use ADO.NET and Entity Framework Core together? Yes, we can use them together in ASP.NET core application since Entity Framework Core is an ORM that is made on top of ADO.NET. ADO.NET is easy to learn and very flexible to create and manage codes.

You can download the source code using the below link:

Download

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