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.

Answer 1

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.