Data Points

Developing Apps with the .NET Compact Framework, SQL Server CE, and Replication

John Papa

Code download available at:DataPoints0309.exe(150 KB)

Contents

Configuration
SQL Server CE Server Agent
Replication Publication
The Application
The Code
Exception Handling
Conclusion

Handheld and other portable devices are shipping with ever growing feature sets. These devices can now communicate on private networks and over the Internet through Wi-Fi and built-in wireless modems that are combined with cell phones. Handheld devices are popular not only for personal use, but also many businesses are taking advantage of the mobility they offer. The growing demand for better portability means an increasing demand for mobile applications. One example could be an application that lets employees update inventory, enter orders, update customer profiles, and perform other data-centric tasks from the field.

In this column, I'll develop a mobile application that can be used to review and update a business' inventory. The application, targeted at handheld Windows® CE-based or Microsoft® Windows Powered Pocket PC devices, can operate as a standalone application or as part of an enterprise-wide application. I will use the Microsoft .NET Compact Framework as it exists in Visual Studio® .NET 2003 to develop the application for Pocket PC devices. One of the key ingredients in this application is that the entire inventory of products can be synchronized and stored on the handheld device within an instance of SQL Server™ CE.

The SQL Server CE database on the Pocket PC device will contain a replicated version of the Northwind database from a back-end SQL Server database. Since the database exists in two places, I will use the merge replication feature of SQL Server in order to maintain the data integrity of both databases.

I'll begin by explaining how to set up the prerequisites for the merge replication scenario including many of the ways that it can be customized. After discussing how to configure the environment, I'll demonstrate how the application functions and takes advantage of both SQL Server CE and the disconnected nature of ADO.NET. As in any application, exception handling is critical. However, when developing for mobile devices, the need for solid exception handling is amplified tremendously. Finally, I'll wrap up by explaining how to trap different errors that can occur between the handheld and the back-end database and how to report them.

Configuration

Before going any further, it is important to understand the prerequisites for developing an application using the .NET Compact Framework and SQL Server CE on a handheld device. The .NET Compact Framework is now installed with Visual Studio .NET 2003. SQL Server CE device components are installed with Visual Studio .NET 2003. SQL Server CE server components can be downloaded from the Microsoft Web site along with any service packs, which I highly recommend that you install. Once the .NET Compact Framework, SQL Server CE, and the latest version of the SQL Server and SQL Server CE service packs are installed, you are well on your way.

While installation is easy, the configuration of merge replication takes some careful consideration. When designing the application, it is important to take a step back and examine how the various moving parts interact with each other. For example, one simple but often forgotten step is to make sure that the SQL Server and SQL Server Agent services are both running on the back-end server. In the sample inventory application, the Northwind database on a back-end SQL Server instance is the central data store containing all information including products, orders, and customers. The application allows handheld devices to store the inventory information from the Northwind database on the back-end database server. The two databases communicate through the merge replication process, which uses the SQL Server CE Server Agent to pass information back and forth.

SQL Server CE Server Agent

The SQL Server CE Server Agent is an ISAPI DLL that is set up in a virtual directory under Microsoft Internet Information Services (IIS). Essentially, it allows the two databases to communicate with each other using the HTTP protocol to relay messages. To set up the SQL Server CE Server Agent, it is easiest to use the SQL Server CE Virtual Directory Creation Wizard. A more advanced approach is do it manually. I created a folder on my server called "C:\Projects\SQLCE" and copied the agent DLL (sscesa20.dll) from the SQL Server CE setup folder under \Program Files\Microsoft SQL Server CE 2.0\Server and registered it there. I then created a virtual directory in IIS with HTTP execute rights called SQLCE and pointed it to the SQL Server CE Server Agent's new content folder location.

When planning for merge replication via the SQL Server CE Server Agent, establishing the appropriate permissions on both the NTFS folder and the virtual directory are vital. The NTFS folder permissions and the virtual directory should allow the handheld application to access the agent. You should use some authentication other than anonymous mode, but for the simplicity of my sample I allowed anonymous access to the virtual directory through the IUSR_machine name account. Because files will be written when merge replication synchronizes, I gave the IUSR_machine name account write access to the folder as well. Again, anonymous access is great for sample applications, but you'll need a more secure authentication method in real apps. For more information on connectivity and security see Configuring Security for Connectivity.

