SQL in web service best practices

Question

I'm making a stateless web service which uses Microsoft SQL Server to read data (and only to read), without using transactions. What will be the best of the following:

1. Starting SqlConnection at each call to the web service, or
2. Storing SqlConnection in a static field.

IMHO, the first approach will cost too much resources (if ten clients make ten requests to the web service, it opens one hundred times the database...), but the second one has maybe some problems? Maybe race condition? Or security issues?

Answer 1

Adding a max/min pool size allows SQL server to pool connections so even though you create a new SqlConnection object, the db can pool connections for you.

(MSDN, min pool size)

eg. ConnectionString="Catalog=MyDb; MinPoolSize=10; MaxPoolSize=10..."

Answer 2

Personally, by default I would open the connection with each call, and rely on connection pooling to sort it out for me.

Answer 3

I wouldn't worry about the connection cost unless you are finding that to be problematic for some reason. Connection Pooling will make most of this less of a concern.

If you are working heavily with the database, or retrieving a large amount of static data you might want to consider implementing some kind of caching to reduce the overall cost of contacting the database period. The more data you can keep in the application cache the better, even with stateless web services.