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!
16  13946 
"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
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
"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.
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.
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!
"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
"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
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
"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
"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
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
"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
"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
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
"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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Ramesh |
last post by:
hi,
Can we use SQLConnection object to connect to oracle?
I understand that for connecting Oracle we have to use
only ODP.net (or) OLEDB Object. Is it correct? Please...
Thanks,
Ramesh
|
by: The Real Andy |
last post by:
OK, please excuse the simple (ambiguous) nature of this question.
I have a winform, set up as a base class. I inherit about 5 instances
from this form, each relating to a db table. Currently I am...
|
by: Julia |
last post by:
Hi,
My RemoteServer is a singleton remote object hosted by windows service and
accessed by ASP.NET application
using remoting,server activated
Basically my RemoteServer need to send several...
|
by: MrMike |
last post by:
I have a web.config file containing the SQLConnection String to a SQL
Database. Before I implemented this SQLConnection String into web.config, I
had individual SQLConnection objects in each...
|
by: Mike Kansky |
last post by:
Is there a way to force SqlConnection object to use SQLNCLI provider (Native
Sql Client)?
If i put Provider=SQLNCLI in the connection string i get:
System.ArgumentException: Keyword not...
|
by: pratham |
last post by:
Hi and regards
What is best method to have an connection to sql, iam using sqlserver
1. have an sqlconnection open when the application start event in
global.asax so that i dont have to open...
|
by: Victor |
last post by:
Hi Guys I have a problem here.
I want to improve the performance for a website. When I looked into the
system, I have found that the system made the "SqlConnection Object" static.
That mean only...
|
by: bytesFlast |
last post by:
Hi all,
I would like to know what performance difference can arise if I use separate SQLConnection objects on each Form of a Multi-Form Windows .NET Application and a Single Common/Global SQL...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
