How Can I Efficiently Insert Multiple Rows into a SQL Server 2008 Database using Parameterized Table-Valued Parameters and C#?

Optimizing Bulk Data Insertion in SQL Server 2008 with C# and Table-Valued Parameters

Inserting numerous rows individually into a SQL Server 2008 database can significantly impact performance. This article demonstrates a more efficient approach using parameterized table-valued parameters and C#.

Defining a User-Defined Table Type

Begin by creating a user-defined table type in SQL Server that mirrors the structure of your target table:

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

Creating the Stored Procedure

Next, create a stored procedure that accepts this table type as input and performs the bulk insertion:

<code class="language-sql">CREATE PROCEDURE MyProcedure
(
    @MyTable dbo.MyTableType READONLY -- Readonly is crucial for table-valued parameters
)
AS
BEGIN
    INSERT INTO MyTable (Col1, Col2)
    SELECT Col1, Col2 
    FROM @MyTable;
END;
GO</code>

C# Implementation for Data Insertion

Finally, use C# to populate a DataTable and pass it as a table-valued parameter to the stored procedure:

<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

using (SqlConnection con = new SqlConnection("ConnectionString"))
{
    using (SqlCommand cmd = new SqlCommand("MyProcedure", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@MyTable", dt); // AddWith Value handles SqlDbType automatically
        con.Open();
        cmd.ExecuteNonQuery();
    }
}</code>

This method leverages the power of table-valued parameters to significantly improve the efficiency of inserting multiple rows into your SQL Server 2008 database from your C# application. This approach offers superior performance compared to individual row insertions.

The above is the detailed content of How Can I Efficiently Insert Multiple Rows into a SQL Server 2008 Database using Parameterized Table-Valued Parameters and C#?. For more information, please follow other related articles on the PHP Chinese website!