Enterprise Data Access with the Microsoft .NET CF/Mobile database

Microsoft's .NET Compact Framework provides Pocket PC developers with the tools needed to create robust applications, and offers several options for accessing and storing data from your enterprise database. This article will examine the most popular of these options, and is intended to be an overview of the subject for anyone contemplating using Pocket PCs in an enterprise setting.

The .NET framework is a platform which speeds development by giving developers access to a library of tested Classes and Functions, which they can use and re-use in their applications. Besides providing this "pre-built code," it also manages the code the developer writes by handling low-level functions such as memory allocation and disposal. The .NET Compact Framework (.NET CF) is a subset of the .NET framework, and is designed to run on Windows CE devices like the Pocket PC.

Before examining how to access your enterprise database, let's first discuss how data is stored on the device once it's been received. An understanding of storage options may help in choosing an access method, and give an appreciation of how .NET offers an end-to-end solution.

Intermittent Access

The reality of building applications for wireless or mobile devices is that access to your network or to the Internet is often intermittent. You may have some control over your environment and wireless networks in say, a factory or restaurant setting, but more often you are accessing data over the Internet, and then not continuously.

Retrieving and updating data may only occur at the start and end of a shift, in which case wireless functionality is not required. Regardless of your needs, a robust application should be able to store the data locally, use and manipulate it, and send changes (updates, inserts, deletions) back to the data source when a connection is available.

This introduces issues related to moving large amounts of data, and update conflicts, i.e. when several clients (Pocket PC applications) retrieve and modify the same data. In this conflict situation, it becomes a race back to the database, where the slowest user actually wins, and overwrites the faster user! Depending on your business this may not be an issue, but it's something to consider when analyzing your business requirements.

Central to the .NET framework is the idea of being disconnected from the central database and still being data-driven. Its central data object is the Dataset, which is like an in-memory representation of a simple database, complete with tables (called Datatables, containing rows and columns), relations (to create parent-child relationships between Datatables) and constraints (unique keys and foreign keys). The Datatable even has associated events, allowing you to control when the data in columns and rows changes in order to perform validation, enforce business rules, etc.

Mobile device constraints

Pocket PC development is much more constrained by limited memory and processing power than development for desktop computers. Constantly accessing data from a database adds the overhead of connecting, querying and retrieving the data each time. The Dataset allows you to store data in memory, with a powerful system for manipulating it. Changes to rows and columns in a Datatable are tracked, and you can use a DataAdapter object to retrieve the Dataset and, later, to automatically send the changes back to the database from which the data came. Once data is in the Dataset, you can easily bind it to .NET CF controls on the screen like the DataGrid. The point is that Microsoft has built its data model around the Dataset and the Datatable, and this is particularly useful to Pocket PC developers because of limited device resources.

Of course the Dataset is still in-memory data storage, and it should be saved elsewhere, i.e. the database from which it came. Otherwise, if the device does a soft reset, all data is lost. The Dataset is closely linked to standard XML, so you can easily load XML documents into a Dataset, and also save the contents of the Dataset to an XML file. So if you're not storing much data on the device, this may be an option.

SQL Server CE

To store larger amounts of data, as well as to have the features of a powerful Database Management System (DBMS), Microsoft offers SQL Server CE. It is a scaled down version of Microsoft's Enterprise DBMS SQL Server 2000. It supports a subset of the same T-SQL language, so there's little learning curve beyond understanding its limitations. You can use T-SQL to create databases, alter tables and enforce referential integrity, as well as select, join, insert, update, and delete data. It supports auto-incrementing (identity) columns. This alone makes it much more powerful than Pocket Access. It also has multiple column indexes, transactions, password protection of databases, and even 128-bit encryption of your data! This is impressive for a DBMS that only takes up 1.5 MB of space on the device.

What are its limitations? It doesn't support more than 1 connection at a time (not really a problem) and it doesn't support stored procedures, although you can use parameterized queries in your command statements, allowing you to insert values. Microsoft says it can support databases of up to 1 GB in size, but I can't imagine testing that limit on a Pocket PC!

Moving data from enterprise to device

Now that storage has been examined, what are the techniques that .NET provides for getting data from your enterprise into the hands of your mobile workforce? We'll start with the simplest and proceed to the more powerful and complex.



Fig. 1: Four methods of enterprise data access with the Microsoft .NET Compact Framework

Direct Querying

