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: enter image description here

Answer 1

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