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

[1UP DF Member Posts:22 Threads:20 Reputation 0]

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.

Code:

Imports MySql.Data.MySqlClient

Code:

Dim connString As String = "server=server.com;database=db_Name;port=3306;user=db_user;password=db_password"

Code:

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:

Code:

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

[Red Painting the town Red Posts:3,748 Threads:228 Reputation 92]

Very cool man, I appreciate this, it's very helpful. Thanks!