The simplest way to access enterprise data from the device is by directly querying it, providing the data is stored in a Microsoft SQL Server database and you can connect to it through a LAN. .NET CF provides the SqlClient class, which allows you to access data in the same manner as in a Web or desktop application on the network. Updates are immediate-you are directly accessing the source. It's fast, but doesn't give any support for update conflicts, lacks support for other types of databases (Oracle, etc), doesn't provide any protection if the connection fails, and requires your enterprise database to be directly accessible-which it would not be over the Internet. But if your users are working in a controlled atmosphere, i.e. in a warehouse, or are wirelessly connected through a VPN, this may be an option.

XML Web Services

The second option is XML Web Services, and this is in some ways the most flexible approach. You can use different server platforms (i.e. Linux) and different backend databases (mySQL, Oracle, etc), as these details are transparent to the mobile client. Data moves back and forth over the Internet (or Intranet) as XML (text) and can use the SOAP (Simple Object Access Protocol) standard. .NET provides a rich Web Service architecture, and by using Microsoft Visual Studio 2003, a developer can easily generate a proxy class that allows them to access functions on the server as if they were local to the device.

When you use the Microsoft ASP.NET Web Service platform on the server, it becomes possible to pull entire Datasets of information. Web Services also allows the pulling of custom objects, providing they can be transformed into XML (serialized). In this way, you could create a Customer object with a table of related orders as a Dataset on the server, and pull it down to the device, allowing you to further synchronize business logic between your applications.

Web Services provide a layer between your device and the enterprise database (using a Web server) where security and business logic can be controlled. But for moving large amounts of data, Web Services are somewhat slow and they don't allow any built-in means of synchronizing data with the enterprise. Here's where it gets really interesting! While Web Services are powerful, Microsoft provides faster and more powerful tools for getting data to and from the Enterprise.

SQL Server CE to the rescue

SQL Server CE offers two powerful tools for enterprise data access: Remote Data Access (RDA) and Merge Replication. Both give mobile devices access to an Enterprise SQL Server Database over HTTP via an IIS Web Directory (see Fig. 1). SQL Server CE Server Tools must be installed on the Web Server for this to take place. Both share the benefit of data being compressed for faster download (as opposed to the plain text of Web Services), and both can recover from communication failures by restarting the transmission from the last transmitted block within a timeout period.

Remote Data Access

Remote Data Access (RDA) allows you to pull data from a SQL Server into SQL Server CE by submitting a SQL statement to the enterprise database. Changes to the locally stored data can be optionally tracked, allowing you to push the changes back to the server. The push can be designated as batched, whereby no updates are performed if one row fails to be accepted (because of data problems, not because of a conflict with data updates from other users). You can also turn batching off and have individual failed upload rows sent to another table for your application to deal with later.

RDA also allows you to submit SQL statements (such as insert, update and delete) directly to a SQL Server. When you pull data to the device, you must first drop the local table and replace it with the new data (an all-or-nothing approach). With some custom coding and knowledge of your data model though, you could create a solution which only pulls down new or changed data (i.e. constructing a Select statement to filter the data) and merges it into the main table, but RDA wasn't inherently designed to do this. When you need to bring large amounts of data from the enterprise database, and expect it to change frequently, Merge Replication may be a better solution.

Merge Replication

Merge Replication allows multiple SQL Servers, which could be in different physical locations, to work autonomously with their own copies of data and later merge any changes back into one uniform result. It does this by having a central database provide a Publication (a collection of tables which can be filtered to expose only the relevant data), to which other databases become "subscribers." Applying an initial snapshot of the schema and data allows incremental changes to be tracked, resulting in only changed information needing to be subsequently moved across the network during synchronization of the databases. When multiple subscription databases attempt to update the same rows of data, Merge Replication also allows you to apply conflict resolution policies to decide which conflicting data changes will update the main publication. To do this, you can apply default resolvers, or write your own custom ones as stored procedures or custom DLLs.

Merge Replication is extended to mobile clients by allowing SQL Server CE to act as an anonymous subscriber to an enterprise Publication, and the client determines when the synchronization takes place (since only the client knows if it is connected). You can create Publications specific to certain groups or individuals, thus giving them access to only the data they need. You can also pass a value to the publication to further filter only the data needed. After applying the initial snapshot of data to the device, only changes to the master data are brought down on subsequent synchronizations. This is advantageous when working with large data sets that need to be brought down in their entirety if updated using RDA. Of course, you should always try to limit the amount of data downloaded to the device to only that which is necessary for the user. For example, you could set up multiple Publications showing customer data for differing regions, allowing salespeople to download only the data relevant to them. This takes strain off processing and searching on the device, as well as the obvious time/bandwidth issues in moving large amounts of data.

