by Muhammed Mohiudeen .N (1 Submission)
Category: Miscellaneous
Compatability: VB.NET
Difficulty: Unknown Difficulty
Originally Published: Wed 16th April 2008
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
This Class Helps To Connect To the mysql server database from the vb.net. The Advantage of the Class is based on ur parameter Appropriate
VBcoders
Imports System.IO
Public Class mcMySQL
Dim lcServer As New String("localhost")
Dim lcUserName As New String("root")
Dim lcPassword As New String("bss")
Dim lcDataBase As New String("finance")
Dim myConnection As New MySqlConnection()
Dim myCommand As New MySqlCommand()
Public myReader As MySqlDataReader
Public ReadOnly Property GetConnection() As MySqlConnection
Get
If Not myConnection.State.ToString.Equals("Closed") Then
GetConnection = myConnection
Else
Me.Konnect()
GetConnection = myConnection
End If
End Get
End Property 'GetConnection - Helps to Get the Connection For the Database
Private Sub ConStringSub()
Dim RowVal(4) As String
Dim Row, DirSetting As String
Dim Line1, Line2 As Integer
Try
' Create an instance of StreamReader to read from a file.
DirSetting = Directory.GetCurrentDirectory()
DirSetting = DirSetting & "\settings.ini"
Using SR As StreamReader = New StreamReader(DirSetting)
Line1 = 0
Line2 = 1
' Read and display the lines from the file until end of the file is reached.
Do
Row = SR.ReadLine()
RowVal(Line1) = Row
Line1 += 1
Loop Until Row Is Nothing
SR.Close()
End Using
For Line2 = 1 To 4
Select Case Line2
'Here Concatinate the Connection String in conString
Case 1
lcServer = RowVal(0)
Case 2
lcDataBase = RowVal(1)
Case 3
lcUserName = RowVal(2)
Case 4
lcPassword = RowVal(3)
End Select
Next
'MsgBox(constring)
Catch Err As Exception
MsgBox("Settings file not found.")
End Try
End Sub
Public Overloads Function SqlExec(ByVal ExeString As String) As Boolean
Me.Konnect()
If Not myConnection.State.ToString.Equals("Closed") Then
If ExeString.ToLower().Trim().StartsWith("insert") Or ExeString.ToLower().Trim().StartsWith("delete") Or ExeString.ToLower().Trim().StartsWith("update") Then
myCommand.Connection = myConnection
myCommand.CommandText = ExeString
Try
myCommand.ExecuteNonQuery()
Catch ex As MySqlException
MsgBox("Error in Query Execution :" & ex.Message)
myConnection.Close()
myConnection.Dispose()
End Try
myConnection.Close()
myConnection.Dispose()
Return True
Else
myCommand.Connection = myConnection
myCommand.CommandText = ExeString
Try
myReader = myCommand.ExecuteReader()
Catch ex As MySqlException
MsgBox("Error in Query Execution :" & ex.Message)
myConnection.Close()
myConnection.Dispose()
End Try
Return True
End If
End If
Return False
End Function
Public Overloads Function SqlExec(ByVal ExeString As String()) As Boolean
'-----Comes Here When i Passed a String Array Having many update or delete or insert statements -------
Me.Konnect()
If Not myConnection.State.ToString.Equals("Closed") Then
For z As Integer = 0 To UBound(ExeString) - 1
If ExeString(z).ToLower.Trim().StartsWith("insert") Or ExeString(z).ToLower.Trim().StartsWith("delete") Or ExeString(z).ToLower.Trim().StartsWith("update") Then
myCommand.Connection = myConnection
myCommand.CommandText = ExeString(z)
Try
myCommand.ExecuteNonQuery()
Catch ex As MySqlException
MsgBox("Error in Query Execution :" & ex.Message)
myConnection.Close()
myConnection.Dispose()
End Try
End If
Next
myConnection.Close()
myConnection.Dispose()
Return True
End If
Return False
End Function
Public Overloads Function SqlExec(ByVal ExeArrayList As ArrayList) As Boolean
'-----Comes Here When i Passed a ArrayList Having many update or delete or insert statements -------
Me.Konnect()
If Not myConnection.State.ToString.Equals("Closed") Then
Dim tEnum As IEnumerator = ExeArrayList.GetEnumerator
Dim tString As String
While tEnum.MoveNext
tString = tEnum.Current
If tString.ToLower.StartsWith("insert") OrElse tString.ToLower.StartsWith("delete") OrElse tString.ToLower.StartsWith("update") Then
myCommand.Connection = myConnection
myCommand.CommandText = tString
Try
myCommand.ExecuteNonQuery()
Catch ex As MySqlException
MsgBox("Error in Query Execution :" & ex.Message)
myConnection.Close()
myConnection.Dispose()
End Try
End If
End While
myConnection.Close()
myConnection.Dispose()
Return True
End If
Return False
End Function
Public Overloads Function SqlExec(ByVal ExeString As String(), ByRef myDataTable As DataTable()) As Boolean
'-----Comes Here When i Passed a String Array Having many select statements -------
If UBound(ExeString) = UBound(myDataTable) Then
Me.Konnect()
If Not myConnection.State.ToString.Equals("Closed") Then
For z As Integer = 0 To UBound(ExeString) - 1
If ExeString(z).ToLower.Trim().StartsWith("select") Then
Dim myAdapter As New MySqlDataAdapter(ExeString(z), myConnection)
Try
myAdapter.Fill(myDataTable(z))
Catch ex As MySqlException
MsgBox("Error in Filling Data Table :" & ex.Message)
myConnection.Close()
myConnection.Dispose()
End Try
End If
Next
myConnection.Close()
myConnection.Dispose()
Return True
End If
End If
Return False
End Function
Public Overloads Function SqlExec(ByVal myQuery As String, ByRef myDataTable As DataTable) As Boolean
Me.Konnect()
If Not myConnection.State.ToString.Equals("Closed") Then
Dim myAdapter As New MySqlDataAdapter(myQuery, myConnection)
Try
myAdapter.Fill(myDataTable)
Catch ex As MySqlException
MsgBox("Error in Filling Data Table :" & ex.Message)
myConnection.Close()
myConnection.Dispose()
End Try
myConnection.Close()
myConnection.Dispose()
Return True
End If
Return False
End Function
Private Sub Konnect()
'Connects with the Server
ConStringSub()
If myConnection.State.ToString().ToLower.Equals("open") Then
myConnection.Close()
End If
myConnection.ConnectionString = "server=" & lcServer & ";" _
& "user id=" & lcUserName & ";" _
& "password=" & lcPassword & ";" _
& "database=" & lcDataBase
Try
myConnection.Open()
Catch ex As MySqlException
MsgBox("Error Connecting to Database: " & ex.Message)
myConnection.Dispose()
End Try
End Sub
End Class
No comments have been posted about This Class Helps To Connect To the mysql server database from the vb.net. The Advantage of the Clas. Why not be the first to post a comment about This Class Helps To Connect To the mysql server database from the vb.net. The Advantage of the Clas.