C＃中执行多个SQL语句 [英] C# Execute Multiple SQL Statements
问题描述
我有一个存储更新语句一类。我不能我并行循环中执行这些语句，因为这是导致死锁。
I have a class that stores update statements. I cant execute these statements within my parallel for loop, as this is causing dead locks.
的语句执行一次循环完成后，在100行，它的工作好了的场景。但是，某些情况下产生了
The statements are executed once the loop is completed, in a scenario for 100 rows it works okay. But certain scenarios generate over 100,000 statements. Executing these statements on a sequential loop takes too long.
我想实现的是，在增加100语句添加到类，我执行一个单独的线程这些声明，并明确声明变量，从而使未来100可以加入并执行。
What I wish to achieve is, upon adding 100 statements to the class, I execute these statements on a separate thread, and clear the statements variable, so that the next 100 can be added and executed.
我是新来的多线程。这是可以实现的？如果其他选项我用不是减少执行时间。请注意，我不能改变的陈述逻辑，因为有连接到它的许多其他因素。
I am new to multi threading. Is this achievable? If not what other option do i use to reduce the time for execution. Note that I can't change the statement logic as there are many other factors attached to it.
让我进一步明确了我的方案。这表我有更持续跟踪SQL行transferd给客户，这样我就不会重新发送相同的对象两次记录表。这是为了减少带宽使用作为对象transferd通过互联网链路。
Let me clarify my scenario further. That table i have is more of a log table that keeps track of sql rows transferd to clients, so that i do not resend the same object twice. This is to reduce bandwidth usage as the objects are transferd over internet link.
Initialy我是尽快执行每个语句，我得到了来自客户端的答复。这个工作很大，当循环是连续的，但被证明是过于缓慢。所以我选择使用并行for循环。这是所有的问题上来作为同桌正在从选择，插入和更新几乎在同一时间造成死锁。
Initialy i was executing each statement as soon as i got a reply from the client. This worked great when the loops were sequential, but proved to be too slow. So i opted to use parallel for loops. This is where all issues came up as the same table was being selected from, inserted and updated at virtually the same time causing dead lock.
所以，我决定继续在字符串列表中的语句后执行。
So i decided to keep the statements in a list of strings to execute later.
我试图将所有字符串成一个字符串中使用的string.join但是这给了我的系统内存溢出异常。从而由一个顺序1执行它们。现在转让需要5分钟，并执行30分钟。所以我要寻找一个解决这个..
I tried converting all strings into a single string using string.join but this gave me system out of memory exception. Thus executing them sequentially one by one. Now the transfer takes 5 minutes and the execution 30 minutes. So i am looking for a solution to this..
推荐答案
因为它听起来像你时间可持续不断得到新的东西插入到你的数据库，你可以使用的SqlTransaction ，在其上运行您的语句时，他们都可用。然后，一旦在一段时间你提交事务。
Since it sounds like you continuosly keep getting new stuff to insert into your database, you can use a SqlTransaction, on which you execute your statements whenever they are available. Then, once in a while you commit the transaction.
检查 MSDN 中的例子如何使用它。
Check MSDN for an example on how to use it.
编辑：如果您在另一方面得到了很多的语句在一个堆来执行，使用SqlBulkCopy的如果可能的话，像LoztInSpace说：
If you on the other hand get a lot of statements to execute in one heap, use SqlBulkCopy if possible, like LoztInSpace says.
这篇关于C＃中执行多个SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
