How to use mysql stored procedures in vb.net

In this tutorial, I will show you how you can convert your bare sql statements that is hard-coded in your vb.net application into stored procedure calls.

When I say hard-coded sql statements, it means that the sql queries used to create, update, delete, or view records in your database is in the application itself.

 

Advantages

  • Faster data retrieval- although there won’t be much speed you will get if you are only fetching from a database with barely 2-3 tables. And not using table joins. And also if your tables has only about a hundred records, you wont notice much difference in speed.
  • Portability- your sql queries are stored in the database itself, so you don’t have to worry about database manipulation if ever your application is not with you.
  • Security- because if anyone tries to peak at your applications source code, they will only see stored procedure calls, instead of sql queries.

 

Requirements

  • WAMP Server
  • Heidi SQL (Portable or installer version will do, but I prefer the portable version)
  • Visual Studio 2005 or later

After downloading and installing the requirements above, your now ready to create stored procedures. Please create a database, and a table within it if you don’t already have one. You can create them on either Heidi SQL or in phpmyadmin.

After doing that, open up heidi sql then input your password if you have one. Then click ‘open’.

image

What you’ll have to do now, is to select your desired database, then select your table. Right click it, select ‘create new’ then  click on ‘stored routine’.

The first thing that you’ll gonna need to do after that, is to put a routing name, I put ‘creates’ but you can put whichever you like:

image

After that, you might want to check the fields that are in your table. To do that, just right click on your table then select ‘edit’. You will then see something like this:

image

What you’ll want to do now is to create names which are similar to the fields in your table. For example, if you have ‘PRODUCT’ field, you might want to change that to ‘prods’. And that name, ‘prods’ will be used as a parameter name. You have to change it because the stored routing may treat your parameter as the field name if you don’t do so.

If you are like me, and you didn’t save the stored routine a while ago. You may want to repeat the procedure. Just right click on the table, select ‘create new’ then click on stored routine. Enter the name of the stored routine, then click on the ‘parameters’ tab.

Remember, just add the names that are similar to those of the field names, and put the same data type of the field you are basing it to:

image

The context is ‘IN’. Because this stored procedure that we are creating is for creating new records. So ‘IN’ means that the data is going to come from the user and not from the database.

You may want to go back to the ‘options tab’ to put the actual code:

INSERT INTO prod_table(PRODUCT, P_DESC, CATEGORY, TOTAL_QTY, QTYHAND,  REORDER_LVL, LIST_PRICE) 
VALUES(prods, descr, cats, tqty, qty, reorder, price);

You have to place the code above, inside the BEGIN and END tags. After that, click on ‘save’ then try to ‘run the routine’.

If all goes well, you should now have a stored routine that can create new records.

Wew, that’s just part 1 of the whole story. Part 2 will be the actual calling of the stored routine from your vb.net application.

So open up visual studio, and create something like this:

image

This is just an example made  for beginners, if you already have your application set then just scroll down to your point of interest.

If you don’t have a parent class in which you can inherit methods. Then create one. Just right click on ‘Windows application 1’ then select ‘add’ then click on ‘class’. Name the class whichever you want then put

imports mysql.data.mysqlclient

as your namespace, if you don’t know what namespace does. For a quick tip, its used to access the methods needed to manipulate the mysql database server.

If you don’t know how to connect mysql and vb.net, I’ve made a guide here.

Now, in your parent class, create a method that will create a new record. Then place this code in that method:

cmd.CommandText = "call creates('" + prods + "','" + descr + "','" + cats + "','" + total + "','" + qtyonhand + "','" + reorder + "','" + lprice + "')"
        
cmd.ExecuteNonQuery()

What the code above does is to call the stored routine ‘creates’. And supplies the necessary data to be included.

In your main form. You can just do something like this inside the event ‘button click’ to call the method ‘creates’ from the parent class that you have created:

     p.prods = TextBox1.Text
     p.descr = TextBox2.Text
     p.cats = TextBox3.Text
     p.total = TextBox4.Text
     p.qtyonhand = TextBox5.Text
     p.reorder = TextBox6.Text
     p.lprice = TextBox7.Text
 
     p.creates()
 

That’s it, if you want stored procedures for update or delete. You can do the same, just change the queries to your liking.

3 thoughts on “How to use mysql stored procedures in vb.net

  1. Pingback: vb.net database connection basics part 1 « Data Integrated Entity

  2. I get error “FUNCTION dbname.stored procedure does not exist. I know it does and also connection is there cause all select statements work fine.

    • are you sure that the name of the database and the stored procedure are correct? Confirm that you actually have the stored procedure in heidi sql and the database where it is attached is the one that you are using.
      Also make sure that the arguments that you have supplied matches the one in your stored procedure.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s