Back to: ADO.NET Core Tutorial For Beginners and Professionals

Bulk Insert Operations using the ADO.NET Core SqlBulkCopy Class

In this article, I will discuss Bulk Insert Operations using ADO.NET Core SqlBulkCopy Class with Examples. Please read our previous article discussing Bulk Operations in ADO.NET Core with Examples. Bulk data operations are essential when dealing with large volumes of data to reduce overhead and improve performance. The ADO.NET Core SqlBulkCopy class provides a high-speed mechanism to bulk-load a SQL Server table with data from another source.

What is the ADO.NET Core SqlBulkCopy Class?

The SqlBulkCopy class in ADO.NET Core provides a high-performance way to efficiently transfer large volumes of data from a source (such as a DataTable) into a SQL Server table. Instead of sending one INSERT statement for each row, SqlBulkCopy opens a direct connection to the server and streams the data in bulk. This approach significantly reduces the overhead associated with multiple round-trips and is well-suited for scenarios where thousands or even millions of rows need to be inserted quickly.

Key Features of the SqlBulkCopy Class:

• High-performance Data Loading: Transfers data quickly by streaming it directly to the server, bypassing multiple individual INSERT statements.
• Flexible Column Mappings: Maps columns in the source data to columns in the target table when their names differ.
• Batch Sizes and Timeouts: Allows configuration of the number of rows per batch and the time the operation can take before timing out.
• Synchronous and Asynchronous Operations: Supports both blocking and non-blocking data transfer methods.
• Identity Handling: Options to generate identities automatically or preserve source values via SqlBulkCopyOptions.KeepIdentity.
• Transactional Support: Pass an explicit SqlTransaction or wrap in BEGIN TRAN / COMMIT.
• Limited to Bulk Insert Operations: Note that SqlBulkCopy is intended only for inserting data into existing tables, not for updates or deletes.

How to Use the ADO.NET Core SqlBulkCopy Class?

To use the SqlBulkCopy class, we typically need to:

• Create a SqlBulkCopy instance, providing a SQL Server connection string or an open SqlConnection.
• Define column mappings if the source and target column names don’t match.
• Set properties such as DestinationTableName to specify where the data will be inserted.
• Use the WriteToServer method, passing a data source such as a DataTable or IDataReader

The following image shows the general syntax of the SqlBulkCopy class:

Syntax Explanations:

• ConnectionString: Defines the SQL Server instance and database.
• DestinationTableName: Fully qualified name of the target table.
• ColumnMappings: Maps source columns to destination columns when names differ. If column names are the same, then it is optional.
• BatchSize: Number of rows per batch.
• BulkCopyTimeout: Time (in seconds) allowed for the operation.
• WriteToServer / WriteToServerAsync: Streams data to SQL Server.

SQL Server Database Setup:

Please execute the following SQL script to create a database and tables for demonstrating bulk operations. The following Script will create a simple database with two tables to receive bulk data. The Employees1 table is without the Identity Column, and the Employees2 table is with the Identity Column.

-- Create EmployeesDB database
CREATE DATABASE EmployeesDB;
GO

-- Switch to EmployeesDB database
USE EmployeesDB;
GO

-- Create the Employees1 table without IDENTITY Column
CREATE TABLE Employees1 (
    EmployeeId INT PRIMARY KEY,
    FirstName NVARCHAR(100),
    LastName NVARCHAR(100),
    Email NVARCHAR(150),
    Department NVARCHAR(100)
);

-- Create the Employees2 table with IDENTITY Column
CREATE TABLE Employees2 (
    EmployeeId INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(100),
    LastName NVARCHAR(100),
    Email NVARCHAR(150),
    Department NVARCHAR(100)
); 

Example to Understand the SqlBulkCopy Class

Let us understand the Bulk Insert operation using the SqlBulkCopy class in a .NET Core Console Application. The SqlBulkCopy class is used only for INSERT operations. The following example code is self-explained, so please read the comment lines for a better understanding.

