locked
What is the best way to handle connection code? RRS feed

  • Question

  • I am confused on the best way to handle connection code. I have seen numerous ways to do it.

     

    Most recently I found a way to handle excptions in a Microsoft "Practical Guidelines and best practices" book that I like.  I am thinking about housing my connection code in a try>finally>catch block in the code below.  Is this the best way to handle a connection?  Is the try/finally block unneccessary?  Thanks to anyone that can help.

     

    public static ConnectToDB()

    {

    try

    try

    {

    // connection code here

    // this would also call a connection string from a config file

    }

    finally

    {

    // close the connection.

     

    }

    }

    catch

    {

    throw

    {

              // error handling would go here incase the connection failed

    }

    }

     

    I also recently saw a blog where someone was saying that the following code will produce the try>finally block in the IL code that will be generated so it isn't neccessary to do this explicitly in the source code.  

     

    using System;
    using System.Data.SqlClient;
    namespace ConsoleApplication1
    {
      public class Program
      {
        public static void Main(string[] args)
        {
          using (SqlConnection cn = new SqlConnection("connection string"))
          {     	
          }
        }
      }
    }

     

     

     

    Thursday, April 24, 2008 8:45 PM

Answers

  • It's true that the using statement will set up a try/finally block for you. 

     

    I sometimes do it this way:

     

    Code Snippet

    // create a sqlconnection, sqlcommand, sqldatareader, etc.  (All my disposable objects).  Set them all to null.

    try

    {

    // instantiate the above created items and use them.

    } finally {

    if (SqlConnection != null)

    SqlConnection.Dispose();

    // etc.

    }

     

     

    Doing this pattern will prevent nested try/finally blocks in the IL code. 

    Thursday, April 24, 2008 8:54 PM
  •  

    the using block will automatically dispose of your object, which is nice and handy but is no harm if you call the dispose method yourself. It just makes it easier and makes sure, I guess from the coding point of view, that you are disposing of an object, the using block is more than enough in most cases, to dispose of your object
    Thursday, April 24, 2008 10:22 PM
  • well yes, it would be better to instantiate the classes in the try block, say if for some reason there is an exception thrown from the constructor of a class, at least you have a catch block there (ok, so you need to add more catch blocks than the SqlException to handle it properly) to handle the exception.

     

    the //whatever is basically yeh as you said.

     

    I personally like to do it the way I had written earlier, its a "small unit", exception caught within the using statement, and you know what you are dealing within that "unit" or using statement, and the object gets dispose automatically because of the using block, makes it tider in a sense also. you can do it this way of course:

     

    SqlCommand command = null;

     

    try

    {

       using (command = new SqlCommand("query", new SqlConnection("connection")))

       {

          command.Connection.Open();

          //do stuff

          command.Connection.Close();

       }

    }

    catch (SqlException ex)

    {

       //handle

    }

    catch (Exception e)

    {

       //general exception caught, handle it

    }

    finally

    {

       //check if connection open, if so, close and dispose

    }

     

     

    Everyone has their preference, most people just like to have the try catch block within the using statement, which would instantiate the object within it. But do read up on what exceptions the class constructor may throw, if any, and implement the try catch block appropriately

    Thursday, April 24, 2008 10:28 PM

All replies

  • It's true that the using statement will set up a try/finally block for you. 

     

    I sometimes do it this way:

     

    Code Snippet

    // create a sqlconnection, sqlcommand, sqldatareader, etc.  (All my disposable objects).  Set them all to null.

    try

    {

    // instantiate the above created items and use them.

    } finally {

    if (SqlConnection != null)

    SqlConnection.Dispose();

    // etc.

    }

     

     

    Doing this pattern will prevent nested try/finally blocks in the IL code. 

    Thursday, April 24, 2008 8:54 PM
  • The best way is as follows:

     

    using (SqlCommand command = new SqlCommand("query", new SqlConncetion(connectionString))

    {

       try

       {

          command.Connection.Open();

          //whatever

       }

       catch (SqlException e)

       {

          //handle

       }

       finally

       {

          if(command.Connection.State == ConnectionState.Open)

          {

             command.Connection.Close();

          }

       }

    }

     

    This is the way I personally do it, as do many developers, including MSFT's

     

    It's funny also because say if we have an N-Tier architecture, and we have a BusinessLogic and DAL, some people develop it so that the DAL has no try/catch blocks, but has a using block, then the try catch is placed in the BusinessLogic...where it handles the exception in any way it needs to.

    Thursday, April 24, 2008 8:57 PM
  • I am a in a little over my head with the IL code stuff so forgive me if I screw up. Is it necessary to call the Dispose() method as in your code example?  I seem to remember reading that the IL will also call this method.  Do you do this as an extra "safety" measure?

     

    Thanks for the help.

    Thursday, April 24, 2008 10:14 PM
  •  

    the using block will automatically dispose of your object, which is nice and handy but is no harm if you call the dispose method yourself. It just makes it easier and makes sure, I guess from the coding point of view, that you are disposing of an object, the using block is more than enough in most cases, to dispose of your object
    Thursday, April 24, 2008 10:22 PM
  • Is the "//whatever" comment where the datareader, dataadapter or other ado.net stuff will be instantiated?  Do you recommend declaring the new instances of the ADO.net stuff before the try>catch>finally block and then instantiating them with in the try block(like David Morton suggested in the other response to my post)? 

     

    thanks for the help.

     

    Thursday, April 24, 2008 10:22 PM
  • well yes, it would be better to instantiate the classes in the try block, say if for some reason there is an exception thrown from the constructor of a class, at least you have a catch block there (ok, so you need to add more catch blocks than the SqlException to handle it properly) to handle the exception.

     

    the //whatever is basically yeh as you said.

     

    I personally like to do it the way I had written earlier, its a "small unit", exception caught within the using statement, and you know what you are dealing within that "unit" or using statement, and the object gets dispose automatically because of the using block, makes it tider in a sense also. you can do it this way of course:

     

    SqlCommand command = null;

     

    try

    {

       using (command = new SqlCommand("query", new SqlConnection("connection")))

       {

          command.Connection.Open();

          //do stuff

          command.Connection.Close();

       }

    }

    catch (SqlException ex)

    {

       //handle

    }

    catch (Exception e)

    {

       //general exception caught, handle it

    }

    finally

    {

       //check if connection open, if so, close and dispose

    }

     

     

    Everyone has their preference, most people just like to have the try catch block within the using statement, which would instantiate the object within it. But do read up on what exceptions the class constructor may throw, if any, and implement the try catch block appropriately

    Thursday, April 24, 2008 10:28 PM
  • Thanks, I like that way. Thanks to both of you for the help.

     

    Thursday, April 24, 2008 10:53 PM