Best connection management

[Hornet77]

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

 

[Miha Markic]

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).

 

[Hornet77]

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

 

[Miha Markic]

Hi Miha

thanks for your reply ;-)

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.

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

Yes, you are killing performances. Otherwise not.

 

[Hornet77]

Miha Markic ha scritto:

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


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

 

[Robbe Morris - [MVP] C#]

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/tutorial...af-5cd3abe27a75/net-propertygrid-control.aspx

 

[William Vaughn]

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)
-----------------------------------------------------------------------------------------------------------------------

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/tutorial...af-5cd3abe27a75/net-propertygrid-control.aspx

Miha Markic ha scritto:


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

 

[Hornet77]

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 ;-)

 

[Miha Markic]

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]

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 { ...} ...
}

 

[Hornet77]

ok, I added the redundant try/finally to manage other circumstance (I
hope this doesn't introduce some other problem when it is just for
closing connection)....

Thank you very much.... you point me in the right direction to write
better code! ;-)

Bye



Miha Markic ha scritto:

 

[William Vaughn]

Ah no. The JIT approach derails (makes impossible) any number of data access
strategies that can dramatically improve performance. I know, as I've used
them to decrease query time by 90%. Since the application manages
server-side objects like TempDB lookup tables (used in subsequent query
JOINs), or server-side cursors where I didn't need to use disconnected data
architectures, I was able to write far more efficient and suitable
applications to support more users more quickly with less hardware. Yes, you
can still build server-side cursors using ADO.NET (ANSI cursors) and no,
they are not suitable for all applications. But consider that the vast
majority of Windows Forms applications have to support 20-200 users,
server-side cursors, use of TempDB table index products and other techniques
that require a persistent connection make sense. Microsoft had apps that
supported over 1000 users on a 386/33 using these techniques.And yes, these
applications use multi-threading and in this case I simply open another
connection.

--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

 

[Guest]

Bill,

Aren't you in effect saying that the vast majority of Windows Forms
applications would be better off using classic ADO than ADO.Net?

Kerry Moorman

Ah no. The JIT approach derails (makes impossible) any number of data access
strategies that can dramatically improve performance. I know, as I've used
them to decrease query time by 90%. Since the application manages
server-side objects like TempDB lookup tables (used in subsequent query
JOINs), or server-side cursors where I didn't need to use disconnected data
architectures, I was able to write far more efficient and suitable
applications to support more users more quickly with less hardware. Yes, you
can still build server-side cursors using ADO.NET (ANSI cursors) and no,
they are not suitable for all applications. But consider that the vast
majority of Windows Forms applications have to support 20-200 users,
server-side cursors, use of TempDB table index products and other techniques
that require a persistent connection make sense. Microsoft had apps that
supported over 1000 users on a 386/33 using these techniques.And yes, these
applications use multi-threading and in this case I simply open another
connection.

--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

 

[William Vaughn]

No, not really. While classic ADO directly supported server-side cursors,
fully async operations (including asyc Open and data retrieval) and other
features not yet implemented, it was tied to the infamous MDAC stack (until
recently) and the vagarities of the changes made to the COM libraries that
implemented it. ADO.NET is limited in several respects (in comparison) but
permits developers to avoid the problems associated with COM and OLE DB
which can hurt performance as it permits developers close access to the TDS
or native protocol streams. I'm happy to be free of OLE DB and other OSFA
interfaces like ODBC. Would I like to see ADO.NET support more "connected"
architectures? You bet. While the disconnected approach makes a lot of sense
for ASP, for Windows Forms (and its replacement in the wings) I think a
connected strategy can make better use of the power and features of SQL
Servers and the DBMS engines like it. Am I holding my breath until they
come? Ah, no. It seems the teams are enamored with the LINQ and Entity Data
Framework technology dejure and will likely to be for the next decade.

--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

Bill,

Aren't you in effect saying that the vast majority of Windows Forms
applications would be better off using classic ADO than ADO.Net?

Kerry Moorman