Web Service SQL Connection Model

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
Tue Jan 31 02:31:35 CST 2006

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.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

"Chris Arnold" <chris.arnold@data-interface.net> wrote in message
news:e3BEBsjJGHA.500@TK2MSFTNGP15.phx.gbl...
> 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
>

Cor
Tue Jan 31 03:23:07 CST 2006


> 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