Windows Mobile 5.0 Data Persistence (DRAFT)

Let me start off by saying that I tried to find a recipe for using Windows Mobile 5.0 with SQL Server 2005 Mobile Edition. I had a hard time finding what I considered to be a complete recipe anywhere. Hence my decision to post this recipe.

My goal when I started this quest was to make a simple mobile data application that could handle input/editing of data either on the Windows Mobile device or on the desktop. That said, this recipe will only deal with data persistence on the mobile device. If I cannot find a good recipe for the data synchronization, I will add one for that process as well.

To start off, you will need to make sure your machine is properly configured. You will need to locate and install the following:

1. Visual Studio .Net 2005 Professional
2. Microsoft SQL Server 2005 Mobile Developer Tools
3. Microsoft SQL Server 2005 Mobile Edition Device SDK
4. Windows Mobile 5.0 Pocket PC SDK

Once these tools are installed you can begin. This recipe is based on two main sources:

• Introduction to Data-Aware Windows Mobile Applications, Pt. 1 (Rory Blythe)
• Microsoft Virtual Labs - Developing a SQL Mobile Application with Visual Studio 2005 and SQL Server 2005

Unfortunately neither of these sources had everything I wanted. Rory's webcast was the closest and easiest to follow. I did run into two problems following his instructions; the record key did not persist from the insert/update screen back to the list screen and the records did not persist between executions on the Pocket PC.

So, here is my method of providing data persistence using SQL Server Mobile. We will do everything within the Visual Studio IDE.

Create the mobile database

1. Start by opening up VS 2005
2. Make sure the Server Explorer window is showing. If not, add it by going to ViewServer Explorer
3. Add a new connection by right clicking on the Data Connections icon and then selecting Add Connection
4. Next to the Data Source box, click on the Change button
5. Switch to Microsoft SQL Server Mobile Edition
6. In Connection Properties select the Create button to create a new database
7. Enter the name and location for your database file (remember this name and location)
8. Enter a password if you wish. If not, you will get a warning because you did not enter a password.
9. Click OK
10. Click OK
11. In Server Explorer you will notice that there is a new connection for your mobile database
12. Expand the connection entry
13. Right click on tables and select Create Table
14. Name the table Test
15. Create a column TestText leave the field defaults the same
16. Create a second column TestKey, UniqueIdentifier, Length 16, No, Yes, Yes
17. Click OK

Create a mobile application to use your database

1. Create a new VS 2005 project of type Windows Mobile 5.0 Pocket PC (if you do not have this option, you will need to install Windows Mobile 5.0 Pocket PC SDK)
2. Once the project is open, switch to the Data Sources window in VS 2005
3. Click on Add New Data Source
4. Choose Database and click Next
5. In the dropdown box, find your SQL Mobile connection and click Next
6. When prompted if you want to copy the database to the project select yes (note, this can cause problems later if the database already exists on the mobile device)
7. Select the tables and other database objects you want to copy
8. At the bottom, there is a box to change the name of the dataset that will be created, you can change it or leave it. I like to change it to match my naming conventions for datasets in my VS projects
9. Click Finish
10. Make sure you can see your Form Designer window and your Data Sources window in the VS IDE
11. Drag the data connection from the Data Sources window to the Form Designer window and drop it anywhere on the form
12. Click on the datagrid and change it's dock property to Fill
13. Click on the triangle in the upper right corner of the datagrid to access it's task list
14. Select Generate Data Forms
15. Open the form code for the ???EditViewDialog.vb (name will vary depending on the name of your datasource
16. In the _Closing event, insert code before the BindingSource.EndEdit line

At this point I have to insert some comments:

Rory brought up a good point in his webcast. He said we should not use an INT field set as an Identity column for the key for the table. Instead we should use a UniqueIdentifier field set to unique, and primary key for the key. The reason is that we can potentially have multiple people on mobile devices in the field all entering new records. If we use INT fields, there is an extremely high likely hood they will not be unique when uploaded to the server. On the other hand GUIDs are very very likely to be unique even if created and the same time on different devices.

So, because we are using a GUID as our key we have to insert some special code to ensure that the GUID is created before the new record is saved back to the database.

1. Again before the BindingSource.EndEdit line insert the following code (this will refer to my control names, but you should get the picture)
2. This code inserts a GUID if there isn't one, but does nothing if there is

Dim DRV As Data.DataRowView = Me.TestBindingSource.Current
Dim TestRow As dsTest.TestRow = DRV.Row

If Me.TestKeyTextBox.Text.Length < testkey =" Guid.NewGuid">

1. Finally in Form1.vb, add a _Closing event for the form and insert this code to push changes back to the database

Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
Me.TestTableAdapter.Update(Me.DsTest)
End Sub