RDA is a powerful and light-weight solution for most situations. But it requires that the working data be downloaded in its entirety during the pull operation, and it doesn't inherently provide for conflict resolution when multiple clients update the same records at the same time. Often though, clients require read-only data, or update data that is relevant only to them and that does not conflict with other users. Merge Replication introduces overhead on the server, and to a lesser extent on the Pocket PC. It can sometimes complicate what could be a simple RDA solution, but it's an elegant solution when the complexity of the problem requires it.

Choose the best approach for your needs

.NET offers several solutions to help you move data between the enterprise and mobile users. Each one has its strengths, and often your solution will employ more than one method. You may need to download large amounts of customer data to the device, thus requiring the speed of RDA. But if you don't need to modify all that data but only, for instance, create new orders for customers, a simple upload using Web Services or even a direct link to a SQL sever on a LAN would work.

You may not even be concerned about wireless access: your delivery drivers might receive orders at the start of the shift using ActiveSync at the office, then carry the data and upload changes to the database at the end of the day. Or you may need your application to be constantly checking for a connection (in the background) and downloading/updating enterprise data. Whatever the challenge, the .NET Compact Framework offers a powerful and flexible platform for solving your mobile business needs.

Installing Microsoft Loopback Adapter

Installing Microsoft Loopback Adapter
=====================================

Go to the Add Hardware control panel

Click Next on the first wizard screen

When asked if the hardware is connected, select Yes and click Next

In the list of installed hardware, scroll to the bottom and select Add a new hardware device and click Next

Choose the option titled Install the hardware that I manually select from a list (Advanced) and click Next

In the list of common hardware types, select Network adapters and click Next

In the list of manufacturers, select Microsoft. Then in the list of network adapters, select Microsoft Loopback Adapter and click Next

Click Next one more time to install the Microsoft Loopback Adapter and then press Finish

Now that you have installed the Microsoft Loopback Adapter, you need to configure an IP address for it.

Configuring Microsoft Loopback Adapter
--------------------------------------
Go to the Network Connections control panel
Locate the LAN or High-Speed Internet connection that has a Device name of Microsoft Loopback Adapter, right-click on it and choose Properties
Select the Internet Protocol (TCP/IP) item and click Properties
Select the option to Use the following IP address and enter 192.168.2.1 for the IP address. This will cause a subnet mask of 255.255.255.0 to appear
Click OK to accept the changes and Close to exit the Properties dialog
Now you can disable your current LAN connection and enable your Microsoft Loopback Adapter connection by right-clicking on each connection in the Network Connections control panel and choosing Enable or Disable.

C# Generics

we presented data structures that stored and manipulated object references. You could store any object in our data structures. One inconvenient aspect of storing object references occurs when retrieving them from a collection. An application normally needs to process specific types of objects. As a result, the object references obtained from a collection typically need to be downcast to an appropriate type to allow the application to process the objects correctly. In addition, data of value types (e.g., int and double) must be boxed to be manipulated with object references, which increases the overhead of processing such data. Also, processing all data as type object limits the C# compiler's ability to perform type checking.
Though we can easily create data structures that manipulate any type of data as objects (as we did in Chapter 25), it would be nice if we could detect type mismatches at compile time-this is known as compile-time type safety. For example, if a Stack should store only int values, attempting to push a string onto that Stack should cause a compile-time error. Similarly, a Sort method should be able to compare elements that are all guaranteed to have the same type. If we create type-specific versions of class Stack class and method Sort, the C# compiler would certainly be able to ensure compile-time type safety. However, this would require that we create many copies of the same basic code.
This chapter discusses one of C#'s newest features-generics-which provides the means to create the general models mentioned above. Generic methods enable you to specify, with a single method declaration, a set of related methods. Generic classes enable you to specify, with a single class declaration, a set of related classes. Similarly, generic interfaces enable you to specify, with a single interface declaration, a set of related interfaces. Generics provide compile-time type safety. [Note: You can also implement generic structs and delegates. For more information, see the C# language specification.]
We can write a generic method for sorting an array of objects, then invoke the generic method separately with an int array, a double array, a string array and so on, to sort each different type of array. The compiler performs type checking to ensure that the array passed to the sorting method contains only elements of the same type. We can write a single generic Stack class that manipulates a stack of objects, then instantiate Stack objects for a stack of ints, a stack of doubles, a stack of strings and so on. The compiler performs type checking to ensure that the Stack stores only elements of the same type.
This chapter presents examples of generic methods and generic classes. It also considers the relationships between generics and other C# features, such as overloading and inheritance. Chapter 27, Collections, discusses the .NET Framework's generic and non-generic collections classes. A collection is a data structure that maintains a group of related objects or values. The .NET Framework collection classes use generics to allow you to specify the exact types of object that a particular collection will store.


