DataGridView With BindingSource

''''''''''''''''''''''''''''''''''''''''
' Data Access
''''''''''''''''''''''''''''''''''''''''
Imports System.Data.SqlClient
Namespace Data
    Public Class BankDAL
        Private _Connection As SqlConnection = UtilitiesDAL.Connection

        'Insert
        Public Function Insert(ByVal Name_ar As String, ByVal Name_en As String) As Integer
            Dim BankId As Integer
            Dim cmdInsert As New SqlCommand("gen_Banks_Insert", _Connection)
            Dim prmName_ar As New SqlParameter("@Name_ar", Name_ar)
            Dim prmName_en As New SqlParameter("@Name_en", Name_en)
            With cmdInsert
                .CommandType = CommandType.StoredProcedure
                .Parameters.Add(prmName_ar)
                .Parameters.Add(prmName_en)
            End With 'cmdInsert
            Try
                _Connection.Open()
                BankId = CType(cmdInsert.ExecuteScalar, Integer)
                _Connection.Close()
            Catch ex As Exception
                Throw ex
            Finally
                _Connection.Close()
            End Try
            Return BankId
        End Function

        'Update
        Public Sub Update(ByVal BankId As Integer, ByVal Name_ar As String, ByVal Name_en As String)
            Dim cmdUpdate As New SqlCommand("gen_Banks_Update", _Connection)
            Dim prmBankId As New SqlParameter("@BankId", BankId)
            Dim prmName_ar As New SqlParameter("@Name_ar", Name_ar)
            Dim prmName_en As New SqlParameter("@Name_en", Name_en)
            With cmdUpdate
                .CommandType = CommandType.StoredProcedure
                .Parameters.Add(prmBankId)
                .Parameters.Add(prmName_ar)
                .Parameters.Add(prmName_en)
            End With 'cmdUpdate
            Try
                _Connection.Open()
                cmdUpdate.ExecuteNonQuery()
                _Connection.Close()
            Catch ex As Exception
                Throw ex
            Finally
                _Connection.Close()
            End Try
        End Sub

        'Delete
        Public Sub Delete(ByVal BankId As Integer)
            Dim cmdDelete As New SqlCommand("gen_Banks_Delete", _Connection)
            Dim prmBankId As New SqlParameter("@BankId", BankId)
            With cmdDelete
                .CommandType = CommandType.StoredProcedure
                .Parameters.Add(prmBankId)
            End With 'cmdDelete
            Try
                _Connection.Open()
                cmdDelete.ExecuteNonQuery()
                _Connection.Close()
            Catch ex As Exception
                Throw ex
            Finally
                _Connection.Close()
            End Try
        End Sub

        'This will Get the Bank By ID
        Public Function GetByID(ByVal BankId As Integer) As SqlDataReader
            Dim cmdGetByKey As New SqlCommand("gen_Banks_GetById", _Connection)
            Dim prmBankId As New SqlParameter("@BankId", BankId)
            With cmdGetByKey
                .CommandType = CommandType.StoredProcedure
                .Parameters.Add(prmBankId)
            End With 'cmdGetByKey
            _Connection.Open()
            Return cmdGetByKey.ExecuteReader(CommandBehavior.CloseConnection)
        End Function

        'This will Get all Banks
        Public Function GetAll() As SqlDataReader
            Dim cmdGetAll As New SqlCommand("gen_Banks_GetAll", _Connection)
            With cmdGetAll
                .CommandType = CommandType.StoredProcedure
            End With 'cmdGetAll
            _Connection.Open()
            Return cmdGetAll.ExecuteReader(CommandBehavior.CloseConnection)
        End Function
    End Class
End Namespace

