Northwind Unplugged: Building a Semi-Connected Application in Visual Basic .NET


Sean "Early" Campbell
Scott "Adopter" Swigart
3 Leaf

September 2003

Summary: Demonstrates how you can build a semi-connected application using Visual Basic .NET. This application automatically detects the connection state and seamlessly switches between online and offline. When offline, any changes to data are automatically cached, and when the connection is reestablished, the data is automatically synchronized with the server. (14 printed pages)

Download the NorthWindUnpluggedSetup.msi sample file.


Installation and Operation
How It Works
Geek Out
Recommended Links


If you have used an application like Microsoft Outlook® 2003 or Groove, then you understand the benefit of semi-connected applications. These applications "just work" without you having to worry about the availability of the network. Let me be clear—a semi-connected application is not one where you, as the user, have to manually flip the toggle switch between online and offline. A true semi-connected application assumes that the network connection can come and go at any time, and the application deals with it without any input from the user. When you are online, the application communicates with the server and saves changes. When you are offline, you should have access to at least a subset of the data, and you should be able to make changes that are automatically synchronized when the network becomes available. This article focuses on building these types of applications.

Goals and Requirements

Before you examine the implementation of Northwind Unplugged, it's worth taking a moment to outline what this application is, and is not, designed to show. The application focuses on the communication between the client and the server in an environment of intermittent network connectivity. You will see the code, and coding techniques, that enable this functionality. That said, this application is not about building the world's most complex data-bound user interface. Nor is this application designed to show advanced ADO.NET on the server side. There are numerous articles that cover both the ins and outs of user interface design, and advanced ADO.NET, and a number of these are listed in the Recommended Links section.

