Obtaining SQL connection most efficiently when using ASP.NET and web services

Question

When using web services (we're specifically using asmx and WCF) with ASP.NET, what is the best way to establish a SQL connection? Right now, I'm establishing a new connection for each web service call, but I'm not convinced this will be too efficient when there will be thousands of users connecting. Any insight on this topic would be much appreciated.

Answer 1

What you are doing is fairly standard.

Assuming you are using the same connection string, the connections will be coming from the connection pool, which is the most efficient way to get connections already.

Only doing the work required and closing the connection on each call is good practice.

One thing you can do is cache results and return the cached results for calls that are not likely to result in changed data over the life of the cache item. This will reduce database calls.

Answer 2

It is strongly recommended that you always close the connection when you are finished using it so that the connection will be returned to the pool. You can do this using either the Close or Dispose methods of the Connection object, or by opening all connections inside a using statement in C#. Connections that are not explicitly closed might not be added or returned to the pool.

You should add "Pooling = true" (and add a non-zero "Min Pool Size") to the connection string.