why DbCommandBuilder (Oracle) produces weird WHERE-clause to UpdateCommand?
- by matti
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
