Commands and CommandBuilder

[Pashkuale]

Hi all.

I know (I hope) the process how CommandBuilder creates the action commands
derived form the select and how register itself as a listern for catching
the rowupdating event of the DataAdapter. But reading msdn docs, I also know
that if I explicitly assign a command object to the InsertCommand property
of the DataAdapter, the DataAdapter itself would have to use this command
without replace it with the autogenerated one.
If I right, why this piece of code don't run properly?

[...]
Dim daCust As New SqlDataAdapter("SELECT * FROM Customers", cn)
Dim cbCust As New SqlCommandBuilder(daCust)
Dim cmd As SqlCommand = cbCust.GetInsertCommand
cmd.CommandText &= ";SELECT @@IDENTITY AS ID"
daCust.InsertCommand = cmd
daCust.Update(dtCust)

If I run this code, the command used for inserting rows is always the
originale created by CommandBuilder and not the modified. But if I create a
new SqlCommand instead of use the one returned by CommandBuilder, this new
command is rightly used for inserting. Why?

Thanks.

 

[Guest]

How are you wiring this up? Just calling Update() on the Adapter? Not that it
really matters, but the above may clue you in on why it is not working, if
you really want to tackle that problem.

Here are my suggestions:

1. Get rid of the CommandBuilder. It was designed for tool designers and not
for Enterprise applications. While it works in many instances, it does not
take much to wire the app properly and avoid the CommandBuilder.

2. Write a stored procedure that has your command and returns SCOPE_IDENTITY
(instead of attempting @@IDENTITY. @@IDENTITY can return incorrect values on
heavily used systems. If you must, use a tool like LLBLGen to create your
sprocs and alter them to add the IDENTITY return.

3. Create stored procedures for the rest of your CRUD. Sure, it takes a
little extra time, but it adds security to you app by forcing the
parameterization of queries (harder (or impossible) to SQL inject).

4. Wire the sprocs manually. This way you can be sure of what the app is
doing (or not doing) and better control your data access. Once again, code
gens can help tremendously.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[Pashkuale]

How are you wiring this up? Just calling Update() on the Adapter? Not that
it really matters, but the above may clue you in on why it is not working,
if you really want to tackle that problem. Here are my suggestions: 1. Get
rid of the CommandBuilder. [...]
2. Write a stored procedure that has your command and returns
SCOPE_IDENTITY [...]
3. Create stored procedures for the rest of your CRUD. [...]
4. Wire the sprocs manually.

Hi Cowboy, I'm "manually" replay to you through my message becouse I cant't
find your reply in my news server.

Thanks for your tips but I know good point and lacks of the CommandBuilder
approach when updating data. My question was only for accademic purpose,
even if I consider CommandBuilder a good and fast solution for certain
scenarios like single user solutions or small competitive systems.

I'd like to know the reason of that behavior only for curiosity, let's say
for knowing what happens behind the scene.

Thanks.

 

[Mark Ashton]

The SqlCommandBuilder tracks the three commands it generates for the
insert/update/delete commands.
When you set the adapter.InsertCommand = bldr.GetInsertCommand, the builder
still sees it as its command.
You can work around this by 'adapter.InsertCommand =
bldr.GetInsertCommand().Clone()'

 

[Pashkuale]

When you set the adapter.InsertCommand = bldr.GetInsertCommand, the
builder still sees it as its command.
You can work around this by 'adapter.InsertCommand =
bldr.GetInsertCommand().Clone()'

Unfortunately, GetInsertCommand().Clone() works only with version 2.0 of the
framework. I still use the 1.1 version.
In this case, I will implement this method manually, cloning the command and
all parameters too.

Thanks for reply.

P.S. I ask myself, why check only for reference and don't check if
commandtext (and the other parameters) is the same before decide if it's the
autogenerated command?