Multiple Tables as SqlDataSource

[Joe Kovac]

Hi!

The informations of our customers are saved in two tables:

Customer: ID, CustomerNumber, ... + Person_ID
Person: ID, Name, ...

I want to have an editable GridView displaying all the data and being
able to add, edit and delete customers.

The select is very easy:
SELECT CustomerNumber, Name FROM Customer, Person
WHERE Customer.Person_ID = Person.ID

But what am I to do with the UDATE, DELETE and INSERT?

Is the SQLDataSource the right option?

Creating custom BusinessObjects and custom BusinessObject adapters is
very much work and I found no good sample for such a case, where
everything is supported, including sorting, etc.

Thanks for your help

Joe

 

[Cowboy \(Gregory A. Beamer\)]

When you use the automated goo, you end up pulling from a table (or view)
and .NET attempts to create the INSERT, UPDATE and DELTE for you. This works
extremely well for a single table and many views, but it is not a good
solution if you have complex relationships represented as a denormalized
view of many tables.

In this case, you can still use the different ADO.NET instruments, but you
have to take control of the queries. One of the easiest ways to do this is
to create your own stored procedures for each of the CRUD steps (Create
(INSERT), Read (SELECT), Update and Delete). ADO.NET gives you the ability
to attach sprocs to the data transport objects. I am not sure this is 100%,
but in the cases where it may not be, there is a way to attach an object
that does allow stored procedures.

Can you use SQL commands without encapsulating them in stored procedures?
Sure. But, if you are taking the time to write hand coded SQL, why not take
the step of abstracting your database schema from your code through stored
procedures?

 

[Joe Kovac]

When you use the automated goo, you end up pulling from a table (or
view) and .NET attempts to create the INSERT, UPDATE and DELTE for you.
This works extremely well for a single table and many views, but it is
not a good solution if you have complex relationships represented as a
denormalized view of many tables.

In this case, you can still use the different ADO.NET instruments, but
you have to take control of the queries. One of the easiest ways to do
this is to create your own stored procedures for each of the CRUD steps
(Create (INSERT), Read (SELECT), Update and Delete). ADO.NET gives you
the ability to attach sprocs to the data transport objects. I am not
sure this is 100%, but in the cases where it may not be, there is a way
to attach an object that does allow stored procedures.

Can you use SQL commands without encapsulating them in stored
procedures? Sure. But, if you are taking the time to write hand coded
SQL, why not take the step of abstracting your database schema from your
code through stored procedures?

Thanks for your comments. I agree with you. Stored Procedures seem to be
the thing to use.
But maybe there is some BusinessObject-Framework that fits as well as
the SqlDataObject? Does anyone make use of such a thing?