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.
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 );
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";
I also have to provide the column mappings, by using the ColumnMappings.Add() method. This method takes 2 parameters:
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);
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.
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:
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 -