C# "using" statement and try/catch

Ive been doing some research today on when an how to use the "using" statement to dispose of my sql objects. However I'm still confused about when and how to catch unforseen errors. I have a simple method here and would appreciate any input on wheter its correct or I'm doing something wrong?

private BindingList<My_Object> Search(int ID)
{
   string strSelectStatement = 
     "SELECT 'coloumns' " +
     "FROM 'table' " +
     "WHERE ID = @ID;";

     DataTabl开发者_Python百科e dt = new DataTable();
     try
     {
        using (SqlConnection sqlConn = new SqlConnection(m_SQLConnectionString))
        {
          using (SqlCommand cmd = new SqlCommand(strSelectStatement, sqlConn))
          {
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID;
            using (SqlDataAdapter adpt = new SqlDataAdapter(cmd))
            {
               adpt.Fill(dt);
            }
          }
        }

        My_Object myObject;
        BindingList<My_Object> myObjectList = new BindingList<My_Object>();

        foreach (DataRow row in dt.Rows)
        {
          myObject = new My_Object();
          //Fill/set myObject properties and add to myObject list
        }

        return myObjectList;
     }
     catch (Exception)
     {
        //throw the the exception with its stack trace up to the main call
        throw;
     }
}

So what my catch here would do is catch an error if anything went wrong when running adapter.Fill, or while building myObject/list for example.

Thanks

---

In C# . The using statement defines the scope of an item to be disposed. This can be called for any object which implements the IDisposable interface.

http://msdn.microsoft.com/en-us/library/system.idisposable.aspx

So if you had to not use using blocks you would call the dispose method on the class to release/clean up resources created by the object.

When calling a class that implements the IDisposable interface, the try/finally pattern make sure that unmanaged resources are disposed of even if an exception interrupts your application.

If an exception is thrown in the case of a using statement the dispose will still be called. You can also stack using statements

using (SqlConnection sqlConn = new SqlConnection(m_SQLConnectionString))
using (SqlCommand cmd = new SqlCommand(strSelectStatement, sqlConn))
          {
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID;
            using (SqlDataAdapter adpt = new SqlDataAdapter(cmd))
            {
               adpt.Fill(dt);
            }
          }

with regards to exception handling. It is not wise to catch all exceptions try to catch the specific exceptions thrown by the class or method. You can view exception details on msdn so SQLConnection : http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.open.aspx

InvalidOperationException
Cannot open a connection without specifying a data source or server.

or

The connection is already open.

SqlException
A connection-level error occurred while opening the connection. If the Number property contains the value 18487 or 18488, this indicates that the specified password has expired or must be reset. See the ChangePassword method for more information.

So these are the exceptions you should cater for. Hope that helps!

---

Don't catch 'unforeseen' errors, since there's nothing you can do if truly unforeseen.

Unless of course you are wishing to handle these errors in some way, say, to log messages - but the system does that for you - then they are no longer 'unforeseen', since you're expecting them.

As for the code posted, there are problems. Firstly, the try / catch could be said to be trying too much, and given that you have usings in there, that is pointless (if exceptions aren't going to be handled.) It also catches a generic exception, which is highly discouraged; catches should be formulated to filter those that you can handle, and in appropriate order. To catch just to throw is also pointless.

---

Don't catch exceptions if you can do nothing about it. If you catch them is in order to clean up the unmanaged ressources or for logging purposes.

You might have a look on MSDN "Best Practices for Handling Exceptions" http://msdn.microsoft.com/en-us/library/seyhszts.aspx

---

You don't need the try..catch {throw}. This is the same as not having a try..catch block at all.

If you want to log the error of display a friendly message, then put the code in the catch { }.

The Dispose will still be called on the SqlConnection, even if the code crashes.

---

You can catch multiple exceptions at the end of your try statement. This means you can catch each different type of error that could occur i.e. InvalidOperationException / SqlException. MSDN Explains here:

http://msdn.microsoft.com/en-us/library/ms173162(v=vs.80).aspx

---

Since you have enclosed your whole code in try/Catch it will catch all errors raised within try/catch code block. But don't follow this apprach only catch those errors specifically which you want to handle or log. this is recommended because catching error is an overhead.