Newing a static var?

  • Thread starter Thread starter Brett Romero
  • Start date Start date
B

Brett Romero

I'd like a variable to be created once and called statically from
anywhere in the app. Is the following sufficient or will multiple
instances still be created:

private static SqlConnection Conn = new SqlConnection();

Is it necessary to create the above as a private field, use a property
and check for null on the field...then new it up and return the
connection?

Thanks,
brett
 
Hi,


Brett Romero said:
I'd like a variable to be created once and called statically from
anywhere in the app. Is the following sufficient or will multiple
instances still be created:

private static SqlConnection Conn = new SqlConnection();

Is it necessary to create the above as a private field, use a property
and check for null on the field...then new it up and return the
connection?
Click to expand...

It's ok, it will be called only once during the live of the AppDomain
(usually the live of the app).
You can create a property to access it:

public static SqlConnection GetConnectin { get{return Conn;}}

Take a look at
http://www.yoda.arachsys.com/csharp/constructors.html

and then , this one explain how the compiler handle those kind of
initializers.

http://www.yoda.arachsys.com/csharp/beforefieldinit.html
 
Brett Romero said:
I'd like a variable to be created once and called statically from
anywhere in the app. Is the following sufficient or will multiple
instances still be created:

private static SqlConnection Conn = new SqlConnection();

Is it necessary to create the above as a private field, use a property
and check for null on the field...then new it up and return the
connection?
Click to expand...

Well, I'd still advise using a property, but you won't need to check it
for null. The static initializer is guaranteed to be called exactly
once.
 
Brett,
My sense is that a SqlConnection object may not be the best candidate to be
static.
What if you forget to close it? What if you decide you need a different
connection string? It potentially becomes problematic.

I'd feel safer and happier by providing myself with a static method
"GetSqlConnection" that accepts a connection string as a ctor parameter and
returns a new SqlConnection.

Then it's up to you to open it, check its State, close it and allow it to
return to the connection pool.

Just my 2 cents.
Peter
 
I disagree with passing a connection string around as a parameter. The
parameter will require extra coding, logic, bugs, etc vs. just calling
a static var, Knowing I will make use of the connection pool, this
connection will be released on app closure as it should be since app
startup created it.

Brett
 
Brett Romero said:
I disagree with passing a connection string around as a parameter. The
parameter will require extra coding, logic, bugs, etc vs. just calling
a static var, Knowing I will make use of the connection pool, this
connection will be released on app closure as it should be since app
startup created it.
Click to expand...

How are you going to make sure the connection is only used by one thing
at a time then? Opening and closing a connection as and when you need
it is a much better practice, IMO.
 
How are you going to make use of the connection pool with a static
connection object? This is not scalable if you deploy your library in a
multi-threaded environment (web app, etc).
 
This isn't going into a multi threaded environment. How does it being
static void using the connection pool?

Thanks,
Brett
 
Well I suppose you could have a pool that only ever had 1 connection in
it (unless one becomes errored) but if you're going to have a static
connection object then why would you even bother closing it? I
apologize though, this deviates from your original question.
 
No - it's a good question. I just want to understand if I'm going into
a direction that doesn't make any sense. My original goal was to keep
from newing up a connection everytime something in the app makes a
connection. Everything in the app uses this same connection so it made
sense to centralize it and reference it statically. It isn't multi
threaded so the connection should be used only once at a
time...correct?

Wouldn't you want to close the connection regardless....when the app
closes in my situation for example?

Thanks,
Brett
 
Brett Romero said:
No - it's a good question. I just want to understand if I'm going into
a direction that doesn't make any sense. My original goal was to keep
from newing up a connection everytime something in the app makes a
connection. Everything in the app uses this same connection so it made
sense to centralize it and reference it statically. It isn't multi
threaded so the connection should be used only once at a
time...correct?
Click to expand...

Not necessarily. What if one database access method calls another one?
You're the one who knows the app best, of course, but it's far from
unusual to want to make one call which requires the database connection
from another which already has it open - sometimes indirectly. At that
point, if you use the connection pool and keep the connection open only
for as long as you need it, you're fine. If, however, if you use a
single connection for the whole time, you're in trouble.
 
Brett Romero said:
I'd like a variable to be created once and called statically from
anywhere in the app. Is the following sufficient or will multiple
instances still be created:

private static SqlConnection Conn = new SqlConnection();

Is it necessary to create the above as a private field, use a property
and check for null on the field...then new it up and return the
connection?
Click to expand...

This is fine as far as static fields go but wrong as far as SqlConnection
goes.
An connections are pooled so the lifetime of a real connection is not the
same as the lifetime of an SqlConnection object.

The correct way to use SqlConnection objects is to keep them open for as
short a time as possible - The exact opposite of what you are trying to do.
This allows efficient use across multiple threads without explicit shared
state.
 
