By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,732 Members | 1,435 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,732 IT Pros & Developers. It's quick & easy.

Singleton SqlConnection object

P: n/a
Hello,

I have implemented the singleton pattern for a class to hold a
SqlConnection object that
will be used thruout the application to create commands. My application
is a simple Windows Form
Application that connects to a MSDE Database. I was reading the thread
yesterday on this very
same subject, but it only mentioned ASP.NET Applications. Can anyone
tell me if it's a good idea
to have one single SqlConnection Object in the application. Please note
that the Singleton Class
has been configured to be thread-safe. Below is some of the code I am
using to call the instance
of my Singleton class:

SqlCommand command = DB_Connection.Instance.Connection.CreateCommand();

Once I am done with the connection, I close it down:

command.Connection.Open();

//Do stuff here

command.Connection.Close();

I also dispose of the command object to make sure I free up resources.
Any information, suggestions, critiques
will be welcomed!
Nov 17 '05 #1
Share this Question
Share on Google+
16 Replies


P: n/a

"ed_p" <ed*@noe-mail.com> wrote in message
news:uV**************@TK2MSFTNGP09.phx.gbl...
Hello,

I have implemented the singleton pattern for a class to hold a
SqlConnection object that
will be used thruout the application to create commands. My application
is a simple Windows Form
Application that connects to a MSDE Database. I was reading the thread
yesterday on this very
same subject, but it only mentioned ASP.NET Applications. Can anyone tell
me if it's a good idea
to have one single SqlConnection Object in the application. Please note
that the Singleton Class
has been configured to be thread-safe. Below is some of the code I am
using to call the instance
of my Singleton class:


In a winforms application all the code runs on the same thread (unliess you
explicitly use threading). So using a singleton connection object will be
just fine. If you need to access the database from a background thread, you
would not want to use your "global" connection.

David
Nov 17 '05 #2

P: n/a
Dave,

Thanks for the quick reply, so even though I made the class thread-safe
I still would not be able to use it from a backgroud thread?

David Browne wrote:
"ed_p" <ed*@noe-mail.com> wrote in message
news:uV**************@TK2MSFTNGP09.phx.gbl...
Hello,

I have implemented the singleton pattern for a class to hold a
SqlConnection object that
will be used thruout the application to create commands. My application
is a simple Windows Form
Application that connects to a MSDE Database. I was reading the thread
yesterday on this very
same subject, but it only mentioned ASP.NET Applications. Can anyone tell
me if it's a good idea
to have one single SqlConnection Object in the application. Please note
that the Singleton Class
has been configured to be thread-safe. Below is some of the code I am
using to call the instance
of my Singleton class:

In a winforms application all the code runs on the same thread (unliess you
explicitly use threading). So using a singleton connection object will be
just fine. If you need to access the database from a background thread, you
would not want to use your "global" connection.

David

Nov 17 '05 #3

P: n/a

"ed_p" <ed*@noe-mail.com> wrote in message
news:uV**************@TK2MSFTNGP09.phx.gbl...
I have implemented the singleton pattern for a class to hold a
SqlConnection object that
will be used thruout the application to create commands.


Why?

"When you use the .NET Framework Data Provider for SQL Server, you do not
need to enable connection pooling because the provider manages this
automatically"

The only overhead you'll incurr by creating a SqlConnection on an "as
needed" basis is the object creation itself. If that's a problem for your
application then I suppose using a singleton makes sense. Otherwise, I would
not.
Nov 17 '05 #4

P: n/a
Ron
If the connection string doesn't change, it's really not a good idea to keep
the connection open all along (if it ever is a good idea...). Conversely,
it's a great idea to use connection pooling. If you use connection pooling,
when you create a new connection object, you are actually getting an instance
from the pool so there is no real overhead. When you close a connection, you
are not really disposing the object but it goes back to the pool. This is
already optimized as is.

"Scott Roberts" wrote:

"ed_p" <ed*@noe-mail.com> wrote in message
news:uV**************@TK2MSFTNGP09.phx.gbl...
I have implemented the singleton pattern for a class to hold a
SqlConnection object that
will be used thruout the application to create commands.


Why?

"When you use the .NET Framework Data Provider for SQL Server, you do not
need to enable connection pooling because the provider manages this
automatically"

The only overhead you'll incurr by creating a SqlConnection on an "as
needed" basis is the object creation itself. If that's a problem for your
application then I suppose using a singleton makes sense. Otherwise, I would
not.

Nov 17 '05 #5

