Web Service SQL Connection Model

31 Jan 2006 7:50 AM

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

31 Jan 2006 8:31 AM

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.

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

Show quote

"Chris Arnold" <chris.arn***@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
>

31 Jan 2006 9:23 AM

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