Data Access Strategies for the Microsoft ..NET Compact Framework

Summary: The .NET Compact Framework can power communication and application data exchange between mobile devices and servers in many different ways. It is important to define a solid data access strategy before designing, developing and deploying solutions as each method has different characteristics. This article discusses the key options: XML-based and those based on SQL Server CE. The importance of defining a Data Access Strategy is critical since it will lead to more efficient and less error-prone development and deployment.

Contents

Introduction
Data Access Strategy Aspects
Code Walkthrough
Conclusion

Introduction

The main focus of this article, from a device perspective, is the Pocket PC platform. However, the Microsoft® .NET Compact Framework also runs on Microsoft Windows® CE .NET devices that implement other form factors than the Pocket PC. The .NET Compact Framework related concepts discussed here can be applied to these devices as well.

A .NET Compact Framework data access strategy deals with a number of aspects. The key aspect, on which all other depend, is connectivity. Since the Pocket PC can be used both online and offline, a data access strategy needs to define how to:

·                   Manage and use data on remote servers when online

·                   Store and use data while offline

·                   Exchange data when the Pocket PC goes online from a previous offline state

In a data access strategy context it is important to establish a common terminology. This is especially true in how data exchange between Pocket PC and server is defined. From a conceptual perspective, there are two methods to exchange data: Data synchronization and data transfer.

Data synchronization supports exchanging data in both directions and implements functionality to ensure that data on both sides is identical. In many scenarios, the same data can be modified in multiple places while the Pocket PCs are offline and then synchronized with the server. Synchronization functionality must therefore, most often, include conflict resolution that deals with concurrency issues that occurs when a disconnected Pocket PC returns online to synchronize data. An example of this scenario occurs when a mobile salesman updates an order in the field while being offline. Before the salesman synchronizes the changes, an online customer service representative updates the same order. A carefully designed conflict resolution mechanism can determine what actions to take when the conflict occurs, for example the mobile salesman overriding the customer service representative based on role or time of synchronization, or if the system should notify a user or group of users with a message that a conflict has occurred.

Data transfer also supports exchanging data in both directions, but there is no functionality in place to ensure that data on both sides are identical and there is no conflict resolution. There are many scenarios where data transfer is a valid method and where data synchronization is not necessary. Examples of data transfer scenarios include transferring static reference data from server to Pocket PC, transferring only new collected data from Pocket PC to server, and transferring data to and from Pocket PCs that explicitly is checked in and checked out or segmented to individual users and marking the data as read-only on the server while it is checked out or owned by a specific user.

The use of the terms "data synchronization" and "data transfer" will hereon reflect their true meanings and when both are implied the term "data exchange" will be used.

Data Access Strategy Aspects

Fundamentally, the .NET Compact Framework data access strategies deal with two aspects:

1.                 How to store data on the Pocket PC. From an application standpoint, data can be persisted in a relational database (such as Microsoft SQL Server™ CE), in local files (such as XML files often managed through DataSets), and in session-based in-memory data structures that are disposed when the application is terminated.

2.                 How to exchange data with the server. Data exchange between the Pocket PC and server can be differently implemented depending on which Pocket PC tier communicates with which server tier. Figure 1 illustrates three tiers in both the Pocket PC as well as in the server.

Figure 1. Application tiers on device versus server

The data exchange options native to the .NET Compact Framework are:

·                   Pocket PC Database to Server Database: The database on the Pocket PC exchanges data directly with the server database. This option is valid in scenarios with little or no business logic involved and in scenarios with a high volume of data, and is implemented using the Remote Data Access and Merge Replication features of SQL Server CE. If data synchronization is required and not only data transfer, then Merge Replication provides with built-in conflict resolution implemented in the server-side SQL Server Reconciler.

·                   Pocket PC Component to Server Component: The Pocket PC application communicates with components on the server. This option enables business logic, implemented as Web services, to be part of the data exchange. This option can be used both if data is persisted in local XML files and in a SQL Server CE database.

·                   Pocket PC Component to Server Database: The Pocket PC connects directly to the server database. This option is often used when the Pocket PC application needs to manage a large amount of data in the remote database without necessarily having to pull the data to the Pocket PC and when business logic is not a primary concern. This option is implemented using the System.Data.SqlClient namespace and can be used both if data is persisted in local XML files and in a SQL Server CE database.

·                   Server-only: The Pocket PC application user interface elements can connect directly to server-side components or can be implemented as a web application utilizing the Pocket PC Web browser. This obviously requires the Pocket PC to be always connected to the server as well as sufficient bandwidth. An example when this is a viable option is in geographically limited scenarios where WiFi networks can be implemented such as in warehouses, healthcare institutions, offices and so on. This option does not require any data to be stored locally on the Pocket PC.

