clsDatabase.vb

 Imports oledb = System.Data.OleDb
Public Class clsDatabase
    Public Class OleDBase
        Dim m_StrConnectionString As String = String.Empty

        Public Enum XmlType As Short
            Normal = 0
            Schema = 1
        End Enum

        Public Sub New(ByVal strConnectionString As String)
            m_StrConnectionString = strConnectionString
        End Sub

        Protected Overrides Sub Finalize()
            MyBase.Finalize()
        End Sub

        Public Property ConnectionString() As String
            Get
                Return m_StrConnectionString
            End Get
            Set(ByVal strValue As String)
                m_StrConnectionString = strValue
            End Set
        End Property

        Public Function Execute(ByVal strQuery As String) As Boolean
            Dim tmpstr() As String = strQuery.Split(";")
            Dim Hasil As Boolean = True
            Dim Koneksi As New oledb.OleDbConnection(m_StrConnectionString)
            For i As Integer = 0 To tmpstr.GetUpperBound(0)
                Dim objCommand As New oledb.OleDbCommand(tmpstr(i), Koneksi)
                If Koneksi.State = System.Data.ConnectionState.Open Or Koneksi.State = ConnectionState.Broken Then Koneksi.Close()
                Koneksi.Open()
                If Trim(tmpstr(i)).Length < 5 Then Exit For
                Dim trans As oledb.OleDbTransaction = Koneksi.BeginTransaction
                objCommand.Connection = Koneksi
                objCommand.Transaction = trans
                Try
                    objCommand.CommandText = Trim(tmpstr(i))
                    objCommand.ExecuteNonQuery()
                    trans.Commit()
                    Koneksi.Close()
                Catch ex As Exception
                    MsgBox(ex.Message)
                    trans.Rollback()
                    Koneksi.Close()
                    Hasil = False
                End Try
            Next
            Koneksi.Dispose()
            Koneksi = Nothing
            Return Hasil
        End Function

        Public Function Ambil_Nilai(ByVal fromField As String, ByVal strQuery As String) As Collection
            Dim Koneksi As New oledb.OleDbConnection(m_StrConnectionString)
            Koneksi.Open()
            Dim objOledbCommand As New System.Data.OleDb.OleDbCommand(strQuery, Koneksi)
            Dim dr As oledb.OleDbDataReader = objOledbCommand.ExecuteReader
            Dim kol As New Collection
            Try
                While dr.Read
                    If dr.HasRows = True Then
                        If Nilai_Dr(dr, fromField).ToString <> "" Then kol.Add(Nilai_Dr(dr, fromField).ToString)
                    End If
                End While
                Koneksi.Close()
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                Koneksi.Close()
            End Try
            If kol.Count = 0 Then kol.Add("")
            Return kol
        End Function

        Public Function Nilai_Dr(ByVal dr As oledb.OleDbDataReader, ByVal nilaifield As String) As String
            Try
                Return IIf(IsDBNull(dr(nilaifield)), "", dr(nilaifield))
            Catch ex As Exception
                Return ""
            End Try
        End Function

