ClassHelper.vb

Imports System.Data
Imports MySql.Data.MySqlClient


Public Class CEmployee

  Inherits CDataAccess
 
    Sub New()
       QueryString = "SELECT * FROM Employees"
       TableName = "Employees"
    End Sub

End Class

Public Class CJobTitle

  Inherits CDataAccess
 
    Sub New()
      QueryString = "SELECT * FROM JobTitles"
      TableName = "JobTitles"
    End Sub

End Class

Public Interface IDataAccess
    Function GetData() As DataTable
    Function Fill(ByRef dt As DataTable) As Integer
    Function Update(ByRef dt As DataTable) As Integer
End Interface


Public Class Class1
    Implements IDataAccess

    Protected QueryString = "SELECT * FROM MyTable"
    Protected TableName = "MyTable"

    Function Fill(ByRef dt As DataTable) As Integer Implements IDataAccess.Fill
        Try
            dt.Clear()
            dt.Merge(Me.GetData())
        Catch ex As Exception
            Throw New ApplicationException("Exception Occured: " & ex.Message)
        End Try
        Return dt.Rows.Count
    End Function

    Function GetData() As DataTable Implements IDataAccess.GetData
        Dim dt As New DataTable
        Try
            Dim cn As New MySqlConnection(My.MySettings.Default.dbSampleConnectionString)
            Dim da As New MySqlDataAdapter(QueryString, cn)
            dt.TableName = TableName
            dt.Clear()
            da.Fill(dt)
        Catch ex As Exception
            Throw New ApplicationException("Exception Occured: " & ex.Message)
        End Try
        Return dt
    End Function

    Function FillByName(ByRef dt As DataTable, ByVal search As String) As Integer
        Try
            dt.Clear()
            dt.Merge(Me.GetDataByName(search))
        Catch ex As Exception
            Throw New ApplicationException("Exception Occured: " & ex.Message)
        End Try
        Return dt.Rows.Count
    End Function

    Function GetDataByName(ByVal search As String) As DataTable
        Dim dt As New DataTable
        Try
            Dim cn As New MySqlConnection(My.MySettings.Default.dbSampleConnectionString)
            Dim da As New MySqlDataAdapter(QueryString & " WHERE koderuang LIKE @p1", cn)
            da.SelectCommand.Parameters.AddWithValue("@p1", "%" & search & "%")
            dt.TableName = TableName
            dt.Clear()
            da.Fill(dt)
        Catch ex As Exception
            Throw New ApplicationException("Exception Occured: " & ex.Message)
        End Try
        Return dt
    End Function

    Function Update(ByRef dt As DataTable) As Integer Implements IDataAccess.Update
        Try
            Dim cn As New MySqlConnection(My.MySettings.Default.dbSampleConnectionString)
            Dim da As New MySqlDataAdapter(QueryString, cn)
            Dim cb As New MySqlCommandBuilder(da)
            da.Update(dt)
        Catch ex As Exception
            Throw New ApplicationException("Exception Occured: " & ex.Message)
        End Try
        Return dt.Rows.Count
    End Function

End Class