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

Using table-valued parameters to implement C# batch parameterized row insertion

In database operations, it is a common requirement to insert multiple rows of data efficiently while ensuring data security. In C#, this can be achieved using table-valued parameters and stored procedures.

In order to avoid repeated use of "INSERT INTO" statements and insert parameterized data rows in batches, please follow the steps below:

1. Create user-defined table type (UDT): Define a table structure to represent the rows to be inserted. For example:

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

2. Create stored procedure: Create a stored procedure that will accept a UDT as a parameter.

   <code class="language-sql">CREATE PROCEDURE MyProcedure
   (
       @MyTable dbo.MyTableType READONLY -- 注意：表值参数必须是只读的！
   )
   AS
   
   INSERT INTO MyTable (Col1, Col2)
   SELECT Col1, Col2
   FROM @MyTable
   
   GO</code>

3. Prepare data: Create a data table and fill it with rows to be inserted.

4. Execute stored procedure: Use the SqlCommand object to execute the stored procedure and pass the data table as a parameter.

   <code class="language-csharp">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();
       }
   }</code>

With this method, you can efficiently insert multiple rows into the database while ensuring data security through parameterized queries. This avoids duplicate and potentially inefficient individual insert statements.

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