Recently there has been a lot of discussion about mobile technology, but one of the often overlooked cornerstones of mobile development is the fact that application mobility means more than just being wireless. Truly mobile applications need to reach everywhere - including remote areas not covered by wireless access as well as the end user flying 40,000 feet in the air somewhere over the Midwestern United States. In these black holes of connectivity, our mobile applications must continue to function and our application data should remain as useful as possible. A convergence of various technologies is helping to make this goal a possibility today.

With the pending release of the Microsoft .NET Compact Framework, developers will gain the ability to write disconnected client-side applications targeting the rich features of the PocketPC and Windows CE operating systems. There are numerous ways to deal with disconnected and offline data in the Compact Framework, but one of the most powerful is with SQL Server CE 2.0. Designed to integrate with the Compact Framework through Visual Studio .NET and the SQL Server CE managed data provider, mobile application developers can now build highly extensible data-driven applications.

To help you understand and view some of the features of the Compact Framework and SQL Server CE, I will touch on extending a fictitious expense reporting application into the mobile environment. Instead of forcing an employee to recreate all of his or her expenses upon his or her return to the office from a business trip, we will enable the employee to enter expenses on the fly, when it is convenient. In addition, we will allow supervisors who manage their staff's expense reports to be productive on the road by enabling them to view and approve or decline these reports.

One of the first questions we need to answer in designing our system is how to keep our data in sync with the primary data store. This becomes critical when extending an existing application out to the edges of the enterprise and supporting numerous devices with disconnected data. The .NET Compact Framework and SQL Server CE offer numerous solutions.

The first solution is to use the managed providers in the .NET Compact Framework to directly access our primary data store (see Figure 1). This approach is valuable if you want to run queries against the data in real time. It also eliminates the need to worry about synchronizing the data later. The downside is that a connection to the primary data store is required at all times, and because the employees might not have connectivity to the corporate servers it is not a viable solution for our problem.

The next option is to use Active Sync's file replication. In this approach you would generate an XML file from the primary data store and transfer this file to the device using the standard Active Sync file features. This is a very easy approach to implement for read-only systems that can benefit from a "flood fill" design. However, our system requires that we input new data and have the ability to edit existing data. The file replication approach has no easy options for synchronizing multiple device updates.

The remaining two approaches will work well for our requirements, so let's take a look at both of them in greater detail.

Replication Method
The first of these options is to use SQL Server CE's replication. Replication is based on SQL Server 2000's merge replication feature. Merge replication allows the data to be updated on both the device and the server and later merged together.

SQL Server CE's replication is a message-based implementation. This allows data to be updated autonomously on the server and device. Establishing an HTTP connection via Internet Information Server (IIS) between the SQL Server Publisher and the device later merges this data. By utilizing HTTP and IIS in this fashion, SQL Server CE is able to take advantage of the built-in authentication and authorization services of IIS as well as being able to connect to a data source located behind a firewall.

To help limit the transmission times over possibly slow wireless links, the replication communication protocol supports the use of compression and the use of encryption to ensure the safety of sensitive user data. In the event of a communication failure, such as a momentary loss of connectivity, the transmission resumes from the last successfully transmitted message buffer.

To help further reduce the volume and length of data during replication, SQL Server CE offers row-and-column based filtering. Normally all data is synchronized during replication; using row filtering allows for a subset of data to be published. For our application we could use row filtering, allowing managers to synchronize expense report records for employees they directly supervise. Column filtering permits the developer to restrict the flow of certain columns to the device, possibly eliminating the replication of large columns of text or images.

The major drawbacks to this synchronization approach are that you must complete the sometimes complex configuration on the server and that your primary data store must be SQL Server 2000.

XML Web Services Method
Since we already have a legacy expense reporting system, we will use the final method for synchronizing our data, which is the use of XML Web services. XML Web services allow us to reuse existing pieces of the legacy application while extending them out to the devices in ways that were never thought of when the application was developed. Web services provide added value with the ability to connect back to the primary data store from any connection within our network - or possibly any Internet-enabled connection. In addition, if you properly design your Web services, the possibility exists to reuse them for new projects, applications, and integration in the future.

Let's take a look at how to implement this solution with a Web services approach and SQL Server CE, starting with the new expense report screen.

When a user enters this screen we need to load the project and expense category value from the database to populate dropdown lists.

Dim cn As New SqlCeConnection("Data Source= \Expense\Expense.sdf;
Password=Expense)

