Update

Now we come to the more difficult problem of updating a table. One the DataTable is loaded with data using the Fill or GetData methods you can work with the rows collection in the usual way. That is you can modify the data, delete rows etc. with out having to worry about what is happening to the data stored in the database or in the Google account in this case. However what if you want to update the external data with the changes you have made in memory?

This is where the TableAdapters Update method comes into play. In principle the Update method should be generated automatically when you create the TableAdapter but in most cases it finds it too difficult and leaves the method undefined. Fortunately it is fairly easy to create an Update method manually.

The key is to supply a SQL command that performs the update. To do this you have to supply a WHERE clause that matches up the data in the DataTable with the data in the database. In most cases the WHERE simply matches records on the key value. You also need a SET clause which stores the new data in the fields you want to change. So for example:

UPDATE table
SET field1=value1
WHERE (key=value2)

This will perform an update on table and will change only the record with key=value2 and it will change  field1 to value1. 

This easy enough but the TableAdapter will apply an UPDATE command to every record in the row collection clearly the UPDATE command has to use variables not constants to indicate what each field is to be updated to. What ADO.NET does is to introduce variables indicated by an @ at the front of the row property name. So for example, each ContactsRow object has a FullName property and to update the external record you would use something like

UPDATE Contacts
SET FullName=@FullName
WHERE (Editlink=@Editlink)

This will update the Contacts table matching up in memory records that have changed to table records that need to be changed by matching Edltlink to the Editlink property - which is the key for this table and so unique. Once the in memory and table records are matched the FullName field is updated by the contents of the FullName property of the row object.

To create the Update method open the DataSet editor and examint he properties of the ContactsTableAdapter. Click on Update command and select New. Next enter the SQL command as listed above and that's all you have to do.

You can now write code like:

MyTable[0].FullName = "I Programmer";
contactsTableAdapter1.Update(MyTable);

and the Google contacts data table will be updated. Of course there is always the possibility that the update will fail for one reason or another so use a Try-Catch.

Notice that only the FullName field is updated even if you change other fields in the in memory record. To change other fields simply add them to the SQL command.

Where next

You should be able to extend the ideas explained in this article to other tables in the Google provider and even to other providers. It is a neat approach to working with web APIs. The DataSet Designer also makes life easier and more reliable. You can use its strongly typed DataSets to work with both LINQ and the Entity Framework but in most cases simply getting, modifying and updating data is all you need and ADO.NET on its own does this very well. It really is only worth thinking of more complex approaches when joins and intertable relationships have to be handled.

More information about  RSSBus Data Providers and to download the trial software.