Newing a static var?

[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

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,
"Brett Romero" <ac*****@cygen.com> wrote in message
news:11**********************@z34g2000cwc.googlegr oups.com...

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?

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/...fieldinit.html

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

[Jon Skeet [C# MVP]]

Brett Romero <ac*****@cygen.com> wrote:

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?

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.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

[Peter Bromberg [C# MVP]]

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
--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"Brett Romero" wrote:

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

[Brett Romero]

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

[Jon Skeet [C# MVP]]

Brett Romero <ac*****@cygen.com> wrote:

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.

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.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

[wfairl]

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

[Brett Romero]

This isn't going into a multi threaded environment. How does it being
static void using the connection pool?

Thanks,
Brett

[wfairl]

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.

[Brett Romero]

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

[Jon Skeet [C# MVP]]

Brett Romero <ac*****@cygen.com> wrote:

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?

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.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

[Nick Hounsome]

"Brett Romero" <ac*****@cygen.com> wrote in message
news:11**********************@z34g2000cwc.googlegr oups.com...

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?

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.

[wfairl]

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

[Jon Skeet [C# MVP]]

Nick Hounsome <Ne**@NickHounsome.Me.Uk> wrote:

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.

It also allows for a certain amount of re-entrancy.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

[Brett Romero]

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

[Joanna Carter [TeamB]]

<wf****@gmail.com> a écrit dans le message de news:
11**********************@i40g2000cwc.googlegroups. com...
| 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

--
Joanna Carter [TeamB]
Consultant Software Engineer

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,
"Brett Romero" <ac*****@cygen.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...

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.

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.AppSett ings["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.

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

"Brett Romero" <ac*****@cygen.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.com...

This isn't going into a multi threaded environment. How does it being
static void using the connection pool?

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/de...taprovider.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.

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

"Brett Romero" <ac*****@cygen.com> wrote in message
news:11*********************@i40g2000cwc.googlegro ups.com...

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?

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.

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

[Brett Romero]

>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

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

"Brett Romero" <ac*****@cygen.com> wrote in message
news:11********************@j33g2000cwa.googlegrou ps.com...

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?

Yes, A Reader is a server base forward only cursor. It use a dedicated
connection. That;s why it has an overloaded method that indicate to close
the connection when SqlDataReader.Close is called.
Once both have completed, they are disposed via the using statement.
Are both returned to the pool or just one since they are identical?
Both, they are two differents.
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?
If an exception is throw and you did not use a using block or a finally
then you have a problem.

QOUTE FROM MSDN:

CAUTION:
It is recommended that you always close the Connection when you are
finished using it in order for the connection to be returned to the pool.
This can be done using either the Close or Dispose methods of the Connection
object. Connections that are not explicitly closed might not be added or
returned to the pool. For example, a connection that has gone out of scope
but that has not been explicitly closed will only be returned to the
connection pool if the maximum pool size has been reached and the connection
is still valid.
END QUOTE

As you can see it might not be returned. so you better close all those
connections always !

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

The pool itself is private to the Sql provider, there is no way AFAIK to
access it from the exterior , nor that you need it, if you follow the rules
using the connections as suggested you should be ok

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation