SQL connection to database repeating

Question

ok now i am using the SQL database to get the values from different tables... so i make the connection and get the values like this:

        DataTable dt = new DataTable();
        SqlConnection connection = new SqlConnection();
        connection.ConnectionString = ConfigurationManager.ConnectionStrings["XYZConnectionString"].ConnectionString;
        connection.Open();
        SqlCommand sqlCmd = new SqlCommand("SELECT * FROM Machines", connection);
        SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);

        sqlCmd.Parameters.AddWithValue("@node", node);
        sqlDa.Fill(dt);
        connection.Close();

so this is one query on the page and i am calling many other queries on the page.

So do i need to open and close the connection everytime...???

also if not this portion is common in all:

DataTable dt = new DataTable();
        SqlConnection connection = new SqlConnection();
        connection.ConnectionString = ConfigurationManager.ConnectionStrings["XYZConnectionString"].ConnectionString;
        connection.Open();

can i like put it in one function and call it instead.. the code would look cleaner... i tried doing that but i get errors like:

Connection does not exist in the current context.

any suggestions???

thanks

Answer 1

1. You can definitely share the "open connection" code, no reason to duplicate it.
2. With the SQL Server provider for ASP.NET, there is very little overhead with "closing" the connection every time. It just returns the connection to your process' connection pool (see here), so opening the next connection will use very little overhead. I think it is good practice to close the connection after each operation

Answer 2

Typically yes, you make individual connections for multiple rowsets.

If you can use joins to produce a single meaningful rowset, that's typically a good thing to do on the server side instead of the client side.

You may also want to look at making multiple connections and using the async features in order to queue all your requests simultaneously instead of sequentially - have a look at this article.

Answer 3

I use "using". You can include as many queries as you like inside. When complete it will clean up for you.

using (SqlConnection cn = new SqlConnection(connectionString))
{
    using (SqlCommand cm = new SqlCommand(commandString, cn))
    {
        cn.Open();
        cm.ExecuteNonQuery();
    }
}

Answer 4

No you do not have to open and close the connection every time as long as you are using the same database. What you need to change is the

sqlCommand's queryString every time.

Like what @durilai said, [using] is useful. Using actually has more functions than this, but essentially it puts a try/catch block around your code and calls dispose to close the connection in this case.

Anything that needs open/close can be used with using, so things such as text writers, or other objects.