Imports System.Data.SqlServerCe

Public Class Form1
    Inherits System.Windows.Forms.Form
    Friend WithEvents MainMenu1 As System.Windows.Forms.MainMenu

    Private dataAdapter As SqlCeDataAdapter
    Private dataSet As dataSet
    Private Const db = "\My Documents\Ugi.sdf"
    Private cs As String = "Data Source=" + db
#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

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

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        MyBase.Dispose(disposing)
    End Sub

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents btnFlush As System.Windows.Forms.Button
    Friend WithEvents btnUpdate As System.Windows.Forms.Button
    Friend WithEvents txtAge As System.Windows.Forms.TextBox
    Friend WithEvents txtSurname As System.Windows.Forms.TextBox
    Friend WithEvents txtName As System.Windows.Forms.TextBox
    Friend WithEvents btnLoad As System.Windows.Forms.Button
    Friend WithEvents cmdPull As System.Windows.Forms.Button
    Friend WithEvents cmdPush As System.Windows.Forms.Button
    Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
    Friend WithEvents cmdPushBasic As System.Windows.Forms.Button
    Friend WithEvents cmdPullBasic As System.Windows.Forms.Button
    Private Sub InitializeComponent()
        Me.MainMenu1 = New System.Windows.Forms.MainMenu
        Me.btnFlush = New System.Windows.Forms.Button
        Me.btnUpdate = New System.Windows.Forms.Button
        Me.txtAge = New System.Windows.Forms.TextBox
        Me.txtSurname = New System.Windows.Forms.TextBox
        Me.txtName = New System.Windows.Forms.TextBox
        Me.btnLoad = New System.Windows.Forms.Button
        Me.cmdPull = New System.Windows.Forms.Button
        Me.cmdPush = New System.Windows.Forms.Button
        Me.DataGrid1 = New System.Windows.Forms.DataGrid
        Me.cmdPushBasic = New System.Windows.Forms.Button
        Me.cmdPullBasic = New System.Windows.Forms.Button
        '
        'btnFlush
        '
        Me.btnFlush.Location = New System.Drawing.Point(120, 104)
        Me.btnFlush.Size = New System.Drawing.Size(106, 20)
        Me.btnFlush.Text = "Flush"
        '
        'btnUpdate
        '
        Me.btnUpdate.Location = New System.Drawing.Point(120, 80)
        Me.btnUpdate.Size = New System.Drawing.Size(106, 20)
        Me.btnUpdate.Text = "Update"
        '
        'txtAge
        '
        Me.txtAge.Location = New System.Drawing.Point(8, 104)
        Me.txtAge.Text = "Age"
        '
        'txtSurname
        '
        Me.txtSurname.Location = New System.Drawing.Point(8, 80)
        Me.txtSurname.Text = "Surname"
        '
        'txtName
        '
        Me.txtName.Location = New System.Drawing.Point(8, 56)
        Me.txtName.Text = "Firstname"
        '
        'btnLoad
        '
        Me.btnLoad.Location = New System.Drawing.Point(120, 56)
        Me.btnLoad.Size = New System.Drawing.Size(107, 20)
        Me.btnLoad.Text = "Load"
        '
        'cmdPull
        '
        Me.cmdPull.Location = New System.Drawing.Point(8, 8)
        Me.cmdPull.Size = New System.Drawing.Size(100, 20)
        Me.cmdPull.Text = "Pull"
        '
        'cmdPush
        '
        Me.cmdPush.Location = New System.Drawing.Point(120, 8)
        Me.cmdPush.Size = New System.Drawing.Size(107, 20)
        Me.cmdPush.Text = "Push"
        '
        'DataGrid1
        '
        Me.DataGrid1.Location = New System.Drawing.Point(8, 136)
        Me.DataGrid1.Size = New System.Drawing.Size(216, 120)
        Me.DataGrid1.Text = "DataGrid1"
        '
        'cmdPushBasic
        '
        Me.cmdPushBasic.Location = New System.Drawing.Point(120, 32)
        Me.cmdPushBasic.Size = New System.Drawing.Size(107, 20)
        Me.cmdPushBasic.Text = "Push Basic"
        '
        'cmdPullBasic
        '
        Me.cmdPullBasic.Location = New System.Drawing.Point(8, 32)
        Me.cmdPullBasic.Size = New System.Drawing.Size(100, 20)
        Me.cmdPullBasic.Text = "Pull Basic"
        '
        'Form1
        '
        Me.Controls.Add(Me.cmdPushBasic)
        Me.Controls.Add(Me.cmdPullBasic)
        Me.Controls.Add(Me.DataGrid1)
        Me.Controls.Add(Me.cmdPush)
        Me.Controls.Add(Me.cmdPull)
        Me.Controls.Add(Me.btnFlush)
        Me.Controls.Add(Me.btnUpdate)
        Me.Controls.Add(Me.txtAge)
        Me.Controls.Add(Me.txtSurname)
        Me.Controls.Add(Me.txtName)
        Me.Controls.Add(Me.btnLoad)
        Me.MaximizeBox = False
        Me.Menu = Me.MainMenu1
        Me.MinimizeBox = False
        Me.Text = "RDA"

    End Sub

