• Home
 · 
• Miscellaneous
 · 
• This Class Helps To Connect To the mysql server database from the vb.net. The Advantage of the Clas

This Class Helps To Connect To the mysql server database from the vb.net. The Advantage of the Clas

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

Rate This Class Helps To Connect To the mysql server database from the vb.net. The Advantage of the Clas

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

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