LoadDataRow(object[], LoadOption) and ConstraintException

See The LoadDataRow and Delete Methods of DataTable for intention.

• Introduction
• Set up a DataTable with 2 rows
• Test: No primary key applied
• Test: Primary key applied, no AcceptChanges before or after LoadDataRow
• Test: No primary key, AcceptChanges before LoadDataRow
• Test: Primary key applied, AcceptChanges before LoadDataRow
• Test: No primary key, LoadDataRow in conjunction with Begin/EndLoadData
• Test: Primary key applied, LoadDataRow in conjunction with Begin/EndLoadData
• Test: Primary key applied, LoadDataRow in conjunction with Begin/EndLoadData, ApplyChanges before BeginLoadData

---

Introduction

In this test, I will use LoadDataRow to perform content update on a DataTable with or without primary key constraint. LoadOption is passed in a parameter with this call LoadDataRow(object[], LoadOption). For result using LoadDataRow(object[], bool), please see this.

When the LoadDataRow method is call, the following statement is used:

   employee.LoadDataRow(
      new object[] { "B223456789", "Denise", "Miller", 13.00 },
      LoadOption.OverwriteChanges);

When primary key is applied, the following statement is used and it will be executed before the LoadDataRow method:

   employee.PrimaryKey = new DataColumn[] {eid}; // eid is the EmpId column  

Before presenting the test results, we will first set up a DataTable with 2 rows without primary key constraint. Our DataTable will have the following columns:

ColumnName	EmpId	FirstName	LastName	Salary	LastName and FirstName **
DataType	string	string	string	decimal	string
MaxLength	10	35	-1	-1	70
Unique	true	false	false	false	false
AllowDBNull	false	false	false	true	true

** The column of LastName and FirstName is defined as an expression column containing the values from the column of LastName and the column of FirstName.

Every test is based on this table with the initial sample data. The 2nd row is the target to update by using the LoadDataRow method. A grid shown in each test is represented the final result of the DataTable even though the update performed by the LoadDataRow method may fail. Execution messages about the RowState of the 2nd row and its versions are shown afterwards. For clarity, the initial state of the 2nd row is also attached to each test for reference.

The rest of the page is generated by the application developed in C# under Visual 2008 Profession SP + .NET 3.5 SP1 on Windows XP Professional SP3.

---

Set up a DataTable with 2 rows
- EmpId is the unique key (eid.Unique = true).
- Primary key is not set up initially.

• Initial Table:

EmpId	FirstName	LastName	Salary	LastName and FirstName
A123456789	Bettye	Williams	11.00	Williams, Bettye
B223456789	Rex	Bloomer	12.00	Bloomer, Rex



• Status duing execution:
Initial state: [B223456789, Rex] RowState: Added
Version: Original doesn't not exist.
Version: Current Value: Rex
Version: Proposed doesn't not exist.
Version: Default Value: Rex

---

No primary key applied
Expect errors [System.Data.UniqueConstraint.CheckConstraint]!
The last row remains unchanged.

• Resulted DataTable:

EmpId	FirstName	LastName	Salary	LastName and FirstName
A123456789	Bettye	Williams	11.00	Williams, Bettye
B223456789	Rex	Bloomer	12.00	Bloomer, Rex



• Status duing execution:
Initial state: [B223456789, Rex] RowState: Added
Version: Original doesn't not exist.
Version: Current Value: Rex
Version: Proposed doesn't not exist.
Version: Default Value: Rex

Executing LoadDataRow(...)


Column 'EmpId' is constrained to be unique.  Value 'B223456789' is already present.
System.Data.ConstraintException
   at System.Data.UniqueConstraint.CheckConstraint(DataRow row, DataRowAction action)
   at System.Data.DataTable.RaiseRowChanging(DataRowChangeEventArgs args, DataRow eRow, DataRowAction eAction, Boolean fireEvent)
   at System.Data.DataTable.SetNewRecordWorker(DataRow row, Int32 proposedRecord, DataRowAction action, Boolean isInMerge, Int32 position, Boolean fireEvent, Exception& deferredException)
   at System.Data.DataTable.InsertRow(DataRow row, Int32 proposedID, Int32 pos, Boolean fireEvent)
   at System.Data.DataTable.LoadRow(Object[] values, LoadOption loadOption, Index searchIndex)
   at System.Data.DataTable.LoadDataRow(Object[] values, LoadOption loadOption)
   ...


