[VB.NET] MySQL Function to Insert/update/delete with parameters

I wrote this bit of code in a project that I am working on for taking screenshots and uploading them to a remote server.

Basically what this does is it accepts the following parameters:
SQLQuery as a string
Parameters and Values as a string array

I am sure there are probably better ways of doing this however this got the job done in a very little amount of code.

In order to use this you would need to include the MySql dll into your project and then refer to it.

Imports MySql.Data.MySqlClient
Dim connString As String = "server=server.com;database=db_Name;port=3306;user=db_user;password=db_password"
Public Sub SaveUpdateDelete(ByVal sql As String, ByVal parameters() As String, ByVal Values() As String)
	Dim con As MySqlConnection = New MySqlConnection(connString)
 
        con.Open()
        Dim cmd As MySqlCommand = New MySqlCommand(sql, con)
 
        For i = 0 To parameters.Count - 1
            cmd.Parameters.AddWithValue("@" & parameters(i).ToString, Values(i))
        Next
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
 
        con.Close()
End Sub

 

To make use of this function:

Private sub DoDBStuff
	Dim sql As String = "INSERT INTO db_table(userID,imageHash,notes,lastEditedDate) VALUES(@userID,@imageHash,@imageDescription,NOW())"
	Dim params() As String = {"userID", "imageHash", "imageDescription"}
	Dim Values() As String = {DataBase_UserID, DataBase_UserName & "/" & foldername & "/" & HashedFileName & "." & FileNameParts(FileNameParts.Count - 1).ToString, ""}
 
	SaveUpdateDelete(sql, params, Values)
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *