Imports System
Imports System.Data
Imports System.Data.SqlClient

Public Class frmPerson
    Private WithEvents ColumnPicture As New DataGridViewImageColumn()
    Dim SqlCon As New SqlConnection("Data Source=IIS-PC\SQLEXPRESS;Initial Catalog=Sample;Persist Security Info=True;User ID=sa;Password=skj")
    Dim Sda As New SqlDataAdapter
    Dim Ds As New DataSet
    Dim Dr As SqlDataReader

    Private Sub frmPerson_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Timer1.Enabled = True
        Call Binding()
        Me.DataGridView1.Columns(0).Width = 60
        Me.DataGridView1.Columns(1).Width = 150
        Me.DataGridView1.Columns(2).Width = 330
    End Sub

    Private Sub Clear()
        txtID.Clear()
        txtName.Clear()
        txtPath.Clear()
        pic1.BackColor = Color.Azure
        txtName.Focus()
    End Sub

    Private Sub Binding()
        Dim Cmd As New SqlCommand("SELECT * FROM Person ORDER BY ID", sqlCon)
        Sda.SelectCommand = Cmd
        Ds.Clear()
        Sda.Fill(Ds, "Person")
        Me.DataGridView1.DataSource = Ds.Tables("Person")

        txtID.DataBindings.Clear()
        txtName.DataBindings.Clear()
        txtPath.DataBindings.Clear()
        txtID.DataBindings.Add("text", Ds.Tables("Person"), "ID")
        txtName.DataBindings.Add("text", Ds.Tables("Person"), "FullName")
        txtPath.DataBindings.Add("text", Ds.Tables("Person"), "ImagePath")
        pic1.ImageLocation = (txtPath.Text.Trim)
    End Sub

    Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
        Call Clear()
        btnNew.Enabled = False
        btnSave.Enabled = True
        txtID.Text = Me.DataGridView1.Rows.GetRowCount(DataGridViewElementStates.Visible)
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim Cmd As New SqlCommand("INSERT INTO Person (ID,FullName,ImagePath) VALUES (@ID,@FullName,@ImagePath)", sqlCon)
        Sda.InsertCommand = Cmd
        With Cmd
            .Parameters.AddWithValue("ID", txtID.Text.Trim)
            .Parameters.AddWithValue("FullName", txtName.Text.Trim)
            .Parameters.AddWithValue("ImagePath", txtPath.Text.Trim)
        End With
        Try
            sqlCon.Open()
            Cmd.ExecuteNonQuery()
            sqlCon.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            Call Binding()
            btnSave.Enabled = False
            btnNew.Enabled = True
        End Try
    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Application.Exit()
    End Sub

    Private Sub btnLoadImage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoadImage.Click
        Dim Ofd As New OpenFileDialog
        Try
            With Ofd
                .Title = "Open Image File"
                .DefaultExt = ".jpg"
                .InitialDirectory = "\Sample_SQL_App\Sample SQL App\Images"
                .Filter = "JPEG FIles|*.jpg|Bitmap Files|*.bmp|GIF Files|*.gif|All Files|*.*"
                If .ShowDialog = Windows.Forms.DialogResult.OK Then
                    pic1.Image = Image.FromFile(.FileName)
                End If
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            txtPath.Text = CType(Ofd.FileName.Trim, String)
            pic1.BackColor = Color.Azure
        End Try
    End Sub

    Private Sub DataGridView1_CellMouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseClick
        Try
            Dim i As Integer = Me.DataGridView1.CurrentRow.Index
            For i = 0 To Me.DataGridView1.Rows.GetNextRow(0, DataGridViewElementStates.Visible)
                pic1.ImageLocation = txtPath.Text.Trim
            Next i
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Dim Cmd As New SqlCommand("DELETE FROM Person WHERE ID =" & txtID.Text.Trim, sqlCon)
        Sda.DeleteCommand = Cmd
        Try
            sqlCon.Open()
            Cmd.ExecuteNonQuery()
            sqlCon.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            Call Binding()
            My.Computer.Audio.PlaySystemSound(Media.SystemSounds.Hand)
        End Try
    End Sub

    Private Sub btnModify_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnModify.Click
        Dim Cmd As New SqlCommand("UPDATE Person SET ID=@ID,FullName=@FullName,ImagePath=@ImagePath WHERE ID=" & txtID.Text.Trim, SqlCon)
        Sda.InsertCommand = Cmd
        With Cmd
            .Parameters.AddWithValue("ID", txtID.Text.Trim)
            .Parameters.AddWithValue("FullName", txtName.Text.Trim)
            .Parameters.AddWithValue("ImagePath", txtPath.Text.Trim)
        End With
        Try
            SqlCon.Open()
            Cmd.ExecuteNonQuery()
            SqlCon.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            Call Binding()
        End Try
    End Sub

    Private Sub btnWriteXML_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnWriteXML.Click
        Dim Cmd As New SqlCommand("SELECT * FROM Person ORDER BY ID", SqlCon)
        Sda.SelectCommand = Cmd
        Ds.Clear()
        Sda.Fill(Ds, "Person")
        Try
            Ds.WriteXml("Person.xml", XmlWriteMode.IgnoreSchema)
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            MsgBox("Successfully created the xml file.", MsgBoxStyle.Information, "Person")
            frmReport.Show()
        End Try
    End Sub

    Private Sub pic1_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles pic1.MouseClick
        Dim uc As New PictureZoomer
        uc.picZoom.ImageLocation = Me.txtPath.Text.Trim
        uc.ShowDialog()
    End Sub

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        Label5.Text = Now.ToLongTimeString
    End Sub
End Class
