BeginningWebDesign
asked on
Technical Answer
Hi
Just a technical question, is this the correct way to close a db connection using ADO.NET 2.0
cmdInsertInfo.Connection.O
try
{
rdrInsertDetails = cmdInsertInfo.ExecuteReade
}
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
Just a technical question, is this the correct way to close a db connection using ADO.NET 2.0
cmdInsertInfo.Connection.O
try
{
rdrInsertDetails = cmdInsertInfo.ExecuteReade
}
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
AGBrown
Oops - forget the first "-- use reader", that should be in the second try/catch only
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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("ConnectionS
{
// Create new command, execute, catch exceptions, etc, etc
}
This construct automatically Disposes of the Connection object for you when it leaves the "using" block.
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("ConnectionS
{
// Create new command, execute, catch exceptions, etc, etc
}
This construct automatically Disposes of the Connection object for you when it leaves the "using" block.
ASKER
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
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
ASKER
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.