After error: [B223456789, Rex] RowState: Added
Version: Original doesn't not exist.
Version: Current Value: Rex
Version: Proposed doesn't not exist.
Version: Default Value: Rex

---

Primary key applied, no AcceptChanges before or after LoadDataRow
No error! The last row is updated.

• Resulted DataTable:

EmpId	FirstName	LastName	Salary	LastName and FirstName
A123456789	Bettye	Williams	11.00	Williams, Bettye
B223456789	Denise	Miller	13.00	Miller, Denise



• Status duing execution:
Initial state: [B223456789, Rex] RowState: Added
Version: Original doesn't not exist.
Version: Current Value: Rex
Version: Proposed doesn't not exist.
Version: Default Value: Rex

Executing LoadDataRow(...)
After LoadDataRow: [B223456789, Denise] RowState: Unchanged
Version: Original Value: Denise
Version: Current Value: Denise
Version: Proposed doesn't not exist.
Version: Default Value: Denise

---

No primary key, AcceptChanges before LoadDataRow
Expect errors [System.Data.UniqueConstraint.CheckConstraint]!
The last row remains unchanged.

• Resulted DataTable:

EmpId	FirstName	LastName	Salary	LastName and FirstName
A123456789	Bettye	Williams	11.00	Williams, Bettye
B223456789	Rex	Bloomer	12.00	Bloomer, Rex



• Status duing execution:
Initial state: [B223456789, Rex] RowState: Added
Version: Original doesn't not exist.
Version: Current Value: Rex
Version: Proposed doesn't not exist.
Version: Default Value: Rex

Apply AcceptChanges(): [B223456789, Rex] RowState: Unchanged
Version: Original Value: Rex
Version: Current Value: Rex
Version: Proposed doesn't not exist.
Version: Default Value: Rex

Executing LoadDataRow(...)


Column 'EmpId' is constrained to be unique.  Value 'B223456789' is already present.
System.Data.ConstraintException
   at System.Data.UniqueConstraint.CheckConstraint(DataRow row, DataRowAction action)
   at System.Data.DataTable.RaiseRowChanging(DataRowChangeEventArgs args, DataRow eRow, DataRowAction eAction, Boolean fireEvent)
   at System.Data.DataTable.SetNewRecordWorker(DataRow row, Int32 proposedRecord, DataRowAction action, Boolean isInMerge, Int32 position, Boolean fireEvent, Exception& deferredException)
   at System.Data.DataTable.InsertRow(DataRow row, Int32 proposedID, Int32 pos, Boolean fireEvent)
   at System.Data.DataTable.LoadRow(Object[] values, LoadOption loadOption, Index searchIndex)
   at System.Data.DataTable.LoadDataRow(Object[] values, LoadOption loadOption)
   ...


After error: [B223456789, Rex] RowState: Unchanged
Version: Original Value: Rex
Version: Current Value: Rex
Version: Proposed doesn't not exist.
Version: Default Value: Rex

---

Primary key applied, AcceptChanges before LoadDataRow
No error! The last row is updated.

• Resulted DataTable:

EmpId	FirstName	LastName	Salary	LastName and FirstName
A123456789	Bettye	Williams	11.00	Williams, Bettye
B223456789	Denise	Miller	13.00	Miller, Denise



• Status duing execution:
Initial state: [B223456789, Rex] RowState: Added
Version: Original doesn't not exist.
Version: Current Value: Rex
Version: Proposed doesn't not exist.
Version: Default Value: Rex

Apply AcceptChanges(): [B223456789, Rex] RowState: Unchanged
Version: Original Value: Rex
Version: Current Value: Rex
Version: Proposed doesn't not exist.
Version: Default Value: Rex

