Updating db from relational view - laborious or what?

[Doug]

Scenario:
I select data from a SQL Server View which links 3 tables, into a
single dataset table. I update some of those fields on a web form.
When I want to update the db, clearly I can't update via a View -
makes perfect sense.

But because I used a View, I can't use the CommandBuilder. I have to
specify my own Commands which will in some cases be huge and complex.
This seems crazy - what if I change the db structure? And because I
used a view, I've got no Primary Keys on my tables so I can't do
things like: DataRow myDataRow1 =
myDataSet.Tables["Customers"].Rows.Find("ALFKI");

It would clearly be simpler if I selected single tables using a Stored
Proc or a Select statement and then built the relationships manually,
but again, that sucks - what if I make changes to the db structure?
The same goes for the DataRow example above: I can specify on the
DataAdapter which column is the Primary Key but I don't want to be
coding db table metadata into my app.

Clearly, selecting and updating relational data is obviously a common
requirement, but ADO.NET makes the whole process incredibly laborious
and painful. And it ties the code directly to the structure of the db
which just has to be wrong.

Or I'm missing something. How are you guys handling the problem?

[William Armstrong]

Without knowing more about your database design, and the nature of the view,
it's hard to say much. Some views can be updatable.

When you say "I've got no Primary Keys on my tables", that's a little
worrying.

I tend to have a DataAccess class which works with SQL stored procedures,
and returns DataSets to it's callers. In some cases I'll have a class which
takes that DataSet, and exposes it in a more UI-friendly way by defining
properties to provide access to the records and fields of the DataSet.

At some level, your app will need to have some awareness of the database
structure. If only to access the data values by their column names in the
DataSet. If you were to change your underlying data structure, you could
still return a DataSet with the previous naming scheme. One way to make
this more explicit, is to avoid "select * ..." style queries. Always define
the fields that you want to be returned explicitly. That way, if you add
some columns to a table, any existing queries will be unaffected.

If you expect to change your database structure so drastically that the
relationships between your tables will be different, you ought to expect to
have to modify at least one aspect of the code that uses it.

[Doug]

William, I think we mostly agree. When I say "I've got no PKs on my
tables" I mean that, although the tables do have PKs (of course), when
they're returned to a DS via a View, it seems that ADO.Net can't
figure out which are the PK columns. So I have to define them myself
in the DS. Which is ridiculous.

As far as the CommandBuilder commands to update the DB are concerned,
as far as I understand it, I need to define the name, type, size etc
of every column I want to update. So it's not a question of only major
updates to the DB breaking the app, any minor - and let's face it,
pretty common - updates such as changing the length of the field will
do it as well.

Actually, I think I'm struggling toward understanding something a lot
more fundamental - it may be easier, from the point of view of
relationally updating a DB, to select individual tables and then
rebuild the relationships using a DataRelation (see
http://www.microsoft.com/mspress/boo...chap/5354i.asp - final para
in answer to the 2nd question).

My point in all of this is that the DAL is going to be tightly bound
to the DB. Even small changes will break the app. What happened to
abstraction and encapsulation? And what happened to RAD? This is all
SO laborious!

[Doug]

I've since found this article, which confirms my suspicions -
CommandBuilder sucks, Official.

http://msdn.microsoft.com/library/de...andbuilder.asp

I've never seen this sort of info anywhere else. Why on earth don't MS
just tell us that it can't handle Relational Views, Joins etc. Ok,
stupid question ...

I also found this, which was very helpful in understanding the
concepts. And I've ordered the book!

http://www.microsoft.com/mspress/boo...pchap/5354.asp

Hope this helps other poor sods trying to come to terms with this
mangled mess.