Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

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
0
BeginningWebDesign
Asked:
BeginningWebDesign
  • 4
  • 2
  • 2
2 Solutions
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now