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

dotnet framework


  • 1. retrieve table names
    I'm writing a CommandBuilder. Is it possible to retrieve from schema data the original names of the tables involved in a select clause? I noticed that a dataSet schema reports a generic "table" on the property "TableName" and it does not report the name of the table that fills the dataset. TableMappings is not a solution for me because the query is my only input. There is a way, by the methods of a dataAdapter o releated objects, to retrive that information ? I don't like to parse the query to get the table name. It could be an unsafe workoround.
  • 2. List od SQL Servers, Database and Tables
    In .NET (pref. c#) how does one get a list of all available SQL Servers, databases and tables. I need to allow the connection to a database, etc, on the fly. kind of like what happens in SQL Query Analyzer thanks m
  • 3. How can I know all available dabases in SQLServer?
    I assume you still have to use DMO to get an enumeration of all databases. That requires interop. If I am incorrect, someone please post, as I would love a full .NET way. The DMO samples can be installed from a SQL Server CD. They are quite easy to alter for .NET. -- Gregory A. Beamer MVP; MCP: +I, SE, SD, DBA Author: ADO.NET and XML: ASP.NET on the Edge **************************************************************************** **** Think Outside the Box! **************************************************************************** **** "Sergio Dinis" < XXXX@XXXXX.COM > wrote in message news: XXXX@XXXXX.COM ... > Hi all. > I want to know how can I get all databases available in my SQL Server using > ADO.NET or other library for VS.NET. > > Thanks > >
  • 4. Filtering Values In A DataColumn
    Hi, I have a table which contains a column called iVolume, which contains positive and negative integers. I want to get hold of the column iVolume and pass it to a method, however I only want the positive values in my DataColumn. I thought I would be able to accomplish this with the Expression property but I get the following error: "Cannot set Expression property due to circular reference in the expression".... I'm not sure what is going on but my code looks like below, does anyone have any idea what I'm doing wrong? DataColumn datacoumnValues = refDataset.Tables [TableName].Columns["iVolume"]; datacoumnValues.Expression = "iVolume > 0";
  • 5. Infinite Memory
    Hello, and thank you for your time. I have recently developed a program that may be of intrest to you. I have been successful in creating a new way to store and infinite amount of data in a finite amount of memory. If you think you might be intrested please contact me. Thanks again for your time. CA

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

Postby marty » Sat, 27 May 2006 03:36:19 GMT


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!


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

Postby Robbe Morris [C# MVP] » Sat, 27 May 2006 10:34:45 GMT

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.

Robbe Morris - 2004-2006 Microsoft MVP C#
Earn money answering .NET questions

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

Postby marty » Sun, 28 May 2006 00:26:14 GMT

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

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

This is what I had in mind yesterday:
1)Control layer will build a dataset that contain dataTables and their
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?


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

Postby jmbledsoe » Tue, 06 Jun 2006 23:40:44 GMT

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.


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

John B.

Similar Threads:

1.maintaining relation ships of tables at run time

Hello Sir,

I have created database(ms access) and tables at runtime how to maintain
relation ships between the tables. by using vb.net.

Thanking u sir.

2.Handling data update errors with multiple tables


Its easy to trap errors such as concurrency error when a single table is 
involved with code similar to this;


        Catch dbcx As Data.DBConcurrencyException

        End Try

How does one handle concurrency exception with an AdpaterManager where 
multiple tables are involved in statement like;


In particular how does one know which table or tables and row/rows are in 



3.Strongly typed datasets: Can table adapter handle multiple results


I though it would be nice to have the solution here instead of going to 
another site :-)

I have encountered an instance where I absolutely needed to fill 2 tables 
with the same SP as I needed to search Suppliers and return also linked 
SupplierContacts in a paging mode.

If you have a generic table adapter that return multiple tables, it will 
return a definition as this:

If you use the designer, and fill using your table adapter it will return 
something like this

This is not very helpful. 

Here is how you do it.

DataSet: dsSuppliers 

Under Supplier Table Adapter, I created a SP and uses the FILL
Select * from suppliers
Select * from SupplierContacts

And here is the code on my dsSupplier

1. Overloads the fill
Namespace dsSuppliersTableAdapters
    Partial Public Class SupplierTableAdapter
        Public Overloads Function Fill(ByVal ds As dsSuppliers) As Integer
            If Me.ClearBeforeFill Then
            End If

            Me.Adapter.SelectCommand = Me.CommandCollection(0) ' SP is the 
first command in the SupplierTableAdapter
            Me.Adapter.TableMappings.Add("Table1", "SupplierContacts")

            Return Me.Adapter.Fill(ds)
        End Function
    End Class
End Namespace

2. Standard use of FILL
Partial Class dsSuppliers
    Public Sub FillSuppliersAndContacts()
        Dim ta As New dsSuppliersTableAdapters.SupplierTableAdapter
    End Sub
End Class


4.how to handle sql with selecting attributes from multiple tables

a sql like: "select t1.a, t2.b from t1, t2 ...",
how does ado.net handle the query result from this kind of sql?


5.The same table cannot be the child table in two nested relations

6. how to save data into multiple table

7. ADO.NET adapter save DataSet - multiple tables changes

8. Query data from multiple tables and saving back

Return to dotnet framework


Who is online

Users browsing this forum: No registered users and 39 guest