C# 将多行更新到 SQL 表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/184096/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update multiple rows into SQL table
提问by Chris Cudmore
Suppose there is a fully populated array of data String[n][3] myData.
假设有一个完全填充的数据 String[n][3] myData 数组。
I want to do this:
我想做这个：
for (String[] row : myData)
{
SQL = "update mytable set col3 = row[2]
where col1 = row[0] and col2=row[1];"
}
Obviously I've left a lot out, but I want to express the idea as succinctly as possible.
显然我遗漏了很多，但我想尽可能简洁地表达这个想法。
Is there a simple way of doing this in a single DB command? How about a not so simple way?
在单个 DB 命令中是否有一种简单的方法可以做到这一点？一个不那么简单的方法怎么样？
EDITS:
Data is not coming from another table (it's a web form submission - Multiple Choice exam)
Seeing as the app is web facing, It's got to be injection proof. Parameterized Queries are my preferred way of going.
I'm using MS-SQL Server 2005
编辑：数据不是来自另一个表（这是一个网络表单提交 - 多项选择考试）
鉴于该应用程序是面向网络的，它必须是注入证明。参数化查询是我的首选方式。
我使用的是 MS-SQL Server 2005
EDIT:Closing, and re-asking as Multiple DB Updates:
编辑：关闭，并重新询问多个数据库更新：
EDIT: Re-opened, as this appears to be a popular question
编辑：重新打开，因为这似乎是一个流行的问题
采纳答案by Ted Elliott
If you are using Sql Server you can use SqlBulkCopy. You would first have to put your data in a DataTable, which would be pretty easy since you already have it in a string array.
如果您使用的是 Sql Server，则可以使用 SqlBulkCopy。您首先必须将数据放在 DataTable 中，这很容易，因为您已经将它放在一个字符串数组中。
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx
回答by fmsf
You can make a big string like:
您可以制作一个大字符串，例如：
for (String[] row : myData)
{
SQL += "update mytable set col3 = row[2]
where col1 = row[0] and col2=row[1];"
}
sqlDriver.doInsertQuery(SQL); // change this to your way of inserting into the db
And just commit it all at once. I'm not very good with SQL so that's how i would do it.
并且一次性全部提交。我对 SQL 不太擅长，所以我会这样做。
The sql engine will just split it by the ';' and do separate inserts on its own. It's ok to add it all in a string though. It's kind the same as if u copy a big string with multiple updates/inserts into the sql prompt
sql 引擎只会用“;”分割它 并单独插入。虽然可以将其全部添加到字符串中。这就像你将一个包含多个更新/插入的大字符串复制到 sql 提示符中一样
回答by EndangeredMassa
Not really. You could create the string with the same loop, then pass your values as parameters, but that will still be multiple database commands.
并不真地。您可以使用相同的循环创建字符串，然后将您的值作为参数传递，但这仍然是多个数据库命令。
for each whatever
sql += "UPDATE ... ;"
end for
execute (sql)
回答by extraneon
This may not be the answer you want, but opening a transaction, executing your statements and then committing the transaction would, from a database point of view, do what you describe.
这可能不是您想要的答案，但从数据库的角度来看，打开一个事务、执行您的语句然后提交该事务将执行您所描述的操作。
The state of the database does not change for other users of the database until the transaction has been completed, and that probably is the preferred effect.
在事务完成之前，数据库的其他用户的数据库状态不会更改，这可能是首选效果。
回答by theraccoonbear
I suspect you will need to use multiple SQL statements. You may find a wrapper to handle the minutiae but underneath I'd imagine it'd iteratively run a SQL statement for each UPDATE.
我怀疑您将需要使用多个 SQL 语句。您可能会找到一个包装器来处理细节，但在下面我想它会为每个 UPDATE 迭代运行 SQL 语句。
回答by Kevin Berridge
It depends on what database you are using. If you're using SQL Server 2008, you can use stored procedure TABLE parameters. This allows you to pass all your values into the stored procedure in a single table, then you can do:
这取决于您使用的数据库。如果您使用的是 SQL Server 2008，则可以使用存储过程TABLE 参数。这允许您将所有值传递到单个表中的存储过程中，然后您可以执行以下操作：
update mytable set mytable.col1 = @tbl.col1
from mytable
inner join @tbl on mytable.col2 = @tbl.col2
If you're using SQL Server 2005, you can use XML. Format your values as XML, then use XQuery statements (ie, 'nodes' and 'value') to parse out the XML. This can also be done in a single SQL statement, and it doesn't require a stored procedure.
如果您使用的是 SQL Server 2005，则可以使用 XML。将值格式化为 XML，然后使用 XQuery 语句（即“节点”和“值”）解析 XML。这也可以在单个 SQL 语句中完成，并且不需要存储过程。
回答by helios
That looks like you want to make an update A, over rows that has coditions B and C. (A, B, C) are stored as tuples (rows) in myData. Isn't it?
看起来您想对具有编码 B 和 C 的行进行更新 A。（A、B、C）作为元组（行）存储在 myData 中。不是吗？
Maybe (if you're using Microsoft SQL Server... I don't know if it exists in Oracle, could be) you can use a JOIN with an UPDATE. You can declare an update over a table joined with another one. If myData comes from another table then you could do (it's not the correct syntax) :
也许（如果您使用的是 Microsoft SQL Server...我不知道它是否存在于 Oracle 中，可能是）您可以将 JOIN 与 UPDATE 一起使用。您可以对与另一个表相连的表声明更新。如果 myData 来自另一个表，那么您可以这样做（这不是正确的语法）：
UPDATE whatchanges wc INNER JOIN changes c ON <yourcondition>
SET wc.col1 = c.newvalue
WHERE ....
(if you want to apply all changes in "changes" table you don't have to use WHERE of course, the INNER JOIN already has selected the correct rows).
（如果要应用“更改”表中的所有更改，当然不必使用 WHERE，INNER JOIN 已经选择了正确的行）。
Of course there are limitations to this kind of update. And it's MS SQL proprietary. So if it's your case I'd suggest to look for it on MS web (keywords: UPDATE and JOIN)
当然，这种更新是有限制的。它是 MS SQL 专有的。因此，如果这是您的情况，我建议您在 MS Web 上查找它（关键字：UPDATE 和 JOIN）
回答by Rob Allen
If you are using Enterprise Libraryin your data access layer, you can create the transaction in .Net, iterate through your procedure calls, then commit/rollback all from .Net.
如果您在数据访问层中使用Enterprise Library，则可以在 .Net 中创建事务，遍历您的过程调用，然后从 .Net 提交/回滚所有内容。
DbTransaction transaction = connection.BeginTransaction();
try
{
for (String[] row : myData)
{
ListDictionary params = new Specialized.ListDictionary();
params.add("@col3", row[2]);
params.add("@col1", row[0]);
params.add("@col2", row[1]);
executeNonQuery("myUpdateProcedure", params);
}
transaction.commit();
}
catch(Exception ex)
{
transaction.rollback();
throw ex;
}
finally
{
connection.close();
}
回答by cmsjr
If for whatever reason you can't perform the update using one of the methods suggested above, the highly inefficient approach below would probably work for you.
如果由于某种原因您无法使用上面建议的方法之一执行更新，那么下面的效率极低的方法可能适合您。
SQL = "Update myTable Set Col3 = Case "
for (String[] row : myData)
{
SQL += "When Col1 = " + Row[0] + " and Col2 = " + Row[1] + " then " + row[2] + " "
}
SQL + = "Else Col3 end"
回答by Hafthor
emit an update that goes against a values table:
发出与值表相反的更新：
UPDATE myTable SET col3=c FROM myTable JOIN (
SELECT 1 as a, 2 as b, 'value1' as c UNION ALL
SELECT 3 as a, 4 as b, 'value2' as c -- etc...
) x ON myTable.col1=x.a AND myTable.col2=x.b
so you just put this together like this:
所以你只需像这样把它放在一起：
// make one of these for each row
String.Format("SELECT {0} as a, {1} as b, '{2}' as c",
row[0], row[1], row[2].Replace("'","''"))
// put it together
string expr = "UPDATE myTable SET col3=c FROM myTable JOIN (" +
String.Join(stringformatarray, " UNION ALL ") +
") x ON myTable.col1=x.a AND myTable.col2=x.b"
or you can use StringBuilder to put this together.
或者您可以使用 StringBuilder 将它们放在一起。
and then, of course, you execute this one string.
然后，当然，你执行这个字符串。
