I need a simple pattern to do the above. Few things to note:
1) I have a class that I am obliged to use that does the actual data retrieving and it return DataTable
2) I am not concerned with the generic interfaces that support all possible database types, we are sticking with one database type.
3) How do I elegantly trap the error and inform the user that error occured.
4) Do not offer me to learn MVC - it is not an option right now.
I am interested in the actual pattern design.
You want to use the MVC pattern to separate business logic from presentation (note that I am NOT talking about the ASP.NET MVC Framework) and the DAO pattern to separate business logic from data access.
Your class that returns a DataTable becomes part of your model. You write a layer (a DAO for each domain object) that takes your DataTable and translates it into your various domain objects. Your UI shouldn't contain any logic that does more than presenting output to the user; anything that is actual logic for retrieving data from your model is handled in a controller layer, that gets the data the user is requesting from the model and sends it to the appropriate view to present it to the user.
Exceptions should be handled at layer boundaries; either catch and do something about it (which might just be to log it and send the user to an error page), or wrap in a higher-level exception as appropriate.
Do not offer me to learn MVC - it is not an option right now.
Given your constraints, it seems your only option right now. Or, at least, the best one.
And it's really not all that difficult.
Do not offer me to learn MVC - it is not an option right now.
You can do a simple MVC or MVP pattern without using the ASP MVC Framework. It is pretty simple to introduce. Here is a pretty simple example MVP Example
There are a number of patterns that approach what you describe. I would recommend getting a copy of Martin Fowler's excellent book Patterns of Enterprise Architecture specifically Chapter 14. Web Presentation Patterns. You will find that any serious attempt to separate the presentation, domain (business logic), and data source will lead you to one of many variations of the same theme.
MVC, MVP, visual proxy, etc all break down to three layers what differs is the responsibilities of each layer and how layers communicate with each other.
For instance, the Passive View pattern basically strips the UI layer of anything not directly related to presentation. The typicall example being a field you want to highlight if a certain condition is true. In a Passive View the form would only only contain the logic to determine the whether the field should be highlighted. The business rule that trigger's this state would be in a presenter/controller layer which does not directly depend on the actual view.
As far as the data source layer, the main benefit isn't being able to switch databases at the drop of a hat. The main benefit is that changes to the database schema only affect the data source layer and don't extend into the rest of the application. If your stuck with datasets a good approach is the Table Data Gateway pattern.