P: n/a
Hi ed_p,
there are quite a few reasons not to do this:

1. If you are running in a multithreaded environment then you do not want
multiple threads executing commands on the same connection, since this could
cause exceptions to be thrown if they both try to utilize the connection at
the same time.

2. Connections should not be kept open for too long, by keeping this
connection to yourself you are tying it up so that other resources cannot use
it. You may keep it open even if you are not using it. Usually you want to
get the connection, use it and close it ASAP.

3. By keeping the connection open for long periods of time you are
increasing the likelyhood that the connection gets forecfully closed from the
database side i.e. by the sys admin or a firewall detects the connection has
been idle for a long time and kills the connection etc. You will need
tobuild code into your class to handle these cases i.e. check the connnection
is still open etc. This is an unnecessary hassle for you.

4. .Net handles this kind of scenario behind the scenes using a Connection
Pool. When you open a connection it initially takes some time to connect, but
from there on whenever you call Close, really the connection is not closed
straight away but returned to a connection pool, ready to be used again.
Next time you call Open you will get an open connection from the pool
immediately without any overhead. So there is really no need to have a
singleton object for your connection.
Infact I had to spend my time recently fixing someone elses code who just
left where I work. He had made a static connection object on the class in a
multi threaded environment, not very pretty :-)

Mark R Dawson
http://www.markdawson.org

"ed_p" wrote:
Hello,

I have implemented the singleton pattern for a class to hold a
SqlConnection object that
will be used thruout the application to create commands. My application
is a simple Windows Form
Application that connects to a MSDE Database. I was reading the thread
yesterday on this very
same subject, but it only mentioned ASP.NET Applications. Can anyone
tell me if it's a good idea
to have one single SqlConnection Object in the application. Please note
that the Singleton Class
has been configured to be thread-safe. Below is some of the code I am
using to call the instance
of my Singleton class:

SqlCommand command = DB_Connection.Instance.Connection.CreateCommand();

Once I am done with the connection, I close it down:

command.Connection.Open();

//Do stuff here

command.Connection.Close();

I also dispose of the command object to make sure I free up resources.
Any information, suggestions, critiques
will be welcomed!

Nov 17 '05 #6

P: n/a

"Mark R. Dawson" <Ma*********@discussions.microsoft.com> wrote in message
news:38**********************************@microsof t.com...
Hi ed_p,
there are quite a few reasons not to do this:

1. If you are running in a multithreaded environment then you do not want
multiple threads executing commands on the same connection, since this
could
cause exceptions to be thrown if they both try to utilize the connection
at
the same time.

2. Connections should not be kept open for too long, by keeping this
connection to yourself you are tying it up so that other resources cannot
use
it. You may keep it open even if you are not using it. Usually you want
to
get the connection, use it and close it ASAP.

3. By keeping the connection open for long periods of time you are
increasing the likelyhood that the connection gets forecfully closed from
the
database side i.e. by the sys admin or a firewall detects the connection
has
been idle for a long time and kills the connection etc. You will need
tobuild code into your class to handle these cases i.e. check the
connnection
is still open etc. This is an unnecessary hassle for you.

4. .Net handles this kind of scenario behind the scenes using a Connection
Pool. When you open a connection it initially takes some time to connect,
but
from there on whenever you call Close, really the connection is not closed
straight away but returned to a connection pool, ready to be used again.
Next time you call Open you will get an open connection from the pool
immediately without any overhead. So there is really no need to have a
singleton object for your connection.


1 and 2 don't apply to Winforms apps. 3 isn't helped by pooling as you
still generally need code to detect broken connections.

The problem with 4 is that when you use connections at places in the stack
you will open multiple connections and consequently cannot enlist the
changes in a single transaction.

David
Nov 17 '05 #7

P: n/a

"ed_p" <ed*@noe-mail.com> wrote in message
news:e1**************@TK2MSFTNGP09.phx.gbl...
Dave,

Thanks for the quick reply, so even though I made the class thread-safe I
still would not be able to use it from a backgroud thread?


Even if

SqlCommand command = DB_Connection.Instance.Connection.CreateCommand();

is thread-safe, you cannot use the returned command if another thread may be
using the connection, so you would have to syncronize the entire block in
which the SqlCommand is used.

David
Nov 17 '05 #8

P: n/a
David Browne wrote:

"Mark R. Dawson" <Ma*********@discussions.microsoft.com> wrote in
message news:38**********************************@microsof t.com...
Hi ed_p,
there are quite a few reasons not to do this:

