Proper place for db connection in SOAP web service (.net)

Question

I have an asp.net web site and a database.

The web site has a web service for storing feedback from my software.

As far as I know, IIS will reuse the created object for sequential requests. This gives a reason to connect to the DB in web service's constructor, properly implement Dispose() method, and use the connection for serving each [WebMethod] Request(). Current edition follows this patters.

On the other hand I'm afraid that the timespan between sequential requests to webservice will be larger that DB connection timeout. I therefore will need to catch some exception and recreate connection (right?)

The alternative approach is to connect and close in each [WebMethod] Foo(). But I'm afraid this may hurt the performance.

To sum up, should I connect to DB in constructor and close connectiion in Dispose() or connect and close DB for each request?

Answer 1

You should connect for each request.

The page instance is only used for a single request, so you can't store a connection in it to reuse it. You would have to store it somewhere else, and that is so complicated (with thread safety and such) that it's definitely not worth it.

When you close the connection, the actual database connection is returned to the connection pool. The connection pool takes care of resetting the connection properly when it's reused for the next connection object that you create, so that you get a connection that is alive and untainted by previous usage.

The connection pool works fine, and you should use it instead of trying to create one yourself.

You don't have to do anything special to use the connection pooling, it's built into the database driver. Just open and close connection objects as usual.

Answer 2

Do you have any connection pooling facilities at your disposal? Your WS objects would grab a connection from the pool, and return them their when finished. The pool deals with timeouts and real opening and closing.

My order of approach would be:

1). if you have connectio pooling use it.

2). Otherwise keep it simple, open and close on each request. Writing a thread-safe connection pool is not trivial.

3). If performance proves to be a problem then investigate if the connect-each-time overhead is the cause. If so, find a pool implmentaiotn or (last resort) code pooling your self.

Answer 3

ADO.NET 2.0 onwards, DB Connections are pooled, so actual opening and closing of connections are managed by DB Connection pool, you can freely open and close them in your individual methods because the Pool will keep them open until a timeout. You can also configure the pool settings.