How to Efficiently Insert Multiple Parameterized Rows into a Database using C#?

Optimizing Database Inserts in C#: A Single Parameterized Query Approach

Inserting numerous rows into a database individually is inefficient. This article demonstrates a high-performance method using a single parameterized query in C# to achieve bulk insertion.

The key is to leverage stored procedures and table-valued parameters. This approach involves three main steps:

1. Defining a Table Type:

   Create a custom SQL Server table type to define the structure of your data, specifying column names and data types.

2. Creating a Stored Procedure:

   Develop a stored procedure that accepts your custom table type as a read-only, table-valued parameter. This procedure will then insert the data into your target database table. Remember, table-valued parameters must be READONLY.

3. Implementing the C# Code:

   In your C# application:

   • Create a DataTable object, mirroring the structure of your custom table type.
   • Populate this DataTable with the data you intend to insert.
   • Execute the stored procedure, passing the DataTable as the table-valued parameter.

Here's an example illustrating this process:

Table Type (SQL):

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

Stored Procedure (SQL):

<code class="language-sql">CREATE PROCEDURE MyProcedure (@MyTable MyTableType READONLY)
AS
BEGIN
    INSERT INTO MyTable (Col1, Col2)
    SELECT Col1, Col2 FROM @MyTable;
END;
GO</code>

C# Code:

<code class="language-csharp">using System.Data;
using System.Data.SqlClient;

// ... other code ...

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

// Populate dt with your data here...  e.g.,
//DataRow row = dt.NewRow();
//row["Col1"] = 1;
//row["Col2"] = "Value1";
//dt.Rows.Add(row);


using (SqlConnection con = new SqlConnection("ConnectionString"))
{
    using (SqlCommand cmd = new SqlCommand("MyProcedure", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@MyTable", dt); //AddWithValue handles type inference
        con.Open();
        cmd.ExecuteNonQuery();
    }
}</code>

This method significantly improves database insertion efficiency by reducing the number of database round trips, leading to faster and more scalable data processing.

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