Connection Question

  • Thread starter Thread starter esha
  • Start date Start date
E

esha

I was suggested by a member of this group not to use global connection
object. Instead of it I was suggested to create a new one in every procedure
and close it at the end of that procedure.
What is the advantage of this approach?
From my beginners point of view it will require some extra code (creation,
opening and closing) and some extra time. What's wrong with the global
connection object which is created at the start of the application and is
closed at the end?
I'm learning Windows Form applications now.

Thank you
Esha
 
esha said:
I was suggested by a member of this group not to use global connection
object. Instead of it I was suggested to create a new one in every procedure
and close it at the end of that procedure.
What is the advantage of this approach?
From my beginners point of view it will require some extra code (creation,
opening and closing) and some extra time. What's wrong with the global
connection object which is created at the start of the application and is
closed at the end?
I'm learning Windows Form applications now.
Click to expand...

The global connection is will either not work in a multithreaded
context or will perform very poorly.

..NET uses a connection pool, so the overhead of open and close
is not that big.

The extra code you write is worth it due to the increased
robustness and reliability.

Arne
 
Thank you.
If I'm not using multithreading (at least for now because to be able to run
I need to learn how to walk first) then what is a preferable approach? -
still local connection for every procedure? Or it will be better to use a
global one?

Esha
 
esha said:
Thank you.
If I'm not using multithreading (at least for now because to be able to run
I need to learn how to walk first) then what is a preferable approach? -
still local connection for every procedure? Or it will be better to use a
global one?
Click to expand...

I can think of a few cases where a global database
connection will work.

I find it hard to come up with an example where it
is the best solution.

The general rule is:
- open as late as possible
- close as soon as possible

Arne
 
Esha,

There is nothing wrong with, however every not in a method placed variable
makes it less easier to review a program. However, you have the choose to
use it global, static or just in the method, just as you prefer. As long as
you open and close it and not dispose it. If you dispose it, than it is in
my opinion easier to place it in a method.

Cor
 
Arne Vajhøj a écrit :
I can think of a few cases where a global database
connection will work.

I find it hard to come up with an example where it
is the best solution.

The general rule is:
- open as late as possible
- close as soon as possible

Arne
Click to expand...

Hi,

You can have a "global" Connection instance and open it every time you
need it, and close it right after. I think it is the same as creating a
new connection from scratch every time, but perhaps slightly more
efficient as well as convenient.

But what if you open your connection and call a method that closes it
though you still want to work on it ? Well, you can open it again...
But what if you do not (or do not want to) know if the methods you call
will close your connection ? Well, you can use the DbConnectionAdapter
class (cf. end of the post). Then, EVERY time you want to open your
connection do the following :
using (DbConnectionAdapter c=new DbConnectionAdapter(myConnection))
{
c.Open();

// Do some work here
}
At the end of the scope, your connection will be in the state you found
it, i.e. opened or closed.

Mathieu

<code>
public class DbConnectionAdapter:
IDisposable
{
public DbConnectionAdapter(IDbConnection connection)
{
_Connection=connection;
_ConnectionState=connection.State;
}

~DbConnectionAdapter()
{
Dispose(false);
}

public void Close()
{
if (_Connection.State!=ConnectionState.Closed)
_Connection.Close();
}

public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}

public void Open()
{
if (_Connection.State!=ConnectionState.Open)
_Connection.Open();
}

protected void Dispose(bool disposing)
{
if (_Disposed)
return;

if (_Connection.State!=_ConnectionState)
switch (_ConnectionState)
{
case ConnectionState.Closed:
_Connection.Close();
break;
case ConnectionState.Open:
_Connection.Open();
break;
}

_Disposed=true;
}

private bool _Disposed;
private IDbConnection _Connection;
private ConnectionState _ConnectionState;
}
</code>
 
Mathieu said:
You can have a "global" Connection instance and open it every time
you need it, and close it right after. I think it is the same as
creating a new connection from scratch every time, but perhaps slightly
more efficient as well as convenient.
Click to expand...

And not thread safe.

Making it a hidden bomb in your code waiting
to create havoc.

Arne
 
Arne Vajhøj a écrit :
And not thread safe.
Click to expand...

Agreed. But taking this into account :
be >> better to use a global one?

Beginner, client application (?), single-threaded : I still think a
global connection is well suited. You cannot grab all the difficulties
at once.
Making it a hidden bomb in your code waiting
to create havoc.
Click to expand...

Unless you know what you are (and have been) doing.
I (maybe wrongly) think that, unless the specifications are really
unclear (and are therefore un-specifications, which happens), you will
know very early whether your code is likely to run in a multithreaded
environment or not, and it is not worth (and often by far) writing
thread-safe code when you do not need to. But the fact that a piece of
code is or is not thread-safe should be clear (it is in MSDN). Thanks
for making it clear here.

Mathieu
 
Mathieu said:
Arne Vajhøj a écrit :

Agreed. But taking this into account :

Beginner, client application (?), single-threaded : I still think a
global connection is well suited. You cannot grab all the difficulties
at once.
Click to expand...

But it is not more difficult to do it right.

I can really not see any reason to learn bad habbits.
Unless you know what you are (and have been) doing.
I (maybe wrongly) think that, unless the specifications are really
unclear (and are therefore un-specifications, which happens), you will
know very early whether your code is likely to run in a multithreaded
environment or not, and it is not worth (and often by far) writing
thread-safe code when you do not need to. But the fact that a piece of
code is or is not thread-safe should be clear (it is in MSDN). Thanks
for making it clear here.
Click to expand...

Code has a tendency to stay around for ever and be used in
different contexts.

In a language like C# there are really very little reason to
not make the code thread safe.

Arne
 
Back
Top