How Exceptions are handled while designing DataLayer or any other layered architecture

I am creating a data access layer where I want to handle exceptions that should be catched by Business layer with clear idea of the source of exception. I am doing something like this..

EDIT

private void OpenConnection()
        {
                if (ConnectionState.Closed == _connection.State)
                    _connection.Open();
        }

In the above given code i know an reason for exception. And want to throw it to be handled at BL to display message.

But I am using this function in DL only with some other line of codes.

protected DataTable GetDataTable(string Query)
 {
     DataTable d开发者_开发问答t =new DataTable();
     SqlCommand cmd = InitializeCommand(Query);

     SqlDataAdapter adp = new SqlDataAdapter(cmd);
     try
     {
         OpenConnection();
         adp.Fill(dt);
         CloseConnection(true);
         return dt;
     }
     catch (SqlException ex)
     { throw ex; }
     finally
     {
         adp.Dispose();
         cmd.Dispose();
     }
 }

Now if an exception occurs in OpenConnection() while trying to open the connection then the application crashes at the line throw ex in OpenConnection itself while I was expecting it to return exception. How should I handle this issue. Moreover extending my question in the second function GetDataTable if an exception occurs what kind of exception should be thrown with same details as occured at this point and how. I only know this way but this is a wong way i think.

throw new ApplicationException(ex.message,ex.innerexception)

Edit

Suppose Connection to server losts or I am using wrong connection string. Now I use GetDataTable function from BL. Where will exception occur and where should I handle it ? I want to know the problem that occured in DL. Considering BL dont know the code of DL

There are several things you can do.

You can have different try blocks, and throw new exceptions according to where you had the problem:

 try
 {
     OpenConnection();
     adp.Fill(dt);
 }
 catch (SqlException ex)
 { throw new SqlException("Could not open/populate", ex); }
 finally
 {
     adp.Dispose();
     cmd.Dispose();
 }

 try
 {
     CloseConnection(true);
     return dt;
 }
 catch (SqlException ex)
 { throw new SqlException("Could not close connection", ex); }
 finally
 {
     adp.Dispose();
     cmd.Dispose();
 }

My suggestion would be to remove the catch and rethrow since this will destroy the stack trace of the exception. You can have a try with no catch since you are throwing an exception anyway. In this way you can preserve the stack trace and still use the finally:

protected DataTable GetDataTable(string Query)
 {
     DataTable dt =new DataTable();
     SqlCommand cmd = InitializeCommand(Query);

     SqlDataAdapter adp = new SqlDataAdapter(cmd);
     try
     {
         OpenConnection();
         adp.Fill(dt);
         CloseConnection(true);
         return dt;
     }
     finally
     {
         adp.Dispose();
         cmd.Dispose();
     }
 }