Design question

I used to be good at this but its been a while and really confused now.

Designing a 3 tier architecture....

lets focus on BLL And DAL.

DAL has a method, which potentially could throw an exception, such as
COMException or SQLException or whatever. It handles it, logging it to say an
event log, but what should that return back to the caller?

And how would the UI know what error message to display, if any, about how
successful the operation has been? 

Should there NOT be any try catch blocks in the BLL And DAL but only in the UI
Where it is calling the method which may throw an exception, then tell the BLL
to handle that exception and appropriately display a message?

What is the best method/practice for handling the error but also reporting it
back to the UI correctly?

should the DAL only return back an "errorcode" of success, which then
the BLL manages/decides if the operation is successful, if NOT then return a
message back to the UI?

how should this work? Thanks. I REALLY would like to know, it's annoying me alot
and want to make sure I make a good design

This is what i am thinking about this. example in C# 

GUI layer
Button_Click event from code-behind in GUI occurs for example:
DataTable dt;
try
{
  //contact some BLL object method
  dt = BLLobject.SomeMethod();
}
catch(Exception ex)
{
  //Message to the user about some Exception
  Label1.Text = ex.Message + ex.StackTrace (show what you want from the catched
Exception-object)
}

Business Logic Layer (BLL)
Some method called from GUI Code-behind like a Button_Click Event
public DataTable SomeMethod()
{
  try
  {
    //contact some DAL object method
    return DALobject.SomeDALMethod();
  }
  catch(Exception ex)
  {
    Do some event-logging or other things...
    throw ex; (object thrown to GUI Layer to use as part of message to user)
  }
}

Data Access Layer (DAL)
Some method called from BLL
public DataTable SomeDALMethod()
{
  try
  {
    //contact with database
    return a datatable;
  }
  catch(Exception ex)
  {
    Do some event-logging or other things...
    throw ex; (object thrown to BLL Layer)
  }
}

I dont know if this helps, but its a try.....

IMO Your business layer should handle all of your exceptions and error logging,
its at this level where you logic decides whether to bubble the exception up to
the UI layer, log it, create a new exception or a combination of the three. If
you check out the enterprise library it has a great Exception Handling
Application Block designed exactly for this. I use it to produce code like this
in the Business Layer:

try
{ 
      // Call to the DAL
      BugTracker.Globals.OperationResult ret = JobDB.InsertJob(jobID, owner,
subject, assignedTo, comment);
      return ret; 
}
catch (Exception ex)
{
      // Check with the Exception Handling Policy on what action to take
      bool rethrow = ExceptionPolicy.HandleException(ex, "Log Only
Policy");
      if (rethrow)
      {
          // We could wrap the exception, or replace it with our custom
exception here so 
          // that we give a more user friendly error to the user
          throw;
      }
     return BugTracker.Globals.OperationResult.Failure;
}ahmedilyas: Should there NOT be any try catch blocks in the BLL And DAL but
only in the UI Where it is calling the method which may throw an exception, then
tell the BLL to handle that exception and appropriately display a message?
No because if this was the case you would have to put the same TRY and CATCH
logic around all the calls to the business layer, if you are calling the same
BLL method from the UI in different places you are starting to duplicate alot of
code and how can you be sure that people will always wrap the calls to the BLL
correctly and in the same way? Handling any errors in the BLL allows you to deal
with them before bubbling them up to the UI.

ahmedilyas:What is the best method/practice for handling the error but also
reporting it back to the UI correctly?
It depends on what you want to show to the User, if you have tried to add a new
record and there was an error, you may want to return a boolean false to the UI
to show that the record has not been added or you may want to return a sucess
object (see example below) which could contain a boolean property for
success/failure and a notes property which could detail why it failed, or you
could throw a custom user friendly exception from the BLL to the UI, or you
could just let the exception bubble up to the UI if you can't handle it
elegantly in your business logic. It all depends on what the UI is doing and
what information you want to give back to them. 

1) Example If you call to the Business Layer is updating records:

My UI
' My UI Calls the BLL and gets a result response
Dim res As result = myBLL.doSomething()

IfNot res.success Then
      
       ' The call to the BLL failed
       ' Show a user friendly message to the user
       lblWarningMess.Text = res.message

EndIf

My Business Layer Method

