LINQ gives us a more sophisticated and powerful to querying data tables. Put this together with a strongly typed TableAdapter and it almost becomes too easy!

In the previous article we looked at the classical approach to ADO.NET but the more sophisticated and powerful, not to mention actually simpler, approach is to use LINQ to query the dataset. This works in more or less the same way no matter what the data provider is so once again we have a generalizable skill worth knowing.

This example uses the Google data provider and how to get started with it is described in detail in Accessing website data using ADO.NET

In summary the steps are:

• download the Google Data Provider trial edition from RSSBus and install it by running the .exe.
• Start Visual Studio 2010 or Express
• Use the Add Connection command in Server Explorer and select the RSSBus Google Data Source as the Data Source. If you add a user name and password you can test the connection at this point.
• Start a new C# Windows Forms project (or any other type of project if you are willing to make the obvious changes to the example)
• Select the project, right click and select Add,New Item. When the Add New Item dialog box appears select DataSet - the DataSet Designer should open.
• use the DataSet designer to create a TableAdapter for the Contacts table - simply drag and drop the Contacts table on the DataSet Designer.

At this point you are ready to start coding and acessing the data represented by the TableAdaptor. You can either create instances of the DataSet and TableAdaptor in code or drag and drop the custom controls that have been added to the Toolbox.

To avoid having to give fully qualified names in the code add:

using System.Data.RSSBus.Google;

to the start of the program.

We need to set the connection string so that the DataAdapter can connect to the "database" i.e. the Google users name and password:

contactsTableAdapter1.Connection = new 
 GoogleConnection(
   "Password=password;User=username");

Using a DataTable

One of the aims of this article is to emphasize the advantages of using a strongly typed DataTable as provided by the DataSet designer. Before looking at this in detail it is worth looking at the standard way of using a DataTable without strong typing - and this is the way that most of the ADO documentation shows you how to do the job.

First we need a DataTable with some data and to get this we can use the TableAdaptor's GetData method:

DataTable MyTable = 
          contactsTableAdapter1.GetData();

Notice that we are treating the return type as a simple DataTable which is all we could get from a more basic approach to creating a DataAdaptor.

To gain access to the data in MyTable we now have to use collection based syntax. For example, to get the third row you would use:

DataRow MyRow = MyTable.Rows[2];

In this case MyRow is a general untyped collection of fields each regarded as of type object. So to retrieve a particular field you would again use general collection syntax:

string fullname = 
             (string) MyRow["FullName"];

Notice the need to cast the object returned to the correct data type corresponding to the field.

The good news is that even if you don't want to use Linq some of the extension methods that were introduced to make it all possible are really very useful additions to standard ADO.NET programming.

For example the new generic Field method will return the correct datatype without a cast:

string fullname = 
         MyRow.Field<string>("FullName");

However Linq really does make queries easy so lets see how to use it with an untyped DataTable.

Untyped Linq

To make use of Linq with an object it has to implement the IEnumerable interface and the DataTable doesn't. The solution is to make use of the AsEnumerable extension method which converts the DataTable to an object which does support IEnumerable. This is really all you need to get started with using Linq on an untyped table - everything else works in roughly the same way except that you have to use the other extension access methods because you can't cast within a Linq query.

For example to  retrieve all of the FullName entries that contain an "a" you would use:

var q = from contact in 
                   MyTable.AsEnumerable()
 where contact.Field<string>
             ("FullName").Contains("a")
 select contact.Field<string>("Fullname");

You can use all of the usual Linq methods and techniques but you have to use the Field<> method to obtain typed data to work with. If you want to set a field to a new value then you have to use SetField<> method in a similar way.

Using a typed TableAdaptor

Now that we have seen how easy it is to use Linq with an untyped table we can stop pretending that the TableAdaptor we created is untyped.As long as we assign it the correct type then it is a typed DataTable:

DataSet1.ContactsDataTable MyTable =  
          contactsTableAdapter1.GetData();

Now we can treat MyTable in its true form rather than being up cast to DataTable. In particular all of the database fields are now available as correctly typed properties:

DataSet1.ContactsRow MyRow = MyTable[2];
string fullname = MyRow.FullName;

Notice that we are no longer using the Rows collection but the class indexer and now we can simply use the FullName property.

This makes working with Linq very much easier.

Typed Linq

The next question is how to use the typed dataset in a Linq query. This is something that isn't covered very well in the documentation. The trick is to use the full generic form of the AsEnumerable extension method. In this case we want it to return not a general row object collection but a collection of the typed rows i.e.  as a DataSet1.ContactsRow.

For example the untyped Linq query given in the previous section could be written:

var q = from contact in MyTable.AsEnumerable 
                 < DataSet1.ContactsRow >()
where contact.FullName.Contains("a")
select contact.FullName;

Once you have used the AsEnumerable<> method with the strongly typed row you can write a Linq query as you would for any IEnumerable supporting class using the correctly typed properties.

This clearly is the best way to do the job.

The conclusions are:

• ADO.NET data providers are a good way to access any data in table form even if it's on the web and actually provided via an API.
• Using the DataSet Designer to create a strongly typed TableAdaptor is easy and provides lots of advantages when you come to write code.
• Actually using the strongly typed TableAdapter is possible with or without Linq