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

Advertisements

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

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

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!