Hi all

I'm developing a software written in c# (.NET 2.0) with a SQL server
2005 database. I have 3 modules that need to access database:

- a simple ASP.NET website receiving event from remote devices via https
calls

- a windows service running 24/7 that act as a server

- a winform application running as a client with user interface,
launched by the user

3 modules use .NET remoting to communicate.

In order to query DB actually I'm using a single connection for each
module, always opened (for the ASP.NET site I use a static instance of
my "dbManager" class)... when I neeed to query the database I use the
already opened connection and leave it opened for further operation.

Is this acceptable? what kind of problems can cause? Should I use
another approach (open and close connection for every operation)?

Thanks in advance

No, this is not a good practice.
Instead, create a new connection instance and open it right before you need
it. After the operation dispose it. Connection pooling will cache physicall
connections for you (given the connecting string is the same).
This is the optimal usage.
In your case, you have a serious problem - you use a single connection
instance (not thread safe) in a multithreaded app (which asp.net is).

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
"Hornet77" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi all
>
> I'm developing a software written in c# (.NET 2.0) with a SQL server 2005
> database. I have 3 modules that need to access database:
>
> - a simple ASP.NET website receiving event from remote devices via https
> calls
>
> - a windows service running 24/7 that act as a server
>
> - a winform application running as a client with user interface, launched
> by the user
>
> 3 modules use .NET remoting to communicate.
>
> In order to query DB actually I'm using a single connection for each
> module, always opened (for the ASP.NET site I use a static instance of my
> "dbManager" class)... when I neeed to query the database I use the already
> opened connection and leave it opened for further operation.
>
> Is this acceptable? what kind of problems can cause? Should I use another
> approach (open and close connection for every operation)?
>
> Thanks in advance

Hi Miha

thanks for your reply ;-)

Miha Markic ha scritto:
> No, this is not a good practice.
> Instead, create a new connection instance and open it right before you
> need it. After the operation dispose it. Connection pooling will cache
> physicall connections for you (given the connecting string is the same).
> This is the optimal usage.

so the best solution is always open and close connection for every
operation on database? I know that open connection is an expensive
operation, so If I need to open and close for every query I have to do
can I produce a performance problem?

> In your case, you have a serious problem - you use a single connection
> instance (not thread safe) in a multithreaded app (which asp.net is).

I use lock statements to avoid this problem.... Am I wrong?

Thanks for your help

"Hornet77" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Miha
>
> thanks for your reply ;-)
>
> Miha Markic ha scritto:
>> No, this is not a good practice.
>> Instead, create a new connection instance and open it right before you
>> need it. After the operation dispose it. Connection pooling will cache
>> physicall connections for you (given the connecting string is the same).
>> This is the optimal usage.
>
> so the best solution is always open and close connection for every
> operation on database? I know that open connection is an expensive
> operation, so If I need to open and close for every query I have to do can
> I produce a performance problem?

Not at all. (physicall) connection pooling is working behind the scenes.
Performance hit is neglible.

>
>> In your case, you have a serious problem - you use a single connection
>> instance (not thread safe) in a multithreaded app (which asp.net is).
>
> I use lock statements to avoid this problem.... Am I wrong?

Yes, you are killing performances. Otherwise not.
--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Miha Markic ha scritto:
>
>> so the best solution is always open and close connection for every
>> operation on database? I know that open connection is an expensive
>> operation, so If I need to open and close for every query I have to do
>> can I produce a performance problem?

>
> Not at all. (physicall) connection pooling is working behind the scenes.
> Performance hit is neglible.
>
>>
>>> In your case, you have a serious problem - you use a single
>>> connection instance (not thread safe) in a multithreaded app (which
>>> asp.net is).
>>
>> I use lock statements to avoid this problem.... Am I wrong?
>
> Yes, you are killing performances. Otherwise not.

All my 3 modules works in a multithread environment so.... if I have in
my "dbManager" class a single SqlConnection instance to open and close
for every operation could be a good solution? or I need to use a new
instance in every "dbManager" method?

Thanks

Open connections as you need them and close them immediately.

Connection pooling holds onto the physical connection even after
you've closed it. From your comments, I "believe" you think
each time you open and close a connection, you are going through
the expensive connect process. You aren't.

As an example. Let's say you needed to walk across the
room and pick up a piece of paper. If you had to do this
ten times, you'd get tired.

Think of connection pooling as someone who will stand
beside you and will hold the pieces of paper while you
are busy. Then, when you need a piece of paper, it quickly
hands it to you and takes it back when you are done.

When it thinks you no longer need the piece of paper, it
will walk across the room and put the paper back.

Kind of a cheesy example but you get the idea...

