ADO.NET Core SqlBulkCopy

ADO.NET Core SqlBulkCopy

In this article, I will discuss ADO.NET Core SqlBulkCopy with Examples. Please read our previous article discussing How to Implement ADO.NET Core Distributed Transactions.

SqlBulkCopy in ADO.NET Core

ADO.NET Core, as part of the .NET platform, supports high-performance bulk data operations through the SqlBulkCopy class. The SqlBulkCopy class in ADO.NET Core is designed to efficiently copy large amounts of data into a SQL Server table or view. It is useful for data warehousing scenarios, batch job processing, or inserting large datasets quickly, bypassing the usual insert operation’s overhead.

It’s part of the Microsoft.Data.SqlClient namespace. The primary advantage of SqlBulkCopy is its efficiency in handling bulk data operations, making it significantly faster than inserting data row by row, especially for large datasets.

Example to Understand SqlBulkCopy in ADO.NET Core:

First, you need to create a database and a table where the dummy data will be inserted. Use the following SQL script to create your database EmployeeDB and Employee table:

CREATE DATABASE EmployeeDB;
GO

USE EmployeeDB;
GO

CREATE TABLE Employees (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100),
    Department VARCHAR(50)
);
GO

Example to Understand SqlBulkCopy in ADO.NET Core

The SqlBulkCopy class works by opening a connection to an SQL Server database and using a special bulk copy operation to transfer data from a data source into an SQL Server table. The operation is highly optimized and bypasses much of the logging that is typically done for transactional integrity, which is why it’s much faster for inserting large numbers of rows. However, because of this, you should use it within the context of a transaction if you need to ensure data integrity across multiple operations.

Here is a basic example to demonstrate how to use SqlBulkCopy with ADO.NET Core to insert data into a SQL Server database:

  • Establish a Connection: First, you need a connection to your SQL Server database.
  • Create an Instance of SqlBulkCopy: Pass the connection string or the connection object itself to the SqlBulkCopy constructor.
  • Specify the Destination Table: Set the DestinationTableName property of the SqlBulkCopy instance to the name of the table you’re copying data into.
  • Specify Column Mapping: Specify the column Mapping between the Data table and the destination table.
  • Copy Data: Use the WriteToServer method to copy data from a data source, like a DataTable or a DataReader, to the destination table.

Modify the Program class as follows:

using Microsoft.Data.SqlClient;
using System.Data;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";
            try
            {
                // Dummy data to insert
                
                DataTable employeesTable = GenerateDummyData();

                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();

                    using (var bulkCopy = new SqlBulkCopy(connection))
                    {
                        //Specify the Destination table Name
                        bulkCopy.DestinationTableName = "Employees";
                        bulkCopy.ColumnMappings.Add("FirstName", "FirstName");
                        bulkCopy.ColumnMappings.Add("LastName", "LastName");
                        bulkCopy.ColumnMappings.Add("Email", "Email");
                        bulkCopy.ColumnMappings.Add("Department", "Department");

                        try
                        {
                            // Write from the source to the destination
                            bulkCopy.WriteToServer(employeesTable);
                            Console.WriteLine("Data inserted successfully.");
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine($"An error occurred: {ex.Message}");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"An error occurred: {ex.Message}");
            }
        }

        static DataTable GenerateDummyData()
        {
            // Create a DataTable with the same schema as the Employees table
            // EmployeeID is the Identity Column, so no need to send EmployeeID column values
            DataTable employeesTable = new DataTable();
            employeesTable.Columns.Add("FirstName", typeof(string));
            employeesTable.Columns.Add("LastName", typeof(string));
            employeesTable.Columns.Add("Email", typeof(string));
            employeesTable.Columns.Add("Department", typeof(string));

            // Add dummy data
            for (int i = 0; i < 1000; i++)
            {
                employeesTable.Rows.Add($"FirstName-{i}", $"LastName-{i}", $"Email{i}@example.com", "IT");
            }

            return employeesTable;
        }
    }
}

Now, run the application, and it will insert 1,000 dummy records into your Employees table using SqlBulkCopy.

