CODEWITHSUNDEEP
sqlweb-servicessqlconnection
Arseni Mourzenko
Arseni Mourzenko

Reputation: 52311

SQL in web service best practices

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?

Upvotes: 1

Views: 596

Answers (3)

GrayWizardx
GrayWizardx

Reputation: 21111

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.

Upvotes: 1

Blorgbeard
Blorgbeard

Reputation: 103447

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

Upvotes: 1

Russell
Russell

Reputation: 17719

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..."

Upvotes: 1

Related Questions