Overloaded methods are often used to perform similar operations on different types of data. To motivate generic methods, let's begin with an example (Fig. 26.1) that contains three overloaded PrintArray methods (lines 23-29, lines 32-38 and lines 41-47). These methods display the elements of an int array, a double array and a char array, respectively. Soon, we will reimplement this program more concisely and elegantly using a single generic method.
Fig. 26.1 Displaying arrays of different types using overloaded methods. 1 // Fig. 25.1: OverloadedMethods.cs
2 // Using overloaded methods to print arrays of different types.
3 using System;
4
5 class OverloadedMethods
6 {
7 static void Main( string[] args )
8 {
9 // create arrays of int, double and char
10 int[] intArray = { 1, 2, 3, 4, 5, 6 };
11 double[] doubleArray = { 1.1, 2.2, 3.3, 4.4, 5.5, 6.6, 7.7 };
12 char[] charArray = { 'H', 'E', 'L', 'L', 'O' };
13
14 Console.WriteLine( "Array intArray contains:" );
15 PrintArray( intArray ); // pass an int array argument
16 Console.WriteLine( "Array doubleArray contains:" );
17 PrintArray( doubleArray ); // pass a double array argument
18 Console.WriteLine( "Array charArray contains:" );
19 PrintArray( charArray ); // pass a char array argument
20 } // end Main
21
22 // output int array
23 static void PrintArray( int[] inputArray )
24 {
25 foreach ( int element in inputArray )
26 Console.Write( element + " " );
27
28 Console.WriteLine( "\n" );
29 } // end method PrintArray
30
31 // output double array
32 static void PrintArray( double[] inputArray )
33 {
34 foreach ( double element in inputArray )
35 Console.Write( element + " " );
36
37 Console.WriteLine( "\n" );
38 } // end method PrintArray
39
40 // output char array
41 static void PrintArray( char[] inputArray )
42 {
43 foreach ( char element in inputArray )
44 Console.Write( element + " " );
45
46 Console.WriteLine( "\n" );
47 } // end method PrintArray
48 } // end class OverloadedMethods
Array intArray contains:
1 2 3 4 5 6

Array doubleArray contains:
1.1 2.2 3.3 4.4 5.5 6.6 7.7

Array charArray contains:H E L L O


The program begins by declaring and initializing three arrays-six-element int array intArray (line 10), seven-element double array doubleArray (line 11) and five-element char array charArray (line 12). Then, lines 14-19 output the arrays.
When the compiler encounters a method call, it attempts to locate a method declaration that has the same method name and parameters that match the argument types in the method call. In this example, each PrintArray call exactly matches one of the PrintArray method declarations. For example, line 15 calls PrintArray with intArray as its argument. At compile time, the compiler determines argument intArray's type (i.e., int[]), attempts to locate a method named PrintArray that specifies a single int[] parameter (which it finds at lines 23-29) and sets up a call to that method. Similarly, when the compiler encounters the PrintArray call at line 17, it determines argument doubleArray's type (i.e., double[]), then attempts to locate a method named PrintArray that specifies a single double[] parameter (which it finds at lines 32-38) and sets up a call to that method. Finally, when the compiler encounters the PrintArray call at line 19, it determines argument charArray's type (i.e., char[]), then attempts to locate a method named PrintArray that specifies a single char[] parameter (which it finds at lines 41-47) and sets up a call to that method.
Study each PrintArray method. Note that the array element type (int, double or char) appears in two locations in each method-the method header (lines 23, 32 and 41) and the foreach statement header (lines 25, 34 and 43). If we replace the element types in each method with a generic name-we chose E to represent the "element" type-then all three methods would look like the one in Fig. 26.2. It appears that if we can replace the array element type in each of the three methods with a single "generic type parameter," then we should be able to declare one PrintArray method that can display the elements of any array. The method in Fig. 26.2 will not compile, because its syntax is not correct. We declare a generic PrintArray method with the proper syntax in Fig. 26.3.
Fig. 26.2 PrintArray method in which actual typenames are replaced by convention with the generic name E 1 static void PrintArray( E[] inputArray )
2 {
3 foreach( E element in inputArray )
4 Console.Write( element + " " );
5
6 Console.WriteLine( "\n" );
7 } // end method PrintArray