Page 1
Page 2
Page 3
Once the devbuzz.sdf SQL CE database
has been created we initialize it using the InitDB
sub to create the DevbuzzTable to hold the test data.
Function InitDB()
Dim SQL As String
Dim arb1 As Integer
If ceConnect() Then
SQL = "CREATE TABLE DevbuzzTable (DevbuzzID integer
Primary Key NOT NULL, TestName nvarchar(30) NOT NULL)"
Dim cmd As New System.Data.SqlServerCe.SqlCeCommand(SQL,
connCE)
cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
Disconnect()
End If
End Function
Once that is complete, and after a quick
test to make sure that the DevbuzzTable exists using sql.TableExists("DevbuzzTable"),
we can proceed with loading the XML:
Loading the data dataset
with the contents of our XML file couldn't be any easier:
Dim fs As New
FileStream(xmlFileSpec, FileMode.Open)
Dim xr As New XmlTextReader(Fs)
data.ReadXml(xr)
xr.Close()
fs.Close()
Updating the database
Now we have a dataset containing the
DevbuzzTable with 500 records. Let's insert this
into our SQL CE database using the data adapter.
'// 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")
sbar.Text = sql.CountRows("DevbuzzTable").ToString
& " rows in the DATABASE"
The SQLDataAdapter uses 4 SQLCommands
to update, insert, select and delete
data reconciling it with the respective data source. It
can be tedious to set these up though, so you can circumvent
the manual process of setting up the update, insert
and delete SQLCommands by instantiating the data
adapter with a select statement thereby taking care of the
Select Command; and then use the SQLCECommand Builder
to automatically generate the rest of the SQLCommands for
single-table updates.
If you have any similar tips on using
.NET cf and SQL Server CE remember that we
run a monthly article draw.
Previous Page