using System.Data;
using Microsoft.Data.SqlClient;

namespace BulkInsertDemo
{
    public class Program
    {
        // Define the connection string.
        private const string ConnectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeesDB;Trusted_Connection=True;TrustServerCertificate=True;";

        static void Main(string[] args)
        {
            try
            {
                Console.WriteLine("Preparing data for bulk insert...");

                // Create a DataTable and fill it with employee records
                DataTable employeeData = GetEmployeeData();

                Console.WriteLine("Performing Bulk Insert using SqlBulkCopy...");

                // Create a connection to the SQL Server database.
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    // Open the database connection.
                    connection.Open();

                    // Initialize the SqlBulkCopy object.
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
                    {
                        // Set the destination table.
                        bulkCopy.DestinationTableName = "Employees1";

                        // Map the columns in the DataTable to the columns in the SQL table.
                        bulkCopy.ColumnMappings.Add("EmployeeId", "EmployeeId");
                        bulkCopy.ColumnMappings.Add("FirstName", "FirstName");
                        bulkCopy.ColumnMappings.Add("LastName", "LastName");
                        bulkCopy.ColumnMappings.Add("Email", "Email");
                        bulkCopy.ColumnMappings.Add("Department", "Department");

                        // Write the data from DataTable to the database table
                        bulkCopy.WriteToServer(employeeData);
                    }
                }

                Console.WriteLine("Bulk insert completed successfully.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error during bulk insert: " + ex.Message);
            }

            Console.ReadLine();
        }

        // Helper method to create a DataTable with mock employee data
        private static DataTable GetEmployeeData()
        {
            DataTable table = new DataTable();

            // Define schema
            table.Columns.Add("EmployeeId", typeof(int));
            table.Columns.Add("FirstName", typeof(string));
            table.Columns.Add("LastName", typeof(string));
            table.Columns.Add("Email", typeof(string));
            table.Columns.Add("Department", typeof(string));

            // Add some sample rows
            table.Rows.Add(101, "Pranaya", "Rout", "pranaya@example.com", "IT");
            table.Rows.Add(102, "Rakesh", "Mishra", "rakesh@example.com", "HR");
            table.Rows.Add(103, "Neha", "Sahu", "neha@example.com", "Finance");

            return table;
        }
    }
}

Code Explanation:

• Creates a DataTable and populates it with mock employee data.
• Initializes SqlBulkCopy and sets DestinationTableName.
• Maps columns manually using ColumnMappings.
• Inserts data using WriteToServer.

Output:

Adding Asynchronous Programming:

Let us convert the previous synchronous bulk insert example to asynchronous, which can enhance its performance, especially in applications that require non-blocking operations. The SqlBulkCopy class provides the WriteToServerAsync method, which can be used to perform the bulk copy operation asynchronously. Let us rewrite the previous example to use asynchronous programming as follows. Here, we replace Open() with OpenAsync(), and WriteToServer with WriteToServerAsync.

using System.Data;
using Microsoft.Data.SqlClient;

namespace BulkInsertDemo
{
    public class Program
    {
        // Define the connection string.
        private const string ConnectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeesDB;Trusted_Connection=True;TrustServerCertificate=True;";

        static async Task Main(string[] args)
        {
            try
            {
                Console.WriteLine("Preparing data for bulk insert...");

                // Create a DataTable and fill it with employee records
                DataTable employeeData = GetEmployeeData();

                Console.WriteLine("Performing Bulk Insert using SqlBulkCopy...");

                // Create a connection to the SQL Server database.
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    // Open the connection asynchronously.
                    await connection.OpenAsync();

                    // Initialize the SqlBulkCopy object.
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
                    {
                        // Set the destination table.
                        bulkCopy.DestinationTableName = "Employees1";

                        // Map the columns in the DataTable to the columns in the SQL table.
                        bulkCopy.ColumnMappings.Add("EmployeeId", "EmployeeId");
                        bulkCopy.ColumnMappings.Add("FirstName", "FirstName");
                        bulkCopy.ColumnMappings.Add("LastName", "LastName");
                        bulkCopy.ColumnMappings.Add("Email", "Email");
                        bulkCopy.ColumnMappings.Add("Department", "Department");

                        // Asynchronously write the data.
                        await bulkCopy.WriteToServerAsync(employeeData);
                    }
                }

                Console.WriteLine("Bulk insert completed successfully.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error during bulk insert: " + ex.Message);
            }

