Bulk Insertion in ADO.NET

Introduction:

Efficient database operations are essential for the performance of any modern application. When dealing with large datasets, traditional insertion methods can lead to significant performance bottlenecks. Fortunately, ADO.NET provides powerful techniques for enhancing database performance, and one such technique is bulk insertion. In this article, we'll explore the concept of bulk insertion, its benefits, provide a detailed implementation example using ADO.NET, and demonstrate how to use it effectively in your applications.

Understanding Bulk Insertion: Bulk insertion is a method used to insert a large volume of data into a database in a single operation. Instead of inserting data row by row, bulk insertion allows for the transfer of data in batches, minimizing round trips between the application and the database server. This approach significantly improves performance, particularly when dealing with extensive datasets.

Benefits of Bulk Insertion:

1. Enhanced Performance: By reducing the number of round trips between the application and the database server, bulk insertion dramatically improves performance.
2. Scalability: Bulk insertion techniques are highly scalable and can efficiently handle large datasets, making them ideal for high-volume applications.
3. Resource Efficiency: With fewer database connections and transactions, bulk insertion optimizes resource utilization, resulting in better overall system performance.

Implementing Bulk Insertion in ADO.NET:

ADO.NET offers two primary methods for implementing bulk insertion: using the SqlBulkCopy class or stored procedures with table-valued parameters (TVPs). While both methods offer advantages, we'll focus on the latter for its structured approach and compatibility with existing stored procedures.

Example Implementation:

Below is a step-by-step implementation example of bulk insertion using stored procedures with TVPs in ADO.NET:

/ Define your entity class
public class MyEntity
{
    // Define properties
    public int Id { get; set; }
    public string Name { get; set; }
    // Add other properties as needed
}

// Define your database service or repository class
public class BulkInsertionService
{
    // Implement BulkInsertAsync method
    public async Task<int> BulkInsertAsync<T>(List<T> list, string procedureName, string userDefinedTableType) where T : class
    {
        var datatable = ListToDataTable(list);
        using var connection = db.GetConnection();
        using var cmd = new SqlCommand(procedureName, connection);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue(userDefinedTableType, datatable);

        await connection.OpenAsync();
        var result = await cmd.ExecuteNonQueryAsync();
        await connection.CloseAsync();
        return result;
    }

    public DataTable ListToDataTable<T>(List<T> items)
    {
        DataTable dataTable = new DataTable(typeof(T).Name);
        PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
        foreach (PropertyInfo prop in props)
        {
            var type = (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>) ? Nullable.GetUnderlyingType(prop.PropertyType) : prop.PropertyType);
            dataTable.Columns.Add(prop.Name, type);
        }
        foreach (T item in items)
        {
            var values = new object[props.Length];
            for (int i = 0; i < props.Length; i++)
            {
                values[i] = props[i].GetValue(item, null);
            }
            dataTable.Rows.Add(values);
        }
        return dataTable;
    }

}

CREATE TYPE dbo.MyUserDefinedTableType AS TABLE
(
    Id INT,
    Name NVARCHAR(50)
);
GO

CREATE PROCEDURE dbo.InsertMyEntities
    @MyEntities dbo.MyUserDefinedTableType READONLY
AS
BEGIN
    SET NOCOUNT ON;

INSERT INTO dbo.TargetTable (Id, Name)
    SELECT Id, Name
    FROM @MyEntities;

--Optionally, return the number of rows affected
    SELECT @@ROWCOUNT AS RowsAffected;
END
GO

Stored procedure

This stored procedure InsertMyEntities takes one parameters:

@MyEntities - This parameter is of the user-defined table type MyUserDefinedTableType which represents the list of entities to be inserted.

Within the stored procedure, you can see an example of how to process the data in @MyEntities. In this case, the stored procedure inserts the data from the table-valued parameter @MyEntities into a target table named TargetTable. You can adjust this logic based on your specific requirements, such as inserting into multiple tables, performing additional processing, or applying business rules.

After the insertion operation, the stored procedure optionally returns the number of rows affected using SELECT @@ROWCOUNT AS RowsAffected;. This can be useful for tracking the success of the operation.

Conclusion

Bulk insertion in ADO.NET is a powerful technique for optimizing database performance, especially when dealing with large datasets. By leveraging bulk insertion techniques, developers can significantly enhance application scalability, resource efficiency, and overall system performance. Incorporating bulk insertion into your database operations arsenal can lead to faster, more responsive applications and happier end-users. So why wait? Start harnessing the power of bulk insertion today and supercharge your database operations!