--
Robbe Morris [Microsoft MVP - Visual C#]
..NET PropertyGrid Control - ListBox, ComboBox, and Custom Classes
http://www.eggheadcafe.com/tutorials...d-control.aspx




"Hornet77" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Miha Markic ha scritto:
>>
>>> so the best solution is always open and close connection for every
>>> operation on database? I know that open connection is an expensive
>>> operation, so If I need to open and close for every query I have to do
>>> can I produce a performance problem?
>
>>
>> Not at all. (physicall) connection pooling is working behind the scenes.
>> Performance hit is neglible.
>>
>>>
>>>> In your case, you have a serious problem - you use a single connection
>>>> instance (not thread safe) in a multithreaded app (which asp.net is).
>>>
>>> I use lock statements to avoid this problem.... Am I wrong?
>>
>> Yes, you are killing performances. Otherwise not.
>
> All my 3 modules works in a multithread environment so.... if I have in my
> "dbManager" class a single SqlConnection instance to open and close for
> every operation could be a good solution? or I need to use a new instance
> in every "dbManager" method?
>
> Thanks
>
>

As I have said many times before, this JIT connection strategy is a "best
practice" for ASP architectures. It is not always (or even usually) the best
choice for "connected" Windows Forms architectures. JIT precludes use of
server-side state management which can dramatically improve query
performance. Consider that each trip to the ConnectionPool requires the
interface to reauthenticate your credentials and reset the connection--these
operations are not free and totally unneeded with client/server
architectures.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"Robbe Morris - [MVP] C#" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Open connections as you need them and close them immediately.
>
> Connection pooling holds onto the physical connection even after
> you've closed it. From your comments, I "believe" you think
> each time you open and close a connection, you are going through
> the expensive connect process. You aren't.
>
> As an example. Let's say you needed to walk across the
> room and pick up a piece of paper. If you had to do this
> ten times, you'd get tired.
>
> Think of connection pooling as someone who will stand
> beside you and will hold the pieces of paper while you
> are busy. Then, when you need a piece of paper, it quickly
> hands it to you and takes it back when you are done.
>
> When it thinks you no longer need the piece of paper, it
> will walk across the room and put the paper back.
>
> Kind of a cheesy example but you get the idea...
>
> --
> Robbe Morris [Microsoft MVP - Visual C#]
> .NET PropertyGrid Control - ListBox, ComboBox, and Custom Classes
> http://www.eggheadcafe.com/tutorials...d-control.aspx
>
>
>
>
> "Hornet77" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Miha Markic ha scritto:
>>>
>>>> so the best solution is always open and close connection for every
>>>> operation on database? I know that open connection is an expensive
>>>> operation, so If I need to open and close for every query I have to do
>>>> can I produce a performance problem?
>>
>>>
>>> Not at all. (physicall) connection pooling is working behind the scenes.
>>> Performance hit is neglible.
>>>
>>>>
>>>>> In your case, you have a serious problem - you use a single connection
>>>>> instance (not thread safe) in a multithreaded app (which asp.net is).
>>>>
>>>> I use lock statements to avoid this problem.... Am I wrong?
>>>
>>> Yes, you are killing performances. Otherwise not.
>>
>> All my 3 modules works in a multithread environment so.... if I have in
>> my "dbManager" class a single SqlConnection instance to open and close
>> for every operation could be a good solution? or I need to use a new
>> instance in every "dbManager" method?
>>
>> Thanks
>>
>>
>

Thanks to all for your advices.... After reading your messages I've done
some heavy changes to my code: now I have a base class to manage db
operation , named "dbManager", in which there are several method to
query the database, useful for all 3 module (ASP.NET app, windows
service that act as server and winform app that act as client); each
method create an istance of sqlConnection, open the connection, do some
work (update, insert, delete ecc...) and finally close the connection;
generic method has this form:

private bool queryDbMethod()
{
SqlConnection sqlConnection = null;

try
{
using (sqlConnection = new SqlConnection(this.connectionString))
{
sqlConnection.Open();

//query db

return true;
}
}
catch (Exception ex)
{
return false;
}
finally
{
if (sqlConnection != null)
{
sqlConnection.Close();
}
}
}

Then, in each module I have a subclass of "dbManager" that implements
specific command: in my ASP.NET code I wrote a "dbManagerASP" class,
derived from "dbManager" and to avoid to create a new instance of
"dbManagerASP" every time I need to call a method from a page, I use a
static istance of the subclass as member of the subclass, and a set of
static method that use the static istance:

public class dbManagerASP: dbManager
{
private static dbManagerASP instance = null;

private dbManagerASP (string cnx) : base(cnx)
{
}

public static bool Method1()
{
try
{
checkInstance();

return instance.method2();
}
catch
{
return false;
}
}

private static void checkInstance()
{
if (instance == null)
{
string cnx = GetDatabaseConnectionString();

instance = new dbManagerASP(cnx);
}
}

private bool method2()
{
SqlConnection sqlConnection = null;
try
{
using (sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();

//do some work

return true;
}
}
catch
{
return false;
}
finally
{
if (sqlConnection != null)
sqlConnection.Close();
}
}
}

is this a decent solution?

Thank you very much and sorry for my very bad english ;-)

"William Vaughn" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> As I have said many times before, this JIT connection strategy is a "best
> practice" for ASP architectures. It is not always (or even usually) the
> best choice for "connected" Windows Forms architectures. JIT precludes use
> of server-side state management which can dramatically improve query
> performance. Consider that each trip to the ConnectionPool requires the
> interface to reauthenticate your credentials and reset the
> connection--these operations are not free and totally unneeded with
> client/server architectures.

Sorry but this statement doesn't hold water. This is the best practice for
winforms client/server apps, too.
1. There are usually not much separate db operations at all and thus
visiting the connection pool once in a while is not a performance hit. OK,
you can start nickpicking that you loose a millisecond in a day.
2. DB operation time compared to "performance hit" is so huge that "perf
hit" might account for 0.00000000000000000000000000000000000001%.
3. When you do multithreading (which I assume you aren't) connection pool
comes in as a great time saver.
4. It is easier to handle connection failures. If it fails due to network
issue (or some other failure unrelated to logic), just (optionally clear the
pool) repeat the operation, no additional code is required.

I am sure there are other benefits as well.
--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

You don't need try/finally just for making sure that connection is closed as
this is enough (Dispose will call Close for you):

using (SqlConnection conn = new SqlConnection(...))
{
try { ...} ...
}

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