Some basic conditions have to be known or established before determining how to address the two aspects "How to store data" and "How to exchange data": Data Characteristics, Connectivity and System Architecture.

Data Characteristics

The Data Characteristics of a solution discloses the following key information:

1.                 Amount of static and transactional data to be stored on the Pocket PC: If the amount of data to be stored on the Pocket PC is low, in this case less than 50 – 100 kb, then the data can be stored in local XML files. If the amount of data is greater, then SQL Server CE will provide better performance and robustness. The reason for this is that data is then accessed with the SQL Server CE Query Engine with support for Structured Query Language (SQL) that yields better performance and manageability.

2.                 Amount of transactional data to be exchanged with the server: If the amount of transactional data to be exchanged between server and Pocket PC is low, for example, less than 500 kb – 1 Mb, then the data can be passed as XML using Web services. If the amount of data is greater, then Remote Data Access and Merge Replication will lead to better performance. The reasons for this are that the SQL Server CE Client and Server Agents implement efficient data compression and that the data has to pass a significantly lesser number of processes before it reaches final destination. For example, when transferring data from the Pocket PC to the server using Remote Data Access, the data does not have to pass the OLEDB CE, CLR/NET CF, SQL Server CE Data Provider, and ADO.NET layers on the Pocket PC side as well as on the server side equivalences once transferred to the server. Instead, the data can be passed directly from the Client Agent to the Server Agent and through Internet Information Server directly into the remote SQL Server.

It is important to note that while it makes sense to use Web services for data exchange if data is stored as local XML files, it is also possible to use Web services if data is stored in SQL Server CE—rather than Remote Data Access and Merge Replication. The reasons for this include the use of business logic and existing system architecture policies that might be in place.

Connectivity

The Connectivity aspects of a solution relate to the bandwidth available when the Pocket PC is online and how often (frequency) the Pocket PC goes online. These factors can sometimes be given as prerequisites and sometimes be defined and controlled.

Due to the nature of XML, which often holds large amounts of repetitive meta-data without compression, Web services require more bandwidth for the same data than SQL Server CE Remote Data Access and Merge Replication. However, the reality shows that it is the feature set of Remote Data Access and Merge Replication that is called for when large amounts of data needs to be exchanged. Also, these two methods are often used when the frequency of data exchange is low, which increases the likelihood that the amount of data that needs to be exchanged is high. This means that, in reality, SQL Server CE Remote Data Access and Merge Replication is often used in high bandwidth scenarios and Web services in low bandwidth scenarios. Therefore, when using Web services, a more "chatty" (many small requests) design is preferred before a "chunky" (few large requests) design.

System Architecture

System Architecture aspects deal with the overall issues of system integration. A mobile solution can be regarded as a standalone solution connecting to existing systems or extensions, core parts of existing systems. Regardless, Pocket PC solutions are most often not standalone since they need to communicate with other systems. Therefore, system architecture has to be defined if one does not exist or adhered to if one does exist.

Today, most systems are implemented as technical interface driven multi-tier solutions. Server-side applications are componentized in order to enable code reuse, separation of business logic and data, and to increase manageability. The use of XML and Web services continue to drive this development and the .NET Compact Framework applications fit right into this architecture because of the native support for XML and Web services consumption.

The other two common system integration implementations are Asynchronous Messaging utilizing queues and Database driven integration. The ..NET Compact Framework applications can adhere to these implementations by using Web Service interfaces to server side MSMQ queues or through the use of Web services between Pocket PC and server and use of queues between server and back office system. Database driven integration is inherently a part of SQL Server CE as already discussed.

Code Walkthrough

Let's take a look at some sample code that shows how to implement the above mentioned options. The source code is from a sample application called Feedback Anyplace which was used in a webcast called Architect Webcast: Designing Integrated Pocket PC Applications with .NET Compact Framework and illustrates how a quality inspector can use a Pocket PC to capture and report defects in a manufacturing scenario. The sample requires the RichInk control from IntelliProg to work. You can download the source code, which includes a the ..NET Compact Framework application, Web services and database components. The sample project is an example of data transfer, not data synchronization.

Storing Data on the Pocket PC

The following code illustrates how to persist a property-bag like class into a DataSet and local XML file.

Public Sub Save()

    ' Save new or update existing item

    ' Dim ds as DataSet

    Dim dr As DataRow

    Dim expr As String = "ID = '" + m_ID + "'"

 

    If m_NewItem Then

        ' Add new row to table

        dr = frmMain.ds.Tables("Feedback").NewRow()

    Else

        ' Find existing row

        ' Use the Select method to find row matching the filter

        dr = frmMain.ds.Tables("Feedback").Select(expr)(0)

    End If

 

    ' Set datarow properties

    dr("ID") = m_ID

