How Can I Efficiently Bulk Insert a DataTable into a SQL Database Using C#?

Optimizing Data Insertion with SqlBulkCopy in C#

For large datasets, inserting data row by row into a SQL Server database is inefficient. The SqlBulkCopy class in C# provides a significantly faster alternative for bulk insertion operations.

Leveraging SqlBulkCopy for Bulk Inserts

SqlBulkCopy is designed to transfer large volumes of data to a database table in a single, optimized transaction. Here's how to implement it:

<code class="language-csharp">using System.Data.SqlClient;

// Establish connection and create SqlBulkCopy object
using (var bulkCopy = new SqlBulkCopy(_connection.ConnectionString, SqlBulkCopyOptions.KeepIdentity))
{
    // Map DataTable columns to database columns
    foreach (DataColumn column in table.Columns)
    {
        bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
    }

    // Specify destination table
    bulkCopy.DestinationTableName = destinationTableName;

    // Set timeout (in seconds) to prevent timeouts on large datasets
    bulkCopy.BulkCopyTimeout = 600;

    // Perform the bulk copy operation
    bulkCopy.WriteToServer(table);
}</code>

Explanation:

• _connection.ConnectionString: Your database connection string.
• table: The DataTable containing the data to be inserted.
• destinationTableName: The name of the target table in your SQL Server database.

Important Considerations:

• Column Mapping: Ensure your DataTable column names precisely match the database column names. If they differ, adjust the ColumnMappings accordingly.
• Identity Column: SqlBulkCopyOptions.KeepIdentity preserves identity column values from your DataTable.
• Timeout: BulkCopyTimeout prevents operation timeouts, especially crucial for substantial datasets. Adjust this value as needed based on your data volume and network conditions.

This method dramatically improves database insertion speed compared to individual row insertions, significantly enhancing application performance when handling large datasets.

The above is the detailed content of How Can I Efficiently Bulk Insert a DataTable into a SQL Database Using C#?. For more information, please follow other related articles on the PHP Chinese website!