DB2CommandBuilder.ConflictOption Property

Syntax

[Visual Basic]
Public Overrides Property ConflictOption As String
[C#]
public override string ConflictOption { get; set; }
[C++]
public:
virtual property String^ ConflictOption {
	String^ get () override;
	void set (String^ value) override;
}
[JScript]
public override function get ConflictOption () : String
public override function set ConflictOption (value : String)

Property value

Sets a value that specifies how conflicting changes to the data source gets detected and resolved. Default option is CompareAllSearchableValues

Remarks

When the ConflictOption property is set, the DB2Command objects created by DB2CommandBuilder are disposed. Any reference to the DB2Command objects in the DB2DataAdapter is reset and external references to those objects is unusable. The columns returned in the data set mainly depend on the associated DB2CommandBuilder.ConflictOption setting, underlying table, and the server version.

When an associated DB2CommandBuilder uses ConflictOption.CompareRowVersion value, the DB2DataAdapter.Fill requests the injection of optimistic locking columns into the result set. This injection, performed only for tables containing a ROW CHANGE TIMESTAMP column, adds either a RID_BIT, or RID column and a ROW CHANGE TOKEN column. The order of the added columns varies by server. IBM® iSeries Version 6 release 1 and later servers returns ROW CHANGE TOKEN, RID while Db2® Version 9.5 and later releases returns RID_BIT, ROW CHANGE TOKEN.

The table 1 summarizes the DB2DataAdapter.Fill option scenarios:

When using ConflictOption.CompareRowVersion, DB2DataAdapter.Fill can be called before DB2DataAdapter.Update is allowed, unless the update operation is composed entirely of insert commands. This helps to ensure the columns of the data set are consistent with any insert, update, and delete commands generated by the DB2CommandBuilder.

When using ConflictOption.CompareRowVersion, the DB2DataAdapter.Update does not modify the injected ROW CHANGE TOKEN column in the data set, as insert and update statements do not return data from the server. If further changes to previously inserted or updated rows are required, the data set must be refetched using DB2DataAdapter.Fill. If the data set object is reused with DB2DataAdapter.Fill, the DataSet.PrimaryKey property must be set to avoid the rows being appended instead of updated.

The table 2 summarizes the properties set by FillSchema for the injected RID_BIT/RID and ROW CHANGE TOKEN DataColumns.

The injected columns described in DB2DataAdapter.Fill (DataSet) is used by the DB2CommandBuilder class to generate the WHERE clause for the UPDATE and DELETE statements. Instead of the default behavior of comparing all values, RID_BIT or RID and ROW CHANGE TOKEN values are used to uniquely identify the row. If CompareRowVersion option is specified and the server fails to inject the optimistic locking columns into result set, the DB2CommandBuilder comes back to the CompareAllSearchableValues behavior.

Examples

CREATE TABLE TAB1(INT1 INT NOT NULL, PRIMARY KEY(INT1))
SELECT * FROM TAB1
CompareAllSearchableValues: DELETE FROM TAB1 WHERE ( (INT1 = ?) )
CompareRowVersion: Reverts back to CompareAllSearchableValues.

CREATE TABLE TAB2(INT1 INT)
SELECT * FROM TAB2
CompareAllSearchableValues: InvalidOperationException because no primary key is returned.
CompareRowVersion: Reverts back to CompareAllSearchableValues

CREATE TABLE TAB3(INT1 INT, TS1 TIMESTAMP NOT NULL GENERATED ALWAYS 
FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP)
SELECT * FROM TAB3
CompareAllSearchableValues: InvalidOperationException because no primary key is returned
CompareRowVersion: DELETE FROM TAB3 WHERE RID_BIT(TAB3)=? AND ROW CHANGE TOKEN FOR TAB3=?

DB2DataAdapter da1 = new DB2DataAdaper("select * from tab", conn);
DB2DataAdapter da2 = new DB2DataAdaper("select * from tab", conn);
DataSet ds1 = new DataSet();
da1.Fill(ds1)1;
DB2CommandBuilder bldr = new DB2CommandBuilder(da1);
bldr.ConflictOption = ConflictOption.CompareRowVersion;
da1.Update(ds1)2;
DataSet ds2 = new DataSet();
DataSet ds3 = new DataSet();
da1.Fill(ds2); 3
da1.Update(ds1)4;
da1.Fill(ds1)5;
da2.Fill(ds3);6
<modify ds1>
<modify ds2>
<modify ds3>
da1.Update(ds2); 
da1.Update(ds3)7; 

Exceptions