Database connection object in shared variable

Discussion in 'ASP .Net' started by Daniel Fernandes, Sep 29, 2004.

  1. Hi there

    Let's say due to several reasons I have an ASP.Net application that
    has a database access class that stores a database connection in a
    shared member :


    public class dataaccess

    private shared DB as System.Data.SqlClient.SqlConnection

    public shared function GetItems as dataset
    ...
    end function

    public shared sub InsertItems as dataset
    ...
    end sub

    end class


    I understand that I shouldn't be doing this and instead do the "open a
    connection as late as possible and close it as soon as possible" thing
    but in that case I can't.

    I also undestand that putting database connection object in a shared
    variable can lead to problems because access to shared members is not
    thread safe.

    Now...Only two times I had such a "predictable" issue happening and
    the exception thrown was "There is already an open DataReader
    associated with this Connection which must be closed first.".

    I tried to programmatically replicate this by either running calling
    multiple pages that will execute a stored procedure containing a
    WAITFOR command or that execute a stored procedure that returns
    thousands of records.
    In both cases I didn't manage to get the exception thrown.

    Which lead to my question which is: under which circumstances the fact
    of using a database connection object in a shared member will lead to
    predictable problems ?

    PS: I believe that in the two times where I had the exception "There
    is already an open..." thrown was when the execution of the stored
    procedure couldn't be performed and therefore resulted into an
    exception due to other issues (execute permission for instance).


    Thanks for your help

    Daniel
     
    Daniel Fernandes, Sep 29, 2004
    #1
    1. Advertising

  2. Hi Daniel,

    I'm going to have to disagree with your premise:

    > I understand that I shouldn't be doing this and instead do the "open a
    > connection as late as possible and close it as soon as possible" thing
    > but in that case I can't.


    "Can't" is not a word that should be in any programmer's vocabulary. Of
    course you can. You just don't know how to.

    Based upon the corrected premise, the entire question is moot. I would
    suggest asking instead, how you CAN work with a Connection in the way it was
    designed to be used.

    In other words, what is it that seems to prevent you from using a Connection
    in the way it was designed to be used?

    --
    HTH,
    Kevin Spencer
    ..Net Developer
    Microsoft MVP
    I get paid good money to
    solve puzzles for a living

    "Daniel Fernandes" <> wrote in message
    news:...
    > Hi there
    >
    > Let's say due to several reasons I have an ASP.Net application that
    > has a database access class that stores a database connection in a
    > shared member :
    >
    >
    > public class dataaccess
    >
    > private shared DB as System.Data.SqlClient.SqlConnection
    >
    > public shared function GetItems as dataset
    > ...
    > end function
    >
    > public shared sub InsertItems as dataset
    > ...
    > end sub
    >
    > end class
    >
    >
    > I understand that I shouldn't be doing this and instead do the "open a
    > connection as late as possible and close it as soon as possible" thing
    > but in that case I can't.
    >
    > I also undestand that putting database connection object in a shared
    > variable can lead to problems because access to shared members is not
    > thread safe.
    >
    > Now...Only two times I had such a "predictable" issue happening and
    > the exception thrown was "There is already an open DataReader
    > associated with this Connection which must be closed first.".
    >
    > I tried to programmatically replicate this by either running calling
    > multiple pages that will execute a stored procedure containing a
    > WAITFOR command or that execute a stored procedure that returns
    > thousands of records.
    > In both cases I didn't manage to get the exception thrown.
    >
    > Which lead to my question which is: under which circumstances the fact
    > of using a database connection object in a shared member will lead to
    > predictable problems ?
    >
    > PS: I believe that in the two times where I had the exception "There
    > is already an open..." thrown was when the execution of the stored
    > procedure couldn't be performed and therefore resulted into an
    > exception due to other issues (execute permission for instance).
    >
    >
    > Thanks for your help
    >
    > Daniel
     
    Kevin Spencer, Sep 29, 2004
    #2
    1. Advertising

  3. eIt will happen any time a connection still has an open object appended to
    it. This could be a long running command, or the issue you are talking about
    (open Reader).

    You can test the type of Exception thrown, however, and determine if it is a
    execution permission. Check the exception objects in System.Data. In
    addition, you can pull the errors collection when you are using SqlClient and
    query the errors returned (SqlErrorCollection class).


    ---

    Gregory A. Beamer
    MVP; MCP: +I, SE, SD, DBA

    ***************************
    Think Outside the Box!
    ***************************

    "Daniel Fernandes" wrote:

    > Hi there
    >
    > Let's say due to several reasons I have an ASP.Net application that
    > has a database access class that stores a database connection in a
    > shared member :
    >
    >
    > public class dataaccess
    >
    > private shared DB as System.Data.SqlClient.SqlConnection
    >
    > public shared function GetItems as dataset
    > ...
    > end function
    >
    > public shared sub InsertItems as dataset
    > ...
    > end sub
    >
    > end class
    >
    >
    > I understand that I shouldn't be doing this and instead do the "open a
    > connection as late as possible and close it as soon as possible" thing
    > but in that case I can't.
    >
    > I also undestand that putting database connection object in a shared
    > variable can lead to problems because access to shared members is not
    > thread safe.
    >
    > Now...Only two times I had such a "predictable" issue happening and
    > the exception thrown was "There is already an open DataReader
    > associated with this Connection which must be closed first.".
    >
    > I tried to programmatically replicate this by either running calling
    > multiple pages that will execute a stored procedure containing a
    > WAITFOR command or that execute a stored procedure that returns
    > thousands of records.
    > In both cases I didn't manage to get the exception thrown.
    >
    > Which lead to my question which is: under which circumstances the fact
    > of using a database connection object in a shared member will lead to
    > predictable problems ?
    >
    > PS: I believe that in the two times where I had the exception "There
    > is already an open..." thrown was when the execution of the stored
    > procedure couldn't be performed and therefore resulted into an
    > exception due to other issues (execute permission for instance).
    >
    >
    > Thanks for your help
    >
    > Daniel
    >
     
    =?Utf-8?B?Q293Ym95IChHcmVnb3J5IEEuIEJlYW1lcikgLSBN, Sep 29, 2004
    #3
  4. Daniel Fernandes

    Scott Allen Guest

    On 29 Sep 2004 07:55:06 -0700, (Daniel
    Fernandes) wrote:

    >
    >I understand that I shouldn't be doing this and instead do the "open a
    >connection as late as possible and close it as soon possible" thing
    >but in that case I can't.
    >


    Hi Daniel:

    By any chance are you trying to restrict the number of connections to
    the database? If so, you could adjust the connection pool size in the
    connection string.

    Perhaps if you tell us why you must use a shaed connection we could
    think of alternatives. I would try to avoid the current implementation
    at all costs. Threading bugs tend to be subtle and sometimes remain
    latent until the worst possible time. Just ask GE Energy!
    http://www.securityfocus.com/news/8412

    --
    Scott
    http://www.OdeToCode.com/
     
    Scott Allen, Sep 29, 2004
    #4
  5. Daniel Fernandes

    Patrice Guest

    When having more than one DataReader on an open connection (though it looks
    like you don't see this, it's likely because of something wrong in your test
    such as IIS6 new application isolation mode perhaps). Though you could
    workaround this is IMO really looking for trouble.

    What is the reason behind having a single shared connection ?

    Patrice

    --

    "Daniel Fernandes" <> a écrit dans le message
    de news:...
    > Hi there
    >
    > Let's say due to several reasons I have an ASP.Net application that
    > has a database access class that stores a database connection in a
    > shared member :
    >
    >
    > public class dataaccess
    >
    > private shared DB as System.Data.SqlClient.SqlConnection
    >
    > public shared function GetItems as dataset
    > ...
    > end function
    >
    > public shared sub InsertItems as dataset
    > ...
    > end sub
    >
    > end class
    >
    >
    > I understand that I shouldn't be doing this and instead do the "open a
    > connection as late as possible and close it as soon as possible" thing
    > but in that case I can't.
    >
    > I also undestand that putting database connection object in a shared
    > variable can lead to problems because access to shared members is not
    > thread safe.
    >
    > Now...Only two times I had such a "predictable" issue happening and
    > the exception thrown was "There is already an open DataReader
    > associated with this Connection which must be closed first.".
    >
    > I tried to programmatically replicate this by either running calling
    > multiple pages that will execute a stored procedure containing a
    > WAITFOR command or that execute a stored procedure that returns
    > thousands of records.
    > In both cases I didn't manage to get the exception thrown.
    >
    > Which lead to my question which is: under which circumstances the fact
    > of using a database connection object in a shared member will lead to
    > predictable problems ?
    >
    > PS: I believe that in the two times where I had the exception "There
    > is already an open..." thrown was when the execution of the stored
    > procedure couldn't be performed and therefore resulted into an
    > exception due to other issues (execute permission for instance).
    >
    >
    > Thanks for your help
    >
    > Daniel
     
    Patrice, Sep 29, 2004
    #5
  6. I do know how to create a data access class that uses connection
    pooling.

    The problem is that I have to use an internal library that provides
    helper classes for data access and in particular there is a
    storedprocedure object that has two constructors :

    1. Where you pass a connection (not a standard sql connection though)
    and you would expect the connection to be opened, the stored procedure
    to be executed and then the connection to be closed. Sadly the
    connection is never closed which means after 100s or so calls SQL Server
    will stop responding. I didn't look at the implementation but my guess
    is the developer uses IDisposable for disposing the connection object
    and this is obviously a mistake because the connection should be closed
    and disposed explicitely after the stored procedure execution.

    2. The second constructor is very similar to 1. and it's expected that
    the connection is kept alive at the end of the stored procedure
    execution. And that is the only one I am using but I would think such a
    mechanism is only safe when in a single thread type application.




    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Daniel Fernandes, Sep 30, 2004
    #6
  7. I finally managed to replicate the problem by having a stored procedure
    returning a large number of rows (5000+) and running multiple ASPX pages
    that did the very same thing.

    I had again the "There is already a datareader associated with this
    connection which must be closed first." exception.

    Although I am not using datareaders I know they are used internally used
    as a dataset is filled by a dataadapter that makes use of datareaders.




    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Daniel Fernandes, Sep 30, 2004
    #7
  8. Hi Daniel,

    I'm not sure what you mean by "an internal library." Internal to what? Do
    you mean that somebody else in your company developed the library? If so,
    that person is the one who should be posting here, as their design is
    fatally flawed. IOW, the problem lies with the library you're using. I can't
    imagine why anyone would be forcing you to use defective software. To build
    good software, you need to USE good software.

    --
    HTH,
    Kevin Spencer
    ..Net Developer
    Microsoft MVP
    I get paid good money to
    solve puzzles for a living

    "Daniel Fernandes" <> wrote in message
    news:#...
    > I do know how to create a data access class that uses connection
    > pooling.
    >
    > The problem is that I have to use an internal library that provides
    > helper classes for data access and in particular there is a
    > storedprocedure object that has two constructors :
    >
    > 1. Where you pass a connection (not a standard sql connection though)
    > and you would expect the connection to be opened, the stored procedure
    > to be executed and then the connection to be closed. Sadly the
    > connection is never closed which means after 100s or so calls SQL Server
    > will stop responding. I didn't look at the implementation but my guess
    > is the developer uses IDisposable for disposing the connection object
    > and this is obviously a mistake because the connection should be closed
    > and disposed explicitely after the stored procedure execution.
    >
    > 2. The second constructor is very similar to 1. and it's expected that
    > the connection is kept alive at the end of the stored procedure
    > execution. And that is the only one I am using but I would think such a
    > mechanism is only safe when in a single thread type application.
    >
    >
    >
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it!
     
    Kevin Spencer, Sep 30, 2004
    #8
  9. Well, there is actually one other possibility. I seem to recall you saying
    that you had problems with an opened connection associated with a
    DataReader. A DataReader is a connected object. It must maintain an opened
    connection to the database for the duration of the time you use it. It can
    only fetch one record from the database at a time. I have developed a Data
    Access class for our company, and it includes methods for getting
    DataReaders. You have to pass a Connection and Command reference to it, so
    that you can close them afterwards. And when you use the DataReader, you'd
    darned well better close the Connection yourself afterwards. Which is why I
    also created a method to which you pass a Connection, and it closes and
    disposes the Connection.

    --
    HTH,
    Kevin Spencer
    ..Net Developer
    Microsoft MVP
    I get paid good money to
    solve puzzles for a living

    "Daniel Fernandes" <> wrote in message
    news:#...
    > I do know how to create a data access class that uses connection
    > pooling.
    >
    > The problem is that I have to use an internal library that provides
    > helper classes for data access and in particular there is a
    > storedprocedure object that has two constructors :
    >
    > 1. Where you pass a connection (not a standard sql connection though)
    > and you would expect the connection to be opened, the stored procedure
    > to be executed and then the connection to be closed. Sadly the
    > connection is never closed which means after 100s or so calls SQL Server
    > will stop responding. I didn't look at the implementation but my guess
    > is the developer uses IDisposable for disposing the connection object
    > and this is obviously a mistake because the connection should be closed
    > and disposed explicitely after the stored procedure execution.
    >
    > 2. The second constructor is very similar to 1. and it's expected that
    > the connection is kept alive at the end of the stored procedure
    > execution. And that is the only one I am using but I would think such a
    > mechanism is only safe when in a single thread type application.
    >
    >
    >
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it!
     
    Kevin Spencer, Sep 30, 2004
    #9
  10. Kevin

    Thanks for your answer.
    By "Internal Library" I meant a framework that have been developed for
    internal use and that indeed has some issues when dealing with
    connections.

    The reason why I am getting an exception related to a Datareader object
    is because the Fill method of a DataAdapter I believe uses a Datareader
    hence the problem.

    I am doing some tests using ACT and I hope I will be able to convince
    the maintainer of the library to update it.

    Cheers
    Daniel




    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Daniel Fernandes, Sep 30, 2004
    #10
  11. Hi Daniel,

    Interesting. Our in-house data class has methods for working with DataSets
    as well. However, these methods open their own Connection, create the
    DataSet, and then close the Connection. The user of the method doesn't even
    have to think about the Connection. In fact, I have found that it is best to
    use Connections internally (inside a method) whenever possible, rather than
    exposing them to the developer, who may or may not be good enough to know
    what to do with them. The only methods we have that expose Connection
    objects are ones that return DataReaders.

    --
    HTH,
    Kevin Spencer
    ..Net Developer
    Microsoft MVP
    I get paid good money to
    solve puzzles for a living

    "Daniel Fernandes" <> wrote in message
    news:...
    > Kevin
    >
    > Thanks for your answer.
    > By "Internal Library" I meant a framework that have been developed for
    > internal use and that indeed has some issues when dealing with
    > connections.
    >
    > The reason why I am getting an exception related to a Datareader object
    > is because the Fill method of a DataAdapter I believe uses a Datareader
    > hence the problem.
    >
    > I am doing some tests using ACT and I hope I will be able to convince
    > the maintainer of the library to update it.
    >
    > Cheers
    > Daniel
    >
    >
    >
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it!
     
    Kevin Spencer, Sep 30, 2004
    #11
  12. Daniel Fernandes

    elizas

    Joined:
    Jan 27, 2010
    Messages:
    27
    Location:
    India
    How to close a Connection object automatically when we close the associated Reader ob

    OleDbDataReader oReader = oCommand.ExecuteReader(CommandBehavior.CloseConnection);

    The argument "CommandBehavior.CloseConnection" will specify that the connection object will close automatically when we close the Reader object.

    The sample code is given below:

    OleDbConnection connectionObj = new OleDbConnection(connectionString);
    OleDbCommand commandObj = new OleDbCommand();
    OleDbDataReader readerObj = null;
    commandObj = new OleDbCommand(sqlQuery, connectionObj);
    connectionObj.Open();
    readerObj = commandObj.ExecuteReader(CommandBehavior.CloseConnection);


    Now when we close the Reader object then the Connection object will close automatically.
     
    elizas, Apr 19, 2010
    #12
    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. John Kraft

    Shared Variable Vs. Session Variable

    John Kraft, Oct 21, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    1,901
    Cowboy \(Gregory A. Beamer\)
    Oct 21, 2003
  2. DJ Dev
    Replies:
    3
    Views:
    16,211
    Gandalf
    Feb 8, 2004
  3. Mythran
    Replies:
    5
    Views:
    4,991
    Mythran
    Oct 5, 2005
  4. Douglass Turner
    Replies:
    2
    Views:
    2,081
    Manfred Bartz
    Sep 4, 2003
  5. Replies:
    1
    Views:
    1,308
    santosh
    Jul 15, 2008
Loading...

Share This Page