ado.net & SqlCommandBuilder()


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 9 of 9

Thread: ado.net & SqlCommandBuilder()

  1. #1
    mark erickson Guest

    ado.net & SqlCommandBuilder()


    Hi,

    When I use the SqlCommandBuilder() to automatically fill in my DataAdapter
    commands, it will throw a fatal error if I don't include a primary key in
    the DataAdapter's SelectCommand. That's fine (sort of!), but have you noticed
    that when it DOES get a primary key, it doesn't USE the Primary Key while
    creating the UpdateCommand! For example, if my select command is "select
    * from inventory" (a table with 93 columns including a primary key column),
    the UpdateCommand that SqlCommandBuilder creates a Where clause with all
    93 columns---ignoring that it could instead use the primary key to find the
    exact row it needs to update!

    So...how is everyone else handling the creation of DataAdapter commands when
    updating the database?

    I'm thinking about writing my own SqlCommandBuilder() to create the DataAdapter
    commands. Does anyone know if any of the Data objects used for DataSet operations
    "knows" if a primary key column is contained in the DataSet? (SqlCommandBuilder
    knows--does it just requery the database schema to search for a primary key?).

    Any input is appreciated, Mark


  2. #2
    Jonathan Allen Guest

    Re: ado.net & SqlCommandBuilder()

    It may be choking on the "*". Try explicitly listing the columns.

    --
    Jonathan Allen


    "mark erickson" <m1erickson@cs.com> wrote in message
    news:3bbcbd9f$1@news.devx.com...
    >
    > Hi,
    >
    > When I use the SqlCommandBuilder() to automatically fill in my DataAdapter
    > commands, it will throw a fatal error if I don't include a primary key in
    > the DataAdapter's SelectCommand. That's fine (sort of!), but have you

    noticed
    > that when it DOES get a primary key, it doesn't USE the Primary Key while
    > creating the UpdateCommand! For example, if my select command is "select
    > * from inventory" (a table with 93 columns including a primary key

    column),
    > the UpdateCommand that SqlCommandBuilder creates a Where clause with all
    > 93 columns---ignoring that it could instead use the primary key to find

    the
    > exact row it needs to update!
    >
    > So...how is everyone else handling the creation of DataAdapter commands

    when
    > updating the database?
    >
    > I'm thinking about writing my own SqlCommandBuilder() to create the

    DataAdapter
    > commands. Does anyone know if any of the Data objects used for DataSet

    operations
    > "knows" if a primary key column is contained in the DataSet?

    (SqlCommandBuilder
    > knows--does it just requery the database schema to search for a primary

    key?).
    >
    > Any input is appreciated, Mark
    >




  3. #3
    martin rydman Guest

    Re: ado.net & SqlCommandBuilder()


    Hi!

    The idea with the update command having all the columns in the WHERE-clause
    is for it to be able to make sure that the underlying data hasn't been changed
    since the dataset was created. If you check the SQL-command (via SQL Profiler,
    for example), you'll notice that the actual values given the WHERE-clause
    are the *original* values. Thus, the dataadapter will know if the update
    failed because of the underlying data having been changed.

    At least, that's my own theory... ;-)

    /Martin

    "Jonathan Allen" <greywolf@cts.com> wrote:
    >It may be choking on the "*". Try explicitly listing the columns.
    >
    >--
    >Jonathan Allen
    >
    >
    >"mark erickson" <m1erickson@cs.com> wrote in message
    >news:3bbcbd9f$1@news.devx.com...
    >>
    >> Hi,
    >>
    >> When I use the SqlCommandBuilder() to automatically fill in my DataAdapter
    >> commands, it will throw a fatal error if I don't include a primary key

    in
    >> the DataAdapter's SelectCommand. That's fine (sort of!), but have you

    >noticed
    >> that when it DOES get a primary key, it doesn't USE the Primary Key while
    >> creating the UpdateCommand! For example, if my select command is "select
    >> * from inventory" (a table with 93 columns including a primary key

    >column),
    >> the UpdateCommand that SqlCommandBuilder creates a Where clause with all
    >> 93 columns---ignoring that it could instead use the primary key to find

    >the
    >> exact row it needs to update!
    >>
    >> So...how is everyone else handling the creation of DataAdapter commands

    >when
    >> updating the database?
    >>
    >> I'm thinking about writing my own SqlCommandBuilder() to create the

    >DataAdapter
    >> commands. Does anyone know if any of the Data objects used for DataSet

    >operations
    >> "knows" if a primary key column is contained in the DataSet?

    >(SqlCommandBuilder
    >> knows--does it just requery the database schema to search for a primary

    >key?).
    >>
    >> Any input is appreciated, Mark
    >>

    >
    >



  4. #4
    Jonathan Allen Guest

    Re: ado.net & SqlCommandBuilder()

    That makes sense to me.

    --
    Jonathan Allen


    "martin rydman" <martin@aprire.se> wrote in message
    news:3bbd5d48$1@news.devx.com...
    >
    > Hi!
    >
    > The idea with the update command having all the columns in the

    WHERE-clause
    > is for it to be able to make sure that the underlying data hasn't been

    changed
    > since the dataset was created. If you check the SQL-command (via SQL

    Profiler,
    > for example), you'll notice that the actual values given the WHERE-clause
    > are the *original* values. Thus, the dataadapter will know if the update
    > failed because of the underlying data having been changed.
    >
    > At least, that's my own theory... ;-)
    >
    > /Martin
    >
    > "Jonathan Allen" <greywolf@cts.com> wrote:
    > >It may be choking on the "*". Try explicitly listing the columns.
    > >
    > >--
    > >Jonathan Allen
    > >
    > >
    > >"mark erickson" <m1erickson@cs.com> wrote in message
    > >news:3bbcbd9f$1@news.devx.com...
    > >>
    > >> Hi,
    > >>
    > >> When I use the SqlCommandBuilder() to automatically fill in my

    DataAdapter
    > >> commands, it will throw a fatal error if I don't include a primary key

    > in
    > >> the DataAdapter's SelectCommand. That's fine (sort of!), but have you

    > >noticed
    > >> that when it DOES get a primary key, it doesn't USE the Primary Key

    while
    > >> creating the UpdateCommand! For example, if my select command is

    "select
    > >> * from inventory" (a table with 93 columns including a primary key

    > >column),
    > >> the UpdateCommand that SqlCommandBuilder creates a Where clause with

    all
    > >> 93 columns---ignoring that it could instead use the primary key to find

    > >the
    > >> exact row it needs to update!
    > >>
    > >> So...how is everyone else handling the creation of DataAdapter commands

    > >when
    > >> updating the database?
    > >>
    > >> I'm thinking about writing my own SqlCommandBuilder() to create the

    > >DataAdapter
    > >> commands. Does anyone know if any of the Data objects used for DataSet

    > >operations
    > >> "knows" if a primary key column is contained in the DataSet?

    > >(SqlCommandBuilder
    > >> knows--does it just requery the database schema to search for a primary

    > >key?).
    > >>
    > >> Any input is appreciated, Mark
    > >>

    > >
    > >

    >




  5. #5
    mark erickson Guest

    Re: ado.net & SqlCommandBuilder()


    Hi,

    Yep, makes sense to me, too.

    Still, it takes 5 seconds for the gigantic WHERE clause to update just a
    few records. So in those cases where I know the underlying data hasn't changed
    (or where it can just be overwritten), I'll use a primary key in the WHERE
    clause.

    How is everyone dealing with creating the Insert/Delete/Update commands in
    the DataAdapter during the update process? By hand? Has someone written
    an alternative to SqlCommandBuilder()?

    Best, Mark



    "Jonathan Allen" <greywolf@cts.com> wrote:
    >That makes sense to me.
    >
    >--
    >Jonathan Allen
    >
    >
    >"martin rydman" <martin@aprire.se> wrote in message
    >news:3bbd5d48$1@news.devx.com...
    >>
    >> Hi!
    >>
    >> The idea with the update command having all the columns in the

    >WHERE-clause
    >> is for it to be able to make sure that the underlying data hasn't been

    >changed
    >> since the dataset was created. If you check the SQL-command (via SQL

    >Profiler,
    >> for example), you'll notice that the actual values given the WHERE-clause
    >> are the *original* values. Thus, the dataadapter will know if the update
    >> failed because of the underlying data having been changed.
    >>
    >> At least, that's my own theory... ;-)
    >>
    >> /Martin
    >>



  6. #6
    Jonathan Allen Guest

    Re: ado.net & SqlCommandBuilder()

    > Still, it takes 5 seconds for the gigantic WHERE clause to update just a
    > few records.


    Well, do you really need all 93 columns?

    --
    Jonathan Allen


    "mark erickson" <m1erickson@cs.com> wrote in message
    news:3bbded61$1@news.devx.com...
    >
    > Hi,
    >
    > Yep, makes sense to me, too.
    >
    > Still, it takes 5 seconds for the gigantic WHERE clause to update just a
    > few records. So in those cases where I know the underlying data hasn't

    changed
    > (or where it can just be overwritten), I'll use a primary key in the WHERE
    > clause.
    >
    > How is everyone dealing with creating the Insert/Delete/Update commands in
    > the DataAdapter during the update process? By hand? Has someone written
    > an alternative to SqlCommandBuilder()?
    >
    > Best, Mark
    >
    >
    >
    > "Jonathan Allen" <greywolf@cts.com> wrote:
    > >That makes sense to me.
    > >
    > >--
    > >Jonathan Allen
    > >
    > >
    > >"martin rydman" <martin@aprire.se> wrote in message
    > >news:3bbd5d48$1@news.devx.com...
    > >>
    > >> Hi!
    > >>
    > >> The idea with the update command having all the columns in the

    > >WHERE-clause
    > >> is for it to be able to make sure that the underlying data hasn't been

    > >changed
    > >> since the dataset was created. If you check the SQL-command (via SQL

    > >Profiler,
    > >> for example), you'll notice that the actual values given the

    WHERE-clause
    > >> are the *original* values. Thus, the dataadapter will know if the

    update
    > >> failed because of the underlying data having been changed.
    > >>
    > >> At least, that's my own theory... ;-)
    > >>
    > >> /Martin
    > >>

    >




  7. #7
    mark erickson Guest

    Re: ado.net & SqlCommandBuilder()


    Hi Jonathan,

    >
    >Well, do you really need all 93 columns?
    >


    No! As you can see, I've discovered some sloppy programming practices on
    my part as I convert to .net. Ya had to bring that up, didn't ya <g>!!!

    Mark



  8. #8
    Jonathan Allen Guest

    Re: ado.net & SqlCommandBuilder()

    > Ya had to bring that up, didn't ya

    At least I didn't mention using stored procedures.

    --
    Jonathan Allen


    "mark erickson" <m1erickson@cs.com> wrote in message
    news:3bbe633a$1@news.devx.com...
    >
    > Hi Jonathan,
    >
    > >
    > >Well, do you really need all 93 columns?
    > >

    >
    > No! As you can see, I've discovered some sloppy programming practices on
    > my part as I convert to .net. Ya had to bring that up, didn't ya <g>!!!
    >
    > Mark
    >
    >




  9. #9
    Patrice Scribe Guest

    Re: ado.net & SqlCommandBuilder()

    You could use a stored procedure or you could also try to add a timestamp
    column to your SQL Server table. This column is automatically updated when a
    record is updated allowing ADO.NET to use only this column to implement
    optimistic locking. At least it was this way back in ODBC days...

    Patrice

    "mark erickson" <m1erickson@cs.com> a écrit dans le message news:
    3bbded61$1@news.devx.com...
    >
    > Hi,
    >
    > Yep, makes sense to me, too.
    >
    > Still, it takes 5 seconds for the gigantic WHERE clause to update just a
    > few records. So in those cases where I know the underlying data hasn't

    changed
    > (or where it can just be overwritten), I'll use a primary key in the WHERE
    > clause.
    >
    > How is everyone dealing with creating the Insert/Delete/Update commands in
    > the DataAdapter during the update process? By hand? Has someone written
    > an alternative to SqlCommandBuilder()?
    >
    > Best, Mark
    >





Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center