            Console.ReadLine();
        }

        // Helper method to create a DataTable with mock employee data
        private static DataTable GetEmployeeData()
        {
            DataTable table = new DataTable();

            // Define schema
            table.Columns.Add("EmployeeId", typeof(int));
            table.Columns.Add("FirstName", typeof(string));
            table.Columns.Add("LastName", typeof(string));
            table.Columns.Add("Email", typeof(string));
            table.Columns.Add("Department", typeof(string));

            // Add some sample rows
            table.Rows.Add(104, "Pranaya", "Rout", "pranaya@example.com", "IT");
            table.Rows.Add(105, "Rakesh", "Mishra", "rakesh@example.com", "HR");
            table.Rows.Add(106, "Neha", "Sahu", "neha@example.com", "Finance");

            return table;
        }
    }
}

Code Explanation:

• Uses asynchronous connection opening (OpenAsync) and bulk copy method (WriteToServerAsync).
• Ideal for web or cloud-based applications requiring non-blocking data operations.

Example Bulk Insert Without Identity Column in ADO.NET Core using SqlBulkCopy Class

Insert without Identity Column refers to performing bulk insert operations into a table where the primary key is an identity column auto-generated by SQL Server. The application does not supply values for identity columns, letting SQL Server generate them.

The following example demonstrates how to bulk-insert records without explicitly providing identity values. It is suitable when identity values should be auto-generated by SQL Server. The example bulk‑inserts rows without supplying the EmployeeId. The SQL Server will auto‑generate them.

using System.Data;
using Microsoft.Data.SqlClient;

namespace BulkInsertDemo
{
    public class Program
    {
        // Define the connection string.
        private const string ConnectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeesDB;Trusted_Connection=True;TrustServerCertificate=True;";

        static async Task Main(string[] args)
        {
            try
            {
                Console.WriteLine("Preparing data for bulk insert...");

                // Create a DataTable and fill it with employee records
                DataTable employeeData = GetEmployeeData();

                Console.WriteLine("Performing Bulk Insert using SqlBulkCopy...");

                // Create a connection to the SQL Server database.
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    // Open the connection asynchronously.
                    await connection.OpenAsync();

                    // Initialize the SqlBulkCopy object.
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
                    {
                        // Set the destination table.
                        bulkCopy.DestinationTableName = "Employees2";

                        // Map columns (exclude identity) in the DataTable to the columns in the SQL table.
                        bulkCopy.ColumnMappings.Add("FirstName", "FirstName");
                        bulkCopy.ColumnMappings.Add("LastName", "LastName");
                        bulkCopy.ColumnMappings.Add("Email", "Email");
                        bulkCopy.ColumnMappings.Add("Department", "Department");

                        // Asynchronously write the data.
                        await bulkCopy.WriteToServerAsync(employeeData);
                    }
                }

                Console.WriteLine("Done: bulk insert without identity column.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error during bulk insert: " + ex.Message);
            }

            Console.ReadLine();
        }

        // Helper method to create a DataTable with mock employee data
        private static DataTable GetEmployeeData()
        {
            // Create a DataTable without the identity column
            DataTable table = new DataTable();

            // Define schema
            table.Columns.Add("FirstName", typeof(string));
            table.Columns.Add("LastName", typeof(string));
            table.Columns.Add("Email", typeof(string));
            table.Columns.Add("Department", typeof(string));

            // Add some sample rows
            table.Rows.Add("Pranaya", "Rout", "pranaya@example.com", "IT");
            table.Rows.Add("Rakesh", "Mishra", "rakesh@example.com", "HR");
            table.Rows.Add("Neha", "Sahu", "neha@example.com", "Finance");

            return table;
        }
    }
}

