How Can I Efficiently Insert Multiple Rows with Parameterized Queries in C#?

Inserting Multiple Parameterized Rows with a Single Query in C#

Parameterized queries allow developers to execute SQL statements without hard-coding values, preventing SQL injection attacks and improving performance. Inserting multiple rows with parameterized values can be efficient, especially when using a larger number of rows.

In the provided code, each row is inserted individually, which can be slow. To address this, you can take advantage of table-valued parameters with SQL Server 2008 and above.

Creating a Table-Valued Parameter

First, create a user-defined table type that defines the columns of your data:

CREATE TYPE MyTableType AS TABLE
(
    Col1 int,
    Col2 varchar(20)
)

Creating a Stored Procedure

Next, create a stored procedure that accepts the table-valued parameter:

CREATE PROCEDURE MyProcedure
(
    @MyTable dbo.MyTableType READONLY 
)
AS

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

GO

Note that the @MyTable parameter is marked as READONLY because table-valued parameters must be read-only.

Executing the Stored Procedure with a DataTable

Finally, execute the stored procedure from C# using a DataTable:

DataTable dt = new DataTable();
dt.Columns.Add("Col1", typeof(int));
dt.Columns.Add("Col2", typeof(string));

// Fill your data table here

using (var con = new SqlConnection("ConnectionString"))
{
    using(var cmd = new SqlCommand("MyProcedure", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@MyTable", SqlDbType.Structured).Value = dt;
        con.Open();
        cmd.ExecuteNonQuery();
    }
}

By using table-valued parameters, you can insert multiple rows with a single parameterized query, significantly improving performance.

The above is the detailed content of How Can I Efficiently Insert Multiple Rows with Parameterized Queries in C#?. For more information, please follow other related articles on the PHP Chinese website!