Data Access Software Architecture?

  • Thread starter Thread starter Tim Smith
  • Start date Start date
T

Tim Smith

Hi,

Does anyone have any professional experience on how to create business
applicaitons that are heavy on database manipulation. Specifically
managing the business logic which involves pulling reference data from
many tables and updates/inserts into many activity tables.

At one point I thought it might be useful to have a class per table
which provides the basic insert/select/update/delete functionality.
public class MyTable {
private int my_table_id = 0;
private String my_description = "";
...
public insert();
...
public select(int pkey_table_id);
}
Then you could have classes that are composed of many table classes or
arrays of table classes. Those classes represent the data model
hierarchy in some fashion.

However this is perhaps not efficient for updating large numbers of
rows and the base class would need to get regenerated after any schema
changes.
--------------
Perhaps a more efficient way is to move all the direct data access to
stored procedures in the database. Throughout the class code we call
the SP, passing and returning data values (and hoping the conversion
works out ok I guess). I suppose you could have a class that groups
related tables and has its own set of stored procedures. At least you
would have compile time checking of your SQL.
--------------
I sometimes find not being able to see SQL in my code a little
frustrating when you are reading code - and god forbid if you start
sharing a Stored Procedure...

Some languages like Powerbuilder are able to retreive views or tables
joined, update the resultset and save back to the server. Are there
any options for this kind of functionality?
--------------

At the end of the day I want to work with objects, but they must
retreive and store data efficiently. I need compile time checking of
my SQL and I need to have it easier to work with a hierarchy of
related tables for updating. If I update one field only, an resulting
SQL update of one time only should be generated. I do not believe the
DataSet does the updating back if it came from multiple tables.

tia

Tim
 
There are a number of ways of handling it, and it really depends on your
needs. I normally go with the class per table methodology. In fact, I've
created a library based around it called DotNetDALLib. It's up on
sourceforge at:

http://dotnetdallib.sourceforge.net

As you mentioned, this isn't necessarily the fastest method. Particularly
for doing large insert operations. For everything else, though, the library
works pretty well.

BTW, if you happen to use it, download the source code, and get the files
that have changed since the last release from CVS, since there are some
major bug fixes and functionality upgrades since the last release. I plan on
doing a new release this week.

You mentioned that the objects need to be recreated if the schema changes.
This is true, but if you use CodeSmith
(http://www.ericjsmith.net/codesmith), DotNetDALLib has a template that will
generate your class from the DB schema automatically.

DotNetDALLib is definitely not the best solution for every situation, but I
find it works well for most situations that I run into. You can supplement
it with custom code as needed. It's biggest strength is its ease of use.
With a single line of code, I can populate a collection with objects from
the database, or with another line, I can save the entire collection. Using
CodeSmith, I don't have to create any of the classes myself. I generate all
the data objects and using attributes and reflection, DotNetDALLib
understands how to deal with the objects.

But like I said, it largely depends on what you need. I always evaluate my
needs before I decide whether or not to use DotNetDALLib on a project. If
speed is of the utmost concern, then custom code is usually better.

Pete
 
Just an additional note on this. I did a release of version 2.1 this morning
which has the latest code fixes, so you don't need to manually patch it
anymore.

Pete
 
Back
Top