SQL interpreter

sqlInterpreter.vb

Imports MySql.Data.MySqlClient


Public Class Form1

    Dim prevConnection As String = "kryssel"

    Private Sub loadDatabases()

        rtbLogs.AppendText("Loading databases" + vbNewLine)

        Dim getConnection As New MySqlConnection

        getConnection.ConnectionString = "server = localhost; uid = root; database = " + prevConnection + "; password = kryssel2821"


        Dim tableModel As DataTable


        Try
            getConnection.Open()

            Dim sqlCommand As MySqlCommand = getConnection.CreateCommand()
            Dim sqlResultSet As MySqlDataReader


            sqlCommand.CommandText = "SHOW DATABASES;"

            sqlResultSet = sqlCommand.ExecuteReader()

            trTables.Nodes.Add(New TreeNode("Databases"))


            Dim count As Integer = 0

            While sqlResultSet.Read()

                trTables.Nodes(0).Nodes.Add(New TreeNode(sqlResultSet.GetString("Database")))

                Dim getConnection2 As MySqlConnection = New MySqlConnection("uid = root; database = " + prevConnection + "; password = kryssel2821; server = localhost")

                Try

                    getConnection2.Open()

                    Dim sqlCommand2 As MySqlCommand = getConnection2.CreateCommand()

                    sqlCommand2.CommandText = "SHOW TABLES FROM " + sqlResultSet.GetString("Database")

                    Dim sqlResultSet2 As MySqlDataReader = sqlCommand2.ExecuteReader()


                    While sqlResultSet2.Read()

                        trTables.Nodes(0).Nodes(count).Nodes.Add(New TreeNode(sqlResultSet2.GetString("Tables_in_" + sqlResultSet.GetString("Database"))))

                    End While

                    count += 1

                Catch er As MySqlException
                    MsgBox(er.Message)
                End Try

                getConnection2.Close()

            End While

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        getConnection.Close()

        rtbLogs.AppendText("Successfully load database " + "kryssel" + vbNewLine)

    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        loadDatabases()
    End Sub

    Private Sub loadColumns(ByVal databaseName As String, ByVal tableName As String, ByRef node As TreeNode)

        rtbLogs.AppendText("load columns of table " + tableName + " from " + databaseName + vbNewLine)

        Dim getDatabaseConnection As MySqlConnection = New MySqlConnection()

            getDatabaseConnection.ConnectionString = "uid = root; password = kryssel2821; database = " + databaseName + "; server = localhost"

            Try

                getDatabaseConnection.Open()

                Dim sqlCommand As MySqlCommand = getDatabaseConnection.CreateCommand()

                sqlCommand.CommandText = "SHOW COLUMNS FROM " + tableName

                Dim sqlResultSet As MySqlDataReader = sqlCommand.ExecuteReader()

                While sqlResultSet.Read()

                    node.Nodes.Add(sqlResultSet.GetString("Field"))

                End While

            Catch ex As MySqlException

                MsgBox(ex.Message)

            End Try

            getDatabaseConnection.Close()

        rtbLogs.AppendText("sucesss" + vbNewLine)
    End Sub

    Sub loadDataTable(ByVal databaseName As String, ByVal tableName As String)

        rtbLogs.AppendText("load data in table" + tableName + " from " + databaseName + vbNewLine)

        Dim getConnection As MySqlConnection = New MySqlConnection()

        Dim tableModel As DataTable = New DataTable()

        Dim getConnectionOnTable As MySqlConnection = New MySqlConnection()

        getConnection.ConnectionString = "uid = root; password = kryssel2821; " +
                                         "database = " + databaseName + "; server = localhost"

        Try

            getConnection.Open()

            Dim sqlCommand As MySqlCommand = getConnection.CreateCommand()

            sqlCommand.CommandText = "SHOW COLUMNS FROM " + tableName

            Dim sqlResultSet As MySqlDataReader = sqlCommand.ExecuteReader()

            While sqlResultSet.Read()

                tableModel.Columns.Add(sqlResultSet.GetString("Field"))

            End While

            sqlResultSet.Close()


            sqlCommand.CommandText = "SELECT * FROM " + tableName
            sqlResultSet = sqlCommand.ExecuteReader()

            While sqlResultSet.Read()
                Dim rowModel As DataRow = tableModel.NewRow()

                For columnIndex = 0 To tableModel.Columns.Count - 1
                    rowModel(columnIndex) = sqlResultSet.GetString(tableModel.Columns(columnIndex).ColumnName)
                Next

                tableModel.Rows.Add(rowModel)

            End While

            resultTable.DataSource = tableModel

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        getConnectionOnTable.Close()

        rtbLogs.AppendText("successfully load data" + vbNewLine)

    End Sub

    Private Sub trTables_NodeMouseDoubleClick(sender As Object, e As TreeNodeMouseClickEventArgs) Handles trTables.NodeMouseDoubleClick
        Dim getNode As TreeNode = trTables.SelectedNode

        If getNode.Level = 2 Then

            loadColumns(getNode.Parent.Text,
                    getNode.Text,
                    getNode)

            loadDataTable(getNode.Parent.Text,
                          getNode.Text)
        ElseIf getNode.Level = 1 Then
            rtbLogs.AppendText("Connected to " + getNode.Text + vbNewLine)
            prevConnection = getNode.Text
        End If



    End Sub

    Private Sub btnExecute_Click(sender As Object, e As EventArgs) Handles btnExecute.Click
        Dim getConnection As MySqlConnection = New MySqlConnection()

        getConnection.ConnectionString = "uid = root; password = kryssel2821; database = " + prevConnection + "; server = localhost"

        Try
            getConnection.Open()

            Dim sqlCommand As MySqlCommand = getConnection.CreateCommand()

            sqlCommand.CommandText = rtbCommandArea.Text

            Dim numberOfRowsAffected As Integer = sqlCommand.ExecuteNonQuery()

            rtbLogs.AppendText("number of rows affected " + CStr(numberOfRowsAffected) + vbNewLine)

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

        getConnection.Close()

        loadDatabases()


    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim getConnection As MySqlConnection = New MySqlConnection()

        getConnection.ConnectionString = "uid = root; password = kryssel2821; database = " + prevConnection + "; server = localhost"

        Try
            getConnection.Open()

            Dim sqlCommand As MySqlCommand = getConnection.CreateCommand()

            sqlCommand.CommandText = rtbCommandArea.Text

            Dim sqlResultSet As MySqlDataReader = sqlCommand.ExecuteReader()

            Dim tableModel As DataTable = New DataTable()

            For columnCount = 0 To sqlResultSet.FieldCount - 1
                tableModel.Columns.Add(sqlResultSet.GetName(columnCount))
            Next

            While sqlResultSet.Read()

                Dim rowModel As DataRow = tableModel.NewRow()

                For columnCount = 0 To sqlResultSet.FieldCount - 1

                    rowModel(columnCount) = sqlResultSet.GetString(sqlResultSet.GetName(columnCount))

                Next

                tableModel.Rows.Add(rowModel)

            End While

            resultTable.DataSource = tableModel

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

        getConnection.Close()
    End Sub
End Class