Learn to use ADO.NET SqlBulkCopy class with Example

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.

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

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

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

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.

Testing SqlBulkCopy

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

Conclusion

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

SHARE THIS ARTICLE

  • linkedin
  • reddit
yogihosting

ABOUT THE AUTHOR

I hope you enjoyed reading this tutorial. If it helped you then consider buying a cup of coffee for me. This will help me in writing more such good tutorials for the readers. Thank you. Buy Me A Coffee donate