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

Question

For example, I need to populate a lot of DataTables with the SQLDataAdapter Fill () method:

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

Even all 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 is called.

What I want to know is how unnecessarily opening and closing SQLConnections affects application performance. How much does it take to scale to see the bad consequences of this problem (100,000 concurrent users?). On a medium-sized website (50,000 daily) is it worth worrying and looking for all Fill () calls, storing them together in code and opening a connection before any Fill () call and closing after that?

Answer 1

ADO.NET has a connection pool, for example. when you close a connection, it does not completely close, but is "recycled" if you request a new connection with exactly the same connection string.

However, if you already know in advance that you will have to call these five Fill methods one by one, I definitely recommend

• connection opening
• reading all five data tables from the database
• close the connection immediately.

He adopted best practice to do it this way, it won’t hurt you - so just do it! :-)

Mark

PS: ADO.NET, , !:-) - .

Answer 2

:

• , ?

, . , . : / , .

SQL- . , ( - - , ).

:

, . SQL ( ).

, X .

.

X, . .

, ... .

Answer 3

NECRO: - "using", , :

using (SqlConnection conn = new SqlConnection())
{
    DataAdapter1.Fill(DataTable1);
    DataAdapter2.Fill(DataTable2);
    DataAdapter3.Fill(DataTable3);
    DataAdapter4.Fill(DataTable4);
    DataAdapter5.Fill(DataTable5);
    ...
    ...
}