locked
Static classes and variables (particularly a db connection) RRS feed

  • Question

  • User1958497753 posted

    So I started working on my first asp.net application that involves logging in and databases, and soon after i started messing around with a static class.  I "discovered" that if you make a variable static, all sessions share that variable (I for some reason was originally assuming that each session had its own copy of that "static" class).  Anyway, after discovering this I thought to myself "how could this possibly be useful to me" and thought that it might be a good idea to make a single static database connection for all of the sessions, rather than storing that as a session variable for each session.  Does anybody know what would be the pros and cons of this approach?  Are there any threading issues that would pop up from doing this (my limited tests indicate that there isnt)?

    Monday, January 11, 2010 5:01 PM

Answers

  • User-821857111 posted

    You should not be considering sharing database connections among users, or making them available in session or global variables in an ASP.NET application. Database connections should only be created when they are needed, and opened as late as possible in your code, then closed as soon as possible.

    The default ADO.NET setup allows for connection pooling. This leaves "closed" connections dormant in a pool so that they can be reused. That's how efficiencies are gained and your application can scale. You do not want the SQL Server sitting there managing a whole load of resources (connections) that are not being used.


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 11, 2010 5:47 PM
  • User-952121411 posted

    To add on to what has already been stated, you want the connection only to persist for the absolute minimum scope needed for the calls you need to make.  A great way to do this is by the 'Using' statement.  Take a look at the code example below:

            Using MyConn As New SqlConnection("Connection String Goes Here")
    
                'Code needing to use the Sql Connection goes in here
                'The Using Block will take care of closing the connection
    
            End Using


    Connections should only be open for as long as needed.  The nice thing is ADO.NET handles Connection Pooling which will reduce the number of new connection that have to be opened each time it is requested.  From MSDN:

    "Connection pooling reduces the number of times that new connections must be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call."

    SQL Server Connection Pooling (ADO.NET):

    http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 12, 2010 9:06 AM

All replies

  • User-821857111 posted

    You should not be considering sharing database connections among users, or making them available in session or global variables in an ASP.NET application. Database connections should only be created when they are needed, and opened as late as possible in your code, then closed as soon as possible.

    The default ADO.NET setup allows for connection pooling. This leaves "closed" connections dormant in a pool so that they can be reused. That's how efficiencies are gained and your application can scale. You do not want the SQL Server sitting there managing a whole load of resources (connections) that are not being used.


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 11, 2010 5:47 PM
  • User-82232418 posted

    When  it comes to database functionality, just use SqlHelper from the Microsoft application data blocks.  You won't need to worry about connections and all the gory details with the ado.net.   It provides simple static methods that will cover 99.9% of your data needs (executing sql, sprocs,  returning dataset, data table, etc...  It's just one file (SqlHelper.cs).  Google it and you find a download from microsoft.

    Here's an example calling a stored procedure which takes 1 parameter:

    DataTable table = SqlHelper.ExecuteDataTable("someConnectionString", "someSProc", someParameter);
    
    foreach (DataRow row in table.Rows)
    {
       string name = (string)row["Name"];
        int age = (int)row["Age"];
     }
    


     

     

    ~Rick

    Monday, January 11, 2010 6:04 PM
  • User1958497753 posted

    I didn't realize leaving connections open was that costly.  So on a given page they should be opened and closed as soon as possible?  I thought it might be more beneficial to keep a connection open for each session to avoid the overhead of connecting over and over.

    Monday, January 11, 2010 11:22 PM
  • User-821857111 posted

    I thought it might be more beneficial to keep a connection open for each session to avoid the overhead of connecting over and over.

    No. Quite the opposite. Connection Pooling manages connections a lot more efficiently than you ever could through code. If a visitor lands on your site by mistake, they create a session. That will last for a minimum of 20 minutes. So will their opened connection, even though it is never used for more than one page for a few seconds.


    Tuesday, January 12, 2010 1:25 AM
  • User-952121411 posted

    To add on to what has already been stated, you want the connection only to persist for the absolute minimum scope needed for the calls you need to make.  A great way to do this is by the 'Using' statement.  Take a look at the code example below:

            Using MyConn As New SqlConnection("Connection String Goes Here")
    
                'Code needing to use the Sql Connection goes in here
                'The Using Block will take care of closing the connection
    
            End Using


    Connections should only be open for as long as needed.  The nice thing is ADO.NET handles Connection Pooling which will reduce the number of new connection that have to be opened each time it is requested.  From MSDN:

    "Connection pooling reduces the number of times that new connections must be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call."

    SQL Server Connection Pooling (ADO.NET):

    http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 12, 2010 9:06 AM