IF you have used Data Adapters, you may have noticed that every call to the Fill method will instantiate a new SQL Connection. Say that you loop through a list of items and call the Data Adapter’s Fill method, each call will create a new SQL process (and can be seen in the SQL Server Activity Monitor); these processes each have their own SQL Connection established with the SQL Server – even when the queries originate from a single application request thread or method.

Not only does this require additional server resources, it is also a quick way to empty out the Connection Pool available in your system. Think of it as a DDOS attack that will ultimately block all communications to your server.

(continue reading…)