2

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;";

     DataTable 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

Improve this question
1
  • If I am on the right path with using the "using" statement and try/catch block with it. Commented Jun 17, 2011 at 9:22

6 Answers 6

Reset to default
3

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!

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Yeah, I had a try/finally first where I manually disposed of my object in the finally clause, but decided today to change to the using statement instead, seems cleaner to me. Ye I stacked 3 using statements in my example, SqlConnection, SqlCommand and SqlDataAdapter :) Thanks for the help!
2

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.

Improve this answer

Comments

2

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

Improve this answer

Comments

1

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.

Improve this answer

1 Comment

Ah ok, seems I understand the using as thats basically coding a try/finally statement, but I need to do more research on proper exception handling instead ^^
0

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

Improve this answer

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.