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:
Now this one seems to be a huge topic, but have no fear, we’ll overcome it. The “may include, but is not limited to” section says: update data by using stored procedures, update DataSets to data source, managing concurrency.
Updating data by using stored procedures is not the hardest task in our lives. Simply build a SqlCommand object, set the CommandType to StoredProcedure, and use parameters (always use parameterized SQL commands, because SQL injection attacks will haunt you in your dreams if you wouldn’t do so). I think this one is out.
To update DataSets (and all the related stuff) is a bit more complicated. You should use DataAdapters (or TableAdapters, strongly typed or not) to solve this issue. DataAdapters expose some properties related to updated data, such as UpdateCommand, or DeleteCommand and InsertCommand. If you remember what I wrote about the states of DataRows, you can easily put the pieces together. If not, here’s a brief refresher:
When you call the Update method of the DataAdapter, it will treat DataRows differently, based on their RowState property. On the ones marked Deleted, it will call the DeleteCommand. In a similar way, Modified rows will be processed by the UpdateCommand, and Added will be by the InsertCommand. Watch out of this, because there may be exam questions lurking out there on this topic.
Transactions are data operations that need to succeed or fail as a group. The usual example is the money transfer: the given amount must be subtracted from an account, and added to another. If an exception occurs during the transfer, everything should be rolled back. No one would tolerate money which evaporates on the internet…
If you’d like to write a transaction manually in C#, using the .NET Framework, there is nothing in your way. You should use the DbTransaction base class, or one of its provider-specific derivatives to implement your custom transaction logic. There are three steps to follow here:
- Create the transaction
- Execute the commands which are parts of the transaction
- Commit or roll back the transaction, depending the outcome
A quick example:
SqlConnection theConnection = new SqlConnection(theConnectionString);
SqlCommand theCommand = new SqlCommand(theCommandText, theConnection);
SqlCommand theCommand2 = new SqlCommand(theCommandText2, theConnection);
SqlTransaction theTransaction = theConnection.BeginTranscation();
theCommand.Transaction = theTransaction;
theCommand2.Transaction = theTransaction;