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