C# sql create one connection and open and close for each query

Question

I recently inherited a C# Web app that creates a new connection for every query like so:

public class QueryForm
{
    public bool getStudents()
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString);
        conn.Open();
        //commands
        conn.Close();
    }

    protected void getProfessors()
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString);
        conn.Open();
        //Commands
        conn.Close();
    }


    protected void getProfessors()
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString);
        conn.Open();
        //Commands
        conn.Close();
    }
}

I understand that this is typically the best way to do it, but is it acceptable or "best practice" to have the constructor create the connection object, and then have each method/Query open and then close that connection like so:

public class QueryForm
{
    SqlConnection conn;

    public QueryForm()
    {
        conn = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString);
    }

    public bool getStudents()
    {
        conn.Open();
        //commands
        conn.Close();
    }

    protected void getProfessors()
    {
        conn.Open();
        //Commands
        conn.Close();
    }

    protected void getCourses()
    {
        conn.Open();
        //Commands
        conn.Close();
    }
}

I prefer the second way as it does not make multiple connection objects. However, the first way would also be preferable if I were to make the methods and class static.

You may want to check this question stackoverflow.com/questions/861552/… — Gonzalo.-
– Gonzalo.-, Commented Jul 8, 2015 at 19:13
@Gonzalo -- In reference to that article, this poster is not mentioning making the SqlConnection static, only the methods and class. — oppassum
– oppassum, Commented Jul 8, 2015 at 19:15
@Oluwafemi "is it acceptable or "best practice" to have the constructor create the connection object, and then have each method/Query open and then close that connection" — Eric
– Eric, Commented Jul 8, 2015 at 19:18

Yousha Aleayoub · Accepted Answer · 2019-12-25 21:45:44Z

11

Either one of these are acceptable. A SqlConnection uses a connection-pool so it shouldn't affect performance much. Having multiple SqlConnection objects isn't going to hurt anything. This comes down to a preference.

I would suggest encapsulating the commands in a using statement if you keep the connections inside the methods, such as:

using (SqlConnection conn = new SqlConnection(...))
{
    conn.Open();
    //commands
    conn.Close();
}

This ensures proper disposal of the connection.

edited Dec 25, 2019 at 21:45

Yousha Aleayoub

5,9294 gold badges61 silver badges72 bronze badges

answered Jul 8, 2015 at 19:14

oppassum

1,76513 silver badges22 bronze badges

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

2 Comments

31eee384 Over a year ago

The using statement means you don't need the .Close(). When the connection is disposed it is closed.

oppassum Over a year ago

Yes but it doesn't hurt, and makes me feel warm and fuzzy inside :-)

Zohar Israel · Accepted Answer · 2015-07-08 20:58:28Z

1

It is important that you keep the connection open for the shortest possible time. SQL connection is using a connection pool so opening a connection actually reuses a connection behind the scenes. If you keep the connection open your at risk of running into error: 'Parallel transactions are not supported' if you use the method BeginTransaction.

This being a web app with a potentially large number of connections, leaving the connection object open longer puts you in risk of exhausting the connection pool and getting: 'The timeout period elapsed prior to obtaining a connection from the pool' error.

Additionally, you must make sure the connection is closed even if an exception is thrown.

This should be done with a using block like:

using (SqlConnection conn = new SqlConnection(...))
{
    conn.Open();
    //commands
}

since exiting a 'using' block calls .Dispose() on the used object.

or with a try / catch / finally block like:

SqlConnection conn = null;

try
{
    conn = new SqlConnection(...);    
    conn.Open();
    //commands
}
catch(Exception ex)
{
    ...
}
finally
{
    if(conn != null)
        conn.Close();
}

answered Jul 8, 2015 at 20:58

Zohar Israel

871 silver badge2 bronze badges

1 Comment

bobbyalex Over a year ago

The remark regarding the exceptions is incorrect. A using block ensures that Dispose is called on the object even if an exception is thrown. learn.microsoft.com/en-us/previous-versions/visualstudio/…

Collectives™ on Stack Overflow

C# sql create one connection and open and close for each query

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related