'______________________________________________________________________________

'                                                  M Y A D O   CLASS

'                                                    Created 1/15/07
'                                                       Ron Kessler
'______________________________________________________________________________

'updated 1/16/07

'   1/16/07  Increased security by using Protected class-level variables and used Friend in Methods
'               so they can be accessed from main form. Felt this was better than Public.
'               Protected means object is visible from the class or derived classes only. Friend includes
'               any class in the assembly.
'               I also made the methods overidable so if you wanted to inherit my class and change functions
'               you can. 

Option Strict On
Option Explicit On


Imports System.Data.SqlClient
Imports ADO_in_Code_with_SQL_Server.Declares

''' <summary>
''' This class handles my ADO actions. It connects to the DB, inserts/updates/deletes records.
''' </summary>
''' <remarks>Created 1/15/07 by Ron Kessler</remarks>



Public Class MYADO

    ''' <summary>
    ''' This method returns all records. It has no arguments.
    ''' </summary>
    ''' <remarks></remarks>


    Protected Friend Overridable Sub GetMyData()

        '---fill the grid with the data
        Try

            myDataSet.Clear()
            dataAdapter.Fill(myDataSet, "customers")
            My.Forms.frmStep3.dgCustomers.DataSource = myDataSet
            My.Forms.frmStep3.dgCustomers.DataMember = "customers"

        Catch ex As Exception
            MsgBox("I could not read the database..." & Err.Description, MsgBoxStyle.Critical, "System Message")
        Finally
            Conn.Close()
        End Try

    End Sub


    ''' <summary>
    ''' This Method updates changes in the DataSet. I accepts no arguments.
    ''' </summary>
    ''' <remarks></remarks>
    ''' 

    Protected Friend Overridable Sub SaveData()

        '---save any changes from the grid editing bit
        Try

            '---you must move to a different record to make the DS accept changes then it updates just fine
            My.Forms.frmStep3.BindingContext(myDataSet.Tables("customers")).Position = 0

            Dim cmd As New SqlCommandBuilder(dataAdapter)      'the commandbuilder creates Inert/Update/Delete commands for us!
            dataAdapter.TableMappings.Clear()                          'must do this or it tries to add my table twice
            dataAdapter.TableMappings.Add("Table", "customers")    'tell the DA we are adding a Table called customers so it knows where to look

            dataAdapter.Update(myDataSet, "customers")               'make it so
            My.Forms.frmStep3.dgCustomers.DataSource = myDataSet
            My.Forms.frmStep3.dgCustomers.DataMember = "customers"   'DataMember = The Table where the data is

            MsgBox("Your data has been updated.", MsgBoxStyle.Information, MSG_TITLE)
        Catch ex As Exception
            MsgBox("I could not save your changes..." & Err.Description, MsgBoxStyle.Critical, MSG_TITLE)

        Finally
            Conn.Close()
        End Try
        GetMyData()


    End Sub


    ''' <summary>
    ''' This method deletes a record selected in the datagridview control. It accepts the current Row as integer.
    ''' </summary>
    ''' <param name="currentRowSelected"></param>
    ''' <remarks>CurrentRowSelected is trapped in the Grid_RowEnter Event</remarks>

    Protected Friend Overridable Sub DeleteRecord(ByVal currentRowSelected As Integer)


        '---note that the currentRowSelected is determined by the DataGrid_RowEnter event. See that event below.

        Try

            Dim customerName As String = ""     'this holds the customers name in the selected record
            Dim numRecords As Integer = 0       'how many records in the DataSet table?


            numRecords = myDataSet.Tables("customers").Rows.Count      'how many records?

            '---get the name from the cell in the selected row. Item = the actual contents of the cell
            customerName = myDataSet.Tables("customers").Rows(currentRowSelected).Item(1).ToString

            '---don't let them select the row used for adding a new record
            If currentRowSelected <= numRecords - 1 Then

                If MessageBox.Show("Are you sure you want to delete " & customerName & "?", MSG_TITLE, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
                    '---now whack it
                    myDataSet.Tables("customers").Rows(currentRowSelected).Delete()

                    Dim cmd As New SqlCommandBuilder(dataAdapter)     'add delete/insert/edit commands to the dataAdapter

                    dataAdapter.TableMappings.Clear()                          'must do this or it tries to add my table twice
                    dataAdapter.TableMappings.Add("Table", "customers") 'tell the DA we are adding a Table called customers so it knows where to look
                    dataAdapter.Update(myDataSet, "customers")                'update the DB itself

                    MsgBox("That customer record has been deleted and the database is updated.", MsgBoxStyle.Information, MSG_TITLE)
                End If
            Else
                MsgBox("Please select an existing record to delete.", MsgBoxStyle.Information, MSG_TITLE)
            End If

        Catch AnyErrors As Exception
            MsgBox("There was an error while deleting this record. " & Err.Description, MsgBoxStyle.Critical, MSG_TITLE)
            Exit Sub
        End Try

        GetMyData()

    End Sub

End Class