'________________________________________________________________

'                                           A D O   S T U F F

'                                                Ron Kessler
'                                             Created 6/2/07

'________________________________________________________________

'This class defines the ADO objects and makes them available without instantiating this class
'Updated 6/3/07
'   6/3/07    Added insert & delete methods

Imports System.Data
Imports System.Data.OleDb

Public Class myADO
    Private Shared connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & "..\..\customers-03.mdb"
    Friend Shared Conn As New OleDbConnection(connString)
    Private Shared sqlString As String = "SELECT * from Customers ORDER BY LastName"
    Private Shared dataAdapter As New OleDbDataAdapter(sqlString, Conn)
    Private Shared cmdBuilder As New OleDbCommandBuilder(dataAdapter)   'adds insert/delete/update commands to DA
    Friend Shared dataSet As New DataSet

    Friend Shared Function FillDB() As Boolean

        Try

            myADO.dataAdapter.Fill(myADO.dataSet, "customers")             'fill the dataset
            Return True

        Catch ex As Exception
            MsgBox("I could not read the database..." & ex.Message, MsgBoxStyle.Critical, "System Message")
            Return False
        Finally

            myADO.Conn.Close()

        End Try

    End Function


    Friend Shared Function DoUpdate(ByVal NewRecord As Boolean) As Boolean
        Try

            Dim result As Boolean = False

            If NewRecord = True Then
                InsertNewRecord()
            Else

                result = myADO.dataAdapter.Update(myADO.dataSet, "Customers")
                MsgBox("Your data has been updated.", MsgBoxStyle.Information, "System Message")

            End If

            '---refresh
            dataSet.Tables("customers").Clear()
            result = myADO.FillDB
            Return True

        Catch ex As Exception
            MessageBox.Show("An error occurred while updating..." & ex.Message, "System Message", _
                 MessageBoxButtons.OK, MessageBoxIcon.Error)

            Return False
        Finally
            myADO.Conn.Close()

        End Try
    End Function


    Friend Shared Function InsertNewRecord() As Boolean
        Try
            '---define a new row for the dataSet
            Dim newRow As DataRow = dataSet.Tables("customers").NewRow

            '---fill the row with data
            newRow("LastName") = My.Forms.frmMain.txtLast.Text.ToString.Trim
            newRow("FirstName") = My.Forms.frmMain.txtFirst.Text.ToString.Trim
            newRow("StreetAddress") = My.Forms.frmMain.txtStreet.Text.ToString.Trim
            newRow("City") = My.Forms.frmMain.txtCity.Text.ToString.Trim
            newRow("State") = My.Forms.frmMain.txtState.Text.ToString.Trim
            newRow("Zip") = My.Forms.frmMain.txtZip.Text.ToString.Trim

            '---now add the row
            myADO.dataSet.Tables("customers").Rows.Add(newRow)


            Return True
        Catch ex As Exception
            Return False
            
        End Try

    End Function

    Friend Shared Function DoDelete(ByVal whichRecord As Integer) As Boolean

        If MessageBox.Show("Are you sure you want to delete this record?", _
            "Ready to delete...", MessageBoxButtons.YesNo, _
            MessageBoxIcon.Question) = DialogResult.Yes Then

            Dim deleteResult As Boolean = False

            '---gets the current record# from the binding manager in frmMain and deletes it in the DS.
            '    then updates the database
            dataSet.Tables("customers").Rows(whichRecord).Delete()
            deleteResult = myADO.DoUpdate(False)

            If Not deleteResult Then
                MessageBox.Show("An error occurred while deleting that record.", "System Message", _
                     MessageBoxButtons.OK, MessageBoxIcon.Error)

            End If
        End If
    End Function

End Class