Replication Publication

Once the means has been established for the SQL Server and SQL Server CE instances to communicate with each other, you need something for them to talk about. Initially, a publication must be created on the SQL Server back end to define the parameters of the merge replication process. The publication defines the data that will be available, and how to resolve any potential replication conflicts. The publication access list defines who's allowed to subscribe to it. The type of authentication used to subscribe is defined by the SQL Server configuration. A publication can be created by starting the Create Publication Wizard, which can be accessed by right-clicking a database and selecting New | Publication from the pop-up menu. Most of the wizard is intuitive, so I will concentrate on the more critical settings.

There are three types of publications that can be created: snapshot, transactional, and merge (see Figure 1). Since the inventory app must allow either the back-end database or the handheld's database to update the data and synchronize with each other, a merge publication best fits the bill. In addition, merge replication is the only form of replication supported by SQL Server CE.

Figure 1** Three Types of Publications **

The next step of the wizard is to indicate what type of subscribers are allowed to subscribe to the publication. Be sure to select at least "Devices running SQL Server CE." Now you must indicate which articles will be published. An article can be a table, view, or even a stored procedure. For the sample application I chose to publish all of the information for simplicity; however, a subset of the tables would be adequate since the only information that I intend for the handheld device to use is related to inventory.

One side effect of setting up a publication to use merge replication is that all articles that are selected to be published, and which do not already have a unique or primary key constraint with the ROWGUIDCOL property set, will have an additional column added called rowguid. An index along with the rowguid column, defined as type uniqueidentifier, will be added when the first snapshot of the publication is taken. The uniqueidentifier value is automatically generated for new rows in the article. The uniqueidentifier value is used to link rows of the back-end database's article to those of the SQL Server CE article.

Publications can also filter the article vertically (limiting columns in the article) or horizontally (limiting rows in the article). This can be done set either through the Create Publication Wizard or through the properties of the publication. For this sample application, I did not create any filters and I also specified that all tables become published articles. Once the wizard completes, it then creates the publication.

When the publication is created, SQL Server also creates several other objects behind the scenes to support the replication process. For example, triggers are created on all published articles that will track changes made to the articles on the server. The changes are sent through views to the MSmerge_contents and MSmerge_tombstone tables, which are also created by the publication. Inserts and updates are tracked in the MSmerge_contents table while deletes are tracked in the MSmerge_tombstone table. These views, triggers, and tables are created to support the replication process that is defined by the publication. SQL Server CE uses a similar process.

Once I created my publication, which I named NorthwindPub, I assigned the SQL Server accounts that could access the publication through the Publication Access List. For my sample, I created a SQL Server login called SqlTestUser and granted it rights to the Northwind database. Then I went to the Publication Access List tab in the properties of my NorthwindPub publication. There, I added the SqlTestUser to the list of valid accounts that can access the publication (see Figure 2).

Figure 2** Northwind Publication Properties **

The final step is to create the first snapshot. Before a new subscriber can accept changes, it must contain all of the tables and data contained in the publication's defined articles. This means that the first time you create a snapshot and then replicate it to the subscriber, all database tables will be created and filled in the subscriber's database (in this case, the SQL Server CE database). On subsequent synchronizations, only modifications are sent between the publisher and subscriber databases.

Once the SQL Server CE Server Agent is set up, the publication has been created, all types of authentication have been defined, and the initial snapshot has been established, the replication process can begin. Now all I need is a subscriber. Enter the sample inventory application.

The Application

The Inventory application is a Smart Device application written in C#. Since I have a Pocket PC, I targeted the application towards the Pocket PC device, but this could easily have been adapted to the more generic Windows CE-based device option. The application's basic requirements include synchronizing the inventory data from the Northwind back-end database with the handheld device. When a product is selected, its price and inventory levels are displayed in textboxes so the user can edit them.

One of the great features of developing with the .NET Compact Framework is that you don't need a Windows CE-based device to develop against. Instead, you can develop and debug using the Pocket PC emulator. The figures shown in this column are of the application running in debug mode in the emulator.

Figure 3** Synchronization Outcome **

