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.
Drinking: Green Peony Green Tea
>>I would counter that, because of connection pooling, the database connection is only opened once and only closed once, in BOTH cases. Sure, the Open() “method call” may be called twice, but the actual database connection is only done once.
Okay I want to talk a bit about that. You are absolutely right in your assumption that “most probably” there’s only one DB connex.
However, there is a slight overhead associated with querying the internal connex. pool ~ you avoid that by doing this. Obviously it isn’t much but still
Right after I posted that, it started eating at me, and I had to see if I could find any evidence that pointed either way.
I just wrote up my findings on my blog:
http://flimflan.com/blog/DoYouBelieveInConnectionPooling.aspx
Turns out we are both right… connection pooling does prevent the connection from being opened twice, but there is some slight overhead involved in the pool management, as you suggest.
Good post, it got me running to the IDE instead of going to bed.
Great point, Joshua.
Physically the connections are only opened and closed once with connection pooling, but the Open() and Close() method calls cause connections to be grabbed and removed from the pool. I didn’t make this clear enough in the post.
I guess your question is do we care about the overhead of moving connections to and from the pool?
My gut is probably no where near the overhead if connection pooling was not being used, but I don’t have an answer as to the extent of the impact.
However, I still think that explicitly calling Open on the connection in this case is a better idea than letting the adapter do needless connection management for each Fill when we know we have additional fills in the future.
Your point is right on, however, and I just wasn’t clear enough in my writing. Thanks for the comment as it has helped me think about this much more clearly.
That justification doesn’t sit well with me. The title of your post even mentions connection pooling, and yet, the body makes it sound like connection pooling doesn’t exist.
“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.”
I would counter that, because of connection pooling, the database connection is only opened once and only closed once, in BOTH cases. Sure, the Open() “method call” may be called twice, but the actual database connection is only done once.
So I’d be curious to see if there is any true numbers to back up the claim of one approach being more performant than the other, or if it is just someone’s opinion of how they think it works.
Hey David,
I’m really enjoying following your blog posts. I have said repeatedly in the book – again and again –
“open connections as late as possible, and close connections as early as possible.”
Now, sometime middle of this week I will be publishing an article which presents an interesting twist to the above rule
. Stay tuned 
SM