When comes time to save to multiple table handling many relations...

[marty]

Hi,

We are working at building a web application using a SQL server
database. This DB already contains many tables and many relations (
"one to many" and "many to many").

How should I develop the "write to database" layer in order to make it
handle many tables with many relations from the .NET code ? And if
possible, in an abstract way.

I'm sure that many already did the same, I just don't want to rebuild
the wheel.

Do you have any idea or link to look at? Thank you!

Regards,
Marty

 

[Robbe Morris [C# MVP]]

Ultimately, each table is its own object. At some point,
you'll need to do inserts, updates, and deletes at
that individual object level.

I would strongly caution you not to try to get
too fancy with integrating multi-table saves in
the same methods or attempting some convoluted
multi-table DataSet saving mechanism.

Your app today is version 1.0. You can't
begin to predict core additions and modifications
that will be requested for version 1.1. Wrap
your database layer up to require to tight
a relationship via your code (not necessarily
talking about foreign key constraints and the like)
and you are asking for trouble. Relationships
you believe are permanent (like marriage for instance)
often are changed.

There is nothing wrong with identifying a "small"
number of places where performance is absolutely
crucial and combining a few items. However, the
number of instances in a well defined system that
require this is very, very small.

Remember, your code appearing taking 100 milliseconds
longer and being easy to maintain is often preferrable
to a mess that runs in 20 milliseconds but all hell breaks
loose when you need to modify it.

 

[marty]

Hi Robbe,

Thank you for your answer.

I just want to detail more to you to know if what I had in mind would
make sense.

Using the MVC design pattern. Here is a case and after, my yesterday's
strategy.

The aspx page is the View, the code behing is the Contol and the logic,
encapsulated in a dll is the Model. For a specific View, the
corresponding Control build a sql query using many inner join. The
Control send the query to the Model. The Model process the query and
return to the Control. The Control can populate the form.

The user fill the form and push the Save button. The Control retrieve
the validated form content and send it to the Model. [According to
your reply, I think I should do that:] The Model then should create all
the insert, update delete query to accomplish the save operation. This
Model fit only for the calling Control. Each Model is specific to its
Control.

This is what I had in mind yesterday:
1)Control layer will build a dataset that contain dataTables and their
dataRelations.
2)This dataset is sent to a generic Model class.
3)Model class will identify parent class and secondly do insert, update
delete from the parent to the children. To do this, the model class
must implement different case of relations such as:
a)one to one from parent to child.
b)one to many form parent to child.
c)many to many from parent to child.
When a programmer would make an application with this Model he should
first know very well his databse. Then he would build the Control
layer according to the database to populate his forms. Keeping the
Model generic and free of Control's specific stuff. The Model would
also manage the open/close connection to the SQLServer layer.

Does that make sense?

Thanks
Marty

 

[jmbledsoe]

If you're using a DataSet in your application, then take a look at the
DataSet Toolkit from Hydrus Software. It uses the schema and
relationships of your DataSet to infer how to fill data and push
updates back to the database in an intelligent manner.

http://www.hydrussoftware.com

Hope that helps, and use the forums if you have any questions about
using it.

John B.
http://johnsbraindump.blogspot.com