I need advice on correct usage of ADO.NET in an ASP.Net environment.
I have an ASP.Net application that accesses data through a referenced
class library. Things start to break down when multiple web clients
attempt to use the application at the same time. A common error that
gets returned is "sqlcommand is currently busy open, fetching".
I can't understand why the users might be using the same instance of
my connection, but this error message would seem to indicate that they
are.

Below is an outline of my applications architecture.
I would appreciate any advice or suggestions as to what I might change
in my approach to get my ASP.Net application connecting to data in a
multiuser environment.

Classes that contains my business logic:

Public Class AAA
Private CON As New SqlClient.SqlConnection()
Private RDR As SqlClient.SqlDataReader
Private CMD As New SqlClient.SqlCommand()

Public Sub DoStuff
Common.ConnectDatabase(CON, "Staff")
CMD.CommandText = "SELECT * FROM tblStaff"
CMD.Connection = CON
RDR = CMD.ExecuteReader
If RDR.Read = False Then
...
...
End If
RDR.Close()
CON.Close()
End Sub
End Class

This module lives in the same dll as my business logic and contains
commonly used stuff like connecting to databases:

Public Module Common
Public Function ConnectDatabase(ByRef CON As SqlClient.SqlConnection,
ByVal sDatabase As String) As Boolean
Try
Select Case sDatabase
Case "Staff"
CON = New System.Data.SqlClient.SqlConnection()
CON.ConnectionString = "Integrated
Security=False;User ID=;Password=;Initial Catalog=Staff;Data
Source=myserver;"
CON.Open()
End Select
Catch
Return False
End Try
Return True
End Function
End Class

Now, my ASP.Net web pages interact with the above code like this:
(Note - all my code is in code behind. This is an example from an
aspx
page called Staff.aspx)

Public Class Staff
Protected MyClass as New AAA()

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
PrintStaff()
End Sub

Private Sub PrintStaff()
MyClass.DoStuff
End Sub
End Class

The philosophy is that everytime a method in my classes is called a
connection is opened, actions are performed, then the connection is
closed. Note that I do not "dispose" of the collection so it should
be returned to the pool. One of the things I am trying to achieve by
following this approach is to take any concern for attaching to
databases away from the coder building the ASP.Net pages. The classes
perform all data activity.

Heath,

Do the easy stuff first. Put the close in a Try/FInally block, making sure
the connection is open or wrapping the close in another Try/Catch.

Kathleen


"Heath Kelly" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I need advice on correct usage of ADO.NET in an ASP.Net environment.
> I have an ASP.Net application that accesses data through a referenced
> class library. Things start to break down when multiple web clients
> attempt to use the application at the same time. A common error that
> gets returned is "sqlcommand is currently busy open, fetching".
> I can't understand why the users might be using the same instance of
> my connection, but this error message would seem to indicate that they
> are.
>
> Below is an outline of my applications architecture.
> I would appreciate any advice or suggestions as to what I might change
> in my approach to get my ASP.Net application connecting to data in a
> multiuser environment.
>
> Classes that contains my business logic:
>
> Public Class AAA
> Private CON As New SqlClient.SqlConnection()
> Private RDR As SqlClient.SqlDataReader
> Private CMD As New SqlClient.SqlCommand()
>
> Public Sub DoStuff
> Common.ConnectDatabase(CON, "Staff")
> CMD.CommandText = "SELECT * FROM tblStaff"
> CMD.Connection = CON
> RDR = CMD.ExecuteReader
> If RDR.Read = False Then
> ...
> ...
> End If
> RDR.Close()
> CON.Close()
> End Sub
> End Class
>
> This module lives in the same dll as my business logic and contains
> commonly used stuff like connecting to databases:
>
> Public Module Common
> Public Function ConnectDatabase(ByRef CON As SqlClient.SqlConnection,
> ByVal sDatabase As String) As Boolean
> Try
> Select Case sDatabase
> Case "Staff"
> CON = New System.Data.SqlClient.SqlConnection()
> CON.ConnectionString = "Integrated
> Security=False;User ID=;Password=;Initial Catalog=Staff;Data
> Source=myserver;"
> CON.Open()
> End Select
> Catch
> Return False
> End Try
> Return True
> End Function
> End Class
>
> Now, my ASP.Net web pages interact with the above code like this:
> (Note - all my code is in code behind. This is an example from an
> aspx
> page called Staff.aspx)
>
> Public Class Staff
> Protected MyClass as New AAA()
>
> Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles MyBase.Load
> PrintStaff()
> End Sub
>
> Private Sub PrintStaff()
> MyClass.DoStuff
> End Sub
> End Class
>
> The philosophy is that everytime a method in my classes is called a
> connection is opened, actions are performed, then the connection is
> closed. Note that I do not "dispose" of the collection so it should
> be returned to the pool. One of the things I am trying to achieve by
> following this approach is to take any concern for attaching to
> databases away from the coder building the ASP.Net pages. The classes
> perform all data activity.