Best way to handle sql connections in webservice?
I have a web service that is called up to 10 clients. The webservice consists of 7 bit asmx pages and has about 100-200 functions per page.
All these functions work with MSSQL2005 or MS SQL2000 database. Some times of the day its heavy traffic from clients and it seems like I am running out of connections on sql server causing all clients to stop.
In each function, I open a connection, do stuff and then close the connection, sometimes sometimes with transactions.
On the server, I can see that it creates a lot of connections, I don't know why they don't go away, but stays there even after the function is complete and complete. So I can say that my 10 clients make over 80 connections from time to time. Sometimes some of them go away, sometimes they are still there a few hours after use. Are there any pools out there?
Question 1: Is there any other way to handle connections that I should be using, for example one connection globally for each web service or in any other way?
Question 2: If its ok to handle connections for each function, then why doesn't it close the connection on the server, making the list of open connections more and more all the time until I log out of connections - error?
This question is related to my other question, but not the one: Strange SQL2005 problem. "SqlConnection does not support concurrent transactions"
Now I am narrowing it down to the "out of connections" error.
source to share
I agree with the comment regarding refactoring, but that's not a problem.
You should definitely use join in every function and it sounds like you are not using them correctly. Assuming the database operations are contained in the function you are calling, your code should look something like this:
using (SqlConnection connection = <connection code>)
{
using (SqlCommand command = <command code>)
{
// Execute.
}
}
On the server side, connections will remain open. By default, the SqlConnection class enables pooling, so you can see the connection is open on the server.
This is normal and to be expected.
source to share
In fact, you can see the connection to the pool. This happens by default in .Net 2+ prior to SQL 2005 (not sure about other versions).
The .Net connection pooling tool will route multiple connections for you so there is less overhead the next time you connect. The CLR can simply give you an already open (and cleaned up) connection, which is several hundred times faster than reconnecting to the database directly. When you call connection.Close (), you just pass the pool connection to be disposed of.
A pool exists for each individual security context connection - this means that all connections created using the same SQL security will share the pool, whereas if you are using Windows authentication, each individual user connection will have its own pool.
You see problems because you are pushing 100 connections - the default maximum amount in the pool (default minimum is 0). At this point, the pool will not be able to provide another connection until it is recycled, so the application will hang or time out. You need to change your connection string to include a higher maximum number (and also see how to shorten your connections).
source to share