#Region "Database To Controls"
        Public Function ToDataGrid(ByVal objDataGrid As DataGridView, ByVal strQuery As String, _
                                 Optional ByVal strTable As String = "") As Boolean
            Try
                Dim Koneksi As New oledb.OleDbConnection(m_StrConnectionString)
                Koneksi.Open()

                Dim objOledbDataAdapter As New oledb.OleDbDataAdapter(strQuery, Koneksi)
                Dim objDataSet As New DataSet

                If strTable = "" Then
                    objOledbDataAdapter.Fill(objDataSet)
                Else
                    objOledbDataAdapter.Fill(objDataSet, strTable)
                End If

                objDataGrid.DataSource = objDataSet.Tables(0)

                Koneksi.Close()
                Koneksi.Dispose()
                objDataSet.Dispose()

                Koneksi = Nothing
                objDataSet = Nothing

                Return True

            Catch ex As Exception
                MsgBox(ex.Message)
                Return False
            End Try

        End Function

        Public Function ToListView(ByVal objListView As ListView, ByVal strQuery As String, _
                          Optional ByVal strTable As String = "", _
                          Optional ByVal intDefautColumSize As Integer = 100) As Boolean
            objListView.View = View.Details
            objListView.FullRowSelect = True
            Try
                Dim Koneksi As New oledb.OleDbConnection(m_StrConnectionString)
                Koneksi.Open()
                Dim objOledbDataAdapter As New oledb.OleDbDataAdapter(strQuery, Koneksi)
                Dim objDataSet As New DataSet

                If strTable = "" Then
                    objOledbDataAdapter.Fill(objDataSet)
                Else
                    objOledbDataAdapter.Fill(objDataSet, strTable)
                End If

                If objDataSet.Tables(0).Rows.Count > 0 Then
                    objListView.Items.Clear()
                    objListView.Columns.Clear()
                    Dim i, y As Integer
                    Dim intColCount As Integer
                    intColCount = objDataSet.Tables(0).Columns.Count - 1

                    For i = 0 To intColCount
                        objListView.Columns.Add(objDataSet.Tables(0).Columns(i).ToString, intDefautColumSize)
                    Next

                    Dim objLVWItem As ListViewItem
                    For i = 0 To objDataSet.Tables(0).Rows.Count - 1
                        If Not IsDBNull(objDataSet.Tables(0).Rows.Item(i).Item(0)) Then
                            objLVWItem = objListView.Items.Add(objDataSet.Tables(0).Rows.Item(i).Item(0).ToString)
                        Else
                            objLVWItem = objListView.Items.Add("")
                        End If

                        For y = 1 To intColCount
                            If Not IsDBNull(objDataSet.Tables(0).Rows.Item(i).Item(y).ToString) Then
                                objLVWItem.SubItems.Add(objDataSet.Tables(0).Rows.Item(i).Item(y).ToString)
                            Else
                                objLVWItem.SubItems.Add("")
                            End If
                        Next
                    Next
                End If
                objListView.Sorting = SortOrder.Ascending
                For i = 0 To objListView.Items.Count - 1
                    If i Mod 2 = 0 Then
                        objListView.Items(i).BackColor = Color.White
                    Else
                        objListView.Items(i).BackColor = Color.LightBlue
                    End If
                Next

                Koneksi.Close()
                objDataSet.Dispose()
                Koneksi.Dispose()
                objOledbDataAdapter.Dispose()

                objDataSet = Nothing
                Koneksi = Nothing
                objOledbDataAdapter = Nothing

                Return True

            Catch ex As Exception
                MsgBox(ex.Message)
                Return False
            End Try
        End Function

        Public Function ToTextBox(ByVal objTextBox As TextBox, _
                                  ByVal strQuery As String, _
                         Optional ByVal strTable As String = "", _
                         Optional ByVal intSepTabs As Integer = 1) As Boolean

            objTextBox.Multiline = True
            Try
                Dim Koneksi As New oledb.OleDbConnection(m_StrConnectionString)
                Koneksi.Open()
                Dim objOledbDataAdapter As New oledb.OleDbDataAdapter(strQuery, Koneksi)
                Dim objDataSet As New DataSet

                If strTable = "" Then
                    objOledbDataAdapter.Fill(objDataSet)
                Else
                    objOledbDataAdapter.Fill(objDataSet, strTable)
                End If

                Dim strTabs As String = String.Empty
                Dim strTemp As String = String.Empty
                Dim x As Integer

                For x = 1 To intSepTabs
                    strTabs &= vbTab
                Next

                If objDataSet.Tables(0).Rows.Count > 0 Then
                    Dim i, y As Integer
                    Dim intColCount As Integer

                    objTextBox.Text = ""

                    intColCount = objDataSet.Tables(0).Columns.Count - 1
                    For i = 0 To intColCount
                        strTemp &= objDataSet.Tables(0).Columns(i).ToString & strTabs
                    Next
                    strTemp &= vbNewLine
                    strTemp &= vbNewLine

                    For i = 0 To objDataSet.Tables(0).Rows.Count - 1
                        If Not IsDBNull(objDataSet.Tables(0).Rows.Item(i).Item(0)) Then
                            strTemp &= objDataSet.Tables(0).Rows.Item(i).Item(0).ToString & strTabs
                        Else
                            strTemp &= " " & strTabs
                        End If
                        For y = 1 To intColCount
                            If Not IsDBNull(objDataSet.Tables(0).Rows.Item(i).Item(y).ToString) Then
                                strTemp &= objDataSet.Tables(0).Rows.Item(i).Item(y).ToString & strTabs
                            Else
                                strTemp &= " " & strTabs
                            End If
                        Next
                        strTemp &= vbNewLine
                    Next
                    objTextBox.Text = strTemp
                End If

                Koneksi.Close()
                objDataSet.Dispose()
                Koneksi.Dispose()
                objOledbDataAdapter.Dispose()

                objDataSet = Nothing
                Koneksi = Nothing
                objOledbDataAdapter = Nothing

                Return True

            Catch ex As Exception
                MsgBox(ex.Message)
                Return False
            End Try

        End Function

        Public Function ToListbox(ByVal objListbox As ListBox, ByVal fromField As String, ByVal strQuery As String) As Boolean
            Dim Koneksi As New oledb.OleDbConnection(m_StrConnectionString)
            Koneksi.Open()
            Dim objOledbCommand As New System.Data.OleDb.OleDbCommand(strQuery, Koneksi)
            Dim dr As oledb.OleDbDataReader = objOledbCommand.ExecuteReader
            Dim tmp As Boolean = False
            Try
                While dr.Read
                    If dr.HasRows = True Then
                        If Nilai_Dr(dr, fromField).ToString <> "" Then objListbox.Items.Add(Nilai_Dr(dr, fromField).ToString)
                    End If
                End While
                Koneksi.Close()
                tmp = True
            Catch ex As Exception
                MsgBox(ex.Message)
                tmp = False
            Finally
                Koneksi.Close()
            End Try
            Return tmp
        End Function

        Public Function ToCombobox(ByVal objCombobox As ComboBox, ByVal fromField As String, ByVal strQuery As String) As Boolean
            Dim Koneksi As New oledb.OleDbConnection(m_StrConnectionString)
            Koneksi.Open()
            Dim objOledbCommand As New System.Data.OleDb.OleDbCommand(strQuery, Koneksi)
            Dim dr As oledb.OleDbDataReader = objOledbCommand.ExecuteReader
            Dim tmp As Boolean = False
            Try
                While dr.Read
                    If dr.HasRows = True Then
                        If Nilai_Dr(dr, fromField).ToString <> "" Then objCombobox.Items.Add(Nilai_Dr(dr, fromField).ToString)
                    End If
                End While
                If objCombobox.Items.Count > 0 Then objCombobox.Text = objCombobox.Items.Item(0)
                Koneksi.Close()
                tmp = True
            Catch ex As Exception
                MsgBox(ex.Message)
                tmp = False
            Finally
                Koneksi.Close()
            End Try
            Return tmp
        End Function

        Public Function ToDataSet(ByVal strQuery As String) As DataSet
            On Error Resume Next
            Dim Koneksi As New oledb.OleDbConnection(m_StrConnectionString)
            Koneksi.Open()
            Dim objOledbDataAdapter As New oledb.OleDbDataAdapter(strQuery, Koneksi)
            Dim objDataSet As New DataSet

            objOledbDataAdapter.Fill(objDataSet)
            Koneksi.Close()
            Return objDataSet
        End Function
