我正在尝试异步查询执行一段时间。我的目标是执行简单的 SQL 语句并且不要等待它们完成。下面的代码适用于 10、500、1000 甚至 5000 个查询。但对于 50000 个查询突然出现错误并说
“BeginExecuteReader 需要一个打开且可用的连接。连接的当前状态为打开。”
有时它会说“...状态:已损坏”
这是 aspnet 测试站点,我认为可能会发生 50.000 个查询。是我错过了什么吗?不应该起作用吗?
我使用 windows7 x64,我相信它与 sql 连接轮询限制有关。你可能会说 50.000 太高了,但我需要避免这个错误以信任代码,但我不知道如何做。
ps:在代码中,我打开连接,但出于测试目的不关闭它。如果我关闭连接回调函数永远不会触发。
有什么建议 ?而且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 类、连接池和重连接逻辑》一文不是答案。我的问题是异步执行。
我尝试使用该代码。我尝试不使用阳光例程:
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 行。
假设我有需要运行这 50.000 个命令的逻辑。我应该怎么做才能避免内存问题或池限制?