Contoh Class Data Access Helper dengan bantuan CommandBuilder untuk generate INSERT/UPDATE/DELETE secara otomatis, disertai dengan contoh penggunaannya di Form1.vb. Update untuk searching yaitu dengan menambahkan TextBox dan Button untuk searching

CEmployee.vb

Imports System.Data
Imports System.Data.SqlServerCe


''' <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>
''' Data Access Layer ke table Employees
''' </summary>
''' <remarks>Data Access Layer ke table Employees, implements IDataAccess</remarks>
Public Class CEmployee
    Implements IDataAccess

    Private QueryString = "SELECT * FROM Employees"

    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 SqlCeConnection(My.MySettings.Default.dbSampleConnectionString)
            Dim da As New SqlCeDataAdapter(QueryString, cn)
            dt.TableName = "Employees"
            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, 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(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(QueryString & " WHERE EmpName LIKE @p1", cn)
            da.SelectCommand.Parameters.AddWithValue("@p1", "%" & search & "%")
            dt.TableName = "Employees"
            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 SqlCeConnection(My.MySettings.Default.dbSampleConnectionString)
            Dim da As New SqlCeDataAdapter(QueryString, 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