ADONET异步执行-连接断开错误 [英] ADONET async execution - connection broken error

问题描述

我正在尝试执行异步查询一段时间，我的目标是执行简单的SQL语句，不要等待它们完成.以下代码适用于10、500或1000甚至5000个查询.但是对于50000个查询，突然出现错误并显示

I'm experimenting with async query execution for some time.My goal is executing simple SQL statements and dont wait them to finish. The below code works well for 10, 500 or 1000 or even 5000 queries. but for 50000 queries suddenly error comes up and says

"BeginExecuteReader需要打开且可用的连接.连接的当前状态为打开."有时会说"...状态已损坏"

"BeginExecuteReader requires an open and available Connection. The connection's current state is open." and sometimes it says "... state is: broken"

这是aspnet测试站点，我认为可能会发生50.000个查询.我错过了什么吗?它不行吗?

this is aspnet test site and I think 50.000 queries can happen. is it me missing something ? shouldn't it work ?

我使用的是Windows7 x64，我相信它与sql连接轮询限制有关.您可能会说50.000太高了，但我需要避免此错误以信任代码，而且我不知道怎么做.

I use windows7 x64 and I belive its something todo with sql connection polling limits. you'll possibly say that 50.000 is too high but I need to avoid this error to trust the code and I don't know how.

ps:在代码中，我打开了连接，但出于测试目的，并未将其关闭.如果我关闭连接回调函数，则永远不会触发.

ps: In code I open connection but don't close it for test purposes. if I close connection callback function never fires.

有什么建议吗?而且在Google上没有太多关于此错误的信息.

any suggestions ? And there are not too much information about this error on google.

Partial Class test
    Inherits System.Web.UI.Page

    Dim cnTest As SqlConnection

    Protected Sub cmdAsyncTest_Click(sender As Object, e As EventArgs) Handles cmdAsyncTest.Click

        Dim s As String = "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"
        Dim sqlstr As String
        Dim x1 As Integer, x2 As Integer, i As Integer

        sqlstr = "INSERT INTO test1 (name,surname,a2) VALUES ('" & s & "','" & s & "',5)"

        Dim cnstr As String = System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionStringLOG").ConnectionString
        cnTest = New SqlConnection(cnstr)
        cnTest.Open()

        watch = Stopwatch.StartNew()
        For i = 0 To 50000
            myExecute_Async(sqlstr)
        Next

    End Sub

    Function myExecute_Async(ByVal sqlstr As String) As String
            Using cmd As New SqlCommand(sqlstr, cnTest)
                cmd.CommandType = CommandType.Text
                cmd.BeginExecuteReader(New AsyncCallback(AddressOf QueryCallback), cmd)
                Return ""
            End Using
    End Function

    Sub QueryCallback(ByVal async As IAsyncResult)
        ' ToDo: something 
    End Sub

End Class



CREATE TABLE [dbo].[test1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NULL,
    [surname] [varchar](50) NULL,
    [a2] [int] NULL,
 CONSTRAINT [PK_test1] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

.NET SqlConnection类，连接池和重新连接逻辑"一文不是答案.我的问题是异步执行.

".NET SqlConnection class, connection pooling and reconnection logic" article is not the answer. my problem is with async execution.

我尝试使用该代码.我尝试不使用sun例程:

I tried to use that code. I tried not to use sun routines:

Dim cnstr As String = System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionStringLOG").ConnectionString
cnTest = New SqlConnection(cnstr)
cnTest.Open()

watch = Stopwatch.StartNew()

For i = 0 To 50000
    Using cmd As New SqlCommand(sqlstr, cnTest)
        '  Return "" & cmd.ExecuteNonQuery()
        cmd.CommandType = CommandType.Text
        cmd.BeginExecuteReader(New AsyncCallback(AddressOf QueryCallback), cmd)
    End Using
Next

这一次，我抛出了'System.OutOfMemoryException'类型的异常.在cmd.BeginExecuteReader行中.

this time I got Exception of type 'System.OutOfMemoryException' was thrown. at the cmd.BeginExecuteReader line.

让我说我有需要运行这50.000命令的逻辑.我应该怎么做才能避免出现内存问题或池限制?

lets say I have logic that needs to run this 50.000 commands. what should I do to avoid memory problems or pooling limits ?

推荐答案

对于那些尝试处理繁重的事务或对主题感兴趣的人:

For those who tries to work with heavy transaction load or interested in topic:

最好的选择是篝火.我也试验了线程，它可以工作，但是我发现我们的hangfire更加简单，您不必担心Web池重新启动，任何事情都可以阻止IIS服务，例如错误...

Best choice would be hangfire. I experimented threads also, it works but I figured our that hangfire is much more simple and you don't need to concern about web pool restarts, anything could stops IIS services like errors...

我调用了一个将10.000条记录插入sql服务器的类，并在for..next中对其进行了100次调用.肯定花了很长时间，但它像一种魅力一样起作用.我也杀死了IIS进程，然后一切都停止了.当我再次启动IIS时，一切都从应有的状态继续进行.

I called a class that inserts 10.000 records to sql server and I called it in for..next with 100 times. sure it took long but it worked like a charm. Also I killed IIS processes and then everything stopped. when I started IIS again everything continued from where it should.

我非常喜欢这种解决方案.

I like this solution much much more.

谢谢.

这篇关于ADONET异步执行-连接断开错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！