SqlCommandBuilder - only check key fields

[gerry]

is it possible to get a SqlCommandBuilder to generate insert/update/delete
commands that do not check for data conflicts ? ie. just execute the
command based on key fields with complete disregard for values in any other
fields.

also , can we get insert & update to work both do a delete insert ? ie. both
will work regardless of whether or not thr record already exists ?

gerry

 

[William Ryan eMVP]

No, and if I understand correctly, No.

CommandBuilders are 'ok' for quick and dirty stuff, but they leave a lot to
be desired. Bill Vaughn has a great article on his site www.betav.com
Articles ->MSDN - Weaning Developers from the CommandBuilder that you'll
invariably find helpful.

HTH,

Bill

 

[gerry]

thanks for the quick response on this one.

i know how to set up the commands manually , but this is for something that
I need to do 'on the fly' with little to no fore-knowledge regarding the
table formats - I just thought that this was such a basic capability in
other MS database products ( vfp ) that it should have been carried over
into the newer stuff , although from past experience with ms rollouts, I
have no idea why I would expect that ;-)

 

[Marina]

The SqlCommand builder was built on the idea that you only want to update
those records that have not changed since you retrieved them. There is no
way to change this behavior that I'm aware of, in fact, here is a snippet of
MS documentation which explicitly states that if you need anything else, you
need to roll your own solution:

The logic for generating commands automatically for UPDATE and DELETE
statements is based on optimistic concurrency. That is, records are not
locked for editing and can be modified by other users or processes at any
time. Because a record could have been modified after it was returned from
the SELECT statement, but before the UPDATE or DELETE statement is issued,
the automatically generated UPDATE or DELETE statement contains a WHERE
clause such that a row is only updated if it contains all original values
and has not been deleted from the data source. This is done to avoid new
data being overwritten. In cases where an automatically generated update
attempts to update a row that has been deleted or that does not contain the
original values found in the DataSet, the command will not affect any
records and a DBConcurrencyException will be thrown.

If you want the UPDATE or DELETE to complete regardless of original values,
you will need to explicitly set the UpdateCommand for the DataAdapter and
not rely on automatic command generation.

 

[William Ryan eMVP]

ADO.NET is a different mode totally. I know this might seem like a PITA but
overall, the model is MUCH easier to work with IMHO.

 

[gerry]

lol - from my background i would muchly beg to differ with that assessment.
i'm not sure how you equate 'PITA' with 'MUCH easier'

ie. doing this is VFP took me about 10 minutes to get up and running using
the vfp CURSORADAPTER ( which was supposedly based on the ADO.NET
DATAADAPTER but then again supposedly not ) and maybe 10 lines of code !

I have the SqlDataAdapter command strings built but am having a hard time
determining what the size parameter should be for the SqlCommand object
creation , the source data is accessed via an OleDbDataAdapter and the
Columns[col].MaxLength is always -1 , am I missing another field that
contains the proper size ? is the Size parameter even required for a
SqlCommand ?

gerry

 

[William Ryan eMVP]

lol - from my background i would muchly beg to differ with that assessment.
i'm not sure how you equate 'PITA' with 'MUCH easier'

The CommandBuilder is the PITA, ADO.NET is what's much easier.

ie. doing this is VFP took me about 10 minutes to get up and running using
the vfp CURSORADAPTER ( which was supposedly based on the ADO.NET
DATAADAPTER but then again supposedly not ) and maybe 10 lines of code !

For full update functionality, you can do it in two lines excluding
declarations if you use the visual tools, and about 10 without them
depending on how you intialize things.

I have the SqlDataAdapter command strings built but am having a hard time
determining what the size parameter should be for the SqlCommand object
creation

You can leave it out and let it happen automagically although I favor
explicit declaration. Set the params the same size as the field in the DB.
, the source data is accessed via an OleDbDataAdapter and the

Columns[col].MaxLength is always -1 , am I missing another field that
contains the proper size ? is the Size parameter even required for a
SqlCommand ?

Not explicitly, this declaration
cmd.Parameters.Add("@SomeValue", 200);
gets you the same place that this does:
cmd.Parameters.Add("@SomeValue", SqlDbType.VarChar, 50).Value = 200;
but with the latter, you are stating your intentions and no inference needs
done. However, I've been told by some far more well versed than me, that
the inference is done so fast that there's virtually no difference.
However, I still use explicit declarations on GP.

HTH,

Bill

 

[gerry]

hi William , thanks for sticking with me on this

The CommandBuilder is the PITA, ADO.NET is what's much easier.

actually CommandBuilder is ideal and simple - its just that it appears to be
a 1st hack with soooo much previously available functionality left out,
right in line with the .NET philosophy ;-)

For full update functionality, you can do it in two lines

can you give an example ? I am talking about moving data between 2
different datasources 'on the fly' with no knowledge of the tables or their
structure other than the name of the primary key. ie. you are given a table
name , a key field and a list of added , changed & deleted record ids from
one datasource to use to update the other. With all the dicking around
building the proper command objects 2 lines seems a little unrealistic -
but maybe i'm just doing this all wrong.

excluding
declarations if you use the visual tools, and about 10 without them
depending on how you intialize things.

I have the SqlDataAdapter command strings built but am having a hard time
determining what the size parameter should be for the SqlCommand object
creation

You can leave it out and let it happen automagically although I favor
explicit declaration. Set the params the same size as the field in the DB.
, the source data is accessed via an OleDbDataAdapter and the

Columns[col].MaxLength is always -1 , am I missing another field that
contains the proper size ? is the Size parameter even required for a
SqlCommand ?

Not explicitly, this declaration
cmd.Parameters.Add("@SomeValue", 200);
gets you the same place that this does:
cmd.Parameters.Add("@SomeValue", SqlDbType.VarChar, 50).Value = 200;
but with the latter, you are stating your intentions and no inference needs
done. However, I've been told by some far more well versed than me, that
the inference is done so fast that there's virtually no difference.
However, I still use explicit declarations on GP.

HTH,

Bill

gerry





PITA
but

rollouts,

I a
lot execute
the in
any

insert

?

exists

?