Technical Answer

Hi
Just a technical question, is this the correct way to close a db connection using ADO.NET 2.0

cmdInsertInfo.Connection.Open();
        try
        {
            rdrInsertDetails = cmdInsertInfo.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (Exception)
        {
            return;
        }
        finally
        {
            cmdInsertInfo.Dispose();
            dbConnection.Dispose();
        }
    }

I'm still not sure whether I still have to add this section: dbConnection.Dispose();

Regards
Caz
BeginningWebDesignAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AGBrownCommented:
AFAIK. You shouldn't dispose the object, instead you should do

        cmdInsertInfo.Connection.Open();
        try
        {
            rdrInsertDetails = cmdInsertInfo.ExecuteReader(CommandBehavior.CloseConnection);
            -- use reader
        }
        catch (Exception)
        {
            cmdInsertInfo.Connection.Close();
            return;
        }

        try
        {
            -- use reader
        }
        catch (Exception)
        {
            rdrInsertDetails.Close();
            return;
        }
        rdrInsertDetails.Close();

Your reader is set to run with CloseConnection, so when it is closed it will close the connection. When you first get the reader, that could fail without opening the reader, in which case you should be sure that the connection is closed. Once the reader is open your code that uses the reader could throw an exception, in which case you should be sure that you close the reader.

Andy
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AGBrownCommented:
Oops - forget the first "-- use reader", that should be in the second try/catch only
0
AGBrownCommented:
I should also have explained why I do it like that in development. If my command fails to execute, then I know I have a problem in the database, so I wrap it in a seperate try...catch to make it easier to notify the web app/forms window what has happened. If using the reader fails, I have a different problem, so I handle it in a different try/catch.

You would, obviously, use finally blocks as appropriate with this scheme, and maybe rearrange it altogether if you were certain that the code that used the reader would throw no errors.

But basically, no: you don't have to call dispose :-) Just make sure that the connection is closed, no matter how the code exits, or you'll snarl up your connection pool pretty rapidly.

Andy
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Carl TawnSystems and Integration DeveloperCommented:
Actually calling Dispose is a good idea. The result of calling Dispose is that it both closes the connection and immediately frees any unmanaged resources, whereas close alone only closes the connection.

See here for more details: http://ryanfarley.com/blog/archive/2004/03/17/444.aspx
0
AGBrownCommented:
Well there we go, and now I look more and more places tell me I'm wrong. There is no "shouldn't" there is only "up to you with SqlClient objects" (http://www.lhotka.net/WeBlog/DisposeYourCommandObjects.aspx).

So change "You shouldn't dispose the object, instead you should do"

should read

"You don't _have_ to dispose the object, it might help performance if you do. You can substitute Dispose for Close in this code:"

But in your code, you have called Close by closing the datareader - so I don't know if you then have to call Dispose. Now I look, I suppose this was actually your original question. MSDN suggests that its an either...or scenario, i.e. if Close if called, Dispose has no effect? CARL_TAWN, any ideas?

Andy
0
Carl TawnSystems and Integration DeveloperCommented:
Not sure of the precise inner workings of Dispose, it will vary from object to object. But I was always taught that if an object implements IDisposable (i.e. has a Dispose method) then you should call it.

It does kind of beg the question as to why there is a Close method as well as Dispose when Dispose calls Close for you. Perhaps there is some benefit to calling Close rather than Dipose if you intend to reopen the connection.

The other alternative of course would be to wrap your Connection in a "using" statement:

    using (SqlConnection dbConnection = new SqlConnection("ConnectionString"))
    {
         // Create new command, execute, catch exceptions, etc, etc
    }

This construct automatically Disposes of the Connection object for you when it leaves the "using" block.
0
BeginningWebDesignAuthor Commented:
Hi
Thanks for the info, i'm going to try both ways to see whats happens and split the points between the 2 of you.

Thanks
Caz
0
BeginningWebDesignAuthor Commented:
I have accepted both answers, its just AGBrown was the top of the list in case anyone reads this question and is wondering on the accepted answer layout.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.