#End Region

    Private Sub BindText()
        txtName.DataBindings.Clear()
        txtSurname.DataBindings.Clear()
        txtAge.DataBindings.Clear()
        txtName.DataBindings.Add("Text", dataSet.Tables(0), "Firstname")
        txtSurname.DataBindings.Add("Text", dataSet.Tables(0), "Surname")
        txtAge.DataBindings.Add("Text", dataSet.Tables(0), "Age")
    End Sub

    Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click
        Dim cn As New SqlCeConnection(cs)
        Dim commandBuilder As SqlCeCommandBuilder

        dataAdapter = New SqlCeDataAdapter("Select * from UgiUser", cn)
        commandBuilder = New SqlCeCommandBuilder(dataAdapter)

        dataSet = New DataSet
        dataAdapter.Fill(dataSet)
        DataGrid1.DataSource = dataSet.Tables(0)

        BindText()
    End Sub

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        Dim cm As CurrencyManager

        cm = CType(Me.BindingContext(dataSet.Tables(0)), CurrencyManager)
        cm.EndCurrentEdit()
    End Sub

    Private Sub btnFlush_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFlush.Click
        Try
            dataAdapter.Update(dataSet)
            MessageBox.Show("Update complete")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub

    Private Sub cmdPull_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdPull.Click
        Dim rda As New SqlCeRemoteDataAccess
        Dim sCon As String = "Provider=SQLOLEDB.1;User ID=sa;Password=saricky;Initial Catalog=Demo;Data Source=FSDEVNET2"

        rda.InternetUrl = "http://fsdevnet2/SqlCe/sscesa20.dll"
        rda.LocalConnectionString = cs

        DoRdaPull(rda, sCon, "UgiUser", "Select Firstname,Surname,Age from UgiUser")
    End Sub

    Private Function ExecSql(ByVal sql As String) As Boolean
        Dim cn As New SqlCeConnection(cs)
        Dim cmd As New SqlCeCommand(sql, cn)

        cmd.CommandType = CommandType.Text

        Try
            cn.Open()
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            cn.Close()
            Return True
        Catch ex As Exception
            If (cn.State = ConnectionState.Open) Then
                cn.Close()
            End If
            Throw ex
        End Try

        Return False
    End Function

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

        For i = 0 To exc.Errors.Count - 1
            MessageBox.Show("Index #" + i.ToString() + "\n" + "Error: " + exc.Errors(i).Message + "\n")
        Next


    End Sub

    Private Sub cmdPush_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdPush.Click

        Dim rda As New SqlCeRemoteDataAccess
        Dim sCon As String = "Provider=SQLOLEDB.1;User ID=sa;Password=saricky;Initial Catalog=Demo;Data Source=FSDEVNET2"

        rda.InternetUrl = "http://fsdevnet2/SqlCe/sscesa20.dll"
        rda.LocalConnectionString = cs

        DoRdaPush(rda, sCon, "UgiUser")

    End Sub

    Private Sub DoRdaPull(ByVal rda As SqlCeRemoteDataAccess, ByVal sConn As String, ByVal tableName As String, ByVal sql As String)

        If (Not System.IO.File.Exists(db)) Then
            Dim eng As New SqlCeEngine(cs)
            eng.CreateDatabase()
        End If

        Try
            ExecSql("DROP TABLE UgiUser")
        Catch exc As SqlCeException
            DisplaySQLCEErrors(exc)
        Finally

            Try
                rda.Pull(tableName, sql, sConn, RdaTrackOption.TrackingOn, tableName + "Error")
                MessageBox.Show("RDA Pull succeded")
            Catch exc As SqlCeException
                DisplaySQLCEErrors(exc)
            Finally
                rda.Dispose()
            End Try
        End Try
    End Sub

    Private Sub DoRdaPush(ByVal rda As SqlCeRemoteDataAccess, ByVal sConn As String, ByVal tableName As String)


        Try
            rda.Push(tableName, sConn)
            MessageBox.Show("RDA Push succeded")
        Catch exc As SqlCeException
            DisplaySQLCEErrors(exc)
        Finally
            rda.Dispose()
        End Try
    End Sub

    Private Sub cmdPullBasic_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdPullBasic.Click
        Dim rda As New SqlCeRemoteDataAccess
        Dim sCon As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Demo;Data Source=."

        rda.InternetUrl = "http://fsdevnet2/SqlCeBasic/sscesa20.dll"
        rda.LocalConnectionString = cs
        rda.InternetPassword = "Sqlce2004"
        rda.InternetLogin = "sqlUser"

        DoRdaPull(rda, sCon, "UgiUser", "Select Firstname,Surname,Age from UgiUser")
    End Sub

    Private Sub cmdPushBasic_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdPushBasic.Click
        Dim rda As New SqlCeRemoteDataAccess
        Dim sCon As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Demo;Data Source=."

        rda.InternetUrl = "http://fsdevnet2/SqlCeBasic/sscesa20.dll"
        rda.LocalConnectionString = cs
        rda.InternetPassword = "Sqlce2004"
        rda.InternetLogin = "sqlUser"

        DoRdaPush(rda, sCon, "UgiUser")
    End Sub
End Class
