Imports APNSoft.WebControls
Imports System.Data.OleDb

Public Class WebForm1
    Inherits System.Web.UI.Page


    'DataGrid declaration
    Protected dg1 As APNSoftDataGrid


    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'Set the DataGrid properties
        dg1.ID = "dg1"
        dg1.BasePage = Me.Page
        dg1.SkinFolder = "~/Skins/APNSoft/"
        dg1.KeyFieldName = "CustomerID"
        dg1.EditForm.Path = "~/Templates/EditForm.ascx"
        dg1.EditForm.ShadowColor = System.Drawing.ColorTranslator.FromHtml("#C0C0C0")
        dg1.EditForm.ShadowDepth = New System.Web.UI.WebControls.Unit(4)
        dg1.EditForm.ShadowOpacity = 35
        dg1.EditForm.PositionInsert = APNSoft.WebControls.GridEditForm.PositionValues.CenterOfGrid
        dg1.EditForm.PositionUpdate = APNSoft.WebControls.GridEditForm.PositionValues.CenterOfGrid

        dg1.ConfirmTextDelete = "Are you sure?"

        'Apply server-side events
        AddHandler dg1.InsertCommand, _
            New DataGridEventHandler(AddressOf Me.dg1_InsertCommand)

        AddHandler dg1.UpdateCommand, _
            New DataGridEventHandler(AddressOf Me.dg1_UpdateCommand)

        AddHandler dg1.DeleteCommand, _
            New DataGridEventHandler(AddressOf Me.dg1_DeleteCommand)

        'Define SQL query
        Dim SQL As String = "SELECT CustomerID, CompanyName, ContactName, " & _
               "Address FROM Customers ORDER BY CustomerID"

        'Create the DataTable object
        Dim myDataTable As DataTable = GetDataSource(SQL)

        'Add column for UpdateDelete
        Dim UpdateDelete As DataColumn = New DataColumn
        UpdateDelete.ColumnName = "UpdateDelete"
        UpdateDelete.DataType = System.Type.GetType("System.String")
        UpdateDelete.DefaultValue = ""
        myDataTable.Columns.Add(UpdateDelete)

        'Set the data source
        dg1.DataSource = myDataTable
        dg1.DataBind()

        'Set properties for the UpdateDelete column
        dg1.Columns("UpdateDelete").HeaderText = ""
        dg1.Columns("UpdateDelete").Template = "~/Templates/UpdateDelete.htm"
        dg1.Columns("UpdateDelete").Sortable = False

    End Sub


    'Server-side procedure for Insert
    Private Sub dg1_InsertCommand(ByVal sender As Object, ByVal e As DataGridEventArgs)

        'Declare database objects
        Dim conn As OleDbConnection = New OleDbConnection
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
            Server.MapPath("~/DataBase/Nwind.mdb")

        'Create SQL Query
        Dim SQL As String = "INSERT INTO Customers " & _
            "(CustomerID, CompanyName, ContactName, Address) " & _
            "VALUES " & _
            "(@CustomerID, @CompanyName, @ContactName, @Address)"

        'Create command
        Dim cmd As OleDbCommand = New OleDbCommand(SQL, conn)

        'Add parameters
        cmd.Parameters.Add("@CustomerID", OleDbType.VarChar).Value = e.EditFormFields("txtCustomerID").ToString()
        cmd.Parameters.Add("@CompanyName", OleDbType.VarChar).Value = e.EditFormFields("txtCompanyName").ToString()
        cmd.Parameters.Add("@ContactName", OleDbType.VarChar).Value = e.EditFormFields("txtContactName").ToString()
        cmd.Parameters.Add("@Address", OleDbType.VarChar).Value = e.EditFormFields("txtAddress").ToString()

        'Execute the query
        conn.Open()
        Try

            cmd.ExecuteNonQuery()

        Finally

            cmd.Dispose()
            conn.Close()

        End Try

        'Should be specified to select created record
        e.GridRowKeyValue = e.EditFormFields("txtCustomerID").ToString()

    End Sub


    'Server-side procedure for Update
    Private Sub dg1_UpdateCommand(ByVal sender As Object, ByVal e As DataGridEventArgs)

        'Get the row
        Dim myGridRow As GridRow = e.GridRow
        If (myGridRow Is Nothing) Then Return

        'Declare database objects
        Dim conn As OleDbConnection = New OleDbConnection
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
            Server.MapPath("~/DataBase/Nwind.mdb")

        'Create SQL Query
        Dim SQL As String = "UPDATE Customers SET " & _
            "CompanyName=@CompanyName, ContactName=@ContactName, Address=@Address " & _
            "WHERE CustomerID=@CustomerID"

        'Create command
        Dim cmd As OleDbCommand = New OleDbCommand(SQL, conn)

        'Add parameters
        cmd.Parameters.Add("@CompanyName", OleDbType.VarChar).Value = e.EditFormFields("txtCompanyName").ToString()
        cmd.Parameters.Add("@ContactName", OleDbType.VarChar).Value = e.EditFormFields("txtContactName").ToString()
        cmd.Parameters.Add("@Address", OleDbType.VarChar).Value = e.EditFormFields("txtAddress").ToString()
        cmd.Parameters.Add("@CustomerID", OleDbType.VarChar).Value = e.EditFormFields("txtCustomerID").ToString()

        'Execute the query
        conn.Open()
        Try

            cmd.ExecuteNonQuery()

        Finally

            cmd.Dispose()
            conn.Close()

        End Try

    End Sub


    'Server-side procedure for Delete
    Private Sub dg1_DeleteCommand(ByVal sender As Object, ByVal e As DataGridEventArgs)

        'Get the row
        Dim myGridRow As GridRow = e.GridRow
        If (myGridRow Is Nothing) Then Return

        'Declare database objects
        Dim conn As OleDbConnection = New OleDbConnection
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
            Server.MapPath("~/DataBase/Nwind.mdb")

        'Create SQL Query
        Dim SQL As String = "DELETE * FROM Customers WHERE CustomerID = @CustomerID"

        'Create command
        Dim cmd As OleDbCommand = New OleDbCommand(SQL, conn)

        'Add parameters
        cmd.Parameters.Add("@CustomerID", OleDbType.VarChar).Value = myGridRow.Cells("CustomerID").Value

        'Execute the query
        conn.Open()
        Try

            cmd.ExecuteNonQuery()

        Finally

            cmd.Dispose()
            conn.Close()

        End Try

    End Sub


    'Returns a DataTable from MS Access database
    Private Function GetDataSource(ByVal query As String) As DataTable

        Dim conn As OleDbConnection = New OleDbConnection
        Dim myOleDbDataAdapter As OleDbDataAdapter = New OleDbDataAdapter
        Dim myDataTable As DataTable = New DataTable

        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
            System.Web.HttpContext.Current.Server.MapPath("~/DataBase/Nwind.mdb")

        myOleDbDataAdapter.SelectCommand = New OleDbCommand(query, conn)
        conn.Open()

        Try
            myOleDbDataAdapter.Fill(myDataTable)
        Finally
            conn.Close()
        End Try

        Return myDataTable

    End Function



#Region " Web Form Designer Generated Code "

    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
    End Sub

    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        InitializeComponent()
    End Sub

#End Region


End Class