why DbCommandBuilder (Oracle) produces weird WHERE-clause to UpdateCommand?

Question

I have a table HolidayHome in oracle db which has unique db index on Id (I haven't specified this in the code in any way for adapter/table/dataset, don't know if i should/can).

DbDataAdapter.SelectCommand is like this:

SELECT Id, ExtId, Label, Location1, Location2, Location3, Location4, 
ClassId, X, Y, UseType 
FROM HolidayHome

but UpdateCommand generated by DbCommandBuilder has very weird where clause:

UPDATE HOLIDAYHOME SET ID = :p1, EXTID = :p2, LABEL = :p3, LOCATION1 = :p4, 
LOCATION2 = :p5, LOCATION3 = :p6, LOCATION4 = :p7, CLASSID = :p8, X = :p9, 
Y = :p10, USETYPE = :p11 
WHERE ((ID = :p12) AND ((:p13 = 1 AND EXTID IS NULL) OR (EXTID = :p14)) AND 
((:p15 = 1 AND LABEL IS NULL) OR (LABEL = :p16)) AND 
((:p17 = 1 AND LOCATION1 IS NULL) OR (LOCATION1 = :p18)) AND 
((:p19 = 1 AND LOCATION2 IS NULL) OR (LOCATION2 = :p20)) AND 
((:p21 = 1 AND LOCATION3 IS NULL) OR (LOCATION3 = :p22)) AND 
((:p23 = 1 AND LOCATION4 IS NULL) OR (LOCATION4 = :p24)) AND 
(CLASSID = :p25) AND (X = :p26) AND (Y = :p27) AND (USETYPE = :p28))

all these fields that have like:

((:p17 = 1 AND LOCATION1 IS NULL) OR (LOCATION1 = :p18))

are defined in oracle db like this:

LOCATION1 VARCHAR2(30)

so they allow null values.

the code looks like this:

        static bool CreateInsertUpdateDeleteCmds(DbDataAdapter dataAdapter)
        {
            DbCommandBuilder builder = _trgtProvFactory.CreateCommandBuilder();
            builder.DataAdapter = dataAdapter;

            // Get the insert, update and delete commands.
            dataAdapter.InsertCommand = builder.GetInsertCommand();
            dataAdapter.UpdateCommand = builder.GetUpdateCommand();
            dataAdapter.DeleteCommand = builder.GetDeleteCommand();
        }

what to do? The UpdateCommand is utter madness.

Thanks & Best Regards: Matti

Answer 1

I really don´t know the purpose of those ((:px = 1 AND XXX IS NULL) OR (XXX = :py)), but the CommandBuilder does generate a where clause to check if the row being updated has been changed since you loaded it. For example if you load a row R1 with values (c1, c2, c3,...,cn) and you change the value of c3 with c3' then the update command text has a where clause that checks for all the original values of the row (e.g. where C1 = c1 and C2 = c2 and ...). If the update command affects 0 rows it means that someone else has updated that row in the time between you loaded it and the time you updated it, and it throws a DbConcurrencyException. I know that you can change that behavior (don´t remember exactly how).

So that is the main reason of the Where clause in the update command.

Answer 2

The problem is related to how database NULLs are represented in your DataTable and how to test if the value of column in the database is still NULL

In SQL, NULL is not a value, it is a state.. so you can't test a sql column for NULL like this: WHERE MyColumn = NULL, this test will always return false

so you need two different test in your WHERE to check if actual column state is still the same.

The DataRows keeps both old and new values so the update command should be:

UPDATE MyTable 
SET KeyColumn = KeyDatacolumn.NewValue, OtherColumn = OtherDataColumn.NewValue
WHERE KeyColumn = KeyDatacolumn.OldValue AND OtherColumn = OtherDataColumn.OldValue

Note that the WHERE condition on OtherColumn is needed only to avoid to overwrite someone else's updates to same record

But, as said before, if OtherColumn is nullable, we can't simply test WHERE OtherColumn = OtherDataColumn.OldValue

so the update command will be:

UPDATE MyTable 
SET KeyColumn = KeyDatacolumn.Value, OtherColumn = OtherDataColumn.NewValue
WHERE
    (KeyColumn = KeyDatacolumn.OldValue) AND  
    (
        (OtherColumn = OtherDataColumn.OldValue)
        OR
        (
            (OtherDataColumn.OldValue.Equals(DBNull))
            AND
            (OtherColumn IS NULL)
        )
    )

You can read the condition on OtherColumn like "where OtherColumn has the same value it had before OR (it was NULL AND it is still NULL )"

So, for nullable columns will be used 2 different parameters for each column, the 1st one will be passed as DataColumn.OldValue.Equals(DBNull) and the 2nd one will be passed as IIF(DataColumn.OldValue.Equals(DBNull), "NULL", DataColumn.OldValue)

I hope to have been of any help
regards