Manage data integrity

The topic of data integrity is an important one. You should always make sure that your data is in a consistent state, particularly during modifications. To do this, you should use the built-in functionality of the DataSet class, which allows you to deal with cascading updates and deletes, manage constraints, and the rest.

There are two key classes in this part of the framework: UniqueConstarint and ForeignKeyConstarint. Both of them defines rules that must be applied to the data stored in the DataSet (as long as the EnforceConstaint property is set to true). Let’s start with the ForeignKeyConstraint, because it’s slightly more complex than the other one.

You set up a ForeignKeyConstraint like this:

ForeignKeyConstraint fkc = new ForeignKeyConstraint(parentColumn, childColumn);

After you have declared it, it behaves in the exact same manner as if it was set up in the data source. There are four actions which can take place when you delete the foreign key column, depending on how you set the DeleteRule property. Also, the UpdateRule can be set to the same values, which are:

  • Cascade: the child rows are modified (deleted or updated) as well.
  • SetNull: the child rows are set to null.
  • SetDefault: the child rows are set to their default values.
  • None: no actions are taken on the child rows.

You can set the AcceptRejectRule to a member of the AcceptRejectRule enumeration, which can be Cascade or None. The AcceptRejectRule is used when you call the AcceptChanges method of the DataTable/DataSet.

The other type of constraints is the UniqueConstraint. It can be added to the Constraints collection (which is exposed through a same-called property of the DataTable), just like the ForeignKeyConstraint. The method is like this:

dtTable.Constraints.Add(new UniqueConstraint(new DataColumn(“IDColumn”)));

The unique constraint does what its name suggests: ensures that no duplicate values can be inserted into the given column in every DataRow. I think there’s no for more explanation.

Another feature of the DataSet is the automatic key generation. It is exposed through three properties of the DataColumn class:

  • AutoIncrement: a Boolean value indicating if auto incrementing the column is set.
  • AutoIncrementSeed: the seed value of the identity key.
  • AutoIncementStep: the value by which the AutoIncrementSeed should be incremented.

When you reconnect a DataSet to the data source after a long time, the possibility of having duplicate identity keys is very large. Because of this, in these scenarios, you should set the AutoIncrementSeed to a negative value, and the step should be also set to a negative number. When updating the data source, it will generate new identity keys for your negative values, based on the database settings, ensuring that there won’t be any computed key conflicts during updates.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s