1. If you are running in a multithreaded environment then you do
not want multiple threads executing commands on the same
connection, since this could cause exceptions to be thrown if they
both try to utilize the connection at the same time.

2. Connections should not be kept open for too long, by keeping this
connection to yourself you are tying it up so that other resources
cannot use it. You may keep it open even if you are not using it.
Usually you want to get the connection, use it and close it ASAP.
[...] 1 and 2 don't apply to Winforms apps.


2 certainly applies when connecting to a shared database server. That's
plain client/server with its inherent scalability problems

Cheers,
--
http://www.joergjooss.de
mailto:ne********@joergjooss.de
Nov 17 '05 #9

P: n/a

"Joerg Jooss" <ne********@joergjooss.de> wrote in message
news:xn****************@msnews.microsoft.com...
David Browne wrote:

"Mark R. Dawson" <Ma*********@discussions.microsoft.com> wrote in
message news:38**********************************@microsof t.com...
> Hi ed_p,
> there are quite a few reasons not to do this:
>
> 1. If you are running in a multithreaded environment then you do
> not want multiple threads executing commands on the same
> connection, since this could cause exceptions to be thrown if they
> both try to utilize the connection at the same time.
>
> 2. Connections should not be kept open for too long, by keeping this
> connection to yourself you are tying it up so that other resources
> cannot use it. You may keep it open even if you are not using it.
> Usually you want to get the connection, use it and close it ASAP.
>

[...]
1 and 2 don't apply to Winforms apps.


2 certainly applies when connecting to a shared database server. That's
plain client/server with its inherent scalability problems


It's a winforms app. Whether or not you use a the connection pool it's a
client/server application. Moreover all applications have "scalability
problems", and they aren't problems until you hit the scalability limits.
Moreover again, scalability problems with client/server have largely been
erased by advances in hardware. The number of simultaneous (idle)
connections a DB server can manage is purely a function of how much memory
it has. Database servers with gigabytes of memory can handle a whole lot of
clients.

David
Nov 17 '05 #10

P: n/a
"David Browne" wrote:
1 and 2 don't apply to Winforms apps. 3 isn't helped by pooling as you
still generally need code to detect broken connections.
1 does apply to WinForms, unless you only are using the main UI thread. If
you create multiple threads to process your tasks or even use the threadpool
and they all try to use the same connection you will end up in trouble. I
had to fix some code at work just this week for someones code who had used a
singleton like connection object in one of our multi threaded UI apps - so
this is a problem and I don't recommend you do it.

2 also applies because if you have many win clients hitting a database and
they all hold a connection for the duration of their lifetime then you app
willl not scale. Maybe you will get 50 apps working no problem but the
minute you start getting a significant number of concurrent clients your DB
will run out of connections or slow down significantly. You will have
scaling issues if you hold DB connections for too long - basic software
principle.

3 True there are still times when the conection pool will return you a
connection that is no longer valid, but you are much less likely to see this
happen from the connection pool than if you keep a connection open for long
periods of time. The connection pool will periodically remove bad
connections as it detects them.

The problem with 4 is that when you use connections at places in the stack
you will open multiple connections and consequently cannot enlist the
changes in a single transaction.

Well - that depends on your software design. If you are calling many
functions then you could pass a single connection throughout the entire call
tree, enabling you to use a single transaction if that is necessary. One the
calls have completed close the connection.
Mark R Dawson
http://www.markdawson.org


"Mark R. Dawson" <Ma*********@discussions.microsoft.com> wrote in message
news:38**********************************@microsof t.com...
Hi ed_p,
there are quite a few reasons not to do this:

1. If you are running in a multithreaded environment then you do not want
multiple threads executing commands on the same connection, since this
could
cause exceptions to be thrown if they both try to utilize the connection
at
the same time.

2. Connections should not be kept open for too long, by keeping this
connection to yourself you are tying it up so that other resources cannot
use
it. You may keep it open even if you are not using it. Usually you want
to
get the connection, use it and close it ASAP.

3. By keeping the connection open for long periods of time you are
increasing the likelyhood that the connection gets forecfully closed from
the
database side i.e. by the sys admin or a firewall detects the connection
has
been idle for a long time and kills the connection etc. You will need
tobuild code into your class to handle these cases i.e. check the
connnection
is still open etc. This is an unnecessary hassle for you.

4. .Net handles this kind of scenario behind the scenes using a Connection
Pool. When you open a connection it initially takes some time to connect,
but
from there on whenever you call Close, really the connection is not closed
straight away but returned to a connection pool, ready to be used again.
Next time you call Open you will get an open connection from the pool
immediately without any overhead. So there is really no need to have a
singleton object for your connection.


