Page 1
Page 2
Page 3
Page 4
Page 5
Figure 4 - EntManager Project Properties
Once this is all set up, the project is ready and it is time to start coding with ADOXCE
and SQL Server. There is a lot of support code in the project to handle opening the database,
populating the treeview and other such items. There are 2 pieces of code that are dealing
with ADOXCE and need to be discussed. The first, shown in Listing 3, handles populating
the treeview with the Database Name, Tables and Columns in the database.
Listing 3 - Code for reading DB
Schema
Private Sub AddDBNode(psDatabase As String)
Dim sDatabaseName As String
Dim iSlashLoc As Integer
Dim tabDBTable As ADOXCE.Table
Dim colColumn As ADOXCE.Column
On Error Resume Next
'Open the Database
Set mcatCurrent = CreateObject("ADOXCE.Catalog.3.1")
mcatCurrent.ActiveConnection = "Provider=Microsoft.SQLSERVER.OLEDB.CE.1.0;
data source=" & psDatabase & ";"
If Err.Number <> 0 Then
MsgBox "Error Opening Database" & vbCrLf & _
"Number: " & Err.Number & vbCrLf & _
"Source: " & Err.Source & vbCrLf & _
"Description: " & Err.Description, vbOKOnly, "EntManager"
Exit Sub
End If
'Get the Database name without the extension
iSlashLoc = InStrRev(psDatabase, "\") + 1
sDatabaseName = Mid(psDatabase, iSlashLoc, Len(psDatabase) - (iSlashLoc
+ 3))
'Add Node under Root with Database Name
tvMain.Nodes.Add "Root", tvwChild, "Database" & mlOpenDatabases,
sDatabaseName
'Add Node unter Database Node for Tables
tvMain.Nodes.Add "Database" & mlOpenDatabases, tvwChild, "Tables"
& mlOpenDatabases, "Tables"
'For each Table in the Catalog's table collection get info about
table
For Each tabDBTable In mcatCurrent.Tables
tvMain.Nodes.Add "Tables" & mlOpenDatabases, tvwChild,
mlOpenDatabases & tabDBTable.Name, tabDBTable.Name
'For each Column in the Tables Columns collection
get info about the column
For Each colColumn In tabDBTable.Columns
tvMain.Nodes.Add mlOpenDatabases & tabDBTable.Name,
tvwChild, mlOpenDatabases & tabDBTable.Name & colColumn.Name,
colColumn.Name
Next
Set tabDBTable = Nothing
Next
Set tabDBTable = Nothing
Set colColumn = Nothing
End Sub
The first step is to create the Catalog object
and set it's ActiveConnection property to a valid connection string.
Notice that the Catalog object does not have an Open method. Once
the active connection property is assigned the Tables collection
can be accessed from the Catalog object. If there are any questions
about the object model for ADOXCE, remember pressing F2 in eVB
and then selecting ADOXCE in the top drop down to filter the view
to ADOXCE will show the ADOXCE objects in the Object Viewer. A
sample of Object Viewer with ADOXCE selected is shown in Figure
5.
Figure 5 - Object Viewer
The best way to iterate through the tables
collection is by dimensioning a variable of the type ADOXCE.Table
and using a For Loop as shown in Listing 3. There is a nested
for loop using a variable of the ADOXCE.Column type to access
all the columns in a Table's Columns collection. In each of these
loops the Table name and Column Names are added in the appropriate
places in the treeview.
Once the treeview is populated the next step
is to list the properties for any valid treeview node that the
user clicks. Listing 4 shows a code sample that detects if a valid
node was clicked. The node's parent text is used to determine
where and what was clicked in the treeview.
Listing 4 - Detecting
a valid node
Private Sub tvMain_NodeClick(ByVal Index As Long)
Dim oNode As Node
Dim liList As ListItem
Dim oProp As ADOXCE.Property
Dim oTable As ADOXCE.Table
Dim oCol As ADOXCE.Column
Dim sMessage As String
Dim sColType As String
Dim iPointer As Integer
Set oNode = tvMain.Nodes(Index)
iPointer = Screen.MousePointer
'Need to use 11 since vbHourglass constant does not work on my device
Screen.MousePointer = 11
'See if it is the root or the tables node and ignore
If oNode.Text <> "SQL Server CE Databases" And oNode.Text <> "Tables" Then
If oNode.Parent.Text = "SQL Server CE Databases" Then ' it is a database
'Code to get and display DB Properties - in listing 5
ElseIf oNode.Parent.Text = "Tables" Then ' It is a table
'Code to get and display DB Properties
Else ' Must be a column
'Code to get and display DB Properties
End If
'Hide the treeview frame and show the Properties Frame
fraTreeView.ZOrder vbSendToBack
fraTreeView.Visible = False
fraList.Visible = True
fraList.ZOrder vbBringToFront
End If
Screen.MousePointer = iPointer
End Sub
There are 3 items to be concerned with: the database, each table, and the columns for each table.
When one of these items is clicked the properties for that item will be displayed in a ListView.
Figure 6 shows a sample screen of the properties for a development database.
Previous
Page Next Page
Previous Page
Next Page