I have received some great feedback from my previous post:
I don't want to beat the post and concepts to death, but I think the post created a bit of confusion and could have been written better, which is what I will attempt to do now.
Goals
My original goal was to introduce a tidbit about the way the DataAdapter manages database connections. A secondary goal was to relate the DataAdapter behavior to the fundamental rule of Connection Pooling - “Open connections to the database as late as possible, and close connections to the database as early as possible.“
DataAdapter Database Connection Management
In general, the rule of thumb with using DataAdapters is to let the DataAdapter open and close the connection when doing a Fill. Why? Because this achieves the fundamental rule of Connection Pooling by opening the connection as late as possible and closing it as early as possible. Realizing the connection to the database is closed, the DataAdapter will open it to retrieve the desired data at the exact moment needed and no earlier. Once finished, the DataAdapter will return the connection to the state in which it was given - in this case Closed.
Thus, the SqlDataAdapter always leaves the connection in the same state it took it as. Pro ADO.NET 2.0 (Page 190)
This isn't new information to me and probably not to you, but things seem a bit clearer to me when talked about in this fashion.
Here is a snippet of code mentioned in the last post that shows how there is no explicit Open and Close of the database connection. SqlDataAdapter is handling the Open and Close for you.
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);
}
However what if you are doing two or more back-to-back Fill(s) in the same snippet of code as follows? Given the information above, if we still allow the DataAdapter to handle connection management, each Fill call will open and close the database connection, causing the connection to be opened and closed twice. Seems like an unnecessary amount of opening and closing of the database connection, when ideally, we could explicity open and close the database connection as shown below and only have this occur only once. This would be the more performant method in my opinion no matter how small the gain in performance.
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();
}
Since the connection is open when the SqlDataAdapter receives it the first time, it kindly leaves it open for itself on the subsequent Fill. The code then explicitly closes the connection as soon as possible, allowing it to be put back into the connection pool if connection pooling is turned on.
Connection Pooling
Establishing and tearing down connections to a database is resource intensive ( slow... ). Because most applications will be re-connecting to a database over and over through its life-cycle, a pool of open connections is created by default for reuse for the application. This is happening under the covers and SqlClient is none the wiser.
When the Open() method is called on the connection, an already open connection is grabbed from the pool to increase performance. When Close() or Dispose() is called on the connection, the connection is essentially returned back to the pool in an open, yet reset, state.
DataAdapter Behavior and Connection Pooling
Using this knowledge, opening and closing a database connection several times with the DataAdapter is not so bad if the connection is taken from the connection pool, because under the covers you are really not physically opening and closing the connection - merely moving the connection to and from the pool.
However, there is still overhead occuring with all this management of the connection pool even if it is slight. From a development perspective, you also don't want to make assumptions that connection pooling is turned on and that the requested connection is coming from the pool.
Therefore, I would still explicity open the connection and close it during back-to-back Fill calls using a DataAdapter
Conclusion
Hopefully this clear up any confusion that may have occurred in my first post.
David Hayden ( Website / Blog )
Drinking: Jasmine Pearls Green Tea