Contoh generalisasi statusisasi Class Data Access. Pengembangan dari => Class Data Access Helper dengan bantuan CommandBuilder untuk generate INSERT/UPDATE/DELETE secara otomatis, Contoh penggunaan dalam Form dapat dilihat di sini => https://gist.github.com/hidayat365/6521333

CDataAccessInherits.vb

Imports System.Data
Imports System.Data.SqlServerCe
 

''' <summary>
''' Data Access Layer ke table Employees
''' </summary>
''' <remarks>
''' Data Access Layer ke table Employees, 
''' Inherits dari CDataAccess dengan Constructor untuk set table dan query
''' </remarks>
Public Class CEmployee
    Inherits CDataAccess

    Sub New()
        BaseQuery = "SELECT * FROM Employees"
        SelectQuery = "SELECT E.*, J.Name FROM Employees E LEFT JOIN JobTitles J ON E.TitleID=J.ID"
        TableName = "Employees"
    End Sub
End Class


''' <summary>
''' Data Access Layer ke table JobTitles
''' </summary>
''' <remarks>
''' Data Access Layer ke table JobTitles, 
''' Inherits dari CDataAccess dengan Constructor untuk set table dan query
''' </remarks>
Public Class CJobTitle
    Inherits CDataAccess

    Sub New()
        BaseQuery = "SELECT * FROM JobTitles"
        SelectQuery = "SELECT * FROM JobTitles"
        TableName = "JobTitles"
    End Sub
End Class

 
''' <summary>
''' Interface IDataAccess
''' </summary>
''' <remarks>Untuk keseragaman metode akses data</remarks>
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
 
 
''' <summary>
''' Template Data Access Layer dengan default ke table Employees
''' </summary>
''' <remarks>Data Access Layer ke table Employees, implements IDataAccess</remarks>
Public Class CDataAccess
    Implements IDataAccess

    Protected BaseQuery = "SELECT * FROM MyTable"
    Protected SelectQuery = "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 FillByName(ByRef dt As DataTable, 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 GetData() As DataTable Implements IDataAccess.GetData
        Dim dt As New DataTable
        Try
            Dim cn As New SqlCeConnection(My.MySettings.Default.dbSampleConnectionString)
            Dim da As New SqlCeDataAdapter(SelectQuery, 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 GetDataByName(search As String) As DataTable
        Dim dt As New DataTable
        Try
            Dim cn As New SqlCeConnection(My.MySettings.Default.dbSampleConnectionString)
            Dim da As New SqlCeDataAdapter(SelectQuery & " WHERE Name 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

    ''' <summary>
    ''' Function Update()
    ''' menggunakan DataAdapter dan CommandBuilder untuk proses penyimpanan ke database
    ''' saat DataAdapter akan membutuhkan SQL DML insert/update/delete maka CommandBuilder
    ''' yang bertugas menyediakannya, termasuk dengan WHERE clause yang bersesuaian
    ''' </summary>
    ''' <param name="dt">DataTable berisi data yang ingin disimpan ke database</param>
    ''' <returns>Jumlah row dalam DataTable dt</returns>
    ''' <remarks>CommandBuilder cukup canggih untuk me-generate SQL yang tepat</remarks>
    Function Update(ByRef dt As DataTable) As Integer Implements IDataAccess.Update
        Try
            Dim cn As New SqlCeConnection(My.MySettings.Default.dbSampleConnectionString)
            Dim da As New SqlCeDataAdapter(BaseQuery, cn)
            Dim cb As New SqlCeCommandBuilder(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


''' <summary>
''' Form1.vb
''' </summary>
''' <remarks>
''' Contoh penggunaan class
''' Data ditampilkan dalam DataGridView1, kemudia user bebas melakukan perubahan
''' Setelah perubahan selesai user harus click tombol button1 untuk simpan ke database
''' </remarks>
Public Class Form1
 
    ' variable penampung data
    Dim dt As New DataTable
 
    ' data access helper untuk table employees
    Dim emp As New CEmployee
 
    ''' <summary>
    ''' load event handler
    ''' </summary>
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
        RetrieveData()
    End Sub
 
    ''' <summary>
    ''' button1 click event handler
    ''' untuk menyimpan perubahan di grid ke database
    ''' </summary>
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        ' akhiri proses edit databinding
        Me.BindingContext(dt).EndCurrentEdit()
        ' update data
        emp.Update(dt)
        ' accpet perubahan di dataset
        dt.AcceptChanges()
        ' retrieve ulang data agar primary key muncul
        RetrieveData()
    End Sub
 
    ''' <summary>
    ''' search button event handler
    ''' </summary>
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        RetrieveData(TextBox1.Text)
    End Sub
 
    ''' <summary>
    ''' refresh data button event handler
    ''' </summary>
    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        RetrieveData()
    End Sub
 
    ''' <summary>
    ''' fungsi pembantu untuk proses pengambilan data
    ''' tanpa parameter => ambil semua data
    ''' </summary>
    Private Sub RetrieveData()
        RetrieveData(String.Empty)
    End Sub
 
    ''' <summary>
    ''' fungsi pembantu untuk proses pengambilan data
    ''' </summary>
    ''' <param name="search">parameter searching</param>
    Private Sub RetrieveData(search As String)
        ' load data dari database
        If (String.IsNullOrEmpty(search)) Then
            emp.Fill(dt)
        Else
            emp.FillByName(dt, search)
        End If
        ' binding data ke grid
        DataGridView1.DataSource = dt
    End Sub
 
End Class

Cool.....