Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Updating db from relational view - laborious or what?

Reply
Thread Tools

Updating db from relational view - laborious or what?

 
 
Doug
Guest
Posts: n/a
 
      10-31-2003
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?
 
Reply With Quote
 
 
 
 
William Armstrong
Guest
Posts: n/a
 
      10-31-2003
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.


 
Reply With Quote
 
 
 
 
Doug
Guest
Posts: n/a
 
      11-02-2003
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!
 
Reply With Quote
 
Doug
Guest
Posts: n/a
 
      11-02-2003
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.
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Updating JTable Atomically - block data changes while updating. news.rcn.com Java 2 12-10-2007 11:49 AM
error handling in user input: is this natural or just laborious sam Python 12 10-08-2006 10:38 AM
View DataSet Relational Data Evan M. ASP .Net Web Controls 1 06-22-2005 03:45 PM
Joining one to many relational table =?Utf-8?B?c3FsbGVhcm5lcg==?= MCSD 2 06-04-2005 10:21 PM
How to make a week view and day view calendar just like month view calendar in .NET ? Parthiv Joshi ASP .Net Web Controls 1 07-06-2004 03:15 PM



Advertisments