Accessing website data using ADO.NET
Written by Alex Armstrong   
Monday, 24 January 2011
Article Index
Accessing website data using ADO.NET
Target Framework
Modifying and Updating tables

Using the right ADO.NET connector you can work with many web APIs as if they were data tables. This is easy and using a strongly typed TableAdapter it's reliable.

Interacting with websites via the wide range of APIs that are on offer is attractive because it provides new possibilities but is slightly depressing because you have to find out how to do the job all over again for each API.

Now there is a better way. RSSbus has implemented a range of common APIs as ADO.NET data providers - Google, QuickBooks, Salesforce and SharePoint, with more planned.

What this means is that if you know how to work with databases using ADO.NET there is nothing more to learn. If you don't know ADO.NET then the good news is that it is easy and generalisable - and so worth learning.

In this article we are going to look at using the Google ADO.NET data provider but the general principles are the same for any ADO.NET data provider. We are are also going to implement a desktop Windows Forms application, but again the principles apply to any platform that can work with ADO.NET - WPF and ASP.NET. This means that you can use the same ideas to implement a desktop or web application.

Getting started with ADO.NET and the Google data provider is made even easier by the use of the designers that Visual Studio has - both the full version and the Express editions. Anything that you can do using one of the designers can be done using nothing but code and in many cases code is the more direct and efficient way to implement things - but when getting started a code generator is often simpler. In this case it also gives you a more powerful strongly typed set of classes to work with.

The Google data provider gives you access to a user's Google Calendar, Contacts, Docs, email, spreadsheets and more. To make it all work you need to download the Google Data Provider trial edition from RSSBus. Simply follow the instructions and install the data provider.

Connections

When you next start Visual Studio you can set up a new Google Data source using the Server Explorer. In a real application you would have to arrange to supply the user name and password programmatically, but the simplest way of trying things out is to supply your own Google user name and password - if you don't have one sign up for a new account, it's free after all.

Use the Add Connection command and select the RSSBus Google Data Source as the Data Source - if you can't find this then you haven't installed the data provider.

In the Add Connection dialog box you simply need to supply the password and user name for the Google account.  These will be used to create a connection string which provides the details needed to make the connection to the Google account. Before moving on to work with the connection it is worth clicking the Test Connection button. This should come back with a "Test connection succeeded" message - if not you probably haven't entered the account details correctly or there is  problem with the Internet connection.

If the connection tests OK then you can also run a query to examine the data. further. How to do this is well explained in the online help so let's move on to look at easy ways of creating some code.

Start a new Windows Forms Application C# application. You can create the same application in much the same way using Visual Basic if you want or and a WPF or ASP.NET project would be created using very similar steps.

The DataSet Designer

The next task is to use the DataSet designer to create a TableAdapter which is roughly speaking a DataSet complete with a DataAdapter. However the TableAdapter is more powerful in that it is strongly typed and has some additional methods. This is one time when using a designer is not only easier it actually generates better code.

Select the project, right click and select Add,New Item. When the Add New Item dialog box appears select DataSet - you can call it anything you like but to make clear how things work the default name is used DataSet1 in this example.

 

Dataset

Once the DataSet has been created you can make use of the DataSet Designer to create the objects you need to work with the database.

Before we do that a quick reminder of how ADO.NET works.

There are a number of .NET classes that represent the data in memory the DataTable being the main one.

There is also a DataAdapter class that makes the connection between the in memory Table and the real database.

The DataAdapter uses a "connection string" to specify the details of the connection.

The new idea in ADO.NET 4 is the TableAdapter. This is a strongly typed DataTable plus a built in DataAdapter. It also has some other useful methods and properties that make is much easier to work with than the simple DataTable and the DataSet Designer is the simplest way of creating one.

If you now look at the RSSBus Google Data Provider in the Server Explorer you will see that there are Feeds, Views and Services. Feeds and Views are essentially data tables with feeds being read/write and views being read-only.

 

tables

 

You can see that there are three data tables in the Feeds section - Calendar, Contact/Groups and Contacts.

We are going to connect to the Contacts table because the application is going to provide synchronisation between local and online contacts.

To create a DataAdapter for the Contacts table - simply drag and drop the Contacts table on the DataSet Designer. A default DataTable is created complete with all all of the columns that the Google data uses and a TableAdapter ready to be used to fill the DataTable. You can specify a SQL query as part of the DataAdapter to extract a subset of the records/columns but the default is to extract everything.



Last Updated ( Tuesday, 08 March 2011 )