Having SQL Connection Open throughout C# windows application

[AS]

Hi,

I have a C# windows desktop application that will be used only by one
user at a time. The database server (SQL Server 2000) also resides on
that machine only.

This application has three logical layers (UI, Business layer and
DataAccess layer). Currently, SQL Server connection is opened when a
user first logs in. This connection object (a global variable) is
passed from every form to the business layer and then to the data
access layer where it is used. Finally, when the user logs out, the
connection is closed.

I am not sure if this is an efficient method. I would rather like to
open conenction in the data access layer when required and close the
connection once the job is done.

But I am not clear about the advantages and disadvantages of either of
these approaches. Any advice would be appreciated.

[Marc Gravell]

Passing the connection (and/or a transaction) between your forms and to all
your data-access calls is an abvious headache; plus (to the purist view) it
breaks the functional separation of UI and data-access, since your UI now
knows about the database.

In ADO.net, you can use your second method very easily and efficiently; by
default, ADO.net connections are pooled, so when you let release it, it is
held for "a while". Next time you create a connection with the same
connection string, it is highly likely that you will retreive the same,
already open, connection to the database. You can see this by running sp_who
after closing a connection (in C#) - you will probably see it remains there
for a short while.

Before .Net 2.0, the main disadvantage of the second approach is that it
made it hard to carry a transaction between related database calls -
however, even this is now addressed if you use a TransactionScope (via
using); this acts like "COM+ without COM+", i.e. it creates an ambient
transaction that ADO.net connections will enlist within if necessary. Under
SQL Server 2005, this uses promotable transactions, which means that it uses
an ADO transaction if you work on one (SQL 2005) server, and then upgrades
to a full DTC transaction if you hit multiple servers in the same
TransactionScope; unfortunately, under SQL 2000 this will always use a full
DTC transaction (and IIRC, serializable isolation), so this may (OK, will)
have a performance hit over ADO transactions. For my view, the benefits
outweigh this (especially since I am migrating code that historically uses
DTC...)

Marc

"AS" <Su*****************@gmail.com> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.com...

Hi,

I have a C# windows desktop application that will be used only by one
user at a time. The database server (SQL Server 2000) also resides on
that machine only.

This application has three logical layers (UI, Business layer and
DataAccess layer). Currently, SQL Server connection is opened when a
user first logs in. This connection object (a global variable) is
passed from every form to the business layer and then to the data
access layer where it is used. Finally, when the user logs out, the
connection is closed.

I am not sure if this is an efficient method. I would rather like to
open conenction in the data access layer when required and close the
connection once the job is done.

But I am not clear about the advantages and disadvantages of either of
these approaches. Any advice would be appreciated.

[AS]

Thanks Marc, for your guidance.

Marc Gravell wrote:

Passing the connection (and/or a transaction) between your forms and to all
your data-access calls is an abvious headache; plus (to the purist view) it
breaks the functional separation of UI and data-access, since your UI now
knows about the database.

In ADO.net, you can use your second method very easily and efficiently; by
default, ADO.net connections are pooled, so when you let release it, it is
held for "a while". Next time you create a connection with the same
connection string, it is highly likely that you will retreive the same,
already open, connection to the database. You can see this by running sp_who
after closing a connection (in C#) - you will probably see it remains there
for a short while.

Before .Net 2.0, the main disadvantage of the second approach is that it
made it hard to carry a transaction between related database calls -
however, even this is now addressed if you use a TransactionScope (via
using); this acts like "COM+ without COM+", i.e. it creates an ambient
transaction that ADO.net connections will enlist within if necessary. Under
SQL Server 2005, this uses promotable transactions, which means that it uses
an ADO transaction if you work on one (SQL 2005) server, and then upgrades
to a full DTC transaction if you hit multiple servers in the same
TransactionScope; unfortunately, under SQL 2000 this will always use a full
DTC transaction (and IIRC, serializable isolation), so this may (OK, will)
have a performance hit over ADO transactions. For my view, the benefits
outweigh this (especially since I am migrating code that historically uses
DTC...)

Marc

"AS" <Su*****************@gmail.com> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.com...

Hi,

I have a C# windows desktop application that will be used only by one
user at a time. The database server (SQL Server 2000) also resides on
that machine only.

This application has three logical layers (UI, Business layer and
DataAccess layer). Currently, SQL Server connection is opened when a
user first logs in. This connection object (a global variable) is
passed from every form to the business layer and then to the data
access layer where it is used. Finally, when the user logs out, the
connection is closed.

I am not sure if this is an efficient method. I would rather like to
open conenction in the data access layer when required and close the
connection once the job is done.

But I am not clear about the advantages and disadvantages of either of
these approaches. Any advice would be appreciated.