1 and 2 don't apply to Winforms apps. 3 isn't helped by pooling as you
still generally need code to detect broken connections.

The problem with 4 is that when you use connections at places in the stack
you will open multiple connections and consequently cannot enlist the
changes in a single transaction.

David

Nov 17 '05 #11

P: n/a
I am not sure that advances in hardware and memory are really a good excuse
to cover a bad software design. You can save the need to buy more expensive
hardware by writing more efficient code.

Sure you don't want to super optimize everything, but for simpe things like
this that don't take much effort to change you really should try.

"David Browne" wrote:

"Joerg Jooss" <ne********@joergjooss.de> wrote in message
news:xn****************@msnews.microsoft.com...
David Browne wrote:

"Mark R. Dawson" <Ma*********@discussions.microsoft.com> wrote in
message news:38**********************************@microsof t.com...
> Hi ed_p,
> there are quite a few reasons not to do this:
>
> 1. If you are running in a multithreaded environment then you do
> not want multiple threads executing commands on the same
> connection, since this could cause exceptions to be thrown if they
> both try to utilize the connection at the same time.
>
> 2. Connections should not be kept open for too long, by keeping this
> connection to yourself you are tying it up so that other resources
> cannot use it. You may keep it open even if you are not using it.
> Usually you want to get the connection, use it and close it ASAP.
>

[...]
1 and 2 don't apply to Winforms apps.


2 certainly applies when connecting to a shared database server. That's
plain client/server with its inherent scalability problems


It's a winforms app. Whether or not you use a the connection pool it's a
client/server application. Moreover all applications have "scalability
problems", and they aren't problems until you hit the scalability limits.
Moreover again, scalability problems with client/server have largely been
erased by advances in hardware. The number of simultaneous (idle)
connections a DB server can manage is purely a function of how much memory
it has. Database servers with gigabytes of memory can handle a whole lot of
clients.

David

Nov 17 '05 #12

P: n/a

"Mark R. Dawson" <Ma*********@discussions.microsoft.com> wrote in message
news:F4**********************************@microsof t.com...
"David Browne" wrote:
1 and 2 don't apply to Winforms apps. 3 isn't helped by pooling as you
still generally need code to detect broken connections.
1 does apply to WinForms, unless you only are using the main UI thread.
If
you create multiple threads to process your tasks or even use the
threadpool
and they all try to use the same connection you will end up in trouble. I
had to fix some code at work just this week for someones code who had used
a
singleton like connection object in one of our multi threaded UI apps - so
this is a problem and I don't recommend you do it.

Yes, which I mentioned previously.
2 also applies because if you have many win clients hitting a database and
they all hold a connection for the duration of their lifetime then you app
willl not scale. Maybe you will get 50 apps working no problem but the
minute you start getting a significant number of concurrent clients your
DB
will run out of connections or slow down significantly. You will have
scaling issues if you hold DB connections for too long - basic software
principle.
But how many? 50 is nowhere near the limit. And the Connection Pool will
hold connections open too. So you would only save connections where your
Winform app stays open significantly longer than your Connection Pool's
connection lifetime.

3 True there are still times when the conection pool will return you a
connection that is no longer valid, but you are much less likely to see
this
happen from the connection pool than if you keep a connection open for
long
periods of time. The connection pool will periodically remove bad
connections as it detects them.

But Winforms apps don't usually stay up continously like services or web
apps.
The problem with 4 is that when you use connections at places in the
stack
you will open multiple connections and consequently cannot enlist the
changes in a single transaction.


Well - that depends on your software design. If you are calling many
functions then you could pass a single connection throughout the entire
call
tree, enabling you to use a single transaction if that is necessary. One
the
calls have completed close the connection.


You could do a lot of things. I don't like that because it mucks up your
method signatures.

I don't think a global database connecion is really a best practice, but in
a simple Winforms app it shouldn't cause any problems.

I kind of like this pattern. It ties your connections to your threads,
which you are already pooling. Each thread gets its own dedicated
connection the first time it needs one, so it works the same in client code
as server code. The pattern could easily be extended to support
transactions and connection timeouts.
public class DatabaseContext
{
[ThreadStatic]
static SqlConnection connection;

[ThreadStatic]
static DateTime lastUsed;

private DatabaseContext() //not creatable
{
}

public static SqlConnection GetConnection()
{

if (connection == null)
{
SqlConnection con = new SqlConnection("whatever");
con.Open();
lastUsed = DateTime.Now;
connection = con;
}
if (DateTime.Now.Subtract(lastUsed).TotalSeconds > 60) //validate it
{
try
{
new SqlCommand("print 'connection ok'",
connection).ExecuteNonQuery();
}
catch (SqlException)
{
connection = null;
return GetConnection(); //recurse
}
}
lastUsed = DateTime.Now;
return connection;
}

}

