Sharing single database connection between several methods simultaneously

Question

I have a page where a user can insert some order about some product. In that page there are also live statistics which is being refreshed every 20 seconds to reflect the current price of a product using asp.net timer. This live panel fetching these prices from a database.

When sometimes an order is being inserted, I get an exception saying that the connection is closed and the insert command cannot continue. I suspect that when the insert command is given, if at that exact moment the live update is being refreshed, then they both need to access the database, and they are using the same SqlConnection Object. So when one finishes, the connection object being closed even if the other one is using the same connection object.

Before closing a connection, how can I be sure that no other methods are using that connection?

The code of my connection class is given below, for better clarification -

using System;
using System.Collections.Generic;
//using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Specialized;
using System.IO;
using System.IO.Ports;


public class ConnectionHelper
{

#region Private Variables
static string BOconnectionString = "";
static SqlConnection BOcon = null;
static string STRSconnectionString = "";
static SqlConnection STRScon = null;
static SqlTransaction tSTRSTrans = null;

public static SqlTransaction STRSTrans
{
    get { return ConnectionHelper.tSTRSTrans; }
    set { ConnectionHelper.tSTRSTrans = value; }
}
#endregion


#region Constructor
ConnectionHelper() { }
#endregion



#region Public Functions 

public static SqlConnection getSTRSConnection()
{
    STRSconnectionString = ConfigurationManager.ConnectionStrings["STRS_WEB_DB2ConnectionString"].ConnectionString;
    try
    {
        if ((STRScon == null) || (STRScon.State == ConnectionState.Closed))
        {
            STRScon = new SqlConnection(STRSconnectionString);
            STRScon.Open();
            //tSTRSTrans = STRScon.BeginTransaction(IsolationLevel.RepeatableRead);
        }


    }
    catch (Exception ex)
    {
        throw new Exception("Error occurred when Connection is going to be opened" + ex.Message);
    }
    return STRScon;
}
public static void closeSTRSConnection()
{
    if ((STRScon != null) || (STRScon.State == ConnectionState.Open))
    {
        STRScon.Close();
    }        
}

#endregion

}

If a method want to access the database, they are obtaining and closing the connection in the following way -

con = ConnectionHelper.getSTRSConnection();
........
........
ConnectionHelper.closeSTRSConnection();

Answer 1

Connection pooling is a great thing, but I would advise staying away from trying to manage connection pooling yourself and letting the engine manage it for you..

If this is ASP.Net you can turn on connection pooling. Read more here

http://www.15seconds.com/Issue/040830.htm

http://aspalliance.com/1099_Understanding_Connection_Pooling_in_NET

Answer 2

Create new objects, dont share objects. ADO.net handles connection pooling, you do not have to try to do it yourself.

Answer 3

I don't think you should use this approach for a web application, I see your intent is to utilize a common connection, but consider refactoring your code so that instead of attempting to use an existing connection i.e. "getSTRSConnection", you simple open a connection | do your work | and close it. As a general practice, opening connections as late as possible is a good idea, and then closing the connection as soon as the work is done is another good idea. Remember that these connections are an expensive resource and should only live during the time they are needed to persist information to your database.

Answer 4

It's a bad idea to have one connection for multi user environment. When you use one connection for all your users if someone performs an action that requires, lets say 15 seconds, just for database access, all other users will have to wait in order to connect to the database.