Imports System.Data
Imports System.Data.OleDb
Public Class clsData

    Public Function OpenCon() As Boolean
        Try
            gStrCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DataPath & ";Persist Security Info=False"
            gOleCon = New OleDbConnection(gStrCon)
            gOleCon.Open()
        Catch e As Exception
            Return False
        End Try
        Return True
    End Function

    Public Function ExecSql(ByVal strSQL As String) As Boolean
        Try
            Dim sql As String
            Dim oleDbInsertCommand As New OleDbCommand
            oleDbInsertCommand.Connection = gOleCon
            oleDbInsertCommand.CommandText = strSQL
            oleDbInsertCommand.ExecuteNonQuery()
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function
    Public Function ExecSql(ByVal strSQL() As String) As Boolean
        Dim i As Integer
        Dim z As Integer
        Try
            Dim oleDbInsertCommand As New OleDbCommand
            oleDbInsertCommand.Connection = gOleCon
            Dim sql As String
            For i = 0 To strSQL.Length - 1
                '  z = InStr(strSQL(i), ";")
                If strSQL(i) Is Nothing Then
                Else
                    'If z > 0 And z = Strings.Len(strSQL(i)) Then

                    '    oleDbInsertCommand.CommandText = GetPstr(strSQL(i))
                    '    oleDbInsertCommand.ExecuteNonQuery()
                    'End If
                    'If z > 0 And z < Strings.Len(strSQL(i)) Then
                    oleDbInsertCommand.CommandText = strSQL(i)
                    oleDbInsertCommand.ExecuteNonQuery()
                    '    oleDbInsertCommand.CommandText = GetReightPstr(strSQL(i))
                    '    oleDbInsertCommand.ExecuteNonQuery()
                    'End If
                    'If z = 0 Then

                    '    oleDbInsertCommand.CommandText = strSQL(i)
                    '    oleDbInsertCommand.ExecuteNonQuery()
                    'End If
                End If
            Next
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function


    Public Function GetDt(ByVal strSql As String) As DataTable
        Dim Scmd As New OleDbCommand(strSql, gOleCon)
        Dim Sda As New OleDbDataAdapter
        Scmd.CommandTimeout = 100000
        Sda.SelectCommand = Scmd
        If gOleCon.State <> ConnectionState.Open Then
            gOleCon.Open()
        End If
        Dim dst As New DataTable
        Sda.Fill(dst)
        Return dst
    End Function

    Public Function ExecSQL(ByVal dt As DataTable) As Boolean  'Exec sql with ;
        Dim myCommand As New OleDbCommand
        Dim myTrans As OleDbTransaction
        myCommand.Connection = gOleCon
        If gOleCon.State <> ConnectionState.Open Then
            gOleCon.Open()
        End If
        myTrans = gOleCon.BeginTransaction()
        myCommand.Transaction = myTrans
        Try
            Dim i As Integer
            Dim sql As String
            For i = 0 To dt.Rows.Count - 1

                myCommand.CommandText = dt.Rows(i)(0)
                myCommand.ExecuteNonQuery()
            Next
            myTrans.Commit()
            Return True
        Catch ex As Exception
            myTrans.Rollback()
            Return False
        End Try
    End Function
End Class