When the application is started, it looks to see if the Northwind database exists on the SQL Server CE instance. The first time the application is run the data has not yet been replicated over to SQL Server CE, so the input boxes are empty. When the user presses the Synch button, the application initiates the merge replication process, bringing over the initial snapshot of the NorthwindPub publication. Since the initial snapshot contains all of the Northwind data, it will take longer to synchronize than subsequent synchronizations. Once the publication has been published to the subscriber, a message box is presented showing the outcome of the synchronization (see Figure 3). The number of rows that were changed by the publisher from the initial snapshot are displayed, along with zero conflicts from the publisher and zero subscriber changes. The initial synchronization also creates the SQL Server CE database file on the Windows CE-based device to store the Northwind data.

Figure 4** Data Displayed **

Once the database has been created, the application displays the list of categories in a combobox control. Figure 4 shows how in the category Beverages, the product Chai is selected and the Chai product's inventory levels and unit price are displayed. At this point, the user can browse and/or edit the products in the different categories. As the user makes changes to the inventory levels, the data is saved to the local DataSet. Basically, the DataSet is loaded from the local SQL Server CE database after a synchronization. The DataSet is then bound to the product's controls and tracks all data changes made by the user.

Figure 5** Changing Inventory Value **

Once the user presses the Save button, all changes are sent to the local SQL Server CE database using ADO.NET. The changes stored in the DataSet are sent to the database through the Update method of a SqlCeDataAdapter. Then, if a user presses the Synch button, the changes made to the local SQL Server CE database (the subscriber) are sent to the back-end database (the publisher) and if any changes were made at the publisher, they are sent to the subscriber through merge replication. Figure 5 shows the outcome after a user changed a single inventory value and no data changes were made at the back-end database.

The Code

The code that makes this all click requires several namespaces to be included in the project. The basic ones required by a smart device application are System, System.Drawing, System.Collections, System.ComponentModel, and System.Windows.Forms.

The additional namespaces that I included are used to write the database file to the handheld device, to communicate with the SQL Server CE database, and to store the product data in a DataSet. Thus, the following namespaces are also required by this project:

using System.Data.SqlServerCe; using System.Data; using System.Data.Common; using System.Text; using System.Xml;

When the application first starts, the connection to the database is defined using a SqlCeConnection object. This object works just like the SqlConnection object, except that it is used to communicate with SQL Server CE databases. Its datasource and connection string are much simpler than the SqlConnection object's connection string since it only has to point to the database file. A SqlCeDataAdapter is also established, with its SelectCommand and UpdateCommand properties set with their corresponding SELECT and UPDATE SQL statements. The DeleteCommand and InsertCommand properties do not need to be set since this application will not allow the user to perform those actions on the inventory data.

Once the application is started, it tries to load the data from the local database. First, the list of categories is retrieved using a SqlCeCommand and a SqlCeDataReader. The class's constructor executes and sets up the ADO.NET objects that were used throughout the application. The SqlCeCommand defines the SQL statement to retrieve the categories. After the SqlCeConnection has been opened and its ExecuteReader method called, the SqlCeCommand returns a SqlCeDataReader that points to the category data. The SqlCeDataReader is then iterated through to add the category data to the cboCategory combobox (see Figure 6).

Figure 6 Loading the Categories

// Get the categories via a DataReader SqlCeCommand oCmd = new SqlCeCommand("SELECT CategoryID, CategoryName FROM Categories " + " ORDER BY CategoryName", this.m_oCn); this.m_oCn.Open(); SqlCeDataReader oDR = oCmd.ExecuteReader(); // Clear the category list and fill it cboCategory.Items.Clear(); while (oDR.Read()) { cboCategory.Items.Add(new Category((string)oDR["CategoryName"], (int)oDR["CategoryID"])); } oDR.Close(); this.m_oCn.Close(); // Select the first category cboCategory.SelectedIndex = 0;

Notice that the items are added to the combobox control in Figure 6 by adding an instance of a Category class to the combobox. The Category class has CategoryName and CategoryID public properties. The CategoryName is displayed in the combobox and the CategoryID is used to identify a unique category. It also has a ToString method which overrides the default ToString method of an object to return the CategoryName. This is how the cboCategory combobox control knows which value to display.

The products are also retrieved when the application loads, storing the entire list of products in a DataSet. The SqlCeDataAdapter has already been set up in the class's constructor to retrieve all of the products, so calling the Fill method of the SqlCeDataAdapter loads the DataSet, as shown here:

