Data Access Architecture Advice

Discussion in 'ASP .Net' started by Heath Kelly, Aug 21, 2003.

  1. Heath Kelly

    Heath Kelly Guest

    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 Kelly, Aug 21, 2003
    #1
    1. Advertising

  2. Heath Kelly

    Frank Drebin Guest

    At what line does it generate that error? I've gotten that before but don't
    remember what the problem was..

    "Heath Kelly" <> wrote in message
    news:...
    > 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.
    >
    >
     
    Frank Drebin, Aug 21, 2003
    #2
    1. Advertising

  3. Heath Kelly

    JohnG Guest

    How big is your table tblStaff? Do you have where clause and indexes with
    it?

    "Heath Kelly" <> wrote in message
    news:...
    > 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.
    >
    >
     
    JohnG, Aug 21, 2003
    #3
  4. Hi Heath,

    I think you may consider following things to reduce the conflicts:

    1. Keep the connection open, don't close it on every request. The Open
    command may comsume some time.
    2. Use dataset and dataadapter instead of the datareader, If you only need
    to retrieve data from database to your business layer, you can consider
    dataadapter's Fill method.
    3. If it is necessary, you can cache some statics records in Class AAA, for
    example, in a dataset, so that you don't need to access database on
    everytime.

    Hope this help

    Luke

    "Microsoft Security Announcement: Have you installed the patch for
    Microsoft Security Bulletin MS03-026?? If not Microsoft strongly advises
    you to review the information at the following link regarding Microsoft
    Security Bulletin MS03-026
    http://www.microsoft.com/security/security_bulletins/ms03-026.asp and/or to
    visit Windows Update at http://windowsupdate.microsoft.com to install the
    patch. Running the SCAN program from the Windows Update site will help to
    insure you are current with all security patches, not just MS03-026."
     
    ¥xÆW·L³n§Þ³N³B¤ä´©³¡, Aug 21, 2003
    #4
  5. The problem is that your class has a global Connection and Command. If you
    try to execute certain database operations using the same instance of the
    class, the different operations will be using the same Connection. This can
    cause the type of problem you describe. Try removing the global variables,
    and create Connection and Command variables inside the functions that use
    them. Make sure the Connection is always closed by the function. Connection
    Pooling will take care of the rest.

    --
    HTH,

    Kevin Spencer
    Microsoft MVP
    ..Net Developer
    http://www.takempis.com
    Neither a follower nor a lender be.

    "Heath Kelly" <> wrote in message
    news:...
    > 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.
    >
    >
     
    Kevin Spencer, Aug 21, 2003
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Muhammad Khan
    Replies:
    4
    Views:
    1,227
    Mike Treseler
    Jul 10, 2003
  2. Replies:
    3
    Views:
    510
    Malcolm
    Sep 29, 2005
  3. rashmi
    Replies:
    2
    Views:
    460
    Grumble
    Jul 5, 2005
  4. Replies:
    3
    Views:
    424
    Malcolm
    Sep 29, 2005
  5. Replies:
    4
    Views:
    661
    Malcolm
    Sep 29, 2005
Loading...

Share This Page