Web Service SQL Connection Model

[Chris Arnold]

Hi All,

I am near completion of a project consisting (broadly) of a web app, a web
service and a SQL server - the usual sort of configuration. Both the SQL
server and the web service are tasked with dealing with large amounts of
data per user; and a large number of users.

At the moment I am using a new SqlConnection object for each request to the
web service. Each of these connections is disposed of properly ...

using (SqlConnection cn = DataService.CreateNewConnection())
{
...
}

Should I, however, be opening 1 single connection at Application startup and
leaving that open for all requests from all Sessions?

What are the pros & cons of both of these approaches?

Many thanks,

Chris

 

[Miha Markic [MVP C#]]

Hi Chris,

You are doing it correctly. Using a global connection is never a good
method.
1. There is no performance hit as connection pool takes care (you might
tweak the number of active connections in the pool through connection
string - see the help files).
2. Doing it this way you are guaranteed that connection is used within
single thread (you can use a single connection only for one operation at
same time).
Just make sure you are disposing connections and everything should be fine.

 

[Cor Ligthert [MVP]]

Just make sure you are disposing connections and everything should be
fine.

--

Although for me is closing enough, is the disposing automaticly done by the
"using" code.

Cor