formtraffic.vb

来自「清华大学出版社出版的 移动应用开发宝典 张大威（2008）的附书源代码」· VB 代码 · 共 271 行

Imports System
Imports System.Drawing
Imports System.Collections
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlServerCe

Namespace SQLCE_RDA

    Public Class FormTraffic

        Private _carsDataSet As DataSet = Nothing
        Private _carsAdapter As SqlCeDataAdapter = Nothing
        Private _carsConnection As SqlCeConnection = Nothing
        Private _CurrentCarID As Integer

        Shared Sub Main()
            Application.Run(New FormTraffic())
        End Sub

        Public Sub New()

            ' This call is required by the Windows Form Designer.
            InitializeComponent()

            ' Add any initialization after the InitializeComponent() call.
            _carsConnection = New SqlCeConnection("Data Source=\My Documents\TrafficRDA.sdf")
            Dim SQL As String = "SELECT CarID, Reg , Location FROM Cars ORDER BY Reg "

            _carsAdapter = New SqlCeDataAdapter(SQL, _carsConnection)

            ' Derive the Insert, Update, Delete commands
            Dim cbr As SqlCeCommandBuilder = New SqlCeCommandBuilder(_carsAdapter)

        End Sub

        Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            RDAPull()
        End Sub

        Private Sub DisplaySQLCEErrors(ByVal ex As SqlCeException)
            Dim i As Integer

            For i = 0 To ex.Errors.Count - 1 Step i + 1
                MessageBox.Show("Index #" + i.ToString() + vbNewLine + ex.Errors(i).Source + vbNewLine + "Error: " + ex.Errors(i).Message, "Error No. " + ex.Errors(i).NativeError.ToString())
            Next
        End Sub

        Private Sub RDAPull()
            ' First ensure we have an empty local SQL CE database
            CreateEmptyDatabase()

            Using rda As New SqlCeRemoteDataAccess()

                'string sCon = "Provider=SQLOLEDB;Data Source=homelaptop;"
                '   + "Initial Catalog=Traffic;"
                '  + "integrated security=SSPI;Persist Security Info=False;";

                Dim sCon As String = "Provider=SQLOLEDB;Data Source=homelaptop;Initial Catalog=Traffic;User ID=bob;Password=bob"

                rda.InternetUrl = "http://homelaptop/SQLReplication/sqlcesa30.dll"
                rda.LocalConnectionString = "Data Source=\My Documents\TrafficRDA.sdf"
                rda.InternetLogin = "homelaptop\bob"
                rda.InternetPassword = "bob"
                Try

                    rda.Pull("Cars", "SELECT CarID,Reg,Location FROM Cars", sCon, RdaTrackOption.TrackingOnWithIndexes, "rdaCarErrors")

                Catch sqlCeEx As SqlCeException

                    DisplaySQLCEErrors(sqlCeEx)

                End Try
                Try
                    rda.Pull("Obs", "SELECT ObsID,CarID,ObsDateTime,ObsNote FROM Obs", sCon, RdaTrackOption.TrackingOn, "rdaObsErrors")
                Catch sqlCeEx As SqlCeException
                    DisplaySQLCEErrors(sqlCeEx)
                End Try
            End Using

            MessageBox.Show("RDA Pull Done!")

            SetupUIDataBinding()
        End Sub

        Private Sub SetupUIDataBinding()

            ' Initialize the DataSet used for maintaining records in the UI
            _carsDataSet = New DataSet()
            _carsAdapter.Fill(_carsDataSet, "Cars")

            ' Workaround for tables with Identity columns - you cannot use RDA to sync tables with Identity columns
            ' so instead configure the column in the DataTable - effectively implement Identity column behaviour
            ' outside the database
            Me._carsDataSet.Tables("Cars").Columns("CarID").AutoIncrement = True
            Me._carsDataSet.Tables("Cars").Columns("CarID").AutoIncrementStep = 1

            'Get current highest value in that column
            _carsConnection.Open()
            Dim MaxCarID As Integer = -1
            Using cmd As New SqlCeCommand("SELECT MAX(CarID) FROM Cars", _carsConnection)

                Dim result As Object = cmd.ExecuteScalar()

                If Not IsDBNull(result) Then
                    MaxCarID = Convert.ToInt32(result)
                End If

            End Using
            _carsConnection.Close()
            ' Set the AutoIncrement seed accordingly
            Me._carsDataSet.Tables("Cars").Columns("CarID").AutoIncrementSeed = MaxCarID + 1

            ' Initialize the data binding to the DataGrid
            Me.bindingSource1.DataSource = _carsDataSet
            Me.bindingSource1.DataMember = "Cars"
        End Sub

        Private Sub SubmitSQLRDA()
            Using rda As New SqlCeRemoteDataAccess()
                'string sCon = "Provider=SQLOLEDB;Data Source=homelaptop;"
                '   + "Initial Catalog=Traffic;"
                '  + "integrated security=SSPI;Persist Security Info=False";
                Dim sCon As String = "Provider=SQLOLEDB;Data Source=homelaptop;Initial Catalog=Traffic;User ID=bob;Password=bob"

                rda.InternetUrl = "http://homelaptop/SQLReplication/sqlcesa30.dll"
                rda.LocalConnectionString = "Data Source=\My Documents\TrafficRDA.sdf"
                rda.InternetLogin = "homelaptop\bob"
                rda.InternetPassword = "bob"
                Try
                    rda.SubmitSql("UPDATE Cars SET Archive = 1", sCon)
                Catch ex As SqlCeException
                    DisplaySQLCEErrors(ex)
                End Try
            End Using
        End Sub

        Private Sub RDAPush()
            ' Write changes back to the SQL CE database
            Me.bindingSource1.EndEdit()
            _carsAdapter.Update(_carsDataSet, "Cars")

            Using rda As New SqlCeRemoteDataAccess()
                'string sCon = "Provider=SQLOLEDB;Data Source=homelaptop;"
                '   + "Initial Catalog=Traffic;"
                '  + "integrated security=SSPI;Persist Security Info=False";
                Dim sCon As String = "Provider=SQLOLEDB;Data Source=homelaptop;Initial Catalog=Traffic;User ID=bob;Password=bob"

                rda.InternetUrl = "http://homelaptop/SQLReplication/sqlcesa30.dll"
                rda.LocalConnectionString = "Data Source=\My Documents\TrafficRDA.sdf"
                rda.InternetLogin = "homelaptop\bob"
                rda.InternetPassword = "bob"
                Try
                    rda.Push("Cars", sCon)
                Catch sqlCeEx As SqlCeException
                    DisplaySQLCEErrors(sqlCeEx)
                End Try
            End Using

            MessageBox.Show("RDA Push Done!")
        End Sub

        Private Sub buttonAddCar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles buttonAddCar.Click
            Dim newrow As DataRow = _carsDataSet.Tables("Cars").NewRow()

            newrow("Reg") = textBoxReg.Text
            newrow("Location") = textBoxLocation.Text

            'Me._carsDataSet.Tables("Cars").Rows.Add(New Object() {Nothing, Me.textBoxReg.Text, Me.textBoxLocation.Text})

            System.Diagnostics.Debug.WriteLine(newrow("carid").ToString())
            _carsDataSet.Tables("Cars").Rows.Add(newrow)
        End Sub

        Private Sub buttonPush_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles buttonPush.Click
            RDAPush()
        End Sub

        Private Sub CreateEmptyDatabase()
            'create the database
            If Not System.IO.File.Exists("\My Documents\TrafficRDA.sdf") Then
                Dim eng As SqlCeEngine = New SqlCeEngine("Data Source=\My Documents\TrafficRDA.sdf")
                eng.CreateDatabase()
            End If

            'clear out old table
            Using cn As New SqlCeConnection("Data Source=\My Documents\TrafficRDA.sdf")
                cn.Open()
                Try
                    Try
                        Using cmd As New SqlCeCommand("DROP TABLE Cars", cn)
                            cmd.ExecuteNonQuery()
                        End Using
                    Catch sqlCeEx As SqlCeException
                        If sqlCeEx.HResult <> -2147217865 Then
                            DisplaySQLCEErrors(sqlCeEx)
                        End If
                    End Try
                    Try
                        Using cmd As New SqlCeCommand("DROP TABLE Obs", cn)

                            cmd.ExecuteNonQuery()
                        End Using
                    Catch sqlCeEx As SqlCeException
                        If sqlCeEx.HResult <> -2147217865 Then
                            DisplaySQLCEErrors(sqlCeEx)
                        End If
                    End Try
                    Try
                        Using cmd As New SqlCeCommand("DROP TABLE rdaCarErrors", cn)

                            cmd.ExecuteNonQuery()
                        End Using
                    Catch sqlCeEx As SqlCeException
                        If sqlCeEx.HResult <> -2147217865 Then
                            DisplaySQLCEErrors(sqlCeEx)
                        End If
                    End Try
                    Try
                        Using cmd As New SqlCeCommand("DROP TABLE rdaObsErrors", cn)

                            cmd.ExecuteNonQuery()
                        End Using
                    Catch sqlCeEx As SqlCeException
                        If sqlCeEx.HResult <> -2147217865 Then
                            DisplaySQLCEErrors(sqlCeEx)
                        End If
                    End Try
                Finally
                    cn.Close()
                End Try
            End Using
        End Sub

        Private Sub textBoxReg_TextChanged(ByVal sender As Object, ByVal e As EventArgs) Handles textBoxReg.TextChanged
            Me.buttonAddCar.Enabled = True
        End Sub

        Private Sub dataGrid1_CurrentCellChanged(ByVal sender As Object, ByVal e As EventArgs) Handles dataGrid1.CurrentCellChanged
            _CurrentCarID = CType((CType(Me.bindingSource1.Current, DataRowView)).Row("CarID"), Integer)
            Me.labelReg.Text = CType((CType(Me.bindingSource1.Current, DataRowView)).Row("Reg"), String)
            FillObsList()
        End Sub

        Private Sub FillObsList()
            'connect to the local database
            Using cn As New SqlCeConnection("Data Source=\My Documents\TrafficRDA.sdf")
                'initialise commands for Obs
                Using cmdObsForCarID As New SqlCeCommand("SELECT ObsID, ObsDateTime, ObsNote FROM Obs WHERE CarID = ? ORDER BY ObsDateTime", cn)
                    cmdObsForCarID.Connection.Open()
                    Try
                        cmdObsForCarID.Parameters.Add("CarID", SqlDbType.Int)
                        cmdObsForCarID.Parameters(0).Value = _CurrentCarID
                        Using rdr As SqlCeDataReader = cmdObsForCarID.ExecuteReader(CommandBehavior.Default)
                            Dim item As ListViewItem
                            While rdr.Read()
                                item = New ListViewItem(rdr.GetInt32(0).ToString())
                                item.SubItems.Add(rdr.GetDateTime(1).ToShortDateString() + " " + rdr.GetDateTime(1).ToShortTimeString())
                                item.SubItems.Add(rdr.GetString(2))
                                listViewObs.Items.Add(item)
                            End While
                            rdr.Close()
                        End Using
                    Finally
                        cmdObsForCarID.Connection.Close()
                    End Try
                End Using
            End Using
        End Sub
    End Class
End Namespace