Desktop_WT_DMS/WT-DMS/CMSQL_Interface.vb

Imports MySql.Data.MySqlClient
Public Class CMSQL_Interface
    Dim m_SqlConn As MySqlConnection = Nothing
    Dim m_SqlAdapter As MySqlDataAdapter
    Dim m_DTable As New DataTable
    Dim m_BaseName As String = ""
    Public Function ConnectionSetting(ByVal strHostIp As String, ByVal DataBaseName As String, ByVal user As String, ByVal password As String) As Boolean
        If m_SqlConn Is Nothing Then
            m_SqlConn = New MySqlConnection
        Else
            m_SqlConn.Close()
        End If

        Dim str As String = "server=" & strHostIp & ";database=" & DataBaseName & ";uid=" & user & ";pwd=" & password & ";SslMode=none;CharSet=utf8mb4;PORT=3307"
        m_SqlConn.ConnectionString = str

        m_BaseName = DataBaseName
        Return (True)
    End Function

    Public Function Query(ByVal strQuery As String, ByRef r_Table As DataTable) As Boolean
        Dim Result As Boolean = False
        Try
            m_SqlConn.Open()
            m_SqlAdapter = New MySqlDataAdapter(strQuery, m_SqlConn)
            r_Table.Clear()
            m_SqlAdapter.Fill(r_Table)

            Result = True
        Catch ex As Exception
            Dim msg = ex.Message
        End Try

        Try
            m_SqlConn.Close()
        Catch ex As Exception
        End Try

        Return Result
    End Function

    Public Function Update(ByRef upTable As DataTable) As Boolean
        Dim Result As Boolean = True
        Dim myCommandBuilder As New MySql.Data.MySqlClient.MySqlCommandBuilder(m_SqlAdapter)

        m_SqlAdapter.DeleteCommand = myCommandBuilder.GetDeleteCommand
        m_SqlAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand
        m_SqlAdapter.InsertCommand = myCommandBuilder.GetInsertCommand

        Try
            m_SqlAdapter.Update(upTable)
        Catch ex As Exception
            Result = False
        End Try
        myCommandBuilder.Dispose()

        Return Result
    End Function

    'Public Function Query(ByVal strQuery As String, ByVal colName As String, ByRef r_DataSet As DataSet) As Boolean
    '    Dim Result As Boolean = False
    '    Try
    '        m_SqlConn.Open()
    '        Dim daAdapter As MySqlDataAdapter = New MySqlDataAdapter(strQuery, m_SqlConn)
    '        daAdapter.Fill(r_DataSet, colName)

    '        Result = True
    '    Catch ex As Exception
    '    End Try

    '    Try
    '        m_SqlConn.Close()
    '    Catch ex As Exception
    '    End Try

    '    Return Result
    'End Function

    Public Function ExeCommand(ByVal strExe As String) As Boolean
        Dim Result As Boolean = False
        Dim myCommand As MySqlCommand = Nothing
        Try
            m_SqlConn.Open()
            myCommand = New MySqlCommand(strExe, m_SqlConn)
            myCommand.ExecuteNonQuery()
            Result = True
        Catch ex As Exception
            Dim msg = ex.Message
        End Try

        Try
            myCommand.Dispose()
        Catch ex As Exception
        End Try
        Try
            m_SqlConn.Close()
        Catch ex As Exception
        End Try

        Return Result
    End Function


    Public Function ExeCommandWithParamters(ByVal strExe As String, ByRef ParamName() As String, ByRef ParamValue() As Object) As Boolean
        Dim Result As Boolean = False
        Dim myCommand As MySqlCommand = Nothing
        Try
            m_SqlConn.Open()
            myCommand = New MySqlCommand(strExe, m_SqlConn)
            For i As Integer = 0 To ParamName.Length - 1
                myCommand.Parameters.AddWithValue(ParamName(i), ParamValue(i))
            Next

            myCommand.ExecuteNonQuery()
            Result = True
        Catch ex As Exception
        End Try

        Try
            myCommand.Dispose()
        Catch ex As Exception
        End Try
        Try
            m_SqlConn.Close()
        Catch ex As Exception
        End Try

        Return Result
    End Function

    Public Function DataReader(ByVal strSelect As String, ByRef valueTable() As Object) As Boolean
        Dim Result As Boolean = False
        Dim myCommand As MySqlCommand = Nothing
        Try
            m_SqlConn.Open()
            myCommand = New MySqlCommand(strSelect, m_SqlConn)
            Dim rData As MySql.Data.MySqlClient.MySqlDataReader
            rData = myCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
            rData.Read()

            Dim objArray(rData.FieldCount - 1) As Object
            For i As Integer = 0 To rData.FieldCount - 1
                objArray(i) = rData.Item(i)
            Next

            valueTable = objArray

            Result = True
        Catch ex As Exception
        End Try

        Try
            myCommand.Dispose()
        Catch ex As Exception
        End Try
        Try
            m_SqlConn.Close()
        Catch ex As Exception
        End Try

        Return Result
    End Function

    Public Function TransactionExeCommand(ByVal strExe As String) As String
        Dim Result As String = ""
        Dim myCommand As MySqlCommand = Nothing
        Try
            m_SqlConn.Open()
            myCommand = New MySqlCommand(strExe, m_SqlConn)
            myCommand.ExecuteNonQuery()
            'myCommand.Transaction.Commit()
            'Result = True
        Catch ex As Exception
            Try
                myCommand.Transaction.Rollback()
                myCommand.Dispose()
                m_SqlConn.Close()
            Catch ex1 As Exception

            End Try

            Result = ex.Message
            Return Result
        End Try

        Try
            myCommand.Dispose()
            m_SqlConn.Close()
        Catch ex As Exception
        End Try


        Return Result
    End Function


    'Public Function Insert(ByVal strInsert As String) As Boolean
    '    Return ExeCommand(strInsert)
    'End Function

    'Public Function Update(ByVal strUpdate As String) As Boolean
    '    Return ExeCommand(strUpdate)
    'End Function

    'Public Function Delete(ByVal strDelete As String) As Boolean
    '    Return ExeCommand(strDelete)
    'End Function
End Class