Output:

Example using KeepIdentity Options

Using SqlBulkCopyOptions.KeepIdentity allows explicit insertion of identity column values from the source. It is useful for data migrations where identity values must match original records. The following example enables inserting specific identity values from a source data set. It is commonly used in data migration or restoring scenarios to maintain identity values from another source. The following example bulk‑inserts rows, including EmployeeId, by passing SqlBulkCopyOptions.KeepIdentity.

using System.Data;
using Microsoft.Data.SqlClient;

namespace BulkInsertDemo
{
    public class Program
    {
        // Define the connection string.
        private const string ConnectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeesDB;Trusted_Connection=True;TrustServerCertificate=True;";

        static async Task Main(string[] args)
        {
            try
            {
                Console.WriteLine("Preparing data for bulk insert...");

                // Create a DataTable and fill it with employee records
                DataTable employeeData = GetEmployeeData();

                Console.WriteLine("Performing Bulk Insert using SqlBulkCopy...");

                // Create a connection to the SQL Server database.
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    // Open the connection asynchronously.
                    await connection.OpenAsync();

                    // Initialize the SqlBulkCopy object.
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity, null))
                    {
                        // Set the destination table.
                        bulkCopy.DestinationTableName = "Employees2";

                        // Map columns in the DataTable to the columns in the SQL table.
                        bulkCopy.ColumnMappings.Add("EmployeeId", "EmployeeId");
                        bulkCopy.ColumnMappings.Add("FirstName", "FirstName");
                        bulkCopy.ColumnMappings.Add("LastName", "LastName");
                        bulkCopy.ColumnMappings.Add("Email", "Email");
                        bulkCopy.ColumnMappings.Add("Department", "Department");

                        // Asynchronously write the data.
                        await bulkCopy.WriteToServerAsync(employeeData);
                    }
                }

                Console.WriteLine("Done: bulk insert with identity column (KeepIdentity).");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error during bulk insert: " + ex.Message);
            }

            Console.ReadLine();
        }

        // Helper method to create a DataTable with mock employee data
        private static DataTable GetEmployeeData()
        {
            // Create a DataTable with the identity column
            DataTable table = new DataTable();

            // Define schema
            table.Columns.Add("EmployeeId", typeof(int));
            table.Columns.Add("FirstName", typeof(string));
            table.Columns.Add("LastName", typeof(string));
            table.Columns.Add("Email", typeof(string));
            table.Columns.Add("Department", typeof(string));

            // Add some sample rows
            table.Rows.Add(104, "Pranaya", "Rout", "pranaya@example.com", "IT");
            table.Rows.Add(105, "Rakesh", "Mishra", "rakesh@example.com", "HR");
            table.Rows.Add(106, "Neha", "Sahu", "neha@example.com", "Finance");

            return table;
        }
    }
}

Output:

When to Use ADO.NET Core SqlBulkCopy in Real-time Applications:

• Large-scale Data Imports: Ideal for migrating large datasets into SQL databases efficiently.
• Performance-Critical Applications: Where inserting individual rows with standard commands is performance-prohibitive.
• ETL (Extract, Transform, Load) Processes: Often used in data warehousing scenarios to rapidly load bulk data.
• Data Migration and Syncing: Useful during application upgrades or batch synchronization tasks.

While SqlBulkCopy is highly efficient, it bypasses some checks (like constraints and triggers), so it’s best suited for well-structured, clean data. Always handle exceptions, log operations, and verify the integrity of the source data before using SqlBulkCopy in production scenarios.

In the next article, I will discuss Bulk Operations using Stored Procedures in ADO.NET Core with Examples. In this article, I explain Bulk Operations using the ADO.NET Core SqlBulkCopy Class with Examples. I would appreciate your feedback. Please post your feedback, questions, or comments about this Bulk Operations using ADO.NET Core SqlBulkCopy Class article.