programming style question (mysql connection)

[timtos]

Hi.

I have to use a database in my c# program.
For that I created a MySqlManager class.
If it is being instanciated a connection will be established like this:

odbcConnection = new OdbcConnection(connectionTarget);
odbcConnection.Open();
(where connectionTarget is a string describing the database access)

I do not close this connection until the main program is terminated.
Is that a good way or is it better to open and close it for every task?

I am asking because I´ve got a problem with the database access.
My program is running 5 or 6 times great and after that I receive an
odbc connection error. I thought that it could be possible that I get
this error if I do not close the connection properly?!?!

After logging in to windows again everything is working fine - for 5 or 6 times...

Thanks for any tips and tricks,
timtos.

 

[Frank Drebin]

It sounds like the connection is getting closed correctly...

But as far as the other question, there is a trade-off and both have pros
and cons. When you open/close the database every time - that saves resources
on your database but might appear slower on your front-end. With leaving the
connection open, you need to put extra error handling all over the place in
case the server goes down and you are eating up a connection with every
instance of your application.

Since it's mySQL and in this particular case, I'd say do the open/close
method...

 

[timtos]

Thanks for your help!
It helped me!

Greetings,
timtos.

 

[William Ryan]

Timtos:

The answer depends on what you are doing with it. If you
are using a DataReader or any of the commandfunctions
like ExecuteScalar or NonQuery..

The other consideration is how many calls to the db are
being done.

1) If you are using an Adapter to fill datatables
(disconnected ADO.NET), then the Adapter will take care
of opening and closing the connections...don't do it
yourself.

2) If you are using the connection to submit updates
only, or fire execute scalar commands periodically, then
close it. The main metric to consider is the frequency
with which you'll make trips to the db. Leaving
connections open is the antithetical to the whole ADO.NET
thinking ---- after all, something that works with one
user might turn into a nightmare with 1000 users. Which
begs the next question, how many users do you have? THe
more users you have, the more you'll want to close those
connections.

I hope this helps,

Bill

W.G Ryan
(e-mail address removed)
www.knowdotnet.com