cn.Open()

Dim da As New SqlCeDataAdapter(Select ProjectName from Projects", cn)

da.Fill(ExpenseReportData, "Projects")

da.SelectCommand.CommandText = "Select ExpenseCategory from ExpenseCategories"

da.Fill(ExpenseReportData, "ExpenseCategories")

cn.Close()

You will use the data in your ExpenseReportData dataset at a later time to bind to the dropdown controls.

It is important to note that currently SQL Server CE is limited to a single connection. This is an issue you must take into account when working with objects that hold open connections to the database, such as the data reader object.

You will notice that the code for working with the SQL Server CE managed provider is very similar to that used for both the SQL Server managed provider in the .NET Compact Framework and the SQL Server managed provider in the .NET Framework. Microsoft has put a great amount of effort into making the learning curve between Windows Forms development and Mobile Forms development as short as possible, permitting developers to leverage their existing knowledge and skill sets. The update code for this screen is comparable in appearance to code that we would write in a Windows desktop application.

Because you are now working in a disconnected environment, you may need to make adjustments allowing for the changing of expense report IDs. In a connected desktop system you would probably rely on an auto-incrementing identity value for unique ID generation. In the disconnected environment you cannot take this approach because you will not be able to obtain a permanent ID until connection with the primary data store is restored. To solve this you can add 1 to the maximum ID in the database and flag the report as being a new report. When you reconnect, you will transmit this record to the database first and get the correct ID. After that you will update the local data representing this change. In addition, choosing to store all of the data in a demoralized layout on the device provides a slight performance gain by reducing the number of joins needed on the smaller processor devices.

Dim cmd As New SqlCeCommand("Select Max(ExpenseReportID) from ExpenseReport")
cmd.Connection = cn

Dim ExpenseReportID As Int32 = CType(cmd.ExecuteScalar( SQL).ToString, Int32) + 1

Besides the basic expense report data, when you reconnect to the database you need to load all of the employee data. To see how you use the Web service in conjunction with SQL Server CE, look at Listing 1.

When the primary database has changed, the tables on the device automatically update to the new format earlier in the sync process. In Listing 1 you will notice that the insert statement is auto-generated based on the format of the dataset. This allows for structure of tables to change with less maintenance required to this section of code.

Conclusion
One of the greatest inhibitors of mobile development has always been the lack of an enterprise-caliber mobile data store. SQL Server CE removes this barrier. Its powerful yet lightweight query engine has plenty of power to support the most demanding mobile application, while providing support for numerous synchronization methods. The ability to not only view but also work with your data at any time and any place allows applications to provide productivity to the mobile employee. Now that you are on your way to creating a truly mobile application, your end user will appreciate the access to their data in these former black holes of connectivity.

Author Bio
Brad McCabe is a technology evangelist for .NET, ASP.NET, and .NET CF for Infragistics (www.infragistics.com), a leader in providing a broad infrastructure of reusable presentation-layer components essential for the creation of next-generation Web-based applications and XML Web services utilizing .NET, COM, and Java. brad@infragistics.com

	



Listing 1

    Private Sub SyncEmployee()
        Dim EmployeeData As DataSet

        With New SyncService.ExpenseSync
            EmployeeData = .GetEmployee()
        End With

        Dim cn As New SqlCeConnection
		(("Data Source= \Expense\Expense.sdf; Password=Expense)

        cn.Open()

        Dim cmd As New SqlCeCommand()
        cmd.Connection = cn

        Dim DataRow As DataRow

        For Each DataRow In EmployeeData.Tables(0).Rows
            With New System.Text.StringBuilder
			("INSERT INTO Employee (")

                Dim ColumnCount As Int32

                For ColumnCount = 0 To DataRow.Table.Columns.Count - 1
                    .Append(DataRow.Table.Columns(ColumnCount).ColumnName)
                    If ColumnCount < DataRow.Table.Columns.Count - 1
					Then
                        .Append(",")
                    End If
                Next

                .Append(") VALUES (")

                For ColumnCount = 0 To DataRow.Table.Columns.Count - 1
                    .Append("'")
                    .Append(DataRow(ColumnCount).ToString)
                    .Append("'")
                    If ColumnCount < DataRow.Table.Columns.Count - 1
					Then
                        .Append(",")
                    End If
                Next

                .Append(")")

    cmd.CommandText = .ToString
            End With
            cmd.ExecuteNonQuery()

        Next

       cn.Close()
    End Sub