C#-SQL：如何执行一批 StoredProcedure？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/200986/
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
C#-SQL: How to execute a batch of StoredProcedure?
提问by Olivier de Rivoyre
Edit:
My problem is not a problem anymore: I have redo my performances tests and I have do a fatal stupid error: I had forget a x1000 to get seconds from milliseconds :/
Sorry for that guys.
For info:
- I do some 1900 updates per second from my PC to the DataBase server on local network.
- 3.200 updates per second if the programs is on same machine than DB.
- 3.500 updates per second from my PC on the DataBase server I do not re-create and re-open a new SQLConnection.
- 5.800 updates per second with a batch text.
For my 10.000 rows, if it take 5 seconds, it is ok for my programs. Sorry to have worry you.
编辑：
我的问题不再是问题：我已经重做我的性能测试，我犯了一个致命的愚蠢错误：我忘记了 x1000 从毫秒中获取秒数：/对不起那些人。
有关信息：
- 我每秒从我的 PC 到本地网络上的数据库服务器执行大约 1900 次更新。
- 如果程序与 DB 位于同一台机器上，则每秒更新 3.200 次。
- 每秒 3.500 次更新从我的 PC 上的数据库服务器上我没有重新创建和重新打开一个新的 SQLConnection。
- 批处理文本每秒 5.800 次更新。对于我的 10.000 行，如果需要 5 秒，我的程序就可以了。抱歉让你担心了。
Actually, I use a SQL stored prodedure to create a row in my database to avoid SQL-injection. In C# I have the following method:
实际上，我使用 SQL 存储过程在我的数据库中创建一行以避免 SQL 注入。在 C# 中，我有以下方法：
public void InsertUser(string userCode)
{
using (SqlConnection sqlConnection = new SqlConnection(this.connectionString))
{
SqlCommand sqlCommand = new SqlCommand("InsertUser", sqlConnection);
sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
sqlCommand.Parameters.Add(new SqlParameter("@UserCode", userCode));
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();///0.2 seconds !ERROR HERE! 0.2ms here,NOT 0.2sec!!!
}
}
It woks great when i have one or two rows to insert. But if i need to create 1.000 users and 10.000 products and 5000 pets, it is not the best solution: I will loose a huge time in netwok transport.
当我要插入一两行时，它会很棒。但是如果我需要创建 1.000 个用户和 10.000 个产品和 5000 个宠物，这不是最好的解决方案：我将在网络传输中浪费大量时间。
I believe, without checkin it, that I can use just a limited amount of callback. So I do not want to call 10.000 times:
我相信，在没有签入的情况下，我只能使用有限数量的回调。所以我不想调用 10.000 次：
sqlCommand.BeginExecuteNonQuery()
Another way will be to create a batch text, but there is a SQL-Injection risk (and it is ugly).
另一种方法是创建批处理文本，但存在 SQL 注入风险（而且很难看）。
Does there is a 'SqlCommandList' object that manage that in .Net? How do I do large writing in database? What the good patern for that?
在 .Net 中是否有一个“SqlCommandList”对象来管理它？如何在数据库中进行大写？那有什么好的模式？
回答by Danimal
Have you considered passing an XML document to a stored procedure, then iterating through that to find the data to insert?
您是否考虑过将 XML 文档传递给存储过程，然后遍历它以查找要插入的数据？
回答by Marc Gravell
Personally, if I regularly expect to do fairly large inserts (10,000 rows would definitely qualify...), I might consider having a separate table for incoming data, and use SqlBulkCopyto populate this table. Then you just execute a single stored procedure that moves the data over into the real table.
就个人而言，如果我经常期望进行相当大的插入（10,000 行肯定符合条件...），我可能会考虑为传入数据创建一个单独的表，并用于SqlBulkCopy填充该表。然后您只需执行一个存储过程，将数据移动到真实表中。
Another approach is to send down xml to the database, and use sqlxml to parse that (much easier with SQL2005 and above) - but this puts extra work on the db server.
另一种方法是将 xml 发送到数据库，并使用 sqlxml 来解析它（使用 SQL2005 及更高版本更容易） - 但这会给数据库服务器带来额外的工作。
回答by Binary Worrier
"it is not the best solution: I will loose a huge time in netwok transport" Can you live with the loss?
“这不是最好的解决方案：我会在网络传输中浪费大量时间”你能忍受这种损失吗？
If this is something you don't do often, then does it matter? Measure it first, if it's a problem then fix it, personally probably I'd go with Marc Gravells table for incoming inserts. Another option is to fire the inserts asynchronously, then you're not waiting on each to finish before you start the next.
如果这是你不经常做的事情，那么这很重要吗？首先测量它，如果它是一个问题然后修复它，我个人可能会使用 Marc Gravells 表来进行插入。另一种选择是异步触发插入，然后在开始下一个之前您不必等待每个完成。
It took me years, but finally I figured out that I shouldn't waste time optimising code that doesn't need it.
我花了很多年，但最终我发现我不应该浪费时间优化不需要它的代码。
Hope this helps (even though I don't think it will, sorry).
希望这会有所帮助（即使我认为不会，抱歉）。
回答by Joel Coehoorn
This should run a little faster:
这应该运行得更快一点：
public void InsertUser(IEnumerable<string> userCodes)
{
using (SqlConnection sqlConnection = new SqlConnection(this.connectionString),
SqlCommand sqlCommand = new SqlCommand("InsertUser", sqlConnection))
{
sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameter param = sqlCommand.Parameters.Add("@UserCode", SqlDbTypes.VarChar);
sqlConnection.Open();
foreach(string code in userCodes)
{
param.Value = code;
sqlCommand.ExecuteNonQuery();///0.2 seconds
}
}
}
That will only open one connection and only create one command, even if you pass it 1000 users. It will still do each insert separately, though. And of course if userCode isn't a string you'll want to re-factor it appropriately. You may also want to look into SQL Server's BULK INSERTcommand.
这只会打开一个连接并只创建一个命令，即使您传递给它 1000 个用户。但是，它仍然会单独执行每个插入。当然，如果 userCode 不是字符串，您将需要适当地重构它。您可能还想查看 SQL Server 的BULK INSERT命令。
回答by Dave Markle
If you really were concerned about this, you could (like you said) batch the commands up in strings like so:
如果您真的很担心这一点，您可以（如您所说）将命令以字符串形式批量处理，如下所示：
var cmd = new SqlCommand();
cmd.Connection = sqlConnection;
for (int i = 0; i < batchSize; i++) {
cmd.CommandText += String.Format("EXEC InsertUser @UserCode{0};", i);
cmd.Parameters.AddWithValue("@UserCode" + i.ToString(), XXXXX);
//... etc ...
}
Because in this scheme, you'd be using a parameter, you don't have more risk of SQL injection than if you used a stored proc. But I question whether or not you'll really save an appreciable amount of time doing this. IMO you should just keep it simple and do it the way you are doing it now.
因为在这个方案中，您将使用参数，与使用存储过程相比，您不会有更多的 SQL 注入风险。但我怀疑您是否真的会节省大量时间。IMO，您应该保持简单，并按照您现在的方式进行操作。
回答by Sam Saffron
Based off Joel's answer, this is the fastest solution short of using either SqlBulkCopy or creating big strings of messy SQL and executing. (I added a transaction which will improve performance quite a lot)
根据乔尔的回答，这是使用 SqlBulkCopy 或创建大串凌乱 SQL 并执行的最快解决方案。（我添加了一个交易，这将大大提高性能）
public void InsertUser(IEnumerabler<string> userCodes)
{
using (SqlConnection sqlConnection = new SqlConnection(this.connectionString))
{
sqlConnection.Open();
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand sqlCommand = new SqlCommand("InsertUser", sqlConnection);
sqlCommand.Transaction = transaction;
sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameter param = sqlCommand.Parameters.Add("@UserCode", SqlDbTypes.VarChar);
foreach(string code in userCodes)
{
param.Value = code;
sqlCommand.ExecuteNonQuery();
}
transaction.Commit();
}
}
回答by Sam Saffron
As per some of the answers above, the most noticeable performance increase for the least effort involves 2 changes to your existing code:
根据上面的一些答案，以最少的努力获得最显着的性能提升涉及对现有代码的 2 处更改：
- Wrapping the updates in a transaction
- Opening only one connection and calling the procedure multiple times with the different parameters.
- 将更新包装在事务中
- 仅打开一个连接并使用不同的参数多次调用该过程。
BULK INSERTs are an option but probably overkill for what you want to do.
BULK INSERTs 是一种选择，但对于您想要做的事情来说可能有点矫枉过正。
回答by gbn
What about UpdateBatchSize of the SQLDataAdaptor?
SQLDataAdaptor 的 UpdateBatchSize 怎么样？
Our front end guys use this to batch a few 10,000 proc calls into chunks
我们的前端人员使用它将几个 10,000 个 proc 调用批处理成块
Our environment disallows "bulkadmin" rights so we can't use BULKINSERT/bcp etc
我们的环境不允许“bulkadmin”权限，因此我们不能使用 BULKINSERT/bcp 等
回答by Ben Taylor
I'm guessing this is a pretty old question.
我猜这是一个很老的问题。
With SQL Server 2008 the answer now is to use a Table Value Parameter. In short, pass in all your variables in a used defined type (table).
对于 SQL Server 2008，现在的答案是使用表值参数。简而言之，在使用的定义类型（表）中传递所有变量。
In SQL, you can now process all of the records as individual items...Actually use set logic and get real performance.
在 SQL 中，您现在可以将所有记录作为单个项目进行处理...实际上使用集合逻辑并获得真正的性能。