David
Nov 17 '05 #13

P: n/a

"Mark R. Dawson" <Ma*********@discussions.microsoft.com> wrote in message
news:3C**********************************@microsof t.com...
I am not sure that advances in hardware and memory are really a good excuse
to cover a bad software design. You can save the need to buy more
expensive
hardware by writing more efficient code.


Ah, but scalability and efficiency are not the same thing. In fact the
designs to increase scalibility usually _reduce_ efficiency.

A couple of examples:

Introducing a middle tier application server to aggregate client connections
and centralize business logic may allow an application to scale out, and may
be good design, but it negatively impacts efficiency by introducing
additional marshaling between components.

Using multiple threads to do some piece of work may allow the job to
complete more quickly, but the overhead required to coordinate the work
increases the overall resource utilization.

David

Nov 17 '05 #14

P: n/a
I believe you are really talking about performance rather efficiency in your
example.

If I write some server side code that takes 1MB of memory for each
concurrent user compared to writing the same code but only takes 0.1MB per
user then my code is more scalable due to writing more memory efficient code.
Efficiency has led to scalability. Keep in mind the code is now more
efficient in terms of memory but I may have negatively impacted performance
for the sake of efficiency.

"David Browne" wrote:

"Mark R. Dawson" <Ma*********@discussions.microsoft.com> wrote in message
news:3C**********************************@microsof t.com...
I am not sure that advances in hardware and memory are really a good excuse
to cover a bad software design. You can save the need to buy more
expensive
hardware by writing more efficient code.


Ah, but scalability and efficiency are not the same thing. In fact the
designs to increase scalibility usually _reduce_ efficiency.

A couple of examples:

Introducing a middle tier application server to aggregate client connections
and centralize business logic may allow an application to scale out, and may
be good design, but it negatively impacts efficiency by introducing
additional marshaling between components.

Using multiple threads to do some piece of work may allow the job to
complete more quickly, but the overhead required to coordinate the work
increases the overall resource utilization.

David


Nov 17 '05 #15

P: n/a

"Mark R. Dawson" <Ma*********@discussions.microsoft.com> wrote in message
news:D6**********************************@microsof t.com...
I believe you are really talking about performance rather efficiency in
your
example.

If I write some server side code that takes 1MB of memory for each
concurrent user compared to writing the same code but only takes 0.1MB per
user then my code is more scalable due to writing more memory efficient
code.
Efficiency has led to scalability. Keep in mind the code is now more
efficient in terms of memory but I may have negatively impacted
performance
for the sake of efficiency.


"Performance" is really a meaningless term.

I agree that increases in Efficiency cause increases Scalability, it's the
reverse that's often not the case.

David
Nov 17 '05 #16

P: n/a
David Browne wrote:

"Joerg Jooss" <ne********@joergjooss.de> wrote in message
news:xn****************@msnews.microsoft.com...
David Browne wrote:
[...]
1 and 2 don't apply to Winforms apps.
2 certainly applies when connecting to a shared database server.
That's plain client/server with its inherent scalability problems


It's a winforms app. Whether or not you use a the connection pool
it's a client/server application.


Um, that's what I said. Or meant ;-) Any WinForms app with live
connections (pooled or not) to a DB is a traditional Client/Server app.
Moreover all applications have
"scalability problems", and they aren't problems until you hit the
scalability limits. Moreover again, scalability problems with
client/server have largely been erased by advances in hardware.
Hear ye, hear ye. Know I'm really interested how you come to this
conclusion?
The
number of simultaneous (idle) connections a DB server can manage is
purely a function of how much memory it has. Database servers with
gigabytes of memory can handle a whole lot of clients.


That's not the point. Why would anybody pay for a database server that
supports 10000 simultaneous connections, when using a different
architecture the requirement shrinks to a 100 connections? Being able
to scale up or out is one thing, maxing the box out from the get-go is
another ;-)

Cheers,

--
http://www.joergjooss.de
mailto:ne********@joergjooss.de
Nov 17 '05 #17

This discussion thread is closed

Replies have been disabled for this discussion.