C# static SQLConnection

Question

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();

    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.

Did you actually try to run this code? It will fail with an ObjectDisposed exception, the second time you try to use the SqlConnection. — Steven
– Steven, Commented Oct 29, 2010 at 13:19
ive alerady use this,it works fine!The propertie "AutoReset",set the connection to null after its executed. — ozsenegal
– ozsenegal, Commented Oct 29, 2010 at 13:20
In this context your DB connection object property would be singleton if you did not close the encapsulated object every time. If you ever create > 1 instance, it's not a singleton property any more. static != singleton. — Steve Townsend
– Steve Townsend, Commented Oct 29, 2010 at 13:36

Jon Skeet · Accepted Answer · 2010-10-29 13:32:02Z

12

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.

edited Oct 29, 2010 at 13:32

answered Oct 29, 2010 at 13:18

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ozsenegal Over a year ago

Look,in this example,everytime the query is executed,the SqlConnection is set to null,and a new SqlConnection is created automatically,if you need to reuse that.The point is that is static,but there's always a new connection,after a query.

Steve Townsend Over a year ago

@user257234 - if you are going to Dispose it every time anyway, making it static buys you nothing (per Jon's note the physical connection is handled under the covers anyway) and is confusing.

Jon Skeet Over a year ago

@user257234: So why bother with the static variable at all?

ozsenegal Over a year ago

There is no benefits to make it static.Actually it is static,cause this class use SingleTon pattern,and to set it on GetInstance method,the Connection needs to be static.

Steve Townsend · Accepted Answer · 2010-10-29 13:19:18Z

3

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.

answered Oct 29, 2010 at 13:19

Steve Townsend

54.4k9 gold badges100 silver badges145 bronze badges

Comments

dotariel · Accepted Answer · 2010-10-29 14:15:06Z

0

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.

answered Oct 29, 2010 at 14:15

dotariel

1,6041 gold badge12 silver badges23 bronze badges

Collectives™ on Stack Overflow

C# static SQLConnection

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related