Opening and closing database connection

Question

4.00/5 (1 vote)

See more:

Polling MySQL database with Timer class every millisecond for 8 hrs a day, wanted to know if I should open and close the db after each connection or should I just leave the connection open until the total process is complete?

Seems pretty inefficient to keep re-establishing a connection every millisecond but I'm only guessing. I recall reading something along the lines of the MySQL using connection pooling and actually not disposing of the connection but adding it back to the connection pool but I'm not exactly sure on that...

Can someone please enlighten me on this one?

-Dee

Posted 24-Nov-11 17:41pm

d.allen101

Updated 26-Nov-11 9:39am

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Nitesh Kejriwal · Answer 1 · 2011-11-24T18:08:00

Solution 1

If the connection is created every millisecond, it would be better to keep connection open..

Posted 24-Nov-11 18:08pm

Nitesh Kejriwal

Comments

d.allen101 25-Nov-11 0:12am

thanks! another question on a different but related topic, is it alright to share that connection object with multiple threads and if so would that also improve performance?

Nitesh Kejriwal 25-Nov-11 0:16am

I think sharing connection object within multiple thread would be a good solution if implemented properly because there is a possibility of deadlocks too. Please check:
http://www.codeproject.com/KB/threads/multithreadedappnconnpool.aspx

As mentioned in article, if you are using shared connection, you should open/close connection everytime.

d.allen101 25-Nov-11 19:41pm

Nitesh thanks for the article. It was VERY informative! It answered exactly what I wanted to know. Thanks again.

Nitesh Kejriwal 26-Nov-11 0:10am

Please mark as answer if you got the solution and upvote so that it can help other people in the community.

**Manoj K Bhoir** · Answer 2 · 2011-11-24T18:13:00

Solution 2

Exactly as the above answer if you want to connect with the DataBase in every second then it is better to keep conncetion open rather than connecting with DataBase in every Second.It saves you system Resources, line of code.

Posted 24-Nov-11 18:13pm

Manoj K Bhoir

Updated 24-Nov-11 21:03pm

v2

Comments

d.allen101 25-Nov-11 0:16am

What about the related comment I posted below the solution?

RaviRanjanKr 25-Nov-11 3:04am

Font was to small. I edited your answer to give appropriate FONT of your Answer.

Sergey Alexandrovich Kryukov · Answer 3 · 2011-11-27T15:33:00

I would advise to use lazy initialization pattern () to open a connection when required and keep it open. Something like this:

C#

class DatabaseWrapper {
   
    internal DatabaseWrapper(/*...*/) {
       //...
       fConnection = new //...
       //...
    }

    /*...*/Connection Connection { // SqlConnection, OleDbConnection, whatever
           get {
              if (fConnection.State != System.Data.ConnectionState.Open)
                 fConnection.Open();
              return fConnection;
           }
    }

    /*...*/ SomeMethod() {
        // here, connection will be guaranteed to open, but only once:
        /*...*/Command command = new /*...*/Command(query, Connection); // SqlCommand, OleDbCommand, whatever...
    }

    /*...*/Connection fConnection; // SqlConnection, OleDbConnection, whatever
}

—SA