Logo

Imports MySql.Data.MySqlClient
Imports System.Configuration

Public Class MySQLDBAccess
#Region "Private Shared Variables"
Private Shared mySqlConnString As String = String.Empty 'MySQL Connection String
Private Shared mySqlConn As New MySqlConnection 'MySQL Connection
Private Shared mySqlCommand As New MySqlCommand 'MySQL Command
Private Shared mySqlDataAdapter As New MySqlDataAdapter 'MySQL Data Adapter
Private Shared mySqlDataReader As MySqlDataReader 'MySQL Data Reader

#End Region

#Region "Public Shared Properties"

Public Shared Property connectionString() As String
Get
Return mySqlConnString
End Get
Set(ByVal value As String)
mySqlConnString = value
End Set
End Property

#End Region

#Region "Public Shared Functions"

'For Testing Database Connection
Public Shared Sub TestMySQLDBConnection(ByRef strErrMsg As String)

Try
mySqlConn = New MySqlConnection()
mySqlConn.ConnectionString = mySqlConnString

mySqlConn.Open()
mySqlConn.Close()
Catch ex As Exception
strErrMsg = "Cannot connect to DiabNet database."
DBL_PRINTLOG("MySQLDBAccess::TestMySQLDBConnection() >> " & ex.Message)
End Try

End Sub

'For SELECT procedures without parameters
Public Shared Function ExecuteCommand(ByVal mySqlQuery As String, ByRef strErrMsg As String) As DataTable

Dim dtDataTable As New DataTable

TestMySQLDBConnection(strErrMsg)

If strErrMsg = String.Empty Then
Try
mySqlConn = New MySqlConnection()
mySqlCommand = New MySqlCommand()

mySqlConn.ConnectionString = mySqlConnString
mySqlConn.Open()

mySqlCommand.Connection = mySqlConn
mySqlCommand.CommandText = mySqlQuery

mySqlDataAdapter.SelectCommand = mySqlCommand
mySqlDataAdapter.Fill(dtDataTable)

mySqlConn.Close()
Catch mySqlEx As MySqlException
strErrMsg = mySqlEx.Message
DBL_PRINTLOG("MySQLDBAccess::ExecuteCommand() MySQLException >> " & strErrMsg)
Catch ex As Exception
strErrMsg = ex.Message
DBL_PRINTLOG("MySQLDBAccess::ExecuteCommand() Exception >> " & strErrMsg)
Finally
mySqlConn.Dispose()
End Try
End If

Return dtDataTable

End Function

'For SELECT procedures with parameters
Public Shared Function ExecuteCommand(ByVal mySqlQuery As String, ByVal htMySqlParameters As Hashtable, ByRef strErrMsg As String) As DataTable

Dim dtDataTable As New DataTable

TestMySQLDBConnection(strErrMsg)

If strErrMsg = String.Empty Then
Try
mySqlConn = New MySqlConnection()
mySqlCommand = New MySqlCommand()

mySqlConn.ConnectionString = mySqlConnString

mySqlCommand.Connection = mySqlConn
mySqlCommand.CommandText = mySqlQuery

Dim ideEnumerator As IDictionaryEnumerator = htMySqlParameters.GetEnumerator()

While ideEnumerator.MoveNext
mySqlCommand.Parameters.Add("?" & ideEnumerator.Key, ideEnumerator.Value)
End While

mySqlConn.Open()

mySqlDataAdapter.SelectCommand = mySqlCommand
mySqlDataAdapter.Fill(dtDataTable)

mySqlConn.Close()

Catch mySqlEx As MySqlException
strErrMsg = mySqlEx.Message
DBL_PRINTLOG("MySQLDBAccess::ExecuteCommand() MySQLException >> " & strErrMsg)
Catch ex As Exception
strErrMsg = ex.Message
DBL_PRINTLOG("MySQLDBAccess::ExecuteCommand() Exception >> " & strErrMsg)
Finally
mySqlConn.Dispose()
End Try
End If

Return dtDataTable

End Function


'For INSERT, UPDATE, and DELETE PROCEDURES
Public Shared Sub ExecuteCommand(ByVal mySqlQuery As String, ByVal htMySqlParameters As Hashtable, ByVal nTransType As Integer, ByRef strErrMsg As String)

Dim nRowsAffected As Integer = 0

TestMySQLDBConnection(strErrMsg)

If strErrMsg = String.Empty Then
Try
mySqlConn = New MySqlConnection()
mySqlCommand = New MySqlCommand()

mySqlConn.ConnectionString = mySqlConnString

mySqlCommand.Connection = mySqlConn
mySqlCommand.CommandText = mySqlQuery

Dim ideEnumerator As IDictionaryEnumerator = htMySqlParameters.GetEnumerator()

While ideEnumerator.MoveNext
mySqlCommand.Parameters.Add("?" & ideEnumerator.Key, ideEnumerator.Value)
End While

mySqlConn.Open()
nRowsAffected = mySqlCommand.ExecuteNonQuery()

' Transaction Types
' 1 - Insert, 2 - Update, 3 - Delete
If nRowsAffected = 0 Then
If nTransType = 1 Then
strErrMsg = "MySQLDBAccess::ExecuteCommand()::Insert Record Failed"
ElseIf nTransType = 2 Then
strErrMsg = "MySQLDBAccess::ExecuteCommand()::Update Record Failed"
ElseIf nTransType = 3 Then
strErrMsg = "MySQLDBAccess::ExecuteCommand()::Delete Record Failed"
End If
DBL_PRINTLOG(strErrMsg)
End If

mySqlConn.Close()

Catch mySqlEx As MySqlException
strErrMsg = mySqlEx.Message
DBL_PRINTLOG("MySQLDBAccess::ExecuteCommand() MySQLException >> " & strErrMsg)
Catch ex As Exception
strErrMsg = ex.Message
DBL_PRINTLOG("MySQLDBAccess::ExecuteCommand() Exception >> " & strErrMsg)
Finally
mySqlConn.Dispose()
End Try
End If

End Sub
#End Region