C# static SQLConnection

Shall we reuse an Static SqlConnection in a class,or its better create a instance of it?

FactoryDB factory = 
    FactoryDB.GetInstance("sp_select_regional", TipoExecucao.StoredProcedure,
        "portal_sadiaConnectionString");

factory.AutoReset = true;

using (FactoryDB.Conn)
{
    factory.ParametersCount = 1;
    factory.Parameters[0] = 
        FactoryDB.CreateParameter(((IMarcas)Comentario).IDMarca, 
            'I', "@int_id_marca");

    factory.AddParameters();

  开发者_Go百科  foreach (DataRow drFilial in factory.GetData().Rows)
    {
        Regionias filial = new Regionias()
        {
            IDRegional = Convert.ToInt32(drFilial["int_id_regional"]),
            TxtRegional = drFilial["txt_regional"].ToString()
        };

        lstRegional.Add(filial);
    }

    return lstRegional;
}

In this example "using" use an static SqlConnection from FactoryDB class,which use SingleTon pattern to get the unique instance of it.

I wonder if is correct to use Connection like this,cause if a want to execute another query in DB,i need to set the "FactoryDB.Conn" propertie to NULL.

---

Create a new instance of SqlConnection every time you need one, and dispose of it as quickly as possible. The underlying connection pooling will be able to manage the physical connections.

If you use a single static connection, things get very complicated if you ever need any multi-threading...

EDIT: If you're setting the value to null so it'll get recreated anyway, what's the benefit of having a static variable at all? Just use a local variable:

using (SqlConnection connection = CreateConnection())
{
    ...
}

Much simpler, no risk of threads trampling on each other.

---

This construct is problematic because the property will get Dispose-d on exit from the scope of the using statement. Managing a property where memory ownership is not completely encapsulated is likely to be confusing and bug-prone.

Typically using is appropriate where you use a local as in:

using (X x = new X())

That aside - you can use a static if you don't expect multiple threads to hit this code at the same time, or prevent that by locking it. Otherwise I would use a local instance created via new in the using statement.

---

The value of using a singleton vs an instantiated object is not clear to me when referring to SQL connections.

As has been mentioned before, connection pooling handles the physical connections to the database. Forcibly closing SQL connections every time a query is executed can actually have a performance impact. The most costly portion of most database operations is establishing the communication.

I would have to agree with the other answers in that the best approach is to create a new SqlConnection whenever you are querying, and allow SQL Server to handle the use and re-use of available connections.