Pocket Access and the .NET Compact Framework

Larry Roof
larryroof.com

October 8, 2003

Summary: Larry Roof delves into data storage for Pocket Access databases in mobile applications and explains the benefits of this type of storage, as well as how you can implement it in your own applications. (11 printed pages)

Download the Pocket Access Demo.exe sample file.

For those that have been following the Two for the Road column, you know that it has been awhile since I have been on the road. Without going into the details, I have been laid up with a back injury that has significantly limited my two main passions—surfing and mobile development. Now the injury limiting my surfing is fairly self-explanatory, but the injury also impacted how long I can sit in front of a computer, which is why there have not been any new articles. Finally, after months of rehabilitation, I'm back at the keyboard and ready to resume writing about mobile development.

It's All About Options

Mobile development is all about options. The more options you have, the more success you will have as a mobile developer. By options, I mean programming techniques and skills.

This is particularly true when it comes to data storage. The way in which data is stored is frequently the cornerstone of a mobile application. How data is stored often impacts how it is transferred and used.

The .NET Compact Framework provides mobile developers with a variety of data storage options including text and binary files, as XML, and within a SQL Server CE database. At first glance, one would think that this would be enough, but as I said, mobile development is all about options.

One data option that the .NET Compact Framework is missing is support for Pocket Access databases. Pocket Access is a scaled-down version of Microsoft Access. It is designed to be a simplistic, small database alternative. The engine, if it can be called that, resides on every Pocket PC. What Pocket Access offers to mobile developers is:

• An object model, ADOCE, which is a Windows® CE implementation of ADO.
• An ActiveSync component that enables you to copy databases to and from a device.
• Functionality to maintain synchronization between a device and device version of a database.
• A minute desktop API, which contains two functions that can be used to programmatically copy tables to and from a device from a PC application.
• Tables in which to store data.

Using ADOCE with Pocket Access

ADOCE is a Windows CE implementation of ADO. Like ADO, it's implemented as an object model. The problem is that the .NET Compact Framework does not provide an inherit method for working with COM components and their associated object models. Thankfully, InTheHand Software (www.inthehand.com) has created an Adoce .NET component that provides ADOCE functionality to the NETCF developer.

Note The ADOCE .NET Wrapper demo version is limited for use in only the Pocket PC emulator.

Adding a Reference to the AdoceNet Namespace

Before you can use Adoce .Net, you must first add a reference to the namespace to your project. To add the reference, perform the following steps:

1. In the Solution Explorer window, right-click the References folder.
2. From the menu, select Add Reference.
3. Select the InTheHand.AdoceNet component.
4. Click the OK button to add the selected component to your project.

Classes Provided Through the AdoceNet Namespace

The AdoceNet namespace offers the following classes:

In the following sections, we will walk through some of the commonly performed ADOCE-related tasks.

Opening a Connection

To begin, you establish a connection to the Pocket Access database using the Connection class. This is typically a three-step operation. First, you need to define a variable that holds the Connection object.

Dim cn As InTheHand.AdoceNet.Connection

Next, you need to check to make sure that the ADOCE version 3.1 object library is resident on the device. Since Pocket PCs come with this library resident, this is usually not is issue.

' Initialise the connection.
  Try
    cn = New InTheHand.AdoceNet.Connection
  Catch ex As TypeLoadException
    MsgBox("ADOCE 3.1 is not installed on your device. Application exiting.")
    Application.Exit()
  End Try

Finally, you can open the connection using the Open method of the Connection object like so:

' Connect to the database.
  cn.Open("\Windows\Start Menu\Programs\larryroof\nwind.cdb")

This connection is used to retrieve records from the database as we'll see in the following section.

Opening a Recordset

Included in the AdoceNet namespace is the Recordset class, which provides an object to hold a group of records. To open and propagate a recordset, you use the Open method of the Recordset object.

Dim rs As New InTheHand.AdoceNet.Recordset
rs.Open("SELECT * FROM Customers", cn)

The first argument of the Open method is either a SQL statement or the name of the table to open. The second argument is the connection that the recordset uses to access the database.

Navigating a Recordset

With recordsets, you are always working with an active record—the record within that set in which you are pointing. The Recordset object provides five methods for moving about a recordset:

• MoveNext: moves to the next record in a recordset.
• MovePrevious: moves to the previous record in a recordset.
• MoveFirst: moves to the first record in a recordset.
• MoveLast: moves to the last record in a recordset.
• Move: moves to a specific record within a recordset.

Note You need to make sure that you don't run off the beginning or end of a recordset when using the MoveNext and MovePrevious methods. The Recordset object includes two properties for this purpose, BOF and EOF, which tell when you when you are at the beginning or end.

Adding a Record

There are two methods of adding a record into a table—the SQL INSERT statement and the Recordset object's AddNew method. The SQL INSERT statement is the preferred approach as it is the most efficient way to add data.

The AddNew method adds a record to an existing recordset. The following code fragment demonstrates using this method:

Dim rs As New InTheHand.AdoceNet.Recordset
rs.AddNew
rs.Fields("Name") = "Larry Roof"
rs.Update

As you can see in the example above, calling the AddNew method is only part of the process. AddNew only adds a blank, new record to the recordset. You must then propagate the fields and finish by calling the Update method.

Modifying a Record

There are two methods for updating a table. The preferred method is to use the SQL UPDATE statement. Like the INSERT statement, the UPDATE statement is the most efficient approach to modify data.

The second method involves using the Update method of the Recordset object. The following code fragment demonstrates using this method:

Dim rs As New InTheHand.AdoceNet.Recordset
rs.Fields("Name") = "Larry Roof"
rs.Update

The Update method is used to modify the active record. You would need to use the suite of Recordset Move-based methods to position yourself at the appropriate record before performing the steps above.

Deleting a Record

There are two methods for deleting a record from a table. As with the previous two examples, the preferred method is to use the SQL DELETE statement as it's the most efficient approach.

The second method involves using the Delete method of the Recordset object. The following code fragment demonstrates using this method:

Dim rs As New InTheHand.AdoceNet.Recordset
rs.Delete

rs.MoveNext
If (rs.EOF) Then
  rs.MoveLast
End If

It's important to note in the example above that after deleting a record from a recordset you need to reposition the active record, and with use of the MoveNext or MovePrevious methods, you should always check to make sure that you haven't run off the end of the recordset.

The Pocket Access Demo Application

To pull our Pocket Access discussion together, I have provided the Pocket Access Demo application (the link to the download is at the top of this article). This application demonstrates a variety of techniques for working with a Pocket Access database with the .NET Compact Framework.

The interface to this application is divided into two parts—one that displays a recordset record-by-record, and the other displays a whole recordset at one time in a DataGrid control. Figure 1 shows the former, and Figure 2 the latter.

Figure 1. Pocket Access Demo application displaying a recordset record-by-record

Figure 2. Figure 1. Pocket Access Demo application displaying the entire recordset in a DataGrid control

Loading in the Data

When the Pocket Access Demo application begins, it propagates both the record and grid displays with data. Most of this happens within the Load event of the Form.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As _
  System.EventArgs) Handles MyBase.Load

' Initialize the connection.
  Try
    cn = New InTheHand.AdoceNet.Connection
  Catch ex As TypeLoadException
    MessageBox.Show("ADOCE 3.1 is not installed on your device. " & _
      "Application exiting.")
    Application.Exit()
  Catch ex As Exception
    MessageBox.Show("The following error occurred: " & vbCrLf & ex.Message)
    Application.Exit()
  End Try

' Connect to the database.
  cn.Open("\Windows\Start Menu\Programs\larryroof\nwind.cdb")

' Load the Employees recordset.
  rsEmployees.Open("Employees", cn, InTheHand.AdoceNet.LockType.Optimistic, _
    CommandType.TableDirect)

' Display the record.
  ShowRecord()

' Load the list of tables in the database.
  rsTables.Open("SELECT * FROM MSysTables WHERE TableFlags = 0", cn)

' Set a flag used to control the loading process.
  Loading = True

' Load the combobox on the grid view with the list of tables.
  While Not rsTables.EOF
    comboboxTables.Items.Add(rsTables.Fields("TableName").ToString)
    rsTables.MoveNext()
  End While

' Clear the loading flag.
  Loading = False

' Select the first table in the combobox, which will trigger filling the grid.
  comboboxTables.SelectedIndex = 0

End Sub

At the top of this event procedure you can see where the connection to the Northwind database is established. Below that, I use the ADOCE .NET Wrapper Recordset object to retrieve the Employees table.

A call to the ShowRecord routine updates the record-by-record display. We will examine this routine later in this article.

At the bottom of the Form Load event procedure is code that loads the ComboBox on the grid display with the list of tables that comprise the Northwind database. This list of tables is generated by querying the MSysTables table within the Pocket Access database. This table is found in every Pocket Access database and holds a list of all of the tables within a database. The TableFlags field defines the type of each table. Those tables with the TableFlags value of "O" are user tables, such as Customers, Employees, Orders, and Order Details.

This event procedure finishes by setting the SelectedIndex property of the ComboBox to 0, effectively selecting the first table in the database, which in turn causes the DataGrid to be loaded.

Displaying Data

There are two routines within this demo application that handle displaying data that has been retrieved from the Pocket Access database—the ShowRecord routine and the SelectedIndexChanged event procedure.

The ShowRecord routine is called every time the active record position within the Employees recordset changes.

Sub ShowRecord()

' Display the record.
  textboxFirst.Text = rsEmployees.Fields("FirstName").ToString
  textboxLast.Text = rsEmployees.Fields("LastName").ToString

End Sub

The Fields collection of the Recordset object is referenced to load the individual fields into our display.

Note We could just as easily flip this arrangement so that the contents of the TextBox controls were being loaded back into the fields for the purpose of updating the database.

Filling the DataGrid control is handled within the SelectedIndexChanged event of the ComboBox control. With this arrangement, every time the user selects a new table from the ComboBox, the DataGrid is reloaded.

Private Sub comboboxTables_SelectedIndexChanged(ByVal sender As Object, _
  ByVal e As System.EventArgs) Handles comboboxTables.SelectedIndexChanged

' Check to see if the loading process is running. If it is, then exit.
  If (Loading) Then
    Exit Sub
  End If

' Close the recordset if already in use.
  If rsGrid.State = 1 Then
    rsGrid.Close()
  End If

' Open the recordset.
  rsGrid.Open(comboboxTables.Text, cn, InTheHand.AdoceNet.LockType.ReadOnly, _
    CommandType.TableDirect)

' Load the grid.
  gridRecords.DataSource = Nothing
  gridRecords.DataSource = rsGrid

End Sub

Unlike the previous example, where we manually loaded the fields into the individual TextBox controls, here we are instead going to use data binding. We start by retrieving the recordset based upon the table selected in the ComboBox. From there all that we need to do is set the DataSource property of the DataGrid control to our recordset.

Note One of the features of the InTheHand implementation of the ADOCE .NET Wrapper is its support for data binding.

Moving a Recordset

The last part of the demo application that we need to examine is the code used to navigate about the recordset. The record-by-record component of the Pocket Access Demo application includes four navigational buttons that enable the user to:

• Move to the first record
• Move to the previous record
• Move to the next record
• Move to the last record.

Each of these functions is implemented within the Click event procedures of the four buttons that are shown below. In each example, after repositioning the pointer within the recordset, we call the ShowRecord procedure to update the display.

Private Sub buttonFirst_Click(ByVal sender As System.Object, _
  ByVal e As System.EventArgs) Handles buttonFirst.Click

' Move to the first record.
  rsEmployees.MoveFirst()
  ShowRecord()

End Sub

Private Sub buttonLast_Click(ByVal sender As System.Object, _
  ByVal e As System.EventArgs) Handles buttonLast.Click

' Move to the last record.
  rsEmployees.MoveLast()
  ShowRecord()

End Sub

Private Sub buttonPrevious_Click(ByVal sender As System.Object, _
  ByVal e As System.EventArgs) Handles buttonPrevious.Click

' Move to the previous record.
  rsEmployees.MovePrevious()
  If (rsEmployees.BOF) Then
    rsEmployees.MoveFirst()
  End If
  ShowRecord()

End Sub

Private Sub buttonNext_Click(ByVal sender As System.Object, _
  ByVal e As System.EventArgs) Handles buttonNext.Click

' Move to the next record.
  rsEmployees.MoveNext()
  If (rsEmployees.EOF) Then
    rsEmployees.MoveLast()
  End If
  ShowRecord()

End Sub

There you have it—the basics for leveraging Pocket Access databases from within a mobile application written with the .NET Compact Framework. As you can see, it is fairly straightforward, particularly if you have some background in ADO.

Pocket Access gives you one more option for storing data. It is particularly well suited for applications that have small- to medium-size data requirements and those applications that want to pass data back to a PC-based Microsoft Access database.

Jump Start Your .NET Compact Framework Development

Want to get up to speed with the .NET Compact Framework in a week? My training can get you there. I offer a five-day NETCF class that provides you with everything you need to get up and running quickly. I show you how to create robust mobile solutions using the .NET Compact Framework, SQL Server CE, and XML. A detailed outline is available at www.larryroof.com.

If you can't get away for a face-to-face class, check out my new book The Definitive Guide to the .NET Compact Framework from Apress. It provides a comprehensive overview of how to create mobile applications with the .NET Compact Framework. It has been a long time coming and I have to thank Dan Fergus for helping me wrap up the book after I got injured.

Back on the Road

That's it for this month. I have to say that it's good to be back. In the coming months I will be covering such topics as working with the Pocket PC registry, using RAPI to manage a device, examining third-party controls, exploring socket communications, and other mobile development topics. Remember, when it comes to mobile development, it's all about options. The more you have the more creative you can be. Until next month, I'll be sitting on my couch watching the surfing channel.

Larry Roof heads up larryroof.com, a firm that specializes in consulting on mobile projects and training on eMbedded Visual Basic, Smart Device Extensions, and SQL Server CE.