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:

Continue reading “Manage data integrity”

Update data

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.

Continue reading “Update data”

Manage transactions

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:

  1. Create the transaction
  2. Execute the commands which are parts of the transaction
  3. 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);
using (theConnection)
{
theConnection.Open();
SqlTransaction theTransaction = theConnection.BeginTranscation();
theCommand.Transaction = theTransaction;
theCommand2.Transaction = theTransaction;
try
{
theCommand.ExecuteNonQuery();
theCommand2.ExecuteNonQuery();
theTransaction.Commit();
}
catch(Exception ex)
{
theTransaction.Rollback();
}
}

Continue reading “Manage transactions”

Work with strongly typed DataSets and DataTables

I think every developer working with any programming languages and frameworks knows the benefits of strongly typed classes. This is particularly true in the .NET Framework, where string literals fall outside the scope of compiler checks, and given this, it’s easy to introduce errors which show up run-time only.

When you are working with DataSets (and the classes of System.Data), it is much more easier to generate these types of errors, because of the massive number of indexers used in this part of the framework. Think about how you’d retrieve DataTables from a DataSet, or DataColumns from a DataTable. Something like this:

DataTable dt = DataSet.Tables[“myTable”];
DataColumn dc = dt.Columns[“myColumn”];

Two possible places where typos can raise hell. Even worse, when dealing with untyped DataSets, casting is heavily used:

DataRow dr = dt.NewRow();
dr[“ProductID”] = 1;
int ProductID = (int)dr[“ProductID”];

Fortunately, there is a solution. However, this is not part of the .NET Framework, and has nothing to do with the System.Data namespace. It is a feature of Visual Studio, and it’s been around since version 7. It is called a strongly-typed DataSet. The main benefits are:

  • Type safety: no casting, and you can use your custom types safely.
  • IntelliSense support: no more typos in strings, and faster lookups, since columns are properties of rows, etc.
  • Wizards: Visual Studio provides powerful wizards to generate your strongly-typed DataSet in seconds.
  • Separation: Visual Studio watches out not to hard-code your connection strings. Instead, it creates a custom entry for the generated connection strings in the connectionStrings section of your .config file.

Continue reading “Work with strongly typed DataSets and DataTables”

Expose a DataTableReader from a DataTable or from a DataSet

As DataReaders were ideal to read information from a connected database quickly (in a read-only, forward-only manner), DataTableReader does the same for your disconnected DataTables. The magic words (which I can’t underpin enough) are read-only, forward-only.

You work with the DataTableReader exactly the same way as you’ve done with DataReaders. The main difference is instantiation. You need to call the CreateDataReader method of a DataSet or a DataTable. When you call the DataSet method, you have the opportunity to pass an unspecified number of DataTable instances as parameter (with the help of the params keyword). Then you can call the NextResult method of DataTableReader to jump to the next table.

An example of how to do it:

DataTable myTable = new DataTable(“MyLittleTable);
SqlDataAdapter myAdapter = new SqlDataAdapter(aValidSqlCommand);
myAdapter.Fill(myTable);
DataTableReader theReader = myTable.CreateDataReader();
while(theReader.Read())
{
for(int i=0; i<theReader.FieldCount; i++)
Console.WriteLine({0}: {1}”, i, theReader.GetValue(i));
}

Continue reading “Expose a DataTableReader from a DataTable or from a DataSet”

Programmatically create data objects

Today is the great DataSet day, which means I hope that I can revise and publish all the following objectives from the syllabus:

  • Programmatically create data objects
  • Work with untyped DataSets and DataTables
  • Expose a DataTableReader form a DataTable or from a DataSet
  • Work with strongly-typed DataSets and DataTables

So much for announcements, let’s dive into the magical world of DataSets (and how to create them programmatically). As you could guess, creating a DataSet takes a single line of code, which is the following:

DataSet ds = new DataSet();

We have one overload of the constructor, which accepts a string as a parameter, and can be used to specify the name of the yet-to-be instantiated DataSet. A little hierarchy: on the top, lives the DataSet. It can have multiple (unlimited) DataTables, and can define any number of DataRelations between these DataTables. DataTables can be further divided to DataRows, DataColumns, and Constraints. They also expose a set of properties, which help you to control the primary keys, DataViews, etc. of them. Let’s not waste more time, and create a full-blown DataSet with two DataTables, a DataRelation, and the rest:
Continue reading “Programmatically create data objects”

Manage occasionally connected data

As I’ve stated before in a number of posts, there are two types of data retrieved from a database: connected, and disconnected. Connected means that you query and update data in real time, thus you are dealing with up-to-date information. However, connected data has some severe limitations, aside from its benefits.

The biggest limitation, which is the biggest benefit in the same time, is that connected data requires an opened database connection. This is sometimes impossible, especially when you are working with data sources that aren’t present in your computer, but sit on remote servers. Sometimes, you can’t access these data sources, and you have two choices: you will only use your application when you are able to create a connection between it and the database server, or you’ll use a disconnected data managing solution. Fortunately, ADO.NET has one, well tailored to your needs.

The heart of all ADO.NET disconnected data classes is the DataSet. Everything dealing with disconnected data is in (or can be put in) context with it. You can store information in your DataSets, serialize them into and from XML files, etc. Even better, when you’ve finished editing your data locally, you have the option to send back everything updated to the remote database server, without writing a single query to do so.

This post was just the introduction of what is yet to come. The next few (few means four now) posts will deal with the features and the handling of the DataSet and all of its related classes: DataTable, DataRelation, DataColumn, DataRow, etc. Stay tuned!