Windows App: Using MySQL and stored procedures

Recently I wrote on how to connect the Win Form app with a Web Server MySQL, and there I wrote a very simple SQL command to load data into DataGridView. Now I’ll show you how to use stored procedures, to manipulate those data.

A Stored Procedures are well known if you’re using MS SQL. However, MySQL uses the same thing with a different name.

Stored Procedures inside MySQL: MySQL supports stored routines (procedures and functions). A stored routine is a set of SQL statements that can be stored in the server. Once this has been done, clients don’t need to keep reissuing the individual statements but can refer to the stored routine instead.

I’m going to explain a simple usage of stored procedures with a Windows app and remote Database running on a MySQL Web Server.

For this tutorial, I’ll use the same database from the previous post, and do a few adjustments. I’m going to create one TextBox, and a Button to call for a stored procedure created inside the MySQL database.

Keep reading and learn: How to use a Stored Procedures within MySQL database

Like I wrote, here we are going to use an already prepared database with a table and some dummy data. To execute a stored procedure, I’m going to use a TextBox1 (as a Parameter) and Button1 (to execute sp_deleteuser).

Button click code

 Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        If TextBox1.Text = "" Or String.IsNullOrEmpty(TextBox1.Text) Then
            MessageBox.Show("Please select data you want to delete")
        Else
            ' call for stored procedure sp_deleteuser with uID parameter 
            Dim cmd As MySqlCommand = mysqlconn.CreateCommand
            mysqlconn.Open()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add(New MySqlParameter("@uID", (TextBox1.Text)))
            cmd.CommandText = "sp_deleteuser"
            Try
                cmd.ExecuteNonQuery()
                MessageBox.Show("User ID " & TextBox1.Text & " Deleted successfully")
                TextBox1.Text = ""
            Catch ex As MySqlException
                MessageBox.Show(ex.Message.ToString(), "Error Message")
            End Try
            cmd.Connection = mysqlconn
            Dim rdr As MySqlDataReader = cmd.ExecuteReader
            Dim dt As New DataTable
            dt.Load(rdr)
            rdr.Close()
            DataGridView1.DataSource = dt
            mysqlconn.Close()
        End If
    End Sub

DataGridView cell click code

Private Sub DataGridView1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        If e.RowIndex >= 0 Then
            Dim row As DataGridViewRow
            row = Me.DataGridView1.Rows(e.RowIndex)
            TextBox1.Text = row.Cells("ID").Value.ToString

        End If
    End Sub

A simple SQL query to create a Routine (stored procedure)

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_deleteuser`(IN `uID` INT UNSIGNED)
    NO SQL
    SQL SECURITY INVOKER
BEGIN
DELETE FROM test_table WHERE ID = uID;
SELECT * FROM test_table ORDER BY ID;
END$$
DELIMITER ;

This routine has been stored in Server Database and we are going to call it through a Button1 code within our Windows app. What it does is delete the user from test_table with a selected ID parameter. This is a good example of using a Stored Procedure instead of writing a Query inside the code.

Once the Stored procedure has been created, the execution Query looks like it is shown on the picture.

MySQL routine - stored procedure
MySQL Routine

This Query has DELETE and SELECT statements. This way, I will delete the user and then populate DataGridView1 with a new set of data.

Now take a look at the working app. All codes you have above, and you can test it by yourself.

MySQL stored procedure
MySQL Routine

The picture above shows the whole code needed to call a stored procedure. The first lines If statement makes sure that the user selects a row he wants to delete. When the row is selected, TextBox1.Text is populated with an ID record and that is the parameter for the prepared MySQL Routine.

Next, we go to Else statement where the actual Stored procedure being prepared. MySqlParameter @uID is declared by a TextBox1.Text value. The CommandText is the actual name of a MySQL Routine “sp_deleteuser“.

The Try code executes the prepared Query and gives a user Message in case that code has been successfully executed. The TextBox1.Text object has been cleared and it is ready for new value.

In case of any error the Catch ex As MySqlException throws us an Error message.

Finally End Try re-loads data showing the user a DataGridView1 with a new populated values (without deleted user).