CODEWITHSUNDEEP
c#sqlsql-server
Eric
Eric

Reputation: 2096

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

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.

Upvotes: 7

Views: 8300

Answers (2)

oppassum
oppassum

Reputation: 1775

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.

Upvotes: 11

Zohar Israel
Zohar Israel

Reputation: 87

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

Upvotes: 1

Related Questions