ado.net & SqlCommandBuilder()

[mark erickson]

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

[Jonathan Allen]

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
>

[martin rydman]

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
>>
>
>

[Jonathan Allen]

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
> >>
> >
> >
>

[mark erickson]

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
>>

[Jonathan Allen]

> 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
> >>
>

[mark erickson]

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

[Jonathan Allen]

> 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
>
>

[Patrice Scribe]

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
>