This month, I will show you how you could enable your applications for an offline mode. By offline, I mean that the user is not plugged to the central database. This feature is surely not required by every application and surely not the complete application should be available offline otherwise you would need to store a huge amount of data on users’ laptops.

What I will show you here is valid for a small to medium volume of data. The data is kept into a XML file when the user is offline.

Happily, the data will not be in a read-only mode. The users will be able to update the data when unplugged and update the main database when they can connect back to the database.

The demo application

The demo application I created simply connects the Northwind database on the local SQL Server using integrated security. You will have to modify the connection string if you don’t run a SQL Server on your PC. Search for the kConnectionString constant declaration if you need to modify it.

Figure 1: The demo application

The demo is using the Customers table and let you navigate through all customers, add new customers, modify and delete existing customers. Buttons also give you the option of rejecting your current modification or all modifications that were made since you last get data form the datasource. Finally, you can save your records to either the SQL server database or into a XML file.

Getting data

You can retrieve data from 2 sources, SQL Server or a XML file. Before being able to load data from a XML file, the users will have to connect to the SQL server to get data then save data to the XML file. Only then they will be able to unplug their laptop from the LAN and to go conquer the world!

The button that retrieves data from SQL simply uses a SqlDataAdapter to fill a strongly-typed dataset. While testing, I found that the DiffGram option that we will use later to save/retrieve data to/from XML does not seems to work with regular dataset.

The button that retrieves data from the XML file also fills the strongly-typed dataset using this simple line of code:

mdsCustomers.ReadXml("OfflineDB.xml", XmlReadMode.DiffGram)

For both buttons, once the data is loaded, methods are called to set the bindings to textbox and to display the positions in the cursor.

A note on DiffGram

The DiffGram option is used to preserve the state of each row into the XML file. The state will be used when data will be sent to the SQL Server so that the SqlDataAdapter that will do the job knows exactly which records needs to be added, modified, or deleted. Without this state, we would need to implement our own mechanism.

To see the impact of the DiffGram option, retrieve some data from the SQL database, add, edit, and delete some customers then save the data to the XML file. Now use your favorite XML viewer to open the file just created (called OfflineDB.xml in your bin folder). If you look carefully to the records you just edited, you will see elements like these:

diffgr:hasChanges="inserted"
diffgr:hasChanges="modified"

You will also find a new section at the bottom of your file () that contains original version of modified records and also the deleted ones.

Saving data

The button to save data to a XML file is almost a single line method. This important line is: mdsCustomers.WriteXml("OfflineDB.xml", XmlWriteMode.DiffGram)

We are using the WriteXml methods of the dataset object. To ensure that we are keeping state of each customer, the DiffGram option is used. Again, without this option, the customers would be saved into the XML file but nothing would inform us about which records need to be inserted, updated, or deleted from the SQL server database.

The button that saves data back to the SQL Server database has more code. Some of these lines request explanations.

This first block creates the SqlCommand object with a select query on the customers table:

objCommand = New SqlClient.SqlCommand
objCommand.CommandText = "SELECT * FROM Customers"
objCommand.Connection = cnnDA

Then a SqlDataAdapter is created and assign the SqlCommand object just created to the SelectCommand property:

Dim objDA As New SqlClient.SqlDataAdapter
objDA.SelectCommand = objCommand

A SqlCommandBuilder object is created which generates the INSERT, UPDATE, and DELETES queries based on the SqlCommand object:

objCB = New SqlClient.SqlCommandBuilder(objDA)

Then a magical line appears:

objDA.Update(mdsCustomers.Customers)

This line uses the SqlDataAdapter we just created (that contains required queries to INSERT, UPDATE, and DELETE from the Customers table) with the dataset that may result from a user that was offline for a week. With all the row state kept by the DiffGram option, the SqlDataAdapter is able to know exactly what has to be sent to the database.

In an ideal world, nobody would touch the customers’ table will a user is away with this data but in a realistic world, such a thing happen everyday and this means that you need to handle concurrency violation. This demo does not handle that. I invite you to read an article published on MSDN by John Papa to find how to solve these conflicts.

Navigating through customers

Since this demo is using data bindings and also a currency manager, navigating through customers is very easy. All we need to do is to set the Position property of the currency manager to another customer. That’s what you will find the navigation buttons (First, Previous, Next, and Last).

The other buttons (Add, Delete, and Cancel) simply deals with the DataView and the CurrencyManager objects like you may have seen elsewhere. Comments have been added to these methods so I won’t explain them in detail here.

Conclusion

Thanks to ADO.Net and its disconnected mode, creating an offline feature for some parts of your application shouldn’t be too hard. Just be sure you do not replicate your entire gigabyte-sized database using this mechanism!

I hope you appreciated the topic and see you next month.