Tech Off Thread

10 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Is it bad to put a While statement before a Fill?

Back to Forum: Tech Off
  • User profile image
    qwert231

    I have a data access component that sometimes returns an error:
    System.InvalidOperationException: There is already an open DataReader associated with this Connection which must be closed first.

    I was thinking of putting a while statement that would run while the connection is open before my fill. Is this dangerous?

    While Me.sda.SelectCommand.Connection.State = ConnectionState.Open

    'Wait

    End While

    Me.sda.Fill(ds)

  • User profile image
    Minh

    qwert231 wrote:
    I have a data access component that sometimes returns an error:
    System.InvalidOperationException: There is already an open DataReader associated with this Connection which must be closed first.

    I was thinking of putting a while statement that would run while the connection is open before my fill. Is this dangerous?

    While Me.sda.SelectCommand.Connection.State = ConnectionState.Open

    'Wait

    End While

    Me.sda.Fill(ds)


    You may want to look for the REAL cause why you're getting that exception, in the mean time, that While statement will jack up CPU utilization to 100% -- so you'd want to do something like this:

    While Me.sda.SelectCommand.Connection.State = ConnectionState.Open
       'CPU-friendly wait

       Thread.Sleep(10)

    End While


  • User profile image
    Sven Groot

    Are you using the same connection across threads? If so, you'd better switch to separate connections, or lock all access to the connection object (and stay locked while and related objects such as data readers are still in use, far from ideal you'll agree).

    If you've got only one thread using the connection, you're forgetting to dispose a DataReader or DataAdapter somewhere.

  • User profile image
    qwert231

    I forgot to add that this component is used by several pages on my site. Another page/session may have been using the component at the same time. The component only has 1 SQL connection for the calls to the database.

  • User profile image
    Maurits

    I recommend a connection-per-call model, with connection pooling turned on.  Works well for me.

  • User profile image
    qwert231

    Any tips on how? Web sites showing examples? (It only happens maybe 5 times a day.)

  • User profile image
    Sven Groot

    qwert231 wrote:
    I forgot to add that this component is used by several pages on my site. Another page/session may have been using the component at the same time. The component only has 1 SQL connection for the calls to the database.

    You definitely don't want to use one connection across page requests. That's asking for trouble. Preferably, always make the connection a local variable and dispose of it before the end of the method in a try/finally construct (or "using" if using C# or VB2005).

  • User profile image
    qwert231

    Can I make a copy of my global SQL connection object? Like:

    public Function mySqlCall(dataId as integer) as dataset
     Dim localSqlConn as new SQLConnection(globalConn.connectionstring)
     Me.cmdSQLCommandIWant.connection = localSqlConn
     Me.SQLDataAdapter.SelectCommand = Me.cmdSQLCommandIWant
     Me.SQLDataADapter.Fill(yada,"Yada")
    end sub

    Let me ask this, I have the .dll for the pages, and the .dll for my Data Access Component. Let's say 3 people acces my site at the same time. So, that's 3 sessions right? Is that 3 instances of my Data Access Component? If I have a globally declared connection (Friend sConn as SQLConnection) is it shared across those 3 instances? Or, because it's set as Friend, is there only 1 sConn object in memory regardless of # of instances?

  • User profile image
    Sven Groot

    Personally, I'd keep *all* my SQL stuff as local variables.

    Public Function mySqlCall(ByVal dataId As Integer) As DataSet
    Dim conn As SqlConnection
    Dim cmd As SqlCommand
    Dim adapter As SqlDataAdapter
    Dim result As DataSet
    Try
    conn = New SqlConnection(GetConnectionStringFromSomewhere)
    conn.Open()
    cmd = conn.CreateCommand()
    cmd.CommandText = GetCommandTextFromSomewhere
    cmd.Parameters.Add("@someparam", dataId)
    adapter = New SqlDataAdapter()
    adapter.SelectCommand = cmd
    result = New DataSet()
    adapter.Fill(result, "yada")
    Return result
    Finally
    If Not adapter Is Nothing Then
    adapter.Dispose()
    End If
    If Not cmd Is Nothing Then
    cmd.Dispose()
    End If
    If Not conn Is Nothing Then
    conn.Dispose()
    End If
    End Try
    End Function


    You have to do the Dispose calls to save resources on the database server. Absolutely vital if you want your site to be scalable. This is why I'm so glad VB2005 finally has a using statement, it makes this so much easier.

  • User profile image
    Tensor

    Sven has said it better than I could. Keep you connections open for as little time as possible - one connection for each call, using try/finally blocks to be as sure as possible that your connection goes away when you are finished with it. Connection pooling will take care of the performance. 

    The slightly different way I would do it to sven is nest my trys;

    dim cn as sqlconnection
    dim dr as sqldatareader

    try
       'open your connection here
       try
          'open your datareader here
       finally
           'get rid of the reader
       end try
    finally
       'get rid of the connection
    end try

      

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.