How Can I Efficiently Bulk Insert Data with Parameterized Queries in C#?

Bulk Inserting Parameterized Variables into a Database in C

In C#, inserting multiple rows into a database can be a time-consuming process if each row requires a separate INSERT statement. To optimize performance, it is desirable to insert multiple rows in a single query while also utilizing parameterized queries to prevent SQL injection attacks.

To achieve this, you can leverage the power of table-valued parameters, which allow you to pass a structured data type (such as a DataTable) to a stored procedure. This enables you to insert multiple rows with a single command.

To implement this approach, follow these steps:

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

Create a UDT to represent the rows you want to insert. For example, if you have columns "Col1" and "Col2", you can create a UDT as follows:

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

2. Create a Stored Procedure

Create a stored procedure to accept the UDT as a parameter:

CREATE PROCEDURE MyProcedure
(
    @MyTable dbo.MyTableType READONLY -- Table valued parameters must be ReadOnly!
)
AS

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

GO

3. Execute the Stored Procedure from C

Populate a DataTable with your data:

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

// Populate data

Open a connection and execute the stored procedure using a SqlCommand:

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 utilizing table-valued parameters and stored procedures, you can significantly improve the performance of bulk inserts, while also maintaining the security and efficiency of parameterized queries.

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