How Can I Efficiently Insert Multiple Parameterized Rows into a SQL Server Database from C#?

Inserting Multiple Parameterized Rows into Database in C

When working with vast datasets or frequent updates, it's crucial to optimize database operations. Inserting multiple rows into a database efficiently in C# poses a significant challenge, especially if the data is received from web sources and needs to be parameterized for security purposes.

Current Approach Limitations

The provided code snippet utilizes a loop to execute individual insert statements for each data element:

foreach(string data in Scraper){
    // ...
    Command.executeNonQuery(); 
    conn.close();
}

However, this iterative approach is inefficient and can lead to performance bottlenecks with larger datasets.

Optimized Solution Using Table Valued Parameters

For faster and more efficient data insertion in SQL Server 2008 and later, table valued parameters offer a compelling solution. By defining a user-defined table type and leveraging it in a stored procedure, you can insert multiple rows in a single operation.

Step 1: Create a User-Defined Table Type

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

Step 2: Create a Stored Procedure with Table Valued Parameter

CREATE PROCEDURE MyProcedure
(
    @MyTable dbo.MyTableType READONLY -- NOTE: table valued parameters must be Readonly!
)
AS

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

Step 3: Execute Stored Procedure from C#

DataTable dt = new DataTable();
// Fill data table with values...

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();
    }
}

Benefits of Using Table Valued Parameters

• Reduced Network Traffic: Instead of sending multiple INSERT statements, only one request is sent, containing a batch of rows.
• Improved Performance: The database engine optimizes the insertion process, leading to faster execution times.
• Enhanced Security: Parameterized queries help prevent SQL injection attacks, as the data values are passed separately from the query.

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