DataAdapter and Database Connections - Performance and Connection Pooling

Update: 11/4/2005 - Read Part 2.

We've all been drilled on the cardinal rule of connection pooling:

"open connections as late as possible, and close connections as early as possible."

Normally when you see code using a DataAdapter like below:

 

using (SqlConnection connection =
        new SqlConnection("...")
    {
        SqlCommand command = connection.CreateCommand();
        command.CommandText = "Select * from Products";
        
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = command;
        
        DataSet dataset = new DataSet();
        
        adapter.Fill(dataset);
    }

 

the connection is not explicitly opened in code, because the DataAdapter will take care of this for you.  And by having the DataAdapter take care of this for you, you are following the cardinal rule of connection pooling mentioned above.

Today while reading I came across some code that explicitly opened the connection as opposed to allowing the DataAdapter to take care of the work.  I thought it might be a typo or some mistake at first glance:

 

using (SqlConnection connection =
        new SqlConnection("...")
    {
        SqlCommand sqlCat = connection.CreateCommand();
        sqlCat.CommandText = "Select * from Categories";
        SqlCommand sqlProd = connection.CreateCommand();
        sqlProd.CommandText = "Select * from Products";
        
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = sqlCat;
        
        DataSet dataset = new DataSet();
        
        connection.Open();
        
        adapter.Fill(dataset, "Categories);
        
        adapter.SelectCommand = sqlProd;
        adapter.Fill(dataset, "Products");
        
        connection.Close();
    }

 

However, then I realized that in the case of multiple back-to-back Fill requests to the DataAdapter, it is more performant to explicitly open the connection in the beginning so that each Fill request on the DataAdapter does not open and close the database connection, resulting in the database connection being opened and closed several times.

Because as stated so eloquently in Pro ADO.NET 2.0 (Page 190)

Thus, the SqlDataAdapter always leaves the connection in the same state it took it as.

 

Pro ASP.NET 2.0 in C# Using 2005 also mentions this performance tip (Page 283), which is where I came across similar code as above in the first place:

Note that the connection is explicity opened in the beginning and closed after two operations, ensuring the best possible performance

 

I don't normally find myself doing back-to-back calls using the DataAdapter, but this is still good fundamental knowledge to have on hand.

 

David Hayden ( Website / Blog )

 

posted on Thursday, November 03, 2005 6:47 PM