How Can C# Efficiently Bulk Insert Parameterized Data into a Database?

Bulk Insert Parameterized Variables into Database using C#

Inserting numerous parametrized variables into a database efficiently is a common challenge faced by developers. C# offers a solution using table-valued parameters and stored procedures to insert multiple rows in a single query.

Creating the User-Defined Table Type

Begin by creating a user-defined table type that mirrors the structure of the target database table:

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

Defining the Stored Procedure

Next, create a stored procedure that accepts the newly created table type as an input 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

GO

This stored procedure will insert the data from the @MyTable table type into the MyTable table in the database.

Executing the Stored Procedure from C#

Finally, execute the stored procedure from C# using the following code:

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

Advantages of Using Table-Valued Parameters

Using table-valued parameters offers several advantages:

• Efficient: A single query replaces multiple insert statements, significantly improving performance.
• Parameterized: Data is passed in a parameterized manner, reducing the risk of SQL injection attacks.
• Extensible: The user-defined table type can be modified to match different scenarios without altering the stored procedure.

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