How bad is opening and closing a SQL connection for several times? What is the exact effect?

Question

For example, I need to fill lots of DataTables with SQLDataAdapter's Fill() method:

DataAdapter1.Fill(DataTable1);
DataAdapter2.Fill(DataTable2);
DataAdapter3.Fill(DataTable3);
DataAdapter4.Fill(DataTable4);
DataAdapter5.Fill(DataTable5);
....
....

Even all the dataadapter objects use the same SQLConnection, each Fill method will open and close the connection unless the connection state is already open before the method call.

What I want to know is how does unnecessarily opening and closing SQLConnections affect the performance of the application. How much does it need to scale to see the bad effects of this problem (100,000s of concurrent users?). In a mid-size website (daily 50000 users) does it worth bothering and searching for all the Fill() calls, keeping them together in the code and opening the connection before any Fill() call and closing afterwards?

Answer 1

ADO.NET has connection pooling, e.g. when you close a connection, it's not really completely closed, but "recycled" if you ask for a new connection with exactly the same connection string.

Nevertheless - if you already know ahead of time that you'll have to call these five Fill methods one by one, I would definitely recommend

• opening the connection
• reading all five data tables from the database
• closing the connection again right away

It's accepted best practice to do it this way, it doesn't hurt you - so just do it! :-)

Marc

PS: Connection pooling in ADO.NET of course only works if you haven't turned it off ! :-) It's on by default - you'd have to explicitly disable it.