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.
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.
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.
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“.
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.
End Try re-loads data showing the user a DataGridView1 with a new populated values (without deleted user).