Important Methods and Properties of SqlBulkCopy in ADO.NET Core

SqlBulkCopy is a class in ADO.NET designed to efficiently bulk-load an SQL Server table with data from another source. Here’s an overview of some of the most important methods and properties of the SqlBulkCopy class in ADO.NET Core:

Important Properties
  • BatchSize: Determines the number of rows in each batch. Setting this value can help manage memory usage during the bulk copy operation.
  • BulkCopyTimeout: Specifies the number of seconds the operation has to complete before it times out. The default is 30 seconds.
  • ColumnMappings: Defines the mapping between the source and destination columns. This is necessary when the source and destination tables don’t have the same schema.
  • DestinationTableName: The name of the destination table in the database. This property must be set before the bulk copy operation begins.
  • EnableStreaming: When set to true, the SqlBulkCopy can read from an IDataReader object using sequential access, reducing memory usage for large data sets.
  • NotifyAfter: The number of rows to be processed before the event SqlRowsCopied is fired. Useful for monitoring the progress of the bulk copy operation.
Important Methods
  • WriteToServer: Overloaded method that copies all rows in the supplied DataTable, IDataReader, or DataRow array to the destination table. This is the core method used to perform the bulk copy operation.
  • Close: Releases all resources used by the SqlBulkCopy object. While not strictly necessary due to the use of managed code, it’s good practice to call this method or use a using statement to ensure resources are disposed of properly.
Example to Understand SqlBulkCopy Methods and Properties in ADO.NET Core
using Microsoft.Data.SqlClient;
using System.Data;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";
            try
            {
                // Dummy data to insert
                
                DataTable employeesTable = GenerateDummyData();

                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();

                    using (var bulkCopy = new SqlBulkCopy(connection))
                    {
                        //Specify the Destination table Name
                        bulkCopy.DestinationTableName = "Employees";
                        bulkCopy.ColumnMappings.Add("FirstName", "FirstName");
                        bulkCopy.ColumnMappings.Add("LastName", "LastName");
                        bulkCopy.ColumnMappings.Add("Email", "Email");
                        bulkCopy.ColumnMappings.Add("Department", "Department");

                        // Optional: Set the number of rows to be processed before a notification event is triggered.
                        bulkCopy.NotifyAfter = 100;
                        bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);

                        // Optional: Set the batch size. The number of rows in each batch. Setting this to 0 means the entire data set is processed in a single batch.
                        bulkCopy.BatchSize = 500;

                        // Optional: Set the bulk copy timeout (in seconds).
                        bulkCopy.BulkCopyTimeout = 60;

                        // Optional: Enable streaming. When set to true, SqlBulkCopy reads from an IDataReader object without loading the entire dataset into memory.
                        bulkCopy.EnableStreaming = true;

                        try
                        {
                            // Perform the bulk copy operation.
                            bulkCopy.WriteToServer(employeesTable);
                            Console.WriteLine("Data inserted successfully.");
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine($"An error occurred: {ex.Message}");
                        }
                        finally
                        {
                            // Explicitly close the SqlBulkCopy instance.
                            bulkCopy.Close();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"An error occurred: {ex.Message}");
            }
        }

        static DataTable GenerateDummyData()
        {
            // Create a DataTable with the same schema as the Employees table
            // EmployeeID is the Identity Column, so no need to send EmployeeID column values
            DataTable employeesTable = new DataTable();
            employeesTable.Columns.Add("FirstName", typeof(string));
            employeesTable.Columns.Add("LastName", typeof(string));
            employeesTable.Columns.Add("Email", typeof(string));
            employeesTable.Columns.Add("Department", typeof(string));

            // Add dummy data
            for (int i = 0; i < 1000; i++)
            {
                employeesTable.Rows.Add($"FirstName-{i}", $"LastName-{i}", $"Email{i}@example.com", "IT");
            }

            return employeesTable;
        }

        static void OnSqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
        {
            Console.WriteLine($"{e.RowsCopied} rows have been copied to the database.");
        }
    }
}
In the above code,
  • BatchSize: Determines the number of rows in each batch. Smaller batch sizes can reduce memory consumption and improve responsiveness, especially for large datasets. In the example, the batch size is set to 500.
  • BulkCopyTimeout: Specifies the timeout (in seconds) for the bulk copy operation. It’s helpful to prevent hanging due to network issues or large data volumes. The default is 30 seconds. Setting it to 0 means no limit. In this example, the timeout is set to 60 seconds.
  • ColumnMappings: Defines how columns from the source are mapped to the destination columns. Maps the columns from the source DataTable to the destination database table. This is crucial for ensuring data is inserted into the correct columns, especially when the order or names of columns do not match.
  • DestinationTableName: Specifies the target table in the database where the data will be inserted.
  • EnableStreaming: When set to true, it enables the SqlBulkCopy to read from an IDataReader with minimal memory footprint, which is useful for very large datasets.
  • NotifyAfter: Specifies the number of rows to be processed before triggering the SqlRowsCopied event. This is useful for monitoring progress, especially with large operations. This can be used for logging progress. In the example, an event handler prints a message every 100 rows.
  • DestinationTableName: The name of the destination table where the data will be copied.
  • WriteToServer: Executes the bulk copy operation, transferring data from the DataTable to the SQL Server table.
  • Close: Explicitly closes the SqlBulkCopy instance. While the using statement ensures that resources are disposed of, calling Close can be used for explicit control, especially when not using using.