Yes, close it when the app closes but in that case the pool would be
gone anyway and when you restart the app you'll have a new pool. You
won't notice the difference in performance between using a connection
pool ("newing" a connection object everytime) and your static
connection object. Also, as I mentioned before, the connection pool
scales perfectly and works perfectly in a multi-threaded environment.
It's not just multi-threading you need to worry about though, what
would you do in the situation where you needed two connections on the
same thread (Maybe while looping through a datareader and you want to
do an update or lookup)?
 
Nick Hounsome said:
The correct way to use SqlConnection objects is to keep them open for as
short a time as possible - The exact opposite of what you are trying to do.
This allows efficient use across multiple threads without explicit shared
state.
Click to expand...

It also allows for a certain amount of re-entrancy.
 
Sounds as though the best route is to new the connection in a using
statement for each use and define the connection string centrally.
This way I dispose of the connection after each use and keep the string
in sync through out the app.

Thanks,
Brett
 
<[email protected]> a écrit dans le message de (e-mail address removed)...
| Yes, close it when the app closes but in that case the pool would be
| gone anyway and when you restart the app you'll have a new pool. You
| won't notice the difference in performance between using a connection
| pool ("newing" a connection object everytime) and your static
| connection object. Also, as I mentioned before, the connection pool
| scales perfectly and works perfectly in a multi-threaded environment.
| It's not just multi-threading you need to worry about though, what
| would you do in the situation where you needed two connections on the
| same thread (Maybe while looping through a datareader and you want to
| do an update or lookup)?

So are we saying that it is actually more efficient to create a new
connection every time you want to touch the database, rather than use the
MARS to allow multiple queries in the same transaction ?

I have a scenario where I store an object that contains nested objects. I
want to store everything in the context of a single transaction; if one part
fails the whole transaction should rollback. I found that this seems to
require using MARS to allow the multiple queries involved.

Or are we saying that I would need MARS within the context of opening a new
connection for every transaction ?

Joanna
 
Hi,


Brett Romero said:
I disagree with passing a connection string around as a parameter. The
parameter will require extra coding, logic, bugs, etc vs. just calling
a static var, Knowing I will make use of the connection pool, this
connection will be released on app closure as it should be since app
startup created it.
Click to expand...

How you are going to use the pool if you will possible have the connection
open all the time?

What if a DataReader is using the connection? in this case nobody else can
use the same connection.

IMO what should be static is a method that return a newly created
SqlConnection. you could do something like:

static public SqlConnection GetConn() { return
GetConn(
System.Configuration.ConfigurationSettings.AppSettings["ConnString"] );}
static public SqlConnection GetConn( string ConnString) { return new
SqlConnection( ConnString);}


Even better should be a class that encapsulate all the access to the DB.
 
Hi,

Brett Romero said:
This isn't going into a multi threaded environment. How does it being
static void using the connection pool?
Click to expand...

What if it does in the future?

Plan ahead always, more in a case like this where it's extremely simple to
have a solution you know will scale should the need arise.

To learn more about how the pooling works take a look at
http://msdn.microsoft.com/library/d...nectionpoolingforsqlservernetdataprovider.asp


basically the idea is that open a new connection to a remote server is
expensive, so the framework keeps a connection open even after you called
Close and you are not longer using the object. if some other part of the
code reqeust a connection instead of creating a new one and open a new
connection to the server your code receives the previous one.
 
Hi,

Brett Romero said:
No - it's a good question. I just want to understand if I'm going into
a direction that doesn't make any sense. My original goal was to keep
from newing up a connection everytime something in the app makes a
connection. Everything in the app uses this same connection so it made
sense to centralize it and reference it statically. It isn't multi
threaded so the connection should be used only once at a
time...correct?
Click to expand...

Even if it sounds weird, you should either close or dispose your connection
as soon as you finish with it. this will allow the connection to return to
the pool and be usable from another part of the app.

This is what I think that happens in the background (but it;s all
speculation from my part)
The SqlConnection object use an internal object that is the one that holds
the connection with the DB. When a new SqlConnection is requested it first
check on an internal pool to see if there are any of these internal objects
available. if so it grab one. otherwise it either create a new one or wait
until one becomes available.
When you close or dispose your connection this internal object is released
and stored back in the pool.


That should be the way it works, more or less. Maybe somebody else can talk
with more ground.
 
When you close or dispose your connection this internal object is released
and stored back in the pool.

Let's say I run an ad hoc query and created the connection via the
"using" statement. During the time that query is running, a data
reader opens, using the same connection string. This is two
connections from the pool right?

Once both have completed, they are disposed via the using statement.
Are both returned to the pool or just one since they are identical?

It would seem feasible to have both in the pool hanging around to avoid
blocking when two connections are needed, which is a high probability
for most apps...I'd think. Especially if a block of code throws an
exception and doesn't dispose of the connection. What happens to the
connection than?

Where exactly in the framework is the connection pool? Where are some
references on that?

Thanks,
Brett
 
Back
Top