Page 1
Page 2
Page 3
and the CountRows function which
we will use later:
Function CountRows(ByVal pTable As
String) As Integer
Dim SQL As String = "SELECT COUNT(*) FROM " &
pTable
If ceConnect() Then
Dim sqlReader As SqlCeDataReader
Dim sqlCmd As New SqlCeCommand(SQL, connCE)
Try
sqlReader = sqlCmd.ExecuteReader
Catch err As SqlCeException
ErrorDisplay(err)
Catch err As Exception
MsgBox("There was an error: " & err.ToString())
End Try
If sqlReader.Read Then
'got the count
CountRows = sqlReader.Item(0)
Else
CountRows = 0
End If
sqlReader = Nothing
sqlCmd = Nothing
Disconnect()
End If
End Function
The code that reads the XML file into
the dataset and then uses the data adapter
to write it to a SQL Server CE is all contained in the btnInit_Click
sub.
Private Sub btnInit_Click(ByVal
sender As System.Object, ByVal e As System.EventArgs) Handles
btnInit.Click
Dim xmlFileSpec As String = "\Windows\devbuzztest.xml"
Dim oTime As New TimeIt()
MessageBox.Show("Initialising the db will take a while.",
"Database Init", MessageBoxButtons.OK, MessageBoxIcon.Asterisk,
& _
MessageBoxDefaultButton.Button1)
Me.Refresh()
'// use CheckDB to create the database
prog.Value = 0
oTime.Start()
If sql.CheckDB(True) Then
sbar.Text = "Database created!"
prog.Value = 1 : sbar.Refresh()
'// use InitDB() to initalise the database with
'// the DevbuzzTable test table defn
sql.InitDB()
'// check whether the table exists
If sql.TableExists("DevbuzzTable") Then
sbar.Text = "DEVBUZZTABLE created!"
prog.Value = 2 : sbar.Refresh()
'// now lets create the table from XML
Dim fs As New FileStream(xmlFileSpec, FileMode.Open)
Dim xr As New XmlTextReader(fs)
data.ReadXml(xr)
xr.Close()
fs.Close()
prog.Value = 4 : sbar.Refresh()
'// make sure everything looks good
sbar.Text = data.Tables("DevbuzzTable").Rows.Count.ToString
& " rows in the DATASET"
prog.Value = 5 : sbar.Refresh()
'// lets update the DATABASE using
the DATASET
Dim SqlDataAdapter1 As New SqlCeDataAdapter("SELECT
DevbuzzID, TestName FROM DevbuzzTable ORDER BY DevbuzzID",
sql.connCE)
Dim sqlCmdBuilder As SqlCeCommandBuilder
sqlCmdBuilder = New SqlCeCommandBuilder(SqlDataAdapter1)
SqlDataAdapter1.Update(data, "DevbuzzTable")
prog.Value = 7 : sbar.Refresh()
sbar.Text = sql.CountRows("DevbuzzTable").ToString
& " rows in the DATABASE"
oTime.Finish()
prog.Value = 8 : sbar.Refresh()
MessageBox.Show("Tutorial data ready. (" &
oTime.Elapsed.ToString & " seconds)")
End If
End If
Breaking it down
The first thing we need to do it create
the SQL Server CE database, overwriting it if necessary.
This is done by the CheckDB function:
Function CheckDB(ByVal pOverWrite As
Boolean) As Boolean
If pOverWrite = True Then
Try
System.IO.File.Delete(ceDBSpec)
Catch
MessageBox.Show(ceDBSpec & " does not exist!")
End Try
Try
If Not System.IO.File.Exists(ceDBSpec) Then
Dim SQLEngine As System.Data.SqlServerCe.Engine
SQLEngine = New System.Data.SqlServerCe.Engine("data
source=" & ceDBSpec)
SQLEngine.CreateDatabase()
End If
m_DBCreated = True
CheckDB = True
Catch err As SqlCeException
ErrorDisplay(err)
m_DBCreated = False
CheckDB = False
Catch err As Exception
MsgBox("There was an error: " & err.ToString())
End Try
End If
End Function
Previous Page
Next Page