C#-参数化多行插入

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了C#-参数化多行插入，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4199字，纯文字阅读大概需要6分钟。

内容图文

有什么方法可以参数化插入多行的SQL INSERT语句(在C#中)？目前,我只能想到一种方法来生成用于插入多行的语句,但这对于SQL注入是相当开放的：

string sql = " INSERT INTO  my_table"
           + " (a, b, c)"
           + " VALUES";

// Add each row of values to the statement
foreach (var item in collection) {
    sql = sql
        + String.Format(" ({0}, {1}, {2}),",
              aVal, bVal, cVal);
}

// Remove the excessive comma
sql = sql.Remove(sql.Length - 1);

什么是更聪明/更安全的方法？

解决方法:

您可以在循环内添加参数,例如：

using (var comm = new SqlCommand()) {
        var counter = 0;
         foreach (var item in collection) {
            sql = sql + String.Format(" (@a{0}, @b{0}, @c{0}),"  counter);

            comm.Parameters.AddWithValue("@a" + counter, aVal); 
            comm.Parameters.AddWithValue("@b" + counter, bVal);
            comm.Parameters.AddWithValue("@c" + counter, cVal);
            counter++;
        }
    }

但是我真的不会像这样进行多行插入. IIRC查询中的最大参数数量约为2100,并且这可能很快变得非常大.无论如何,当您遍历一个集合时,您可以将其发送到循环中的数据库中,例如：

using (var con = new SqlConnection("connectionString here"))
{
   con.Open();
   var sql = "INSERT INTO  my_table (a, b, c) VALUES (@a,@b,@c);"

   using (var comm = new SqlCommand(sql, con))
   {
       comm.Parameters.Add("@a", SqlDbType.Int);
       comm.Parameters.Add("@b", SqlDbType.NVarChar);
       comm.Parameters.Add("@c", SqlDbType.Int);
       foreach (var item in collection) {
       {
           comm.Parameters["@a"].Value = aVal;
           comm.Parameters["@b"].Value = bVal;
           comm.Parameters["@b"].Size = bVal.Length;
           comm.Parameters["@c"].Value = cVal;

           comm.ExecuteNonQuery();
       }
   }
}

该语句仅准备一次(并且比具有100个参数的巨大语句要快),并且当一条记录失败时,它不会使所有记录失败(为此添加一些异常处理).如果您想在一条记录失败时全部失败,则可以将其包装在事务中.

编辑：
当然,当您通常必须输入1000行时,这种方法也不是最有效的,您的DBA可能会开始抱怨.
还有其他方法可以解决此问题,以消除数据库中的压力：例如,在数据库中创建一个存储过程,该存储过程将插入xml文档中的数据,或者使用表值参数.
NYCdotNet写了2个关于这些选项的不错的博客,我不会在这里重新创建,但是值得探讨(按照指南,我将从博客下面粘贴一些代码,但要注明到期日期：NYCdotNet)
XML document approach
Table Valued Parameters

关于TVP的博客中的“肉”(在VB.NET中,但这无关紧要)：

So I created this “generic” table-valued type:

 CREATE TYPE dbo.UniqueIntegerList AS TABLE

 (

        TheInteger INT NOT NULL

     PRIMARY KEY (TheInteger)

 );

Creating the Save Stored Procedure

Next, I created a new stored procedure which would accept my new
Table-Valued Type as a parameter.

 CREATE PROC DoTableValuedParameterInsert(@ProductIDs
 dbo.UniqueIntegerList READONLY)

 AS BEGIN



        INSERT INTO ProductsAccess(ProductID)

        SELECT TheInteger AS [ProductID]

        FROM @ProductIDs;



 END

In this procedure, I am passing in a parameter called @ProductIDs.
This is of type “dbo.UniqueIntegerList” which I just created in the
previous step. SQL Server looks at this and says “oh I know what this
is – this type is actually a table”. Since it knows that the
UniqueIntegerList type is a table, I can select from it just like I
could select from any other table-valued variable. You have to mark
the parameter as READONLY because SQL 2008 doesn’t support updating
and returning a passed table-valued parameter.

Creating the Save Routine

Then I had to create a new save routine on my business object that
would call the new stored procedure. The way you prepare the
Table-Valued parameter is to create a DataTable object with the same
column signature as the Table-Valued type, populate it, and then pass
it inside a SqlParameter object as SqlDbType.Structured.

 Public Sub SaveViaTableValuedParameter()



   'Prepare the Table-valued Parameter'

  Dim objUniqueIntegerList As New DataTable

  Dim objColumn As DataColumn =
  objUniqueIntegerList.Columns.Add("TheInteger", _

  System.Type.GetType("System.Int32"))

   objColumn.Unique = True



   'Populate the Table-valued Parameter with the data to save'

   For Each Item As Product In Me.Values

     objUniqueIntegerList.Rows.Add(Item.ProductID)

   Next



   'Connect to the DB and save it.'

   Using objConn As New SqlConnection(DBConnectionString())

     objConn.Open()

     Using objCmd As New SqlCommand("dbo.DoTableValuedParameterInsert")

       objCmd.CommandType = CommandType.StoredProcedure

       objCmd.Connection = objConn

       objCmd.Parameters.Add("ProductIDs", SqlDbType.Structured)



       objCmd.Parameters(0).Value = objUniqueIntegerList



       objCmd.ExecuteNonQuery()

     End Using

     objConn.Close()

   End Using

 End Sub

内容总结

以上是互联网集市为您收集整理的C#-参数化多行插入全部内容，希望文章能够帮你解决C#-参数化多行插入所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端