Executing LoadDataRow(...)
After LoadDataRow: [B223456789, Denise] RowState: Unchanged
Version: Original Value: Denise
Version: Current Value: Denise
Version: Proposed doesn't not exist.
Version: Default Value: Denise

---

No primary key, LoadDataRow in conjunction with Begin/EndLoadData
Expect errors [System.Data.DataTable.EnableConstraints()]!
Duplicate rows are found.

• Resulted DataTable:

EmpId	FirstName	LastName	Salary	LastName and FirstName
A123456789	Bettye	Williams	11.00	Williams, Bettye
B223456789	Rex	Bloomer	12.00	Bloomer, Rex
B223456789	Denise	Miller	13.00	Miller, Denise



• Status duing execution:
Initial state: [B223456789, Rex] RowState: Added
Version: Original doesn't not exist.
Version: Current Value: Rex
Version: Proposed doesn't not exist.
Version: Default Value: Rex



BeginLoadData(): turn off notification/constraints/indexing.

Executing LoadDataRow(...)
After LoadDataRow: [B223456789, Denise] RowState: Unchanged
Version: Original Value: Denise
Version: Current Value: Denise
Version: Proposed doesn't not exist.
Version: Default Value: Denise



EndLoadData(): turn on validation, constraints, etc.

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
System.Data.ConstraintException
   at System.Data.DataTable.EnableConstraints()
   at System.Data.DataTable.set_EnforceConstraints(Boolean value)
   at System.Data.DataTable.EndLoadData()
   ...


After error: [B223456789, Rex] RowState: Added
Version: Original doesn't not exist.
Version: Current Value: Rex
Version: Proposed doesn't not exist.
Version: Default Value: Rex

---

Primary key applied, LoadDataRow in conjunction with Begin/EndLoadData
No error! The last row is updated.

• Resulted DataTable:

EmpId	FirstName	LastName	Salary	LastName and FirstName
A123456789	Bettye	Williams	11.00	Williams, Bettye
B223456789	Denise	Miller	13.00	Miller, Denise



• Status duing execution:
Initial state: [B223456789, Rex] RowState: Added
Version: Original doesn't not exist.
Version: Current Value: Rex
Version: Proposed doesn't not exist.
Version: Default Value: Rex



BeginLoadData(): turn off notification/constraints/indexing.

Executing LoadDataRow(...)
After LoadDataRow: [B223456789, Denise] RowState: Unchanged
Version: Original Value: Denise
Version: Current Value: Denise
Version: Proposed doesn't not exist.
Version: Default Value: Denise



EndLoadData(): turn on validation, constraints, etc.

[B223456789, Denise] RowState: Unchanged
Version: Original Value: Denise
Version: Current Value: Denise
Version: Proposed doesn't not exist.
Version: Default Value: Denise

---

Primary key applied, LoadDataRow in conjunction with Begin/EndLoadData, ApplyChanges before BeginLoadData
No error! The last row is updated.

• Resulted DataTable:

EmpId	FirstName	LastName	Salary	LastName and FirstName
A123456789	Bettye	Williams	11.00	Williams, Bettye
B223456789	Denise	Miller	13.00	Miller, Denise



• Status duing execution:
Initial state: [B223456789, Rex] RowState: Added
Version: Original doesn't not exist.
Version: Current Value: Rex
Version: Proposed doesn't not exist.
Version: Default Value: Rex

Apply AcceptChanges(): [B223456789, Rex] RowState: Unchanged
Version: Original Value: Rex
Version: Current Value: Rex
Version: Proposed doesn't not exist.
Version: Default Value: Rex



BeginLoadData(): turn off notification/constraints/indexing.

Executing LoadDataRow(...)
After LoadDataRow: [B223456789, Denise] RowState: Unchanged
Version: Original Value: Denise
Version: Current Value: Denise
Version: Proposed doesn't not exist.
Version: Default Value: Denise



EndLoadData(): turn on validation, constraints, etc.

[B223456789, Denise] RowState: Unchanged
Version: Original Value: Denise
Version: Current Value: Denise
Version: Proposed doesn't not exist.
Version: Default Value: Denise