参数化插入多行 [英] Parameterize insert of multiple rows
问题描述
有什么方法可以参数化插入多行的 SQL INSERT 语句(在 C# 中)?目前我只能想到一种方法，生成用于插入多行的语句，但这对 SQL 注入非常开放:
string sql = " INSERT INTO my_table"+ " (a, b, c)"+ "值";//将每行值添加到语句中foreach(集合中的var项目){sql = sql+ String.Format(" ({0}, {1}, {2}),",aVal、bVal、cVal)；}//去除多余的逗号sql = sql.Remove(sql.Length - 1);更聪明/更安全的方法是什么?
您可以在循环中添加参数，例如:
using (var comm = new SqlCommand()) {无功计数器 = 0;foreach(集合中的变量项目){sql = sql + String.Format(" (@a{0}, @b{0}, @c{0})," 计数器);comm.Parameters.AddWithValue("@a" + counter, aVal);comm.Parameters.AddWithValue("@b" + counter, bVal);comm.Parameters.AddWithValue("@c" + counter, cVal);计数器++；}}但我真的不会做这样的多行插入.IIRC 查询中的最大参数数量约为 2100，这可能会很快变得非常大.无论如何，当您遍历集合时，您可以将其发送到循环中的数据库，例如:
using (var con = new SqlConnection("connectionString here")){con.Open();var sql = "INSERT INTO my_table (a, b, c) VALUES (@a,@b,@c);"使用 (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(集合中的变量项目){{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 文档方法表值参数
关于 TVP 的博客中的肉"(在 VB.NET 中，但这应该无关紧要):
<块引用>所以我创建了这个通用"表值类型:
CREATE TYPE dbo.UniqueIntegerList AS TABLE(TheInteger INT NOT NULL主键(整数));<块引用>
创建保存存储过程
接下来，我创建了一个新的存储过程，它将接受我的新存储过程表值类型作为参数.
CREATE PROC DoTableValuedParameterInsert(@ProductIDsdbo.UniqueIntegerList 只读)开始时INSERT INTO ProductsAccess(ProductID)SELECT TheInteger AS [ProductID]从@ProductIDs;结尾<块引用>
在这个过程中，我传入了一个名为@ProductIDs 的参数.这是我刚刚在前一步.SQL Server 看着这个并说哦，我知道这是什么是 - 这种类型实际上是一个表".因为它知道UniqueIntegerList 类型是一个表，我可以像我一样从中选择可以从任何其他表值变量中进行选择.你必须标记参数为 READONLY 因为 SQL 2008 不支持更新并返回一个传递的表值参数.
创建保存程序
然后我必须在我的业务对象上创建一个新的保存例程将调用新的存储过程.你准备的方式Table-Valued 参数是创建一个具有相同属性的DataTable对象列签名作为表值类型，填充它，然后通过它在 SqlParameter 对象中作为 SqlDbType.Structured.
Public Sub SaveViaTableValuedParameter()'准备表值参数'Dim objUniqueIntegerList 作为新数据表Dim objColumn 作为 DataColumn =objUniqueIntegerList.Columns.Add("TheInteger", _System.Type.GetType("System.Int32"))objColumn.Unique = True'用要保存的数据填充表值参数'对于在 Me.Values 中作为产品的每个项目objUniqueIntegerList.Rows.Add(Item.ProductID)下一个'连接到数据库并保存它.使用 objConn 作为新的 SqlConnection(DBConnectionString())objConn.Open()使用 objCmd 作为新的 SqlCommand("dbo.DoTableValuedParameterInsert")objCmd.CommandType = CommandType.StoredProcedureobjCmd.Connection = objConnobjCmd.Parameters.Add("ProductIDs", SqlDbType.Structured)objCmd.Parameters(0).Value = objUniqueIntegerListobjCmd.ExecuteNonQuery()结束使用objConn.Close()结束使用结束子Is there any way to parameterize an SQL INSERT statement (in C#), which inserts multiple rows? Currently I can think of only one way, to generate a statement for inserting mulitple rows, but that is quite open to SQL injection:
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);
What is the smarter/safer way to do this?
You could add paramaters inside the loop, like:
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++;
}
}
But I really wouldn't do a multi-row insert like this. IIRC the maximum amount of parameters in a query is about 2100, and this could get very big very fast. As you're looping through a collection anyway, you could just send it to the database in your loop, something like:
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();
}
}
}
The statement is prepared only once (and faster than a huge statement with 100's of parameters), and it doesn't fail all records when one record fails (add some exception handling for that). If you want to fail all when one record fails, you could wrap the thing up in a transaction.
Edit: Ofcourse, when you regularly have to input 1000's of rows, this approach isn't the most efficient either, and your DBA might start to complain. There are other approaches to this problem to remove the strain from the database: for example, create a stored procedure in your database that will insert the data from an xml document, or use Table Valued Parameters. NYCdotNet wrote 2 nice blogs about these options, which I won't recreate here, but they're worth exploring (I'll paste some code below from the blog, as per guidelines, but credit where it's due: NYCdotNet) XML document approach Table Valued Parameters
The "meat" from the blog about TVP (in VB.NET but that shouldn't matter):
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
这篇关于参数化插入多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