this.m_oDA.Fill(this.m_oDs, "Products");

Notice how in Figure 6, after the categories have been loaded into the cboCategory combobox, the first category in the list is selected. This causes the FilterProducts method to be invoked, which filters the products DataSet to only show the products that belong to the selected category. Figure 7 shows the main code in the FilterProducts method, which first grabs the selected category. This is done by casting the cboCategory combobox control's SelectedItem property to the Category class. Then, a DataView object is created that filters the products DataSet by the selected CategoryID value. The oDV DataView does not remove the data from the DataSet, but rather filters the DataSet so only the desired products are accessible via the DataView.

Figure 7 Filtering the Products

// Get the selected category and cast it back to the Category class Category oCat = (Category)cboCategory.SelectedItem; // Create a DataView that filters the Products DataTable by the // selected category DataView oDV = new DataView(this.m_oDs.Tables["Products"], "CategoryID = " + Convert.ToString(oCat.CategoryID), "ProductName", DataViewRowState.CurrentRows); // Clear all bindings cboProduct.DataBindings.Clear(); txtUnitPrice.DataBindings.Clear(); txtUnitsInStock.DataBindings.Clear(); txtUnitsOnOrder.DataBindings.Clear(); txtReorderLevel.DataBindings.Clear(); // Bind the DataView to the controls cboProduct.DataSource = oDV; cboProduct.DisplayMember = "ProductName"; cboProduct.ValueMember = "ProductID"; txtUnitPrice.DataBindings.Add("Text", oDV, "UnitPrice"); txtUnitsInStock.DataBindings.Add("Text", oDV, "UnitsInStock"); txtUnitsOnOrder.DataBindings.Add("Text", oDV, "UnitsOnOrder"); txtReorderLevel.DataBindings.Add("Text", oDV, "ReorderLevel"); // Select the first Product cboProduct.SelectedIndex = 0;

Next, all controls' bindings are removed and reset to the new DataView object. This process allows the application to load all products into a DataSet at once and only show a filtered subset of the data to the user. Keep in mind that any pending changes made by the user to the data is still tracked, whether or not the data is visible in the current DataView. For example, let's assume that a user changes the reorder level for the Chai product under the Beverages category and then changes the category to Seafood. Even though the Chai product is no longer in the current DataView, it is still in the DataSet. The pending change still exists and will continue to exist until the user presses either Cancel, which calls the DataSet's RejectChanges method, or presses the Save button.

When the user presses the Save button, the first step is to make sure that the last edit that the user made was captured. This is ensured by grabbing the row number of the row that is currently visible in the bound controls. Then the row number is used to reference the current row on the DataSet's Products table so EndEdit can be invoked. This method tells the DataRow that editing of the row is complete, so if any changes were made, they are captured:

//— Make sure the last edit was captured. int nPosition = BindingContext[this.m_oDs.Tables["Products"]].Position; this.m_oDs.Tables["Products"].Rows[nPosition].EndEdit();

In the Inventory application, saving occurs at three different levels: locally to the DataSet, from the DataSet to the local SQL Server CE database, or between the local SQL Server CE database and the back-end SQL Server database through merge replication.

When the user presses the Synch button, the merge replication process is kicked off. Figure 8 shows the code that instantiates the SqlCeReplication object (part of the System.Data.SqlServerCe namespace) and sets all of its properties required for merge replication to take place between the back-end and local databases. The Publisher property is set to the name of the server where the publication exists. The PublisherLogin and PublisherPassword properties are set to the SQL Server accounts that I created and gave permissions to the publication. The InternetUrl, InternetLogin, and InternetPassword properties are used to tell the subscriber where to send its request for merge replication. For merge replication to occur, the subscriber has to talk through the SQL Server CE Server Agent, which in this application is located at https://lancelot/sqlce/sscesa20.dll.

Figure 8 Kicking Off Merge Replication