#End Region

#Region "Enumerate"
        Public Function EnumerateTables() As Collection
            Dim tmp As New Collection
            Try
                Dim koneksi As New oledb.OleDbConnection(m_StrConnectionString)
                koneksi.Open()

                Dim dbTableSchema As DataTable = koneksi.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})

                Dim int As Integer
                For int = 0 To dbTableSchema.Rows.Count - 1
                    tmp.Add(dbTableSchema.Rows(int)!TABLE_NAME.ToString())
                Next

                dbTableSchema.Dispose() : dbTableSchema = Nothing
                koneksi.Close() : koneksi.Dispose() : koneksi = Nothing
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

            If tmp.Count < 1 Then tmp.Add("")
            Return tmp

        End Function

        Public Function EnumerateFields(ByVal namaTable As String) As Collection
            If namaTable = "" Then
                Return Nothing
                Exit Function
            End If

            Dim tmp As New Collection
            Try
                Dim koneksi As New oledb.OleDbConnection(m_StrConnectionString)
                koneksi.Open()

                Dim objOleDbDataAdapter As New oledb.OleDbDataAdapter("SELECT * FROM " & namaTable, koneksi)
                Dim tmp2 As New DataTable

                objOleDbDataAdapter.FillSchema(tmp2, SchemaType.Source)

                For Each Column As DataColumn In tmp2.Columns
                    tmp.Add(Column.ColumnName & " (" & Column.DataType.Name & ")")
                Next
                koneksi.Close() : koneksi.Dispose()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
            If tmp.Count < 1 Then tmp.Add("")
            Return tmp
        End Function

        Public Function EnumerateRecord(ByVal namaTable As String) As Integer
            If namaTable = "" Then
                Return Nothing
                Exit Function
            End If
            Dim tmp As Integer
            Try
                tmp = CType(Ambil_Nilai("ukur", "Select count(*) as ukur from [" & namaTable & "]").Item(1), Integer)
            Catch ex As Exception
            End Try
            Return tmp
        End Function
#End Region
    End Class
End Class

Project Homepage: