Multi user connect and do CRUD simultaneously

I'm using ASP.NET Framework 4, IIS 7 and SQL Server 2008 R2.

I have got an error like: {column} not found in selected DataSource, SQL Reader is close, ....

It only happens when:

1. Multiple users are connected.
2. They make CRUD (Create, Retrive, Update, Delete) calls simultaneously.

Strangely, it escape my catch:

try{
    Connexion_D.GetConnected();
    // doing CRUD
}
catch{
    // catching Error, avoid yellow page aspx
}
finally
{
    Connexion_D.CloseConnection();
} 

And my connection class:

public class Connexion_D
{

    static public SqlConnection conn;
    static public SqlConnection GetConnected()
    {
开发者_如何学C        try
        {
            String strConnectionString = ConfigurationManager.ConnectionStrings["xxxxx"].ConnectionString;
            conn = new SqlConnection(strConnectionString);
        }
        catch (Exception excThrown)
        {
            conn = null;
            throw new Exception(excThrown.InnerException.Message, excThrown);
        }

        // Ouverture et restitution de la connexion en cours
        if (conn.State == ConnectionState.Closed) conn.Open();
        return conn;
    }
    static public Boolean IsConnected
    {
        get { return (conn != null) && (conn.State != ConnectionState.Closed) && (conn.State != ConnectionState.Broken); }
    }

    static public void CloseConnection()
    {
        // Libération de la connexion si elle existe
        if (IsConnected) conn.Close();
    }
}

So I don't think the code is wrong / has a bug.

I think it may be the configuration of IIS and SQL server.

Any ideas?

Thanx in advance.

If I understand what you're doing correctly, then this looks very dubious:

static public SqlConnection conn;     
static public SqlConnection GetConnected() {         
    try         
    {             
        String strConnectionString = ConfigurationManager.ConnectionStrings["xxxxx"].ConnectionString;             
        conn = new SqlConnection(strConnectionString);
    } 
}

static public void CloseConnection() {           
    // Libération de la connexion si elle existe           
    if (IsConnected) conn.Close();       
}   

You're working with a static connection variable, which means that when you close it, you're closing the last one that was opened.

In a multi-user scenario, you could be having this happen:

• User A: Create connection (and return connection 1)
• User A: Execute query (runs against connection 1)
• User B: Create connection (and return connection 2)
• User A: Close connection (last one opened was 2, so that's closed)
• User B: Execute query (runs against connection 2, it's already been closed... bang)

As an aside, you should probably reconsider having your connection as a public member variable:

static public SqlConnection conn;     

This is generally considered bad practice and can lead to unexpected / hard to track down errors in the future if any code outside of your class starts messing about with it's internal variables.

EDIT:

The most obvious solution would seem to be to stop the connection from being static. You client code might then look something like this:

try{
    // use using block around connection, calls dispose automatically when
    // block ends...
    using(var connectionWrapper = new Connexion_D()) {
        var connectedConnection = connectionWrapper.GetConnected();        
        // do CRUD
    }
}
catch{
    // catching Error, avoid yellow page aspx
    // Really you should probably be doing something with the exception (logging?)
    // particularly since you go to the effort of throwing it from your Connection_D
    // class.
}

With your class code looking like:

/* Implement IDisposable to cleanup connection */
public class Connexion_D : IDisposable 
{
    public SqlConnection conn;

    public SqlConnection GetConnected()
    {
        try
        {
            String strConnectionString = ConfigurationManager.ConnectionStrings["xxxxx"].ConnectionString;
            conn = new SqlConnection(strConnectionString);
        }
        catch (Exception excThrown)
        {
            conn = null;
            throw new Exception(excThrown.InnerException.Message, excThrown);
        }

        // Ouverture et restitution de la connexion en cours
        if (conn.State == ConnectionState.Closed) conn.Open();
        return conn;
    }
    public Boolean IsConnected
    {
        get { return (conn != null) && (conn.State != ConnectionState.Closed) && (conn.State != ConnectionState.Broken); }
    }

    public void CloseConnection()
    {
        // Libération de la connexion si elle existe
        if (IsConnected) { 
            conn.Close();
            conn = null;
        }
    }

    // Implement IDisposable.
    // Do not make this method virtual.
    // A derived class should not be able to override this method.
    public void Dispose()
    {
        // Close connection
    }
}

See this for more information about implementing IDisposable.