Imports Microsoft.VisualBasic
Imports System.Data.Sql
Public Class clskoneksi
    Protected tblkontak = New DataTable
    Protected SQL As String
    Protected cn As OleDb.OleDbConnection
    Protected cmd As OleDb.OleDbCommand
    Protected da As OleDb.OleDbDataAdapter
    Protected ds As DataSet
    Protected dt As DataTable

    Public Function OpenConn() As Boolean
        cn = New OleDb.OleDbConnection("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Password=;Initial Catalog=YYYY;Data Source=XXXXX")
        cn.Open()
        If cn.State <> ConnectionState.Open Then
            Return False
        Else
            Return True
        End If
    End Function

    Public Sub CloseConn()
        If Not IsNothing(cn) Then
            cn.Close()
            cn = Nothing
        End If
    End Sub

    Public Function ExecuteQuery(ByVal Query As String) As DataTable
        If Not OpenConn() Then
            MsgBox("Koneksi Gagal !", MsgBoxStyle.Critical, "Access Fail")
            Return Nothing
            Exit Function
        End If
        cmd = New OleDb.OleDbCommand(Query, cn)
        da = New OleDb.OleDbDataAdapter
        da.SelectCommand = cmd
        ds = New Data.DataSet
        da.Fill(ds)
        dt = ds.Tables(0)
        Return dt
        dt = Nothing
        ds = Nothing
        da = Nothing
        cmd = Nothing
        CloseConn()
    End Function

    Public Sub ExecuteNonQuery(ByVal query As String)
        If Not OpenConn() Then
            MsgBox("Koneksi Gagal !", MsgBoxStyle.Critical, "Access Fail")
            Exit Sub
        End If
        cmd = New OleDb.OleDbCommand
        cmd.Connection = cn
        cmd.CommandType = CommandType.Text
        cmd.CommandText = query
        cmd.ExecuteNonQuery()
        cmd = Nothing
        CloseConn()
    End Sub
End Class