Cleaning Up Resources Best Practice

[adamroof]

Whats the best way to consume sql resources and ensure they are released when finished? I previously used alot of "finally" statements, however that doesnt always seem to release properly, especially if im returning a value (within the try block, ya i know). And lately i see alot of code examples that dont even use the finally block.

I'll post a few of "my way", if you could help identify a best practice change for me, that would be super!

How would i change at least the ExecuteNonQuery sample to use the using() mechanism? If that is the better way to go.

1. ExecuteNonQuery's

public void updatePackageShip(int formID, string notes)
{
    SqlConnection sqlCon = new SqlConnection(conString);
    SqlCommand sqlCom = new SqlCommand("config_UpdatePackageShip", sqlCon);
    sqlCom.CommandType = CommandType.StoredProcedure;
    sqlCom.Parameters.Add("@fid", SqlDbType.Int).Value = formID;
    sqlCom.Parameters.Add("@notes", SqlDbType.VarChar, 2000).Value = notes;

    try
    {
        sqlCon.Open();
        sqlCom.ExecuteNonQuery();
        sqlCon.Close();
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message);
    }
}

2. DataSet's

public DataSet ToDoList(int userID)
{
    SqlConnection sqlCon = new SqlConnection(conString);
    SqlCommand sqlCom = new SqlCommand("sp_OppToDo", sqlCon);
    sqlCom.CommandType = CommandType.StoredProcedure;
    sqlCom.Parameters.Add("@userID", SqlDbType.Int).Value = userID;
    SqlDataAdapter da = new SqlDataAdapter(sqlCom);
    DataSet ds = new DataSet();
    try
    {
        da.Fill(ds);
        sqlCon.Close();
        return ds;
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message);
    }
}

 

[jmeckley]

in you code above connection.close should be moved to the finally block. otherwise it is not called when an exception is thrown.

Sqlconnection c = new Sqlconnection();
try
{
  c.open();
  using(Sqlcommand cmd = c.CreateCommand())
  {
    cmd.ComamndText
    cmd.ExecutNonQuery();
  }
}
finally
{
  c.dispose(); // this will close an open connection for you.
}

is the same as

using(Sqlconnection c = new Sqlconnection())
{
  c.Open();
  using(Sqlcommand cmd = c.CreateCommand())
  {
    cmd.ComamndText
    cmd.ExecutNonQuery();
  }
}

dispose is called automatically.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[adamroof]

im confused still on the using block.
Does that replace the try catch block?
How would i use my StoredProc and parameters and conString with that?

 

[jmeckley]

using(Sqlconnection c = new Sqlconnection("connection string goes here"))
{
  c.Open();
  using(Sqlcommand cmd = c.CreateCommand())
  {
    cmd.ComamndText "exec myproc @a, @b";
    cmd.AddParameterWithValue("a", 1);
    cmd.AddParameterWithValue("b", DateTime.Now);
    cmd.ExecutNonQuery();
  }
}

there is no catch, if an exception occurs the command and connection are closed/disposed, then the exception is thrown.

if you wanted to catch the exception you would need to wrap the using block with a try/catch.

try
{
   using(new SqlConnection(string.Empty)) { }
}
catch (Exception e)
{
   Console.WriteLine(e.ToString());
}

in a web application a user is usually redirected to an error page when an exception is thrown. this can be done from the global.asax file.

public class Global : HttpApplication
{
   public Global()
   {
      Error += LogAndRedirect();
   }

   private void LogAndRedirect(object sender, eventArgs e)
   {
      Log.Error(Server.GetLastException());
      Response.Redirect("~/GenericError.aspx");
   }
}

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[GoTerps88]

Actually if you use the using statement, you don't need the close or the dispose methods because they are implicitly called by the using block.

That's the real benefit.

Some people prefer to enclose the using statement within a try catch or try finally block so you can catch the exceptions.

And you can implement the using blocks for more than database connections. You can use them for datasets, datareaders, and commands. Moreover, you can use them for generic collections, lists, etc.

 

[GoTerps88]

I believe the using block will also close and dispose your connection if an exception is thrown. Basically a using block mimics a try finally behind the scenes.

Below is a link that describes this in better detail.

http://davidhayden.com/blog/dave/archive/2005/01/13/773.aspx

 

[jmeckley]

Terps you are correct. Maybe my example above wasn't clear on that, because that's what I thought I said. Either way, thanks for being explicit about it

Jason Meckley
Programmer
Specialty Bakers, Inc.