Updating db from relational view - laborious or what?

Discussion in 'ASP .Net' started by Doug, Oct 31, 2003.

  1. Doug

    Doug Guest

    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?
    Doug, Oct 31, 2003
    #1
    1. Advertising

  2. 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.
    William Armstrong, Oct 31, 2003
    #2
    1. Advertising

  3. Doug

    Doug Guest

    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/books/sampchap/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, Nov 2, 2003
    #3
  4. Doug

    Doug Guest

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

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/commandbuilder.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/books/sampchap/5354.asp

    Hope this helps other poor sods trying to come to terms with this
    mangled mess.
    Doug, Nov 2, 2003
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. rmorvay
    Replies:
    1
    Views:
    418
    rmorvay
    Jun 4, 2004
  2. sam
    Replies:
    12
    Views:
    649
    Steve Holden
    Oct 8, 2006
  3. news.rcn.com
    Replies:
    2
    Views:
    1,113
    Roedy Green
    Dec 10, 2007
  4. Parthiv Joshi
    Replies:
    1
    Views:
    655
    Samuel L Matzen
    Jul 6, 2004
  5. Evan M.

    View DataSet Relational Data

    Evan M., Jun 21, 2005, in forum: ASP .Net Web Controls
    Replies:
    1
    Views:
    138
    Harolds
    Jun 22, 2005
Loading...

Share This Page