Looking for a decent data access architecture to implement

[Johann Blake]

I am looking for a good solution on how to implement data access in an
application so that there is a clean separation between the data access
layer, the business layer and the GUI layer. I am looking for a robust
solution for a major application. Almost every developer seems to come
up with a completely different solution. While many of them are not
bad, I really want a very good one.

My database is SQL Server 2000 and I am using Visual Studio .NET for
application development. In the past I use to have a "data access
layer" that I wrote in code to insulate my business logic layer from
the actual database. The idea behind this was to make the app
independent of the actual database since it may have been necessary to
support different databases.

That was several years ago.

The problem I found with having a data access layer was that you had to
create classes and methods that abstract the data so that it isn't
database specific. This resulted in a lot of code creating recordsets
and migrating the data from the business layer to the data access
layer. Another problem with this approach is that every time you need
to make a change to the database model you need to change the code in
the data access layer and even the business layer (in the case of
adding a new field to a table or deleting one). This becomes tedious.
Furthermore, it was not possible to have data bound controls in the
GUI. Any changes to data in a textbox or grid had to be manually
changed in code to the underlying recordset before updating the
database.

The optimal solution that I was looking for was what ADO.NET appears to
offer. By using DataSets and XML, it is possible to abstract the actual
data source. In other words, the DataSet is independent of the actual
database or data source. But I am still left with a few issues that
seem awkward to handle.

First is using a DataSet with a control so that it is data bound. If I
bind a control to a DataSet, how is it possible to have some sort of
intermediate checking when data is added, modified or deleted? Is there
some event that can be trapped prior to an update so that I can inspect
the data, possibly modify it if necessary or even cancel the update?
How does this event fit into a business logic layer? Are these events
specific to DataSets or the control? From what I've seen, they seem to
short-circuit the business layer, i.e., changes in the GUI go straight
to the DataSet. Should these data change events be considered a data
access layer? If they are considered a data access layer, I would
assume that the proper programming technique would be to contain these
notification events in a class of their own separate from the GUI or
business layer. If you've done this before, how have you implemented
this separation.

Another issue has to do with maintaining the database model. I use to
use Visio Enterprise Architect that supports database modelling but I
came to the conclusion after lengthy use that this tool is ill-suited
for doing database modelling and maintenance. It really is a band-aid.
A better tool might be Rational Rose but currently I can't afford that.
What I have noticed is that in Visual Studio .NET you can use Sever
Explorer to open a database and create a diagram, which also can be
done using SQL Server Enterprise Manager. What I like about using
Visual Studio .NET for maintaining the database is the tight
integration with the other tools used to create DataSets. But it isn't
clear to me how to synchronize changes in the database to the various
elements in my app that use the database. In other words, if I add a
field, do I need to do a refresh somewhere to cause any currently
existing DataSets to reflect these changes?

Currently I haven't seen a decent solution on how to make a DataGrid
bind to a DataSet that allows me to get notifications prior to record
updates. Most of the samples I've seen posted all use code to add new
records.

I suppose what I am looking for is a solution that is cleanly separates
the data access layer from both the GUI and business logic layer and
where maintenance can be done as easily as possible using graphical
tools like those and assistants. Can you give me a few hints on the
architecture and/or method/processes you use for implementing data
access.

Any links to articles on this subject would be welcomed.

Thanks
Johann Blake

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

http://msdn.microsoft.com/practices/

Will give you what you want.

cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"Johann Blake" <jo*********@ya hoo.com> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. .

I am looking for a good solution on how to implement data access in an
application so that there is a clean separation between the data access
layer, the business layer and the GUI layer. I am looking for a robust
solution for a major application. Almost every developer seems to come
up with a completely different solution. While many of them are not
bad, I really want a very good one.

My database is SQL Server 2000 and I am using Visual Studio .NET for
application development. In the past I use to have a "data access
layer" that I wrote in code to insulate my business logic layer from
the actual database. The idea behind this was to make the app
independent of the actual database since it may have been necessary to
support different databases.

That was several years ago.

The problem I found with having a data access layer was that you had to
create classes and methods that abstract the data so that it isn't
database specific. This resulted in a lot of code creating recordsets
and migrating the data from the business layer to the data access
layer. Another problem with this approach is that every time you need
to make a change to the database model you need to change the code in
the data access layer and even the business layer (in the case of
adding a new field to a table or deleting one). This becomes tedious.
Furthermore, it was not possible to have data bound controls in the
GUI. Any changes to data in a textbox or grid had to be manually
changed in code to the underlying recordset before updating the
database.

The optimal solution that I was looking for was what ADO.NET appears to
offer. By using DataSets and XML, it is possible to abstract the actual
data source. In other words, the DataSet is independent of the actual
database or data source. But I am still left with a few issues that
seem awkward to handle.

First is using a DataSet with a control so that it is data bound. If I
bind a control to a DataSet, how is it possible to have some sort of
intermediate checking when data is added, modified or deleted? Is there
some event that can be trapped prior to an update so that I can inspect
the data, possibly modify it if necessary or even cancel the update?
How does this event fit into a business logic layer? Are these events
specific to DataSets or the control? From what I've seen, they seem to
short-circuit the business layer, i.e., changes in the GUI go straight
to the DataSet. Should these data change events be considered a data
access layer? If they are considered a data access layer, I would
assume that the proper programming technique would be to contain these
notification events in a class of their own separate from the GUI or
business layer. If you've done this before, how have you implemented
this separation.

Another issue has to do with maintaining the database model. I use to
use Visio Enterprise Architect that supports database modelling but I
came to the conclusion after lengthy use that this tool is ill-suited
for doing database modelling and maintenance. It really is a band-aid.
A better tool might be Rational Rose but currently I can't afford that.
What I have noticed is that in Visual Studio .NET you can use Sever
Explorer to open a database and create a diagram, which also can be
done using SQL Server Enterprise Manager. What I like about using
Visual Studio .NET for maintaining the database is the tight
integration with the other tools used to create DataSets. But it isn't
clear to me how to synchronize changes in the database to the various
elements in my app that use the database. In other words, if I add a
field, do I need to do a refresh somewhere to cause any currently
existing DataSets to reflect these changes?

Currently I haven't seen a decent solution on how to make a DataGrid
bind to a DataSet that allows me to get notifications prior to record
updates. Most of the samples I've seen posted all use code to add new
records.

I suppose what I am looking for is a solution that is cleanly separates
the data access layer from both the GUI and business logic layer and
where maintenance can be done as easily as possible using graphical
tools like those and assistants. Can you give me a few hints on the
architecture and/or method/processes you use for implementing data
access.

Any links to articles on this subject would be welcomed.

Thanks
Johann Blake