I have been experimenting with doing an asynchronous request for some time. My goal is to execute simple SQL statements and not wait for them to complete. The code below works well for 10, 500 or 1000, or even 5000 queries. but for 50,000 requests, an error suddenly appears and says
"BeginExecuteReader requires an open and accessible connection. The current state of the connection is open." and sometimes he says: "... condition: broken"
this is an aspnet test site, and I think 50,000 requests could happen. Is this something I'm missing? shouldn't it work?
I use windows7 x64 and I believe that it does something with sql connection polling limits. you might say that 50,000 is too big, but I need to avoid this error in order to trust the code, and I don’t know how to do it.
ps: In the code, I open the connection, but do not close it for testing purposes. if I close the connection callback function, it never fires.
any suggestions? And there is not much information about this error in 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
The ".NET SqlConnection class, connection pooling and reconnection logic" article is not the answer. my problem is asynchronous execution.
I tried to use this code. I tried not to use the sun procedures:
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
This time I got an exception like "Fix System.OutOfMemoryException". in the cmd.BeginExecuteReader line.
, , 50 000 . , ?