When you run the above code, you will get the following output:

Example to Understand SqlBulkCopy Methods and Properties in ADO.NET Core

Async Implementation of SqlBulkCopy in ADO.NET Core

Let us convert the previous synchronous bulk copy example to asynchronous, which can enhance its performance, especially in applications that require non-blocking operations. The .NET Core framework provides asynchronous versions of many SqlBulkCopy methods, including WriteToServerAsync, which can be used to perform the bulk copy operation asynchronously. Here’s how you can modify the previous example to use async/await pattern:

using Microsoft.Data.SqlClient;
using System.Data;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static async Task Main(string[] args)
        {
            string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";
            try
            {
                // Dummy data to insert
                
                DataTable employeesTable = GenerateDummyData();

                using (var connection = new SqlConnection(connectionString))
                {
                    await connection.OpenAsync();

                    using (var bulkCopy = new SqlBulkCopy(connection))
                    {
                        //Specify the Destination table Name
                        bulkCopy.DestinationTableName = "Employees";
                        bulkCopy.ColumnMappings.Add("FirstName", "FirstName");
                        bulkCopy.ColumnMappings.Add("LastName", "LastName");
                        bulkCopy.ColumnMappings.Add("Email", "Email");
                        bulkCopy.ColumnMappings.Add("Department", "Department");

                        // Optional: Set the number of rows to be processed before a notification event is triggered.
                        bulkCopy.NotifyAfter = 100;
                        bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);

                        // Optional: Set the batch size. The number of rows in each batch. Setting this to 0 means the entire data set is processed in a single batch.
                        bulkCopy.BatchSize = 500;

                        // Optional: Set the bulk copy timeout (in seconds).
                        bulkCopy.BulkCopyTimeout = 60;

                        // Optional: Enable streaming. When set to true, SqlBulkCopy reads from an IDataReader object without loading the entire dataset into memory.
                        bulkCopy.EnableStreaming = true;

                        try
                        {
                            // Perform the bulk copy operation.
                            await bulkCopy.WriteToServerAsync(employeesTable);
                            Console.WriteLine("Data inserted successfully.");
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine($"An error occurred: {ex.Message}");
                        }
                        finally
                        {
                            // Explicitly close the SqlBulkCopy instance.
                            bulkCopy.Close();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"An error occurred: {ex.Message}");
            }
        }

        static DataTable GenerateDummyData()
        {
            // Create a DataTable with the same schema as the Employees table
            // EmployeeID is the Identity Column, so no need to send EmployeeID column values
            DataTable employeesTable = new DataTable();
            employeesTable.Columns.Add("FirstName", typeof(string));
            employeesTable.Columns.Add("LastName", typeof(string));
            employeesTable.Columns.Add("Email", typeof(string));
            employeesTable.Columns.Add("Department", typeof(string));

            // Add dummy data
            for (int i = 0; i < 1000; i++)
            {
                employeesTable.Rows.Add($"FirstName-{i}", $"LastName-{i}", $"Email{i}@example.com", "IT");
            }

            return employeesTable;
        }

        static void OnSqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
        {
            Console.WriteLine($"{e.RowsCopied} rows have been copied to the database.");
        }
    }
}
Key Changes for Async Implementation
  • await connection.OpenAsync();: Opens the connection asynchronously.
  • await bulkCopy.WriteToServerAsync(dataTable);: Performs the bulk copy operation asynchronously. This method does not block the calling thread while the operation is in progress, which means your application remains responsive.
  • Asynchronous event handlers: The SqlRowsCopied event handler remains synchronous because it’s a callback invoked by the SqlBulkCopy object. Events in .NET do not have a built-in asynchronous pattern. If you need to perform asynchronous operations in response to an event, you would typically offload that work to a separate async method from within the event handler.
