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"
#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 DataGrid1 As System.Windows.Forms.DataGrid
    Friend WithEvents iPanel As Microsoft.WindowsCE.Forms.InputPanel
    Friend WithEvents cmdCreate 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.DataGrid1 = New System.Windows.Forms.DataGrid
        Me.iPanel = New Microsoft.WindowsCE.Forms.InputPanel
        Me.cmdCreate = New System.Windows.Forms.Button
        '
        'btnFlush
        '
        Me.btnFlush.Location = New System.Drawing.Point(120, 83)
        Me.btnFlush.Size = New System.Drawing.Size(106, 20)
        Me.btnFlush.Text = "Flush"
        '
        'btnUpdate
        '
        Me.btnUpdate.Location = New System.Drawing.Point(120, 59)
        Me.btnUpdate.Size = New System.Drawing.Size(106, 20)
        Me.btnUpdate.Text = "Update"
        '
        'txtAge
        '
        Me.txtAge.Location = New System.Drawing.Point(8, 83)
        Me.txtAge.Text = "Age"
        '
        'txtSurname
        '
        Me.txtSurname.Location = New System.Drawing.Point(8, 59)
        Me.txtSurname.Text = "Surname"
        '
        'txtName
        '
        Me.txtName.Location = New System.Drawing.Point(8, 35)
        Me.txtName.Text = "Firstname"
        '
        'btnLoad
        '
        Me.btnLoad.Location = New System.Drawing.Point(120, 35)
        Me.btnLoad.Size = New System.Drawing.Size(107, 20)
        Me.btnLoad.Text = "Load"
        '
        'DataGrid1
        '
        Me.DataGrid1.Location = New System.Drawing.Point(8, 107)
        Me.DataGrid1.Size = New System.Drawing.Size(216, 144)
        Me.DataGrid1.Text = "DataGrid1"
        '
        'cmdCreate
        '
        Me.cmdCreate.Location = New System.Drawing.Point(8, 8)
        Me.cmdCreate.Size = New System.Drawing.Size(216, 20)
        Me.cmdCreate.Text = "Create"
        '
        'Form1
        '
        Me.Controls.Add(Me.cmdCreate)
        Me.Controls.Add(Me.DataGrid1)
        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 = "Local"

    End Sub

#End Region

    Private Sub txt_GotFocus(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtAge.GotFocus, txtName.GotFocus, txtSurname.GotFocus
        iPanel.Enabled = True
    End Sub

    Private Sub txt_LostFocus(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtAge.LostFocus, txtName.LostFocus, txtSurname.LostFocus
        iPanel.Enabled = False
    End Sub

    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("Data Source=" + db)
        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 cmdCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCreate.Click
        Dim create As String

        If (System.IO.File.Exists(db)) Then
            System.IO.File.Delete(db)
        End If

        Try
            Dim eng As SqlCeEngine
            eng = New SqlCeEngine("Data Source=" + db)
            eng.CreateDatabase()

            create = "CREATE TABLE UgiUser(Firstname nvarchar(10) PRIMARY KEY not null,Surname nvarchar(20) not null,Age int not null)"
            ExecSql(create)
            ExecSql("INSERT INTO UgiUser VALUES('Fabio','Santini',33)")
            ExecSql("INSERT INTO UgiUser VALUES('Mario','Rossi',40)")
            ExecSql("INSERT INTO UgiUser VALUES('Luca','Bianchi',12)")
            ExecSql("INSERT INTO UgiUser VALUES('Giuseppe','Verdi',120)")
            ExecSql("INSERT INTO UgiUser VALUES('Carlo','Rosa',2)")

            MessageBox.Show("Table UgiUser created.")

        Catch exc As SqlCeException
            MessageBox.Show(exc.Message)
        End Try
    End Sub

    Private Function ExecSql(ByVal sql As String) As Boolean
        Dim cn As New SqlCeConnection("Data Source=" + db)
        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
            Return False
        End Try


    End Function
End Class