''''''''''''''''''''''''''''''''''''''''
' Entity 
''''''''''''''''''''''''''''''''''''''''
Imports BingoPOS.DataAccess.Data
Namespace Business
    Public Class Bank

        Private _BankId As Integer
        Private _Name_ar As String
        Private _Name_en As String

        Public ReadOnly Property BankID() As Integer
            Get
                Return _BankId
            End Get
        End Property
        Public Property Name_ar() As String
            Get
                Return _Name_ar
            End Get
            Set(ByVal Value As String)
                _Name_ar = Value
            End Set
        End Property
        Public Property Name_en() As String
            Get
                Return _Name_en
            End Get
            Set(ByVal Value As String)
                _Name_en = Value
            End Set
        End Property

        'Default Constructor
        Public Sub New()

        End Sub

        'This constructor will load the  Bank information according to the BankId
        Public Sub New(ByVal BankID As Integer)
            _BankId = BankID
            Dim oDataReader As SqlClient.SqlDataReader
            Dim oBankDAL As New BankDAL
            oDataReader = oBankDAL.GetByID(_BankId)
            If oDataReader.HasRows Then
                While oDataReader.Read
                    _Name_ar = oDataReader("Name_ar")
                    _Name_en = oDataReader("Name_en")
                End While
            End If
            oDataReader.Close()
        End Sub

        'The Construcor will load all of the Bank  member variables.
        Public Sub New(ByVal BankId As Integer, ByVal Name_ar As String, ByVal Name_en As String)
            Me._BankId = BankId
            Me._Name_ar = Name_ar
            Me._Name_en = Name_en
        End Sub

        'Insert
        Public Sub Insert()
            Dim oBankDAL As New BankDAL
            _BankId = oBankDAL.Insert(_Name_ar, _Name_en)
        End Sub

        'Update
        Public Sub Update()
            Dim oBankDAL As New BankDAL
            oBankDAL.Update(_BankId, _Name_ar, _Name_en)
        End Sub

        'Delete
        Public Sub Delete()
            Dim oBankDAL As New BankDAL
            oBankDAL.Delete(_BankId)
        End Sub
    End Class
End Namespace

''''''''''''''''''''''''''''''''''''''''
' Collection
''''''''''''''''''''''''''''''''''''''''
Imports BingoPOS.DataAccess.Data
Imports System.Data.SqlClient

Namespace Business
    Public Class BankCollection
        Inherits CollectionBase

        'The Default Constructor for Bank
        Public Sub New()

        End Sub

        'This will add a new Bank to the Collection
        Public Shadows Sub Add(ByVal oBank As Bank)
            List.Add(oBank)
        End Sub
        'This will remove a Bank from the Collection
        Public Shadows Sub Remove(ByVal oBank As Bank)
            For intIndex As Integer = 0 To List.Count - 1
                If oBank.BankID = CType(List.Item(intIndex), Bank).BankID Then
                    List.Remove(List.Item(intIndex))
                    Exit For
                End If
            Next
        End Sub
 
        'This sub will load the list with the information in the data reader 
        Private Sub LoadListFromDataReader(ByVal oDataReader As SqlDataReader)
            If oDataReader.HasRows Then
                'Make sure to clear the inner list before filling it with the new information
                List.Clear()
                While oDataReader.Read
                    List.Add(New Bank(oDataReader("BankId"), oDataReader("Name_ar"), oDataReader("Name_en")))
                End While
            End If
        End Sub

        'This will load the collection with all records in the table
        Public Sub GetAll()
            Dim oDataReader As SqlClient.SqlDataReader
            Dim oBankDAL As New BankDAL
            oDataReader = oBankDAL.GetAll()
            If oDataReader.HasRows Then
                'Make sure to clear the inner list before filling it with the new information
                List.Clear()
                While oDataReader.Read
                    List.Add(New Bank(oDataReader("BankId"), oDataReader("Name_ar"), oDataReader("Name_en")))
                End While
            End If
            oDataReader.Close()
        End Sub
    End Class
End Namespace


''''''''''''''''''''''''''''''''''''''''
' Form
''''''''''''''''''''''''''''''''''''''''
Imports BingoPOS.Business.Business
Imports System.ComponentModel

Public Class BankSetup
    Private oBankCollection As New BankCollection
    Private oBank As Bank
    Private WithEvents BankBindingSource As BindingSource

    Public Sub New()
        ' This call is required by the Windows Form Designer.
        InitializeComponent()
        ' Add any initialization after the InitializeComponent() call.
        Call FillBanks()
    End Sub

    'Fill DataGridView.
    Private Sub FillBanks()
        oBankCollection = New BankCollection
        oBankCollection.GetAll()
        BankBindingSource = New BindingSource
        BankBindingSource.DataSource = oBankCollection
        Me.grv_banks.DataSource = BankBindingSource
    End Sub



    'Delete Selected Banks.
    Private Sub grv_banks_UserDeletingRow(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowCancelEventArgs) Handles grv_banks.UserDeletingRow
        For Each DataGridViewRow In Me.grv_banks.SelectedRows
            CType(BankBindingSource.Current, Bank).Delete()
        Next
    End Sub
End Class