Data Access and Resource Questions 1

[andegre]

Hi all, I have an assembly that takes care of all data requests (ie contains sql connections, db2, and oledb). When I instantiate the class for this, and call one of the "fill" methods, it creates a SqlDataAdapter and also a SqlCmd. The "fill" method then calls another method that actually executes the fill while creating a SqlConnection. In this method, the finally statement does a conn.Close(), but does not do a conn.Dispose(). Also, the original calling method (the first "fill" method) does not dispose of the SqlCommand.

Question, do I need to be closing out those resources to prevent a memory leak?

Also, if you have a return statement inside a try block, will it still hit the finally statement?

Thanks!

 

[jmeckley]

1. yes you still need to dispose of it to properly clean up connections. this is especially important when an exception is thrown. put the dispose call in the finally block.

2. yes. read up on how try/catch/finally works this will explain the details of how this mechanism works. most of the time i just let the error bubble up and use

using(var something = new ObjectImplementingIDisposable())
{
   something.DoWork();
}

which translates to

var something = new ObjectImplementingIDisposable()
try
{
   something.DoWork();
}
finally
{
   if(something != null)
   {
       something.Dispose();
   }
}

also be aware that creating a new connection for each sql statement is not very efficient. sometimes it's all you need is 1 sql statement executed. but if you need more than 1 look into aggregating multiple commands using 1 connection instance for that context.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190