//Set up the Replication properties oRpl = new SqlCeReplication(); oRpl.Publisher = "lancelot"; oRpl.PublisherLogin = "SqlTestUser"; oRpl.PublisherPassword = "hmmm"; oRpl.InternetUrl = "https://lancelot/sqlce/sscesa20.dll"; oRpl.InternetLogin = "iusr_lancelot"; oRpl.InternetPassword = ""; oRpl.Subscriber = "CESubscriberTest"; oRpl.Publication = "NorthwindPub"; oRpl.PublisherDatabase = "Northwind"; oRpl.SubscriberConnectionString = "Provider=Microsoft.SQLSERVER.OLEDB.CE.2.0;Data Source=" + this.m_sDataSource;

The Subscriber property of the SqlCeReplication object is set to the name of the subscriber, which is used to identify which subscriber is requesting the merge replication process to be kicked off. There could be multiple subscribers, all running various Pocket PC devices. Therefore, the subscriber name can assist the publisher in tracking the subscribers' changes. The Publication and PublicationDatabase properties must be set to the name of the publication and database, respectively, that the device wants to replicate with. Finally, the SubscriberConnectionString property is set to point to the SQL Server CE database instance so the replication process can read changes from the SQL Server CE database and send any changes to the database. Once these properties have been set, the SqlCeReplication object's Synchronize method can be invoked and the data can be reloaded into the application:

// Perform the synchronization and load the data oRpl.Synchronize(); LoadData(false);

Exception Handling

The work involved in developing a Smart Device Application project in Visual Studio .NET can be divided into a two main categories: configuring merge replication and writing the application. Although configuring merge replication requires careful planning, the process is straightforward. Developing the application for the handheld device is very similar to developing a Windows Forms app. However, debugging errors in the application may make you pull your hair out. The debugging tools are very useful, but just like any application, they are only as useful as the information that they yield from exceptions. A solid exception handling strategy is just as critical in developing applications for handheld devices as it is for any other project type.

When you consider all of the replication configuration settings, security requirements, authentication, installation prerequisites, and connectivity issues, there are many ways that things can go amiss. It is important to be able to determine what causes an error, and the SqlCeException object generally contains useful information on where to focus your attention.

Figure 9 shows the ShowSqlCeErrors method that iterates through the SqlCeError objects, which represent the different errors that occurred. The ShowSqlCeErrors method loops through the SqlCeError objects and several of its properties to retrieve the details of the errors. It then formats the error information into a string and displays each error within the exception to the user via the MessageBox object. This method is quite simple to implement and I recommend that it be used as it can save a ton of wasted time in chasing down the root cause of exceptions.

Figure 9 Displaying Exceptions Gracefully

private void ShowSqlCeErrors(SqlCeException exSql) { StringBuilder oSBMsg = new StringBuilder(); int i = 0; foreach (SqlCeError err in exSql.Errors) { i++; oSBMsg.Append("\n Error # " + i + " of " + exSql.Errors.Count); oSBMsg.Append("\n Error Code: " + err.HResult); oSBMsg.Append("\n Message : " + err.Message); oSBMsg.Append("\n Minor Err.: " + err.NativeError); oSBMsg.Append("\n Source : " + err.Source); for (int j = 0; j < err.NumericErrorParameters.Length; j++) { if (err.NumericErrorParameters[j] != 0) { oSBMsg.Append("\n Numeric Parameter: " + err.NumericErrorParameters[j]); } } for (int k = 0; k < err.ErrorParameters.Length; k++) { if (err.ErrorParameters[k] != string.Empty) { oSBMsg.Append("\n Error Parameter : " + err.ErrorParameters[k]); } } MessageBox.Show(oSBMsg.ToString()); oSBMsg.Remove(0, oSBMsg.Length); } }

Conclusion

In this sample I've demonstrated how to implement an application for a Windows CE-based device that allows users to take advantage of disconnected data. The data can be stored, viewed, and manipulated on the handheld device and then resynchronized with the back-end database through the local network or even the Internet. One of the fundamental players in this application is ADO.NET. Its disconnected nature and XML foundation allow the data to be very portable yet, at the same time, highly functional. Using these techniques and tools, developing an enterprise business solution that includes mobile devices is within the reach of developers.

Send your questions and comments for John to  mmdata@microsoft.com.

John Papa is a baseball fanatic who spends most of his summer nights rooting for the Yankees with his two little girls, wife, and faithful dog, Kadi. He has authored several books on ADO, XML, and SQL Server and can often be found speaking at industry conferences such as VSLive. You can reach him at mmdata@microsoft.com.