This article is designed to distil a number of techniques that we have used when building semi-connected applications. In the construction of this sample application, we placed ourselves under a number of constraints:

  • The application must communicate with an unmodified Northwind database.

    In most cases, you should be able build a semi-connected application against an existing back-end database, without making any modifications to the back end. For this reason, we decided that modifying the Northwind database was off-limits, and the applications should be fully functional against a legacy database. That said, if you do have the flexibility of modifying the back end, it's likely that you can do a number of optimizations that will increase the efficiency when the user performs searches and synchronizes changes.

  • The application must detect the connection state and respond appropriately.

    Once again, it's the responsibility of the application, not the user, to determine the connection state and take appropriate action.

  • All synchronization must be done through Web services.

    The application could have been coded to talk directly to SQL Server, but this would have imposed certain limitations. Ideally, a semi-connected application should make a reasonable attempt to work whenever it finds any form of network connection. If the application connected directly to SQL Server, then it would work well when used within the intranet, but it may not be able to reach the SQL Server when only the Internet is available (as it's generally considered a bad idea to plug SQL Server directly into the Internet). By communicating through Web services over HTTP, it simply increases the likelihood that you will be able to reach the endpoint when retrieving data and synchronizing changes.

  • The client must not require a local database.

    If the semi-connected client will not locally cache massive amounts of data, you should consider not requiring a client instance of SQL Server, MSDE, or other database. Eliminating a client database, if nothing else, simplifies deployment and makes it easier to leverage no-touch deployment or the Application Updater Block.

At this point, it's time to see the application in operation.

Installation and Operation

This application was built and tested on Microsoft Windows® XP, but it should work on other operating systems. You begin by downloading the application from the link at the top of the article.

Application Setup

Once the application is downloaded, you can extract the content from the .msi file. The .msi contains a setup program that installs the Web service and configures permissions on the database so that the application functions. Simply double-click on Setup.exe. This is a simple managed application that performs the configuration. If you want to know what this setup is doing, you can simply open the solution in the SetupSource folder.

Note   For this setup to be successful, you must have IIS and SQL Server running on the target machine (the machine on which the setup is run), although you can change the target SQL Server by editing the source for the Setup.exe application. You must also have the Northwind sample database installed on your SQL Server, but it is easy to get if you don't have it installed as a SQL script for it is provided with the .NET Framework SDK.

Once the application is configured, you can double-click on NorthwindUnplugged.sln to open the solution that contains the client and Web service projects. From here, you can run the application.

Application Operation

When the application starts up, it presents you with the user interface as shown in Figure 1 below.

Figure 1. Northwind Unplugged user interface

When the application starts up, it immediately attempts to communicate with the Web service to determine if connectivity is available, and to download the initial Customer list. If the Web service is reached, the status bar indicates this by stating that the application is Online. Otherwise, the application indicates that it is working in an Offline mode.

To search for orders, you simply select a customer (if you type the first couple letters, the ComboBox autocompletes the entry), and click Search. If the application is online, the application queries the Web service and displays the results. If the application is offline, the application displays data from the cache, assuming that these orders have been previously retrieved.

One thing that's interesting about the application is what's happening in the background. The application determines online/offline status when it starts, but a semi-connected application needs to assume that the network can disappear (or reappear) at any point. Therefore, the application periodically checks to see if it can still communicate with the Web service and updates the status accordingly. In addition, you can modify the data that appears in the DataGrid, and the application periodically synchronizes the changes.

Any data that has been retrieved remains cached by the client application. On application shutdown, the cached data is persisted to disk and reloaded when the application starts, as the application may start up in an environment without network connectivity. If you want to clear the client cache, you can use the File | Clear Cache menu command.

How It Works

The background work of the application is initiated by a Timer control named tmrPing. Initially, the value of the Timer is set to a very low value so that it ticks almost immediately when the application starts. On the first tick, the timer changes its interval so that it only fires once every 10 seconds:

Private Sub tmrPing_Tick(ByVal sender As System.Object, ByVal e As _
  System.EventArgs) Handles tmrPing.Tick
    CType(sender, Timer).Interval = 10000
End Sub

The real synchronization work has been factored out of the user interface and placed into the DataManager class. You can see that every 10 seconds, when the timer ticks, it calls the Ping method of the DataManager class.

Public Sub Ping()
    wsNorthwind.BeginPing(AddressOf PingCompleteInvoke, Nothing)
End Sub

This method simply calls the Ping method of a Web service. Because it uses BeginPing, rather than just Ping, the Web method is invoked asynchronously. This means that the client application continues execution without waiting for the Web method to return. When the Ping Web method does eventually complete, it triggers the PingCompleteInvoke callback (which was specified when BeginPing was called). This creates a subtle problem. The callback is normally where you would retrieve the results from the Web service. However, because of the way that asynchronous callbacks work in .NET, PingCompleteInvoke is running on a background thread. You can see this by setting a breakpoint and viewing the Threads debugger window shown in Figure 2.

click for larger image

Figure 2. Screenshot of setting a breakpoint and viewing it in the Threads debugger window

Multithreaded Programming

When you look at that debugger window and see multiple threads, you should be concerned. True multithreaded programming is heinously complex. First, none of the Windows Forms controls (TextBox, DataGrid, and so on) are thread safe. This means that you should never touch a property or call a method of a control on anything other than the main thread. If you look back at the debugger window, you can see that the main thread has an ID of 3940. We are currently on thread 3692. This is not the main thread, so Windows Forms controls are off limits.

What else is off limits? Well, almost everything. For example, if you want to set a Boolean value (for example, the application uses a Boolean called Online to track application state), this isn't thread safe. You need to wrap all access to anything that is shared between threads with thread synchronization logic. For example, before I change the value of Online, I need to insure that no other thread is accessing that value. This is accomplished with the SyncLock keyword:

SyncLock (obj)
    Online = True
End SyncLock

Now, if I make sure that I use SyncLock everywhere that I access the Online variable, then access to Online is thread safe. If I forget the SyncLock, it's not a compile time error, it's (probably) not even a runtime error. However, every time something accesses Online, there's the potential that another thread is working with it at the same time. In which case, the results are undefined and you have a bug that's completely intermittent and difficult to track down.

It gets worse. You can see that there's a lot that you have to do with a simple Boolean. Let's say you have a DataSet. Let's also say that something like a DataGrid is bound to that DataSet. Now, you can't simply wrap access to the DataSet in SyncLock blocks. Why? The DataGrid isn't going to SyncLock its access to the DataSet, so while you might do it in all your code, that's not the only code that's touching the DataSet. What to do? Well, I suppose you could create a class that inherits DataSet and wraps every single property and method to provide the synchronization logic, but frankly, this is Visual Basic® .NET, and you shouldn't have to work that hard. There is another way.


If you think about it, you want to call the Web service on a background thread, but once the method has completed, it's probably fine to work with the data on the main thread. It turns out that for a different reason there is a way to move data back to the main thread. Remember, Windows Forms controls are not thread safe. Microsoft knew that it would be a common scenario to do work in the background and update the user interface based on the results. So, you are provided a mechanism to marshal data back to the main thread. This functionality is provided by the Control class, which Forms and user interface controls inherit. You will notice that the DataManager also inherits this base class. By doing this, the DataManager can marshal the call back to the main thread as follows:

Protected Sub PingCompleteInvoke(ByVal ar As IAsyncResult)
    Me.Invoke(New PingCompleteDelegate(AddressOf Me.PingComplete), _
        New Object() {ar})
End Sub

There are a couple of things going on here. First, when the callback fires, the PingCompleteInvoke method is executed on the callback thread. It is passed an IAsyncResult that can be used to retrieve the results of the Web service. This method uses Me.Invoke to execute PingComplete on the main thread. PingComplete is passed the IAsyncResult so that it can retrieve the results of the Web method. Figure 3 shows that PingComplete is, in fact, executing on the main thread.

click for larger image

Figure 3. PingComplete executing on the main thread

You can see that Me.Invoke called the PingComplete method, but it also moved execution back to the main thread. Here's the beauty of this operation. In PingComplete, you can modify global values, DataSets, anything you want, and you don't need to worry about thread synchronization because now you're on the main thread. In other words, variables are only modified from the main thread, which alleviates the need for any thread synchronization code. If you examine the body of PingComplete, you can see that SyncLock is not required or used:

    Protected Sub PingComplete(ByVal ar As IAsyncResult)
            Online = True
        Catch ex As Exception
            Online = False
        End Try
    End Sub

The call to EndPing simply retrieves the results of the Web service. If the Web service is not accessible, then the call to EndPing throws an exception, and the catch block sets the application to Offline mode. If the call is successful, then the application attempts to do some data synchronization (if any is required) with calls to CheckLookups and SyncOrders.

One possible concern is that the Ping method is being called every 10 seconds, which seems like it might generate a lot of server traffic. However, if you look at implementation of the Ping Web method, you can see that it's very lightweight:

<WebMethod(CacheDuration:=3600)> _
Public Sub Ping()
End Sub

Ping literally does nothing. In fact, it uses caching so that the empty method only gets executed once per hour. All the other times, the results are simply served from the cache. Even though it's being called frequently, it requires very little from the server.

Data Synchronization

The real work of the application centers on data synchronization. There are two main sets of data that the application needs to synchronize. The first is a set of lookup tables that includes the list of customers. From the perspective of the client, the lookup tables are read-only, which simplifies synchronization. The customer orders are also synchronized, and this is data that the client can update, so this requires a bit more logic.

Retrieving new lookups

When the application starts up, it attempts to load the lookup information from a local XML file. However, although the lookup information is fairly static, it can change periodically (customers can be added). So, as soon as the application goes online, it attempts to download new lookup information from the server. Once the lookups are downloaded, the application will not download them again until the application restarts. The code to accomplish this is as follows:

Public Sub CheckLookups()
    If Not lookupsDownloaded And Online Then
        wsNorthwind.BeginRetrieveLookups( _
            AddressOf RetrieveCompleteInvoke, Nothing)
    End If
End Sub

Protected Sub RetrieveCompleteInvoke(ByVal ar As IAsyncResult)
    Me.Invoke(New WebMethodCompleteDelegate( _
        AddressOf Me.RetrieveLookupsComplete), _
        New Object() {ar})
End Sub

Protected Delegate Sub WebMethodCompleteDelegate(ByVal ar As IAsyncResult)
Protected Sub RetrieveLookupsComplete(ByVal ar As IAsyncResult)
        Dim ds As dsOrders = wsNorthwind.EndRetrieveLookups(ar)
        RaiseEvent LookupChanging()
        syncChanges = False
        syncChanges = True
        lookupsDownloaded = True
        RaiseEvent LookupChanged()
    Catch exc As Exception
    End Try
End Sub

The operation of this code should start to look familiar. The Web method is called asynchronously by calling BeginRetrieveLookups. This triggers the callback function RetrieveCompleteInvoke. Since this is not on the main thread, the call is marshaled to the main thread with Me.Invoke, and RetrieveLookupComplete is called. This method retrieves the lookup information from the Web service as a DataSet.

At this point, you have a DataSet that may contain order information, and you have another DataSet that contains the updated lookup tables. You can put the two together using the Merge method of the DataSet. The data that is merged into m_Orders is considered to be changed, so AcceptChanges is called to clear the changed flag on these rows.

There are a couple of other things going on in this method. The LookupChanging and LookupChanged events are raised. The main form sinks these events and when it sees that the lookup information has changed, it repopulates the customer's drop-down information.

When you call Merge, it also triggers row and column changed events on the DataSet, but from the perspective of our application, these aren't real changes. The only changes that the application needs to track are changes that the user makes through the DataGrid because those need to get pushed back to the server.

Synchronizing orders

The orders information is a little more complex because the user can change the orders, and the changes need to be synchronized periodically while the application is running. The first challenge is to detect changes. When you make changes to through the DataGrid, the grid updates the underlying DataSet, and this DataSet fires events:

Public Sub RowChanged(ByVal sender As Object, _
    ByVal e As DataRowChangeEventArgs)
    If syncChanges AndAlso m_Orders.HasChanges Then
        ordersChanges.Merge(m_Orders.GetChanges(), False)
        syncChanges = False
        syncChanges = True
    End If
End Sub

Private Sub ColumnChanged(ByVal sender As Object, _
    ByVal e As DataColumnChangeEventArgs)
    If syncChanges AndAlso m_Orders.HasChanges Then
        ordersChanges.Merge(m_Orders.GetChanges(), False)
        syncChanges = False
        syncChanges = True
    End If
End Sub

When these events fire, the code determines if these are real changes. Any time the DataSet is loaded or merged from XML, syncChanges is set to false so these event handlers ignore those changes. However, if the changes are the result of the user modifying data in the DataGrid, then syncChanges is true and additional processing happens.

The call to m_Orders.GetChanges() returns a new DataSet that contains only the changes to the orders. This DataSet is then merged into the ordersChanges DataSet, which tracks all the changes that the user has made. As the user continues to edit rows through the grid, they continue to get merged into ordersChanges.

The call to m_Orders.AcceptChanges marks all the rows in that DataSet as unchanged. In other words, once the changes are saved off to ordersChanges, the m_Orders DataSet no longer needs to track these changes. OrdersChanges only needs to hold on to the changes until they can be synced to the server. Once they're synced, ordersChanges can be cleared.

When does the sync happen? Like all asynchronous operations in this application, it's triggered off of the Ping timer. When a ping succeeds, SyncOrders is called:

Protected Sub SyncOrders()
    If syncChanges Then
        If ordersChanges.HasChanges Then
            wsNorthwind.BeginSaveOrders(ordersChanges, _
                AddressOf Me.SyncCompleteInvoke, Nothing)
        End If
    End If
End Sub

Protected Sub SyncCompleteInvoke(ByVal ar As IAsyncResult)
    Me.Invoke(New WebMethodCompleteDelegate( _
        AddressOf Me.SyncOrdersComplete), New Object() {ar})
End Sub

Protected Sub SyncOrdersComplete(ByVal ar As IAsyncResult)
    Catch ex As Exception
        ' The orders weren't successfully sync'd.  Try again later.
    End Try
End Sub

SyncOrders calls BeginSaveOrders, passing in the ordersChanges DataSet. When the call is completed, it's first marshaled back to the main thread and SyncOrdersComplete is invoked. You don't know if the call was actually successful until you call EndSaveOrders. If EndSaveOrders doesn't throw an exception, then you know the sync worked and ordersChanges can be cleared.

Caching locally

Sometimes, the application is shutdown before the changes can be synchronized. In this case, the application has to persist the data locally. The XML support in the DataSet makes this easy to do.

Public Sub SaveLocal()
    m_Orders.WriteXml(appData & "\orders.xml")
    ordersChanges.WriteXml(appData & "\orderschanges.xml", _
End Sub

The m_Orders DataSet never contains changed rows because as soon as the changes are detected, they are saved off to ordersChanges, so m_Orders can be saved with a simple WriteXml. OrdersChanges, on the other hand, contains only changed rows. To synchronize with the database, the DataSet needs to track the current values for the rows, but also their original values. When you persist the DataSet using the XmlWriteMode.DiffGram, then all this information is saved.

This covers the operation of the application and shows how a semi-connected client is constructed. When building semi-connected applications, there are a few recommendations:

  • Whenever possible, call Web method asynchronously, but marshal back to the main thread before you deal with the results. This eliminates the need to write complex and error-prone thread synchronization logic.
  • GetChanges can be used to factor changed rows out of a DataSet.
  • Merge lets you merge new rows on the server into the DataSet on the client.
  • Read/WriteXml let the client persist data locally.

Geek Out

This article has outlined a simple, but fully functional semi-connected application. Even this application could be optimized in a number of ways:

  • Tune the ping and synchronization interval.

    Right now, the application determines if it's online by pinging a Web method every 10 seconds. This could be modified to be a self-tuning interval based on the response time for the ping and the application state.

  • Try, try again.

    The application is hard-coded to talk to a single Web service, but the URL property of the proxy class allows you to point to a different Web service at runtime. When the application is moved from one network environment to another, it's likely that you might have an IP address and valid connection, but maybe DNS isn't available or properly configured. The application could use the DNS class to resolve the IP address of the Web server and cache this information so that if the client can resolve the DNS for some reason, it could set the URL to point directly to the IP. If the user travels across the country, they will be running the application in a completely different geographic region. Then application could download a list of Web services, ping them all, the lock on to the one with the lowest response time.

  • Bi-directional sync.

    One of the original constraints was that no modifications would be made to the back-end database. However, if you could make changes to the back-end database, then you could enable more sophisticated scenarios. For example, right now the client will not know that rows on the server have changed unless the client re-queries for those rows. By modifying the back-end database so that each row contains a timestamp, the client could be modified to efficiently query the server for just changed rows, providing a bi-directional sync.

  • Build a framework.

    This application uses a DataManager class that is hard-coded for the specifics of this application and the data that it will be synchronizing. If you are planning on adding semi-connected functionality to a number of applications, you may want to think about building a more generic framework. For example, you could create a class that exposes properties for the DataSet and synchronization methods, such as:

    Dim sync as New GenericSyncClass()
    sync.DataSet = dataSetToSync
    sync.UpdateMethod = AddressOf webService.SyncMethod
    sync.SelectMethod = AddressOf webService.SelectMethod

    Such a class could then handle all of the synchronization behind the scenes. Or, for a simple approach, you could inherit the Web service proxy class and simply implement retry functionality.


Users, more and more, are expecting applications to work well in semi-connected environments. This is the result of a number of converging factors. First, laptop sales have now exceeded desktop sales. This means that users are mobile. Also, most new laptops now include 802.11 wireless cards as standard equipment. So, users are not only mobile, they come and go through areas of connectivity. Laptops let users get to a location and set up shop, but new devices are emerging that are literally designed to let the user work on the move. In fact, a major advantage of a Tablet PC is that it can be used while standing or walking.

Users are also being shown the light. Applications like Outlook 2003 work great in semi-connected environments, and do not require the user to manually switch between online and offline mode. Applications like Groove were architected from the start to be semi-connected.

As the decision makers in organizations are exposed to good examples of semi-connected applications, and as the semi-connected hardware (laptops, Tablet PCs, and so on) becomes ubiquitous, it's only a matter of time before decision makers demand that their applications work this way.

The predicament is that the .NET Framework does not inherently support semi-connected applications. Some of the building blocks are there with DataSet functionality like Merge, GetChanges, and Read/WriteXml. However, the transport of the data falls onto your shoulders. This article shows techniques that can get you started down the path.

Sean Campbell and Scott Swigart are Sr. Principals of 3 Leaf. At 3 Leaf, Sean and Scott spend their time analyzing how emerging technologies can be used when building solutions. With this knowledge, 3 Leaf helps companies successfully integrate new technologies into their business, through a variety of consulting, mentoring, and training services. For the companies that are inventing emerging technologies, 3 Leaf builds highly technical content that user of that technology can utilize when doing real implementations. You can contact Sean and Scott at,, or through the 3 Leaf Web site at