VB.NET class for MySQL CRUD

Question

I wrote this code for a few basic CRUD (Create, Read, Update, Delete) applications, but I think it can be improved and refactored. I have 3 SELECT type functions(return a table, return a value, return a record), 1 DELETE and 1 INSERT/UPDATE which are all the same setup with ExecuteNonQuery.

Imports MySql.Data.MySqlClient

Public Class VBMySQL
    Public Property Server = "xxx.xxx.xxx.xxx"
    Public Property UserName = "user"
    Public Property Password = "password"
    Public Property Database = "db"

    Public Function Connect_Server() As String
        Connect_Server = "server=" & Server & ";" & "userid=" & UserName & ";" & "password=" & Password & ";" & "database=" & Database
        Return Connect_Server
    End Function
    Public Function Retrieve_Row(SQLStatement As String) As ArrayList
        Dim Result As New ArrayList
        Dim Reader As MySqlDataReader
        Dim MySQL_Connection As New MySqlConnection
        Dim MySQL_Command As New MySqlCommand
        Try
            MySQL_Connection.ConnectionString = Connect_Server()
            MySQL_Command.CommandText = SQLStatement
            MySQL_Command.Connection = MySQL_Connection
            MySQL_Connection.Open()
            Reader = MySQL_Command.ExecuteReader
            While Reader.Read()
                Dim dict As New Dictionary(Of String, Object)
                For count As Integer = 0 To (Reader.FieldCount - 1)
                    dict.Add(Reader.GetName(count), Reader(count))
                Next
                Result.Add(dict)
            End While
            Reader.Close()
            MySQL_Connection.Close()
        Catch ex As Exception
            Console.WriteLine("MySQL retrieve row: " & ex.Message & Err.Number)
        Finally
            MySQL_Connection.Dispose()
            MySQL_Connection = Nothing
            Reader = Nothing
        End Try
        GC.Collect()
        Return Result
    End Function
    Public Function Retrieve_Value(SQLStatement As String) As String
        Dim Result As String
        Dim MySQL_Connection As New MySqlConnection
        Dim MySQL_Command As New MySqlCommand
        Try
            MySQL_Connection.ConnectionString = Connect_Server()
            MySQL_Command.CommandText = SQLStatement
            MySQL_Command.Connection = MySQL_Connection
            MySQL_Connection.Open()
            Result = MySQL_Command.ExecuteScalar()
            MySQL_Connection.Close()
        Catch ex As Exception
            Console.WriteLine("MySQL retrieve value: " & ex.Message & Err.Number)
            Result = Nothing
        Finally
            MySQL_Connection.Dispose()
            MySQL_Connection = Nothing
        End Try
        GC.Collect()
        Return Result
    End Function
    Public Function Retrieve_Table(SQLStatement As String) As DataTable
        Dim table As New DataTable
        Dim Reader As MySqlDataReader
        Dim MySQL_Connection As New MySqlConnection
        Dim MySQL_Command As New MySqlCommand
        Try
            MySQL_Connection.ConnectionString = Connect_Server()
            MySQL_Command.CommandText = SQLStatement
            MySQL_Command.Connection = MySQL_Connection
            MySQL_Connection.Open()
            Reader = MySQL_Command.ExecuteReader
            table.Load(Reader)
            Reader.Close()
            MySQL_Connection.Close()
        Catch ex As Exception
            Console.WriteLine("MySQL retrieve table: " & ex.Message & Err.Number)
            table = Nothing
        Finally
            MySQL_Connection.Dispose()
            MySQL_Connection = Nothing
            Reader = Nothing
        End Try
        GC.Collect()
        Return table
    End Function
    Public Function Insert_Row(SQLStatement As String) As Boolean
        Insert_Row = False
        Dim MySQL_Connection As New MySqlConnection
        Dim MySQL_Command As New MySqlCommand
        Try
            MySQL_Connection.ConnectionString = Connect_Server()
            MySQL_Command.CommandText = SQLStatement
            MySQL_Command.Connection = MySQL_Connection
            MySQL_Connection.Open()
            MySQL_Command.ExecuteNonQuery()
            MySQL_Connection.Close()
            Insert_Row = True
        Catch ex As MySqlException
            Console.WriteLine("MySQL insert: " & ex.Message & Err.Number)
            Insert_Row = False
        Finally
            MySQL_Connection.Dispose()
            MySQL_Connection = Nothing
        End Try
        GC.Collect()
    End Function
    Public Function Delete_Row(SQLStatement As String) As Boolean
        Delete_Row = False
        Dim MySQL_Connection As New MySqlConnection
        Dim MySQL_Command As New MySqlCommand
        Try
            MySQL_Connection.ConnectionString = Connect_Server()
            MySQL_Command.CommandText = SQLStatement
            MySQL_Command.Connection = MySQL_Connection
            MySQL_Connection.Open()
            MySQL_Command.ExecuteNonQuery()
            MySQL_Connection.Close()
            Delete_Row = True
        Catch ex As MySqlException
            Console.WriteLine("MySQL delete: " & ex.Message & Err.Number)
            Delete_Row = False
        Finally
            MySQL_Connection.Dispose()
            MySQL_Connection = Nothing
        End Try
        GC.Collect()
    End Function
