How Can I Efficiently Bulk Insert Parameterized Data into SQL Server Using C#?

Optimizing Bulk Data Insertion into SQL Server with C#

Handling large datasets often necessitates efficient database insertion techniques. When dealing with web-sourced data, parameterized queries are crucial for security and performance. This article demonstrates a superior method to the common, inefficient row-by-row approach.

Addressing Inefficient Single-Row Inserts

Iterative insertion using individual INSERT statements (e.g., INSERT INTO DATABASE('web',@data)) is slow and vulnerable to SQL injection.

The Solution: Table-Valued Parameters (TVPs)

SQL Server 2008 and later versions support Table-Valued Parameters (TVPs), offering a highly efficient method for bulk insertion while maintaining parameterization for security.

1. Defining a User-Defined Table Type (UDT)

Begin by creating a UDT that mirrors the structure of your target database table:

<code class="language-sql">CREATE TYPE MyTableType AS TABLE
(
    Col1 int,
    Col2 varchar(20)
)
GO</code>

2. Creating a Stored Procedure with a TVP

Next, create a stored procedure accepting the TVP:

<code class="language-sql">CREATE PROCEDURE MyProcedure
(
    @MyTable dbo.MyTableType READONLY -- READONLY is crucial for TVPs!
)
AS

INSERT INTO MyTable (Col1, Col2)
SELECT Col1, Col2
FROM @MyTable

GO</code>

3. C# Implementation using the TVP

The C# code utilizes a DataTable to hold the data before insertion:

<code class="language-csharp">DataTable dt = new DataTable();
dt.Columns.Add("Col1", typeof(int));
dt.Columns.Add("Col2", typeof(string));

// Populate the DataTable with your data here

using (var con = new SqlConnection("ConnectionString"))
{
    using (var cmd = new SqlCommand("MyProcedure", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@MyTable", dt); // Simplified parameter addition
        con.Open();
        cmd.ExecuteNonQuery();
    }
}</code>

This approach leverages TVPs for significantly faster and more secure bulk data insertion compared to individual INSERT statements. The use of AddWithValue simplifies parameter handling. Remember to replace "ConnectionString" with your actual connection string.

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