dr("PlantSection") = m_PlantSection

    dr("Part") = m_Part

    dr("DefectScope") = m_DefectScope

    dr("ScopeID") = m_ScopeID

    dr("DefectType") = m_DefectType

    dr("RichInk") = m_RichInk

 

    If m_NewItem Then

        ' Add row to dataset, if new item

        frmMain.ds.Tables("Feedback").Rows.Add(dr)

    End If

 

    ' Save data

    frmMain.ds.AcceptChanges()

    frmMain.ds.WriteXml(frmMain.dataSource)

 

End Sub

Exchanging Data with the Server

The figure below illustrates exchanging data using Web services. In corporate solutions, it is common to use an integration platform such as Biztalk Server.

Figure 2. XML Web Service data exchange

The Pocket PC client calls the server-side Web service and passes a DataSet (ds) with data:

Private Sub Synchronize()

    Dim username As String = "JohnS"

    Dim blnSuccess As Boolean

    ' Synchronize using XML Web Service

    Cursor.Current = Cursors.WaitCursor

    Dim wsFeedback As New wsFeedback.feedback

    blnSuccess = wsFeedback.InsertFeedback(ds, username)

 

    Cursor.Current = Cursors.Default

End Sub

The server-side Web service accepts the DataSet and passes the XML directly to a SQL Server 2000 stored procedure which uses SQLXML and OPENXML to parse the XML and insert the new data into the appropriate table.

<WebMethod()> _

Public Function InsertFeedback(ByVal ds As DataSet, ByVal username As

  String) As Boolean

    Dim con As New SqlConnection(connectionstring)

    Dim cmd As New SqlCommand("p_Feedback_i", con)

    cmd.CommandType = CommandType.StoredProcedure

 

    ' Set parameters

    Dim prmXML As SqlParameter = cmd.Parameters.Add("@XML",

      SqlDbType.NText)

    prmXML.Direction = ParameterDirection.Input

    prmXML.Value = ds.GetXml

 

    Dim prmUsername As SqlParameter = cmd.Parameters.Add("@Username",

      SqlDbType.NVarChar)

    prmUsername.Direction = ParameterDirection.Input

    prmUsername.Value = username

 

    Try

    con.Open()

        cmd.ExecuteNonQuery()

    Catch ex As Exception

        ' Handle, log and re-throw error

        Throw ex

 

    Finally

        con.Close()

    End Try

 

    Return True

 

End Function

 

The stored procedure inserts the new data:

 

CREATE PROCEDURE p_Feedback_i

   @XML ntext,

   @Username nvarchar(50)

 

AS

 

SET NOCOUNT ON

 

DECLARE @iDoc   integer

DECLARE @Error   integer

 

/* Create XML document. */

EXEC sp_xml_preparedocument @iDoc OUTPUT, @XML

 

/* Insert new records */

INSERT INTO    Feedback

      (

      FeedbackID,

      PlantSection,

      Part,

      DefectScope,

      ScopeID,

      DefectType,

      RichInk,

      Username

      )

SELECT     ID,

      PlantSection,

      Part,

      DefectScope,

      ScopeID,

      DefectType,

      RichInk,

      @Username

FROM OPENXML (@iDoc, '/DataSet/Feedback',2) WITH

      (

      ID   uniqueidentifier,

      PlantSection   int,

      Part      int,

      DefectScope   int,

      ScopeID   nvarchar(50),

      DefectType   int,

      RichInk      nvarchar(50)

      )

 

SELECT @Error = @@ERROR

IF (@Error <> 0)

BEGIN

   GOTO Errorhandler

END

 

/* Remove the XML document*/

EXEC sp_xml_removedocument @iDoc

 

RETURN

 

Errorhandler:

 

IF NOT @iDoc IS NULL

   EXEC sp_xml_removedocument @iDoc

 

RAISERROR (@Error,16,1)

 

RETURN

The following figure illustrates SQL Server CE Remote Data Access and Merge Replication data exchange.

Figure 3. Database driven data exchange

Read more in the article SQL Server 2000 Windows CE Edition and the .NET Compact Framework, and for more info on Merge Replication, see Programming Merge Replication with the Microsoft .NET Compact Framework.

Conclusion

Understanding Data Characteristics, Connectivity and System Architecture is necessary to determine an appropriate Data Access Strategy. It is important to note that a Pocket PC solution can be put through a number of very different scenarios and situations. It is therefore quite possible that a Data Access Strategy can define the use of both local XML files, SQL Server CE databases, Web services and SQL Server CE Remote Data Access and Merge Replication depending on current situation and settings.

The .NET Compact Framework enables system designers and developers to choose appropriate methods of data access depending on a number of different scenarios. With a defined data access strategy and the new toolbox it is easier to stay focused on the actual solution benefits and design.