End Class

Implementing insert in a simple example where I am not preventing sql injection:

 Private Sub btnCreate_Click(sender As Object, e As EventArgs) Handles btnCreate.Click
        Dim CreateMySQL As New VBMySQL With {.Database = d, .Server = s, .Password = p, .UserName = u}
        Try
            CreateMySQL.Insert_Row("INSERT INTO `Test_Table`(`entry_by`, `test_data`) VALUES('" & txtEntryBy.Text & "','" & txtData.Text & "')")
        Catch ex As Exception
            Console.WriteLine("Creation error: " & ex.Message)
        End Try
    End Sub

Implementing select table into the datagrid:

Private Sub btnRetrieve_Click(sender As Object, e As EventArgs) Handles btnRetrieve.Click
    ReadDataFromDB()
End Sub
Private Sub ReadDataFromDB()
    Dim retrieveTable As New DataTable
    Dim RetrieveMySQL As New VBMySQL With {.Database = d, .Server = s, .Password = p, .UserName = u}
    Try
        retrieveTable = RetrieveMySQL.Retrieve_Table("SELECT * FROM `Test_Table`")
        If retrieveTable.CreateDataReader.HasRows = False Then 
            Console.WriteLine("No records for table")
        End If
        Dim row As DataRow
        dgTest.Rows.Clear()
        For Each row In retrieveTable.Rows
            dgTest.Rows.Add(row("ID_test"), row("entry_by"), Format(row("entry_date"), "MM-dd-yyyy"), row("test_data")) 
        Next
        retrieveTable = Nothing
    Catch ex As Exception
        Console.WriteLine("Retrieve error: " & ex.Message)
    End Try
End Sub

Implementing update in the datagrid

 Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
    Dim UpdateMySQL As New VBMySQL With {.Database = d, .Server = s, .Password = p, .UserName = u}
    Try
        UpdateMySQL.Insert_Row("UPDATE `Test_Table` SET `entry_by` = '" & txtEntryBy.Text & "', `test_data`= '" & txtData.Text & "' WHERE `ID_test` = " & dgTest.SelectedCells.Item(0).Value)
        ReadDataFromDB()
    Catch ex As Exception
        Console.WriteLine("Updating error: " & ex.Message)
    End Try
End Sub

Implementing delete

 Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
    Dim DeleteMySQL As New VBMySQL With {.Database = d, .Server = s, .Password = p, .UserName = u}
    Try
        DeleteMySQL.Delete_Row("DELETE FROM `Test_Table` WHERE `ID_test` = " & dgTest.SelectedCells.Item(0).Value)
        ReadDataFromDB()
    Catch ex As Exception
        Console.WriteLine("Deleting error: " & ex.Message)
    End Try
End Sub

The form for the example application looks like this:

Malachi · Accepted Answer · 2018-04-05 14:56:36Z

You created public properties for

UserName
Password
Server
Database

but you shouldn't allow these things to be editable by other objects

these are things that need to be hidden from everyone except this class, the consumer of this class should not know what these values are, or be allowed to change them without following a strict set of rules defined in the class itself

I think that what you want is a protected property, something that is only available to the class and any class that inherits the class.

the Server property would look like this:

Protected Property Server
    Get
        Return _Server
    End Get
    Set
        _Server = Value
    End Set
End Property

Your function that gets the connection string needs to be renamed, it is not connecting the server all by itself, it is returning a connection string. I suggest: GetConnectionString or something like that

I also believe that this can be done in a single line, and if you can use string interpolation you can get rid of those pesky ampersands too.

Your code:

Public Function Connect_Server() As String
    Connect_Server = "server=" & Server & ";" & "userid=" & UserName & ";" & "password=" & Password & ";" & "database=" & Database
    Return Connect_Server
End Function

after my suggestions:

Public Function GetConnectionString() As String
    Return $"server = {Server};userid={UserName};password{Password};database{Database}"
End Function

And then you can use some using statements to clean up the clean up of the Retrieve_Row function

by doing this you won't have to call the Garbage Collector or close your Connection, Command or Reader. you can also assign the properties of the command with the Object Initializer syntax.

so now the code looks like this:

Public Function Retrieve_Row(SQLStatement As String) As ArrayList
    Try
        Dim result As New ArrayList
        Using MySqlConnection As New MySqlConnection With {.ConnectionString = GetConnectionString()}
            MySqlConnection.Open()
            Using MySqlCommand As New MySqlCommand _
                With {.CommandText = SQLStatement,
                            .Connection = MySqlConnection}
                Using reader = MySqlCommand.ExecuteReader
                    While reader.Read()
                        Dim Dict As New Dictionary(Of String, Object)
                        For count As Integer = 0 To (reader.FieldCount - 1)
                            Dict.Add(reader.GetName(count), reader(count))
                        Next
                        result.Add(Dict)
                    End While
                End Using
            End Using
        End Using
        Return result
    Catch ex As Exception
        Console.WriteLine("MySQL retrieve row: " & ex.Message & Err.Number)
    End Try
End Function

Stack Exchange Network

VB.NET class for MySQL CRUD

1 Answer 1

You must log in to answer this question.

Hot Network Questions

VB.NET class for MySQL CRUD

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions