Page 1
Page 2
Page 3
Page 4
Page 5
One of the biggest problems with being on the leading,
or bleeding (depending on your point of view), edge of development is the lack
of solid information that is available from anywhere but the documentation. Even
the documentation can be wrong or misleading at times. SQL Server 2000 CE Edition
(SSCE) is a new "1.0 release" product from Microsoft and there is little information
available outside the documentation. Another aspect of a 1.0 release is the lack of
tools from the manufacturer and third parties. For anyone familiar with using SQL
Server on the Desktop, and now looking to use SSCE, one tool that will be missed
right away is Enterprise Manager. This article will cover some of the aspects of
managing a database on the CE device and the tools that are available to make the
job of managing database(s) easier.
What you need to run this code
eVB (Embedded Visual Basic)
SQL Server 2000 Windows CE Edition
SQL Server 2000 Developer Edition (needed to install SQL Server
2000 Windows CE Edition)
ADOCE 3.1 (includes the ADOXCE Object Library)
Working knowledge of eVB, TreeView, ListView and MenuBar Controls
Some Background
Before getting started and discussing the
code, a little background and basics need to be covered. In most
business applications the developer is concerned with accessing
data in a database that is already defined. The developer uses
ActiveX Data Objects (ADO) and/or Data Manipulation Language (DML)
such as insert/update/delete statements. When a developer or Database
Administrator (DBA) wants to modify a database structure with
SQL Server on a standard windows machine (not a Windows CE machine),
there are a few different ways to create and modify databases.
Developers can use Enterprise Manager to modify the database and
tables or Data Definition Language (DDL) such as Create Table;
Drop Database etc.
A third way that is not very widely known, but very easy to use, is another object model provided
by Microsoft, ADOX - ActiveX Data Objects Extensions for Data Definition Language and Security.
This object model allows a developer to get access to the entire database structure. Luckily for
us Microsoft has seen the need for ADOX on the CE machine and has provided a subset of ADOX via
the ADOXCE - ActiveX Data Objects Extensions for Data Definition Language and Security on
Windows CE - object model as seen in figure 1.
At first glance the object model may seem simple, and it is fairly easy to understand. However,
it does start to become more involved when taking into account all of the object relationships
or when trying to programmatically establish table relationships like Foreign Keys. Notice how,
in the object model, the Table, Key and Index objects all have Columns collections. ADOXCE is,
of course, more lightweight than ADOX (it's CE) and since SSCE has no support for stored
procedures and views, there is not any support in the object model.
Note that there is a properties collection
for every Object in the model. This collection is used by the
OLEDB provider to allow for any number of provider specific properties
that do not map to any ADOXCE object properties. For example the
SSCE database can be encrypted by setting a provider specific
database property "SSCE:Encrypt Database". This property is available
from the Connection Object. One of the interesting things I discovered
is the lack of a "connection" object. A connection can only established
by manipulating the ActiveConnection property. Once the ActiveConnection
property has been set, the connection object gets implicitly created
and access to the object is available via the ActiveConnection
property. Even though the Connection object is not shown and does
not have a Properties collection in the object model, the connection
properties can be accessed from the Catalogs ActiveConnection
property. The code snippet below shows an example of how to access
the Connection object from the Catalog object.
Dim catCurrent As ADOXCE.Catalog
Set catCurrent = CreateObject("ADOXCE.Catalog.3.1")
catCurrent.ActiveConnection = "Provider=Microsoft.SQLSERVER.OLEDB.CE.1.0;
data source=\SecDev.sdf"
MsgBox "Provider: " & catCurrent.ActiveConnection.Provider, vbOKOnly
Notice the line Set
catCurrent = CreateObject("ADOXCE.Catalog.3.1"). This actually
differs from the ADOXCE documentation. In many places in the documentation
you will see Set catCurrent = CreateObject("ADOXCE.Catalog").
However, as documented it will not work. This goes for any ADOCE
object you are trying to instantiate, make sure to add the version
'.3.1' to the end of the CLSID.
Next Page