Efficient Batch Insertion in SQL Server Using ADO.NET and C#

Introduction

When dealing with large datasets, the efficiency of database operations becomes crucial. Inserting records one by one into a SQL Server database can be time-consuming and resource-intensive. Fortunately, ADO.NET provides a solution in the form of the SqlBulkCopy class. This tool allows for batch insertion of records, significantly improving performance and reducing overhead. In this post, we’ll explore how to use SqlBulkCopy in C# to efficiently insert large volumes of data into SQL Server.

What is SqlBulkCopy?

SqlBulkCopy is a class in ADO.NET designed for fast, efficient insertion of large volumes of data into a SQL Server database. Unlike traditional INSERT statements, which process one row at a time, SqlBulkCopy allows you to load data in bulk. This method reduces the number of database round-trips and minimizes logging, making it an excellent choice for scenarios where performance is a priority.

Why Use SqlBulkCopy?

• Performance: SqlBulkCopy is optimized for high-speed data insertion, making it far more efficient than inserting rows individually.
• Efficiency: By sending all data in a single operation, SqlBulkCopy reduces the network overhead and the load on your SQL Server.
• Scalability: It’s well-suited for scenarios involving large datasets, such as data warehousing or ETL processes, where bulk data transfer is common.

Creating the Batch Insert Class

To streamline the process of batch insertion, we’ll create a class named TransactionBulkInserter. This class encapsulates the logic needed to insert a large number of records into a SQL Server table in one operation. Below is the code for this class:

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

namespace DatabaseUtilities
{
    public class TransactionBulkInserter
    {
        private readonly string _connectionString;

        public TransactionBulkInserter(string connectionString)
        {
            _connectionString = connectionString;
        }

        public void BulkInsertTransactions(DataTable transactions)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(_connectionString))
                {
                    connection.Open();
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
                    {
                        bulkCopy.DestinationTableName = "dbo.Transactions";
                        bulkCopy.WriteToServer(transactions);
                    }
                }
                Console.WriteLine("Bulk insert completed successfully.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("An error occurred during bulk insert: " + ex.Message);
            }
        }

        public static DataTable CreateTransactionDataTable()
        {
            DataTable transactionsTable = new DataTable();

            transactionsTable.Columns.Add("TransactionID", typeof(int));
            transactionsTable.Columns.Add("TransactionDate", typeof(DateTime));
            transactionsTable.Columns.Add("Amount", typeof(decimal));
            transactionsTable.Columns.Add("Description", typeof(string));

            return transactionsTable;
        }

        public static void PopulateTransactionDataTable(DataTable transactionsTable, int numberOfRecords)
        {
            Random random = new Random();

            for (int i = 1; i <= numberOfRecords; i++)
            {
                DataRow row = transactionsTable.NewRow();
                row["TransactionID"] = i;
                row["TransactionDate"] = DateTime.Now.AddDays(-i);
                row["Amount"] = Math.Round((decimal)(random.NextDouble() * 1000), 2);
                row["Description"] = $"Transaction {i}";
                transactionsTable.Rows.Add(row);
            }
        }
    }
}

Key Components Explained

Connection String: The TransactionBulkInserter class is initialized with a connection string. This string is used to establish a connection to the SQL Server database. The connection string is stored in a private field within the class and is essential for any database operation.

BulkInsertTransactions Method: This method is the heart of the class, responsible for performing the batch insert operation. It takes a DataTable as input and uses the SqlBulkCopy class to insert all records in one go. The DestinationTableName property is set to specify the target table in SQL Server.

CreateTransactionDataTable Method: This static method creates and returns a DataTable with a schema that matches the SQL Server Transactions table. It defines columns for TransactionID, TransactionDate, Amount, and Description.

PopulateTransactionDataTable Method: This method populates the DataTable with a specified number of records. It generates random transaction data, simulating real-world scenarios. Each new record is added to the DataTable, ready for bulk insertion.

How to Use the TransactionBulkInserter Class

To use the TransactionBulkInserter class, you’ll create an instance of it, prepare the data, and then execute the bulk insert operation. Below is an example of how to implement this in a console application:

using System;
using System.Data;
using DatabaseUtilities; // Import the namespace containing TransactionBulkInserter

namespace ConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "Server=vm0;Database=YourDatabaseName;Integrated Security=True;";
            TransactionBulkInserter inserter = new TransactionBulkInserter(connectionString);

            // Create and populate the DataTable
            DataTable transactionsTable = TransactionBulkInserter.CreateTransactionDataTable();
            TransactionBulkInserter.PopulateTransactionDataTable(transactionsTable, 500);

            // Perform the bulk insert
            inserter.BulkInsertTransactions(transactionsTable);

            Console.WriteLine("Operation completed. Press any key to exit.");
            Console.ReadKey();
        }
    }
}

Step-by-Step Walkthrough

Initialize the Inserter: First, you create an instance of TransactionBulkInserter, passing the connection string as an argument. This connection string should point to your SQL Server database.

Create and Populate the DataTable: Use the CreateTransactionDataTable method to create a DataTable that matches your SQL Server table’s schema. Populate this DataTable with data using the PopulateTransactionDataTable method. In the example above, 500 records are generated.

Perform the Bulk Insert: Call the BulkInsertTransactions method, passing the populated DataTable as an argument. This method will efficiently insert all the records into the SQL Server database in one operation.

Conclusion

Batch insertion is a powerful technique for efficiently handling large datasets in SQL Server. The TransactionBulkInserter class leverages ADO.NET’s SqlBulkCopy to provide a simple yet effective way to perform bulk inserts. By using this class, you can improve the performance of your data operations and streamline the process of loading large volumes of data into your database. This approach is especially useful in environments where speed and efficiency are priority.