PublicFunction doSomething() As Result

      Dim aResult As Result

      Try
             ' Call to DAL
            dal.Something()

           aResult = New Result(True, "")

      Catch sqlEx As Data.SqlClient.SqlException
              aResult = New Result(False, "Something went wrong with the
database friendly message")
              ' log exception....
      Catch ex As Exception
              aResult = New Result(False, "Something went wrong friendly
message")
              ' log exception....
      EndTry

      Return aResult
EndFunction

My Result Object:

PublicClass Result

       Private _Mess AsString
       Private _Success AsBoolean      PublicSubNew(ByVal Success AsBoolean,
ByVal mess AsString)
            _Success = Success
            _Mess = mess
      EndSub

      PublicReadOnlyProperty Message() AsString
            Get
                 Return _Mess
            EndGet
      EndProperty      PublicReadOnlyProperty Success() AsBoolean
            Get
                 Return _Success
            EndGet
      EndProperty

EndClass

2) Example if your BLL is returning something

My UI

Dim res As Result
Dim myCustomer As Customer' My UI Calls the BLL tries to get the customer
object
myCustomer = MyBLL.getSomething(1, res)

IfNot res.Success Then
          ' The call to the BLL failed
          ' Show a user friendly message to the user
          lblWarningMess.Text = res.Message
Else
         ' Use the Customer object
         lblHello.Text = String.Format("Hello ", myCustomer.name)
EndIfMy Business Layer Method

PublicFunction getSomething(ByVal customerID AsLong, ByVal sucessObject As
Result) As Customer

        Dim aCustomer As Customer
 
        Try
             ' Call to DAL
             aCustomer = dal.getMeACustomer(cutomerID)
          
            sucessObject = New Result(True, "")

        Catch sqlEx As Data.SqlClient.SqlException
                  sucessObject = New Result(False, "Something went wrong
with the database friendly message")
                  ' log exception....
        Catch ex As Exception
                  sucessObject = New Result(False, "Something went wrong
friendly message")
                 ' log exception....
        EndTry        Return aCustomer

EndFunction ahmedilyas:I REALLY would like to know, it's annoying me alot and
want to make sure I make a good design
The answer is as always... it depends :0)
If you are wriitng a simple report that will be used by the guys in the office I
would just let the exception bubble up to the UI and not handle it. If however
you are producing a commerical application you may want to never throw an
exception back up the stack but instead catch it, log it and return a
success/failure object with user friendly info on what went wrong and how to
resolve it.

The sample offered by Scott looks fine. But I doubt that business layer should
handle all exceptions and UI layer don't have any exception handler.

The idea is, every layer only handle the exceptions that it can handle, and
return success or fail or other result, and only throw exceptions it can not
handle.
BLL should not seal the unexpected exceptions and only tell the UI layer that
"I failed for unknown reason." It should bubble this exception to UI
instead. Then UI layer has the detail of the exception. Very possible that UI
layer can not handle it either. But at least, UI layer should be able to know
what happened and decide how to handle it by itself.

On the basis of Scott's example, I changed the code as below:

My UI
' My UI Calls the BLL and gets a result response
try
 Dim res As result = myBLL.doSomething()

 If Not res.success Then
      
         ' The call to the BLL failed
         ' Show a user friendly message to the user
         lblWarningMess.Text = res.message 

 End If 
Catch ex As Exception
 'The call to the BLL failed with unexpected exceptions, take some action if
needed. 
 'Say prompt alert window in the UI, display error message in differnt color,
etc.
 lblWarningMess.Text = ex.Message + ex.StackTrace 
End Try

My Business Layer Method 

Public Function doSomething() As Result

      Dim aResult As Result

      Try
             ' Call to DAL
            dal.Something()

           aResult = New Result(True, "") 
       ' We could check for all the different exceptions to see what exactly
went wrong....
       Catch sqlEx As Data.SqlClient.SqlException
              aResult = New Result(False, "Something went wrong with the
database friendly message")
              ' log exception....
      Catch ex As Exception
              aResult = New Result(False, "Something went wrong for unknown
reason friendly message")
              ' log exception....
  throw ex
      End Try

      Return aResult
End Function

Thats a really good point dingmingyu, if we can't handle it in the Business
layer we should throw it back up the call stack.

PublicFunction doSomething() As Result

      Dim aResult As Result
 
      Try
             ' Call to DAL
            dal.Something()

            aResult = New Result(True, "")

           ' We could check for all the different exceptions to see what exactly
went wrong....      Catch sqlEx As Data.SqlClient.SqlException
         
             aResult = New Result(False, "Something went wrong with the
database friendly message")
             ' log exception....

      Catch ex As Exception

            ' We do not know why we errored so log and rethrow
            aResult = New Result(False, "Something went wrong for unknown
reason friendly message") 

           ' Bubble the exception back up to the UI
            Throw ex
      EndTry

      Return aResult

EndFunction