Closing Database Connections ... very quick question

Question

4.00/5 (1 vote)

See more:

SQL-CE

VB

SQL-Server

, +

As per the Subject line, a very quick question ...

I have code similar to this in various places in my program :

VB

If mySqlConn.State = ConnectionState.Closed Then mySqlConn.Open()
            Using mySqlRdr As SqlCeDataReader = mySqlCmd.ExecuteReader()
                mySqlCmd.ExecuteNonQuery()
                mySqlConn.Close()
                Me.Hide()
                MasterForm.Show()
            End Using

I've just noticed that although I always Open mySqlConn ... in some places I close MySqlConn & in some places I close mySqlRdr ... which should I be doing, or should I be doing both ?!?

Posted 20-Jul-12 8:24am

Gary Heath

Add a Solution

Comments

[no name] 20-Jul-12 14:28pm

For objects that implement IDisposable you should be wrapping their usage in using blocks like you are for your reader.

Gary Heath 20-Jul-12 14:36pm

Some of them are in Using blocks, some aren't, I need to go through and standardise them ... thanks

1 solution

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)

OriginalGriff · Accepted Answer · 2012-07-20T08:29:00

Solution 1

In the code above, you close both your Connection (explicitly, via teh Close method) and the Readewr (implitly, via the using block which does a Close and Dispose).

What you should be doing is closing both and (preferably) Disposing both, since they are scarce resources and should not be hogged!
Personally, I use a using block on the Connection, Command and Reader to be sure.

Posted 20-Jul-12 8:29am

OriginalGriff

Comments

Gary Heath 20-Jul-12 14:37pm

Yes, I just mentioned in the comment above that some of them are, I need to standardise them ... does this mean that if they are in Using blocks, I can omit the Close, or is best practice to close the Connection anyway ?

lewax00 20-Jul-12 14:42pm

Generally, Close is just a call to Dispose anyways (assuming it derives from the Stream class, or uses a similar design). Calling it again doesn't hurt, but when the object is disposed it should release all resources, so you shouldn't need to. The best practice is really just to wrap it in a using block, and let the framework/language do it's job.

Gary Heath 20-Jul-12 14:54pm

Everything's wrapped in Using blocks now, thanks everybody ... and yes, lewax00, that makes a lot of sense !!!

OriginalGriff 20-Jul-12 15:02pm

That is certainly not the case for SqlConnections - Close and Dispose are different animals. Forms are the same, and I would guess that MySqlConnections are as well.
http://blogs.msdn.com/b/kimhamil/archive/2008/03/15/the-often-non-difference-between-close-and-dispose.aspx

lewax00 20-Jul-12 16:05pm

MSDN disagrees, "Close and Dispose are functionally equivalent", from http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.close.aspx#Y0

Gary Heath 20-Jul-12 15:15pm

Bloody hell, excuse me if I give that article a miss for the time being, I'm still struggling with the basic concept of the language !!!

OriginalGriff 20-Jul-12 15:28pm

:laugh:
It was aimed to lewax00 - it does get a bit technical for beginners!

Check the indentation on the messages - it shows what was a reply to what. :)

Gary Heath 20-Jul-12 15:38pm

Ok yes, I see now !!! I'm an experienced programmer, but all on IBM mainframes & have been mucking around with VBA, VB6 & now VB.net for about 4 or 5 years, I tried C# but gave it up, it's way too mental for an old boy like me !!! This is my first foray into SQL as well (though I've used DB2) and whilst the power and the adaptability of all this stuff is brilliant, it's also very difficult to grasp !!! Thanks for your help, both here and on previous questions, it's always appreciated ...

OriginalGriff 20-Jul-12 15:43pm

You're welcome!