Bulk Update and Delete using SqlBulkCopy in ADO.NET Core

SqlBulkCopy is designed explicitly for bulk insert operations into an SQL Server database and does not directly support bulk update or delete operations. However, you can achieve bulk update and delete functionalities by combining SqlBulkCopy and temporary tables or staging tables in SQL Server. Here’s how you can perform bulk update and delete operations using this approach:

using Microsoft.Data.SqlClient;
using System.Data;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static async Task Main(string[] args)
        {
            string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";
            try
            {
                await PerformBulkUpdateAndDeleteAsync(connectionString);
            }
            catch (Exception ex)
            {
                Console.WriteLine($"An error occurred: {ex.Message}");
            }
        }
        static async Task PerformBulkUpdateAndDeleteAsync(string connectionString)
        {
            DataTable dataTable = GenerateDummyDataForUpdateAndDelete(); // Assume this generates the appropriate data
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();

                // Step 1: Create a temporary table
                string createTempTableSql = @"
                CREATE TABLE #TempEmployees (
                    EmployeeID INT,
                    FirstName NVARCHAR(50),
                    LastName NVARCHAR(50),
                    Email NVARCHAR(100),
                    Department NVARCHAR(50)
                );";
                using (SqlCommand command = new SqlCommand(createTempTableSql, connection))
                {
                    await command.ExecuteNonQueryAsync();
                }
                Console.WriteLine("Temporary Table TempEmployees Created");

                // Step 2: Bulk insert into the temporary table
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
                {
                    bulkCopy.DestinationTableName = "#TempEmployees";
                    await bulkCopy.WriteToServerAsync(dataTable);
                    Console.WriteLine("Temporary Table Filled with data from Data table");
                }

                // Step 3: Perform bulk update and delete using the temporary table
                // Example: Update operation
                string updateSql = @"
                UPDATE e
                SET e.FirstName = te.FirstName,
                    e.LastName = te.LastName,
                    e.Email = te.Email,
                    e.Department = te.Department
                FROM Employees e
                INNER JOIN #TempEmployees te ON e.EmployeeID = te.EmployeeID;";
                

                // Example: Delete operation
                string deleteSql = @"
                DELETE e
                FROM Employees e
                WHERE EXISTS (
                    SELECT 1 FROM #TempEmployees te WHERE te.EmployeeID = e.EmployeeID
                );";

                using (SqlCommand updateCommand = new SqlCommand(updateSql, connection))
                {
                    await updateCommand.ExecuteNonQueryAsync();
                    Console.WriteLine("Bulk Update Successful");
                }

                using (SqlCommand deleteCommand = new SqlCommand(deleteSql, connection))
                {
                    await deleteCommand.ExecuteNonQueryAsync();
                    Console.WriteLine("Bulk Delete Successful");
                }

                // Optionally, drop the temporary table if you're done with it
                // Temporary tables are automatically dropped when the connection is closed,
                // but explicitly dropping can be good for clarity and resource management
                string dropTempTableSql = "DROP TABLE #TempEmployees;";
                using (SqlCommand command = new SqlCommand(dropTempTableSql, connection))
                {
                    await command.ExecuteNonQueryAsync();
                    Console.WriteLine("Temporary Table TempEmployees Deleted");
                }
            }
        }

        static DataTable GenerateDummyDataForUpdateAndDelete()
        {
            // Generate and return a DataTable with the structure matching the Employees table
            // This DataTable should contain the data you wish to update or use for deletion criteria
            DataTable table = new DataTable();
            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));

            // Populate the table with dummy data for the first 100 employees
            for (int i = 1; i <= 100; i++)
            {
                // Generate dummy data
                string firstName = $"NewFirst{i}";
                string lastName = $"NewLast{i}";
                string email = $"newemail{i}@example.com";
                string department = "UpdatedDepartment"; 

                table.Rows.Add(i, firstName, lastName, email, department);
            }

            return table;
        }
    }
}
Explanation:
  • Create a Temporary Table: A temporary table #TempEmployees is created with the same structure as the Employees table. This table is used for the bulk operation.
  • Bulk Insert: Data intended for updating or deleting operations is bulk-inserted into the temporary table.
  • Bulk Update/Delete: SQL commands are executed to update and delete records in the Employees table based on the contents of the temporary table.
  • Clean Up: Although temporary tables are automatically dropped at the end of the session, the code explicitly drops the temporary table for clarity.

When you run the above code, you will get the following output:

Bulk Update and Delete using SqlBulkCopy in ADO.NET Core

Points to Consider when working with SqlBulkCopy in ADO.NET Core
  • Transaction Management: SqlBulkCopy can participate in database transactions. Use the SqlTransaction object if you need to roll back the copy operation in case of errors.
  • Performance Tuning: The performance of SqlBulkCopy can be optimized by adjusting batch sizes and the timeout property. Experiment with these settings based on your specific scenario.
  • Column Mappings: If the source and destination tables don’t have the same schema, you’ll need to define column mappings using the ColumnMappings property.
  • Bulk Copy Options: The SqlBulkCopyOptions enumeration provides options that enable you to use features like keeping identity values or checking constraints during the bulk copy operation.
When to Use SqlBulkCopy in ADO.NET Core?

SqlBulkCopy is a class in ADO.NET designed to efficiently bulk-load an SQL Server table with data from another source. It’s particularly useful when you need to efficiently insert large volumes of data into an SQL Server database. Here’s when to consider using SqlBulkCopy in ADO.NET Core:

  • Large Volume Data Insertion: If you’re working with large datasets that need to be inserted into a SQL Server database, SqlBulkCopy can significantly reduce the amount of time it takes compared to inserting each row individually.
  • ETL Processes: In Extract, Transform, Load (ETL) processes, where data is being moved from various sources into a SQL Server database for data warehousing or reporting purposes, SqlBulkCopy can handle bulk data loads efficiently.
  • Performance Optimization: When performance is a critical factor, and you need to minimize the load time for bulk data operations, SqlBulkCopy can optimize these operations. It is much faster than executing individual insert statements or using other methods of bulk-loading data.
  • Batch Processing: For applications that involve batch processing of data, where data is collected over a period and then processed in bulk, SqlBulkCopy can efficiently insert the collected data into an SQL Server database.
  • Minimal Logging: SqlBulkCopy can take advantage of minimal logging under certain conditions (like when the target table is empty and the recovery model is set to bulk-logged or simple), leading to faster insertions and less transaction log space usage.
  • Data Migration: During data migration tasks, where data needs to be moved from one database or format to another (for instance, from a CSV file or an Excel spreadsheet into a SQL Server database), SqlBulkCopy can facilitate the rapid data transfer.

In the next article, I will discuss How to Implement Bulk Insert, Update, and Delete Operations using ADO.NET Core with Examples. In this article, I explain ADO.NET Core SqlBulkCopy with Examples. I would like to have your feedback. Please post your feedback, questions, or comments about this ADO.NET Core SqlBulkCopy article.

Leave a Reply

Your email address will not be published. Required fields are marked *