'______________________________________________________________________________
' 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
'''
''' This class handles my ADO actions. It connects to the DB, inserts/updates/deletes records.
'''
''' Created 1/15/07 by Ron Kessler
Public Class MYADO
'''
''' This method returns all records. It has no arguments.
'''
'''
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
'''
''' This Method updates changes in the DataSet. I accepts no arguments.
'''
'''
'''
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
'''
''' This method deletes a record selected in the datagridview control. It accepts the current Row as integer.
'''
'''
''' CurrentRowSelected is trapped in the Grid_RowEnter Event
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