Private Function MakeOutput(ByVal typ As Integer) As String
Dim lDadapt As System.Data.SqlServerCe.SqlCeDataAdapter
Dim lDset As New System.Data.DataSet
Dim st1 As String
Dim cmd As System.Data.SqlServerCe.SqlCeCommand
Try
cmd = dbM.GetCommand
Catch ex As Exception
MsgBox("Database is not valid.")
MakeOutput = ""
Exit Function
End Try
Try
'For either one, the ID must be included.
cmd.CommandText = "SELECT Ident FROM IDTable WHERE IDId = 1"
lDadapt = New System.Data.SqlServerCe.SqlCeDataAdapter(cmd)
lDadapt.Fill(lDset, "Identifier")
lDadapt.Dispose()
If typ = 1 Then
cmd.CommandText = "SELECT * FROM EffortTable WHERE Downloaded < 2"
'Get the tables and put them in the dataset.
lDadapt = New System.Data.SqlServerCe.SqlCeDataAdapter(cmd)
lDadapt.Fill(lDset, "EffortTable")
lDadapt.Dispose()
cmd.CommandText = "SELECT * FROM FishTable WHERE Downloaded < 2 "
lDadapt = New System.Data.SqlServerCe.SqlCeDataAdapter(cmd)
lDadapt.Fill(lDset, "FishTable")
lDadapt.Dispose()
cmd.CommandText = "SELECT * FROM MaxAnglerNumber WHERE Downloaded < 2 "
lDadapt = New System.Data.SqlServerCe.SqlCeDataAdapter(cmd)
lDadapt.Fill(lDset, "MaxAnglerNumber")
If lDset.Tables("EffortTable").Rows.Count = 0 And lDset.Tables("FishTable").Rows.Count = 0 Then
Windows.Forms.MessageBox.Show("There is no new data to be sent, so nothing will be sent.")
st1 = ""
Else
'This may not be the best way to do this, but get a string of the XML.
st1 = lDset.GetXml()
End If
Else
cmd.CommandText = "SELECT * FROM EffortTable"
'Get the tables and put them in the dataset.
lDadapt = New System.Data.SqlServerCe.SqlCeDataAdapter(cmd)
lDadapt.Fill(lDset, "EffortTable")
lDadapt.Dispose()
cmd.CommandText = "SELECT * FROM FishTable"
lDadapt = New System.Data.SqlServerCe.SqlCeDataAdapter(cmd)
lDadapt.Fill(lDset, "FishTable")
lDadapt.Dispose()
cmd.CommandText = "SELECT * FROM MaxAnglerNumber"
lDadapt = New System.Data.SqlServerCe.SqlCeDataAdapter(cmd)
lDadapt.Fill(lDset, "MaxAnglerNumber")
'This may not be the best way to do this, but get a string of the XML.
st1 = lDset.GetXml()
End If
MakeOutput = st1
Catch ex As System.Data.SqlServerCe.SqlCeException
Windows.Forms.MessageBox.Show("Error making output file." & Chr(10) & ex.Message)
MakeOutput = "FAIL"
End Try
End Function
'This sets the downloaded bit for all of the data in the tables.
Private Sub UpdateTables()
Dim cmd As System.Data.SqlServerCe.SqlCeCommand
Try
cmd = dbM.GetCommand
Catch ex As Exception
MsgBox("Updating Downloaded failed for unknown reason.")
End Try
Try
cmd.CommandText = "UPDATE FishTable SET Downloaded = 2"
cmd.ExecuteNonQuery()
cmd.CommandText = "UPDATE EffortTable SET Downloaded = 2"
cmd.ExecuteNonQuery()
cmd.CommandText = "UPDATE MaxAnglerNumber SET Downloaded = 2"
cmd.ExecuteNonQuery()
Catch ex As System.Data.SqlServerCe.SqlCeException
MsgBox("Updating download failed because of: " & ex.Message)
cmd.Dispose()
End Try
End Sub
Private Function GetTables(ByVal stXML As String) As Boolean
Dim retVal As Integer
Dim dSet As New System.Data.DataSet
Dim sRead As System.IO.StringReader
Dim xmlTxtR As System.Xml.XmlTextReader
Dim cmd As System.Data.SqlServerCe.SqlCeCommand
Dim dTable As System.Data.DataTable
Dim rw As System.Data.DataRow
'This should take what was passed in and turn it into a string.
sRead = New System.IO.StringReader(stXML)
xmlTxtR = New System.Xml.XmlTextReader(sRead)
dSet.ReadXml(xmlTxtR, XmlReadMode.InferSchema)
'Now we need to kill off anything that is in the shift or location table, because if
'there is anything in there that is not in the incoming thing, you are basically ****ed.
'However, this could also mean that you should clear the data from the database, but that
'is probably NOT a very good idea, because actual data is lost. Residual data associated
'with table entries that no longer exist are a problem on the downstream side, not the
'upstream side.
Try
cmd = dbM.GetCommand
cmd.CommandText = ("DELETE FROM ShiftTable")
cmd.ExecuteNonQuery()
cmd.CommandText = ("DELETE FROM LocationTable")
cmd.ExecuteNonQuery()
Catch ex As System.Data.SqlServerCe.SqlCeException
MsgBox(ex.Message)
End Try
'Now the data from the dataset must be shifted to the table.
dTable = dSet.Tables("ShiftTable")
For Each rw In dTable.Rows
cmd.CommandText = ("INSERT INTO ShiftTable (ShiftName) VALUES(?)")
cmd.Parameters.Add("ShiftName", rw.Item("ShiftName"))
cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
Next
dTable = dSet.Tables("LocationTable")
For Each rw In dTable.Rows
cmd.CommandText = ("INSERT INTO LocationTable (LocationName) VALUES(?)")
cmd.Parameters.Add("LocationName", rw.Item("LocationName"))
cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
Next
cmd.Dispose()
sRead.Close()
xmlTxtR.Close()
GetTables = True
End Function
[RESOLVED] Windows CE 5.0 CDB and MDB files
Reply With Quote
Originally Posted by petevick
