Examining SQL Server Compact Database in Visual Basic.net

Once in a lifetime you reach a point that you have to examine a database in Visual Basic. Lately I wanted to copy structure and data from a SQL Server Compact database to a SQL Server 2008 database. I searched the internet for a simple freeware tool to do this, but I couldn't find any. After searching for quite a while, I decided to build a copy program my own.

This tool had to solve two issues:

1. Examine the SQL Server Compact database structure
2. Copy this structure and the data to the Sql Server database

This article describes the code to examine the structure.In an other article I describe how to use this structure to make a copy to the SQL Server database.

The table to be examined

In a Sql Server Compact Database I have a table, named CopyTest. In the image beneath you see a screenshot taken in Visual Basic.net from the structure of this table.

Form to examine the Structure of Sql Server Compact Database

I started to build a form with one button and one treeview. The goal was to examine the table structure of the Sql Server Compact Database and the data, and to show these in the Treeview. I used the following code. Make sure to import the System.Data.SqlServerCe namespace; without this the code won't work:

Dim conn1 As SqlCeConnection 'Connection to the database      

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

conn1 = New SqlCeConnection("Data Source=d:\test\MyDB.sdf;Password=123ABC")
conn1.Open() 'Open Database

ReadTable("CopyTest")

' ReadTable("SomeOtherTable") : easy to extend for other tables

End Sub

Private Sub ReadTable(ByVal sTable As String)

Dim cmd As New SqlCeCommand("Select * from " & sTable, conn1) 'Define what to examine
Dim dr As SqlCeDataReader
Dim flds As String = ""

dr = cmd.ExecuteReader

TreeView1.Nodes.Add("table" & sTable, sTable) 'Add the tablename to the Treeview
TreeView1.Nodes("table" & sTable).Nodes.Add("table" & sTable & "Data", "Data") 'Now add a branche for the data

TreeView1.Nodes("table" & sTable).Nodes.Add("table" & sTable & "Structure", "Structure") 'Another branche for the structure

For i As Integer = 0 To dr.FieldCount - 1 'First fill the structure
TreeView1.Nodes("table" & sTable).Nodes("table" & sTable & "Structure").Nodes.Add("struct" & sTable & i.ToString.Trim, dr.GetName(i) & " - " & dr.GetFieldType(i).ToString)
Next

Dim sDataRec As String = "" 'To show the data, build a string first

Do While dr.Read 'Loop throught complete table

sDataRec = ""

For i As Integer = 0 To dr.FieldCount - 1
If Not IsDBNull(dr.Item(i)) Then 'If field is Null you can't address it in expression, so:
sDataRec &= dr.Item(i) & " -/- "
Else
sDataRec &= "Null -/- "
End If
Next i

'Now add the complete data string to the Treeview
TreeView1.Nodes("table" & sTable).Nodes("table" & sTable & "Data").Nodes.Add("rec" & dr.Item("id").ToString.Trim, sDataRec)

Loop

End Sub

You can see the result of the analysis of the structure and the data from the Sql Server Compact Database below.