How to Efficiently Insert Multiple Rows with Parameterized Variables in C# Using Table-Valued Parameters?

Leveraging Table-Valued Parameters for Bulk Data Insertion in C#

This guide demonstrates how to efficiently insert multiple rows into a SQL Server database (2008 or later) using a single parameterized query in C#. This technique, employing table-valued parameters, significantly improves performance over individual insert statements.

Defining the User-Defined Table Type

Begin by creating a user-defined table type in SQL Server to represent the data structure for your insertion:

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

Stored Procedure Creation

Next, create a stored procedure that accepts this user-defined table type as a parameter:

<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: Data Table and Stored Procedure Execution

In your C# code, construct a DataTable to hold the data you intend to insert:

<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...  For example:
DataRow row1 = dt.NewRow();
row1["Col1"] = 1;
row1["Col2"] = "Value 1";
dt.Rows.Add(row1);

// ...add more rows as needed...</code>

Finally, execute the stored procedure using SqlCommand:

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

This method provides a highly efficient way to insert multiple rows with parameterized values, preventing SQL injection vulnerabilities and boosting database performance compared to individual INSERT statements. Remember to replace "YourConnectionString" with your actual connection string.

The above is the detailed content of How to Efficiently Insert Multiple Rows with Parameterized Variables in C# Using Table-Valued Parameters?. For more information, please follow other related articles on the PHP Chinese website!