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.