Development | .NET Compact Framework

Pocket Access Data provider for the .NET Compact Framework
Written by Peter Foot  [author's bio]  [read 103591 times]
Edited by Derek

         

Page 1  Page 2  Page 3 

Why Pocket Access?

The omission of a Pocket Access database provider has sparked much discussion from the very first release of the .NET Compact Framework. While Microsoft had provided developers with SQL Server CE, there is still a strong need for Pocket Access databases, both for updating applications previously written in eVB, and for any other application where installation of SQL Server CE could be considered overkill. With this in mind I wrote a wrapper library around the familiar ADOCE ActiveX control.

What is ADOCE .NET Wrapper?

The ADOCE .NET Wrapper is a combination of .NET classes and an unmanaged C++ dll which provides the bridge with the ActiveX control. To Use ADOCE from your .NET applications you add the ADOCE .NET Wrapper as a reference to your project.

The objects, methods and properties available to you should be very familiar if you have used the ADOCE control with eVB. The objects are all contained within the InTheHand.AdoceNet namespace, so when you are using this from your VB.NET code you should add an imports directive to your code file so that you dont have to type out the fully qualified names each time:

Imports InTheHand.AdoceNet

Connecting to a Database

You open a connection to a Pocket Access file using the Connection object. The following code snippet shows how to open the file Trees.cdb from the My Documents folder of the device:

Dim cnDataFile As Connection
cnDataFile = New Connection()

'pass full filename to open method
cnDataFile.Open(\My Documents\Trees.cdb)

With a single Connection you can then go on to query the database with several Recordset objects.

Retrieving Records

The Recordset is the core object within the ADOCE .NET Wrapper. With it you can issue SQL queries and return views of your data. The following code snippet shows a query to select Tree names from the database with a height greater than 3 metres.

'create and initialise a new Recordset
Dim rsTrees As Recordset
rsTrees = New Recordset()

'store SQL statement
Dim SQL As String
SQL = _
   "SELECT TreeName FROM Trees WHERE Height > 3"

rsTrees.Open(SQL, cnDataFile, _
   LockType.Optimistic, CommandType.Text)

Note that the LockType and CommandType are optional, by specifying just a query and a Connection the Recordset is opened Read-Only. The values within the table are held within the Recordset object, and we can extract them from a specific location, loop through the table or use a new feature of .NET Compact Framework Data Binding.

Populating Lists

A traditional way of filling a list from a Recordset is to use a While Not loop that exits when the Recordset.EOF flag is True. The BOF and EOF properties indicate positions before and after valid rows in the Recordset. Here is a code snippet to demonstrate filling a ListView from a Recordset:

'loop while there are still records
While Not rsTrees.EOF

   'create new listview item
   Dim lviNewItem As ListViewItem 

   'assign index field value to item text
   Dim strIndex As String
   strIndex = rsTrees.Fields("Index").ToString
   lviNewItem = New ListViewItem(strIndex)

   'add tree name as sub-item
   Dim strTreeName As String
   strTreeName = _
      rsTrees.Fields("TreeName").ToString
   lviNewItem.SubItems.Add(strTreeName)

   'add the item to the list
   lstTreeList.Items.Add(lviNewItem)

   'move to the next record
   rsTrees.MoveNext()

End While

Next Page 

Back to .NET Compact Framework | [Article Index]