Should I open and close db for each query?

Question

I am using old school ADO.net with C# so there is a lot of this kind of code. Is it better to make one function per query and open and close db each time, or run multiple queries with the same connection obect? Below is just one query for example purpose only.

 using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectMain"].ConnectionString))
    {
        // Add user to database, so they can't vote multiple times
        string sql = " insert into PollRespondents (PollId, MemberId) values (@PollId, @MemberId)";

        SqlCommand sqlCmd = new SqlCommand(sql, connection);

        sqlCmd.Parameters.Add("@PollId", SqlDbType.Int);
        sqlCmd.Parameters["@PollId"].Value = PollId;

        sqlCmd.Parameters.Add("@MemberId", SqlDbType.Int);
        sqlCmd.Parameters["@MemberId"].Value = Session["MemberId"];

        try
        {
            connection.Open();
            Int32 rowsAffected = (int)sqlCmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            //Console.WriteLine(ex.Message);
        }
    }

Answer 1

For most cases, opening and closing a connection per query is the way to go (as Chris Lively pointed out). However, There are some cases where you'll run into performance bottlenecks with this solution though.

For example, when dealing with very large volumes of relatively quick to execute queries that are dependent on previous results, I might suggest executing multiple queries in a single connection. You might encounter this when doing batch processing of data, or data massaging for reporting purposes.

Always be sure to use the 'using' wrapper to avoid mem leaks though, regardless of which pattern you follow.

Answer 2

Well, you could measure; but as long as you are using the connections (so they are disposed even if you get an exception), and have pooling enabled (for SQL server it is enabled by default) it won't matter hugely; closing (or disposing) just returns the underlying connection to the pool. Both approaches work. Sorry, that doesn't help much ;p

Just don't keep an open connection while you do other lengthy non-db work. Close it and re-open it; you may actually get the same underlying connection back, but somebody else (another thread) might have made use of it while you weren't.