How Can I Perform Efficient Parameterized Bulk Inserts in C# and SQL Server?

Performing Bulk Inserts with Parameterized Variables in C#

In database operations, it's often desirable to insert multiple parameterized variables into a database, especially when the database is receiving data from a web application. To optimize such operations and avoid repetition, one can utilize a stored procedure and a table-valued parameter.

To achieve this in C#, follow these steps:

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

   • Define a UDT to match the structure of the data you want to insert. For example:

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

2. Create a Stored Procedure with Table-Valued Parameter:

   • Create a stored procedure that accepts the UDT as a parameter. The procedure will insert the data into the target database table:

     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

3. Populate a DataTable:

   • Initialize a DataTable with column definitions matching the UDT and populate it with your data.

4. Execute the Stored Procedure with Parameters:

   • Establish a connection to the database and create a command object for the stored procedure.
   • Add the populated DataTable to the command parameters as a structured type parameter.

   • Execute the command to perform the bulk insert.

     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 following these steps, you can efficiently perform parameterized bulk inserts to a SQL Server database using C# code. This approach not only prevents repetition but also optimizes database performance by executing a single query for multiple rows.

The above is the detailed content of How Can I Perform Efficient Parameterized Bulk Inserts in C# and SQL Server?. For more information, please follow other related articles on the PHP Chinese website!