Using SqlBulkCopy to Insert a List of Objects in C#

If you have an array or list of objects, the first step is to convert them into a DataTable, and then you can use SqlBulkCopy to perform the bulk insert.

Creating Table

Assume we have the following Orders table in SQL Server:

CREATE TABLE [dbo].[Orders](
    [OrderId] [int] IDENTITY(1,1) NOT NULL,
    [CustomerId] [int] NOT NULL,
    [OrderDate] [datetime] NOT NULL,
    [TotalAmount] [decimal](18,2) NOT NULL,
    rders] PRIMARY KEY CLUSTERED 
    (
        [OrderId] ASC
    )
) ON [PRIMARY]

DTO Mapping

Next, we map this table to a DTO (Data Transfer Object) class.

public class OrderDTO
{
    public int OrderId { get; set; }
    public int CustomerId { get; set; }
    public DateTime OrderDate { get; set; }
    public decimal TotalAmount { get; set; }
    public string OrderStatus { get; set; }
}

Convert List of Orders to DataTable

We need to convert a list of OrderDTO objects into a DataTable to use SqlBulkCopy.

using System.ComponentModel;
using System.Data;

namespace MyApp
{
    public static class IEnumerableExtensions
    {
        public static DataTable ToDataTable<T>(this IEnumerable<T> data)
        {
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
            DataTable table = new DataTable();
            foreach (PropertyDescriptor prop in properties)
            {
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
            }

            foreach (T item in data)
            {
                DataRow row = table.NewRow();
                foreach (PropertyDescriptor prop in properties)
                    row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
                table.Rows.Add(row);
            }

            return table;
        }
    }
}

This ToDataTable method takes a collection of objects (in this case, OrderDTO), and converts it into a DataTable that is compatible with SqlBulkCopy.

Bulk Insert with SqlBulkCopy

Now, let’s use SqlBulkCopy to insert the OrderDTO list into the Orders table. Below is the method BulkInsertOrdersAsync that takes an IEnumerable<OrderDTO> and inserts it into the database:

using Microsoft.Data.SqlClient;
using System.Collections.Generic;
using System.Threading.Tasks;

public class OrderService
{
    private readonly string _connectionString = "your connection string";

    public async Task BulkInsertOrdersAsync(IEnumerable<OrderDTO> orders)
    {
        try
        {
            using var connection = new SqlConnection(_connectionString);
            using var bulkCopy = new SqlBulkCopy(connection)
            {
                DestinationTableName = "Orders"  // Set destination table
            };

            // Open the connection to the database
            await connection.OpenAsync();

            // Convert the orders list to a DataTable and write to the server
            await bulkCopy.WriteToServerAsync(orders.ToDataTable());
        }
        catch (SqlException e)
        {
            throw new Exception($"Failed to insert orders: {e.Message}", e);
        }
    }
}

In this example:

• We open a connection to the SQL Server using SqlConnection.
• We initialize the SqlBulkCopy object and set the DestinationTableName to "Orders", which is the name of our target SQL table.
• The WriteToServerAsync method is called, which inserts the data in bulk by converting the orders list to a DataTable using our ToDataTable extension method.
• Any exceptions during the process are caught and rethrown with a custom error message.

This method is flexible and can be applied to any list of objects that you need to insert into a SQL Server database. Just map your objects to a DTO, convert them to a DataTable, and let SqlBulkCopy do the heavy lifting.

With SqlBulkCopy, you can efficiently insert large volumes of data from a list of objects into SQL Server. By converting the objects to a DataTable, you ensure that the data is in the right format for bulk insertion.