MySQL Stored Procedures

MySQL Stored Procedures

A stored procedure is made up of one or more SQL statements or commands and is stored within the database. Stored procedures can be used to perform any type of database operation such as retrieving one or more rows, inserting, updating, deleting data, or perhaps multiple database operations at once.

Before moving on, let’s take a look at creating a stored procedure. Launch MySQL Query Browser, connect to your versedb database, open a new Script Tab, and execute the following script.

DELIMITER $$

DROP PROCEDURE IF EXISTS `versedb`.`usp_Verse_GetList`$$
CREATE PROCEDURE `usp_Verse_GetList`()
BEGIN
  SELECT  verse_id,
          verse_text,
          verse_ref
  FROM    verse
  ORDER BY
          verse_ref DESC;
END$$

DELIMITER ;

 

This simple stored procedure retrieves all the rows in the verse table ordered by verse reference in descending order. To see this stored procedure in action, open a new Query Tab and execute the following command.

 

CALL usp_Verse_GetList();

 

What you should see (assuming you have data in your verse table from Part 1), is a list of all the rows in your table ordered by the verse reference in descending order, just as you might expect.

One of the primary benefits of using stored procedures is that SQL statements and logic can be maintained apart from the applications that use them. So, instead of embedding SQL commands in your application, your application only needs to know how to execute the stored procedures it needs.

Stored procedures also support parameters. In this way, a single stored procedure can be used in many scenarios without having to be modified. For example, you can create a stored procedure that can retrieve a single row in your table based upon a primary key value passed as a parameter.

DELIMITER $$

DROP PROCEDURE IF EXISTS `versedb`.`usp_Verse_Get_By_Id`$$
CREATE PROCEDURE `usp_Verse_Get_By_Id`(v_id INT)
BEGIN
  SELECT  verse_id,
          verse_text,
          verse_ref
  FROM    verse
  WHERE   verse_id = v_id;
END$$

 

To see the stored procedure in action, execute the following command.

 

CALL usp_Verse_Get_By_Id(2);

 

What you should see is a single row returned from the database where the verse_id field is equal to 2. To retrieve a different row, you would simply replace the value 2 with a different valid primary key value. If you specify a value that does not exist in the table, then no rows would be returned.

A couple more things to mention: it is possible to define output parameters as well as for input, and stored procedures do not have to return any rows. We will look at an example of both in a later article, but imagine the scenario of creating a stored procedure to insert a new row into a table. You might pass the values for the row as input parameters, return the new primary key ID assigned to the inserted row as an output parameter, and would not need to return any rows of data.

Calling MySQL Stored Procedures from ASP.NET

Executing a stored procedure using ASP.NET is nearly identical to executing straight SQL. Unless a procedure requires parameters, you only need to provide the name of the stored procedure in the command text, and specify the CommandType property is of type StoredProcedure.

// Get the MySQL connection string stored in the Web.config
string cnnString = ConfigurationSettings.AppSettings["ConnectionString"];

// Create a connection object and data adapter
MySqlConnection cnx = new MySqlConnection(cnnString);
MySqlDataAdapter adapter = new MySqlDataAdapter();

// Create a SQL command object
string cmdText = "usp_Verse_GetList";
MySqlCommand cmd = new MySqlCommand(cmdText, cnx);

// Set the command type to StoredProcedure
cmd.CommandType = CommandType.StoredProcedure;

// Create and fill a DataSet
DataSet ds = new DataSet();
adapter.SelectCommand = cmd;
adapter.Fill(ds);

 

One difference you might notice from the previous article is retrieving the connection string from the web.config using ConfigurationSettings.AppSettings, which is typically best practice. If you are not familiar with this technique, you would simply create an <appSettings> block in your web.config that looks like the following.

 

<appSettings>
  <add key="ConnectionString"
    value="Server=localhost;Port=3306;Database=versedb;Uid=root;Pwd=mySecret" />
</appSettings>

 

You would need to add the <appSettings> section directly after <configuration> and before <system.web>. If you already have an <appSettings> section defined, you only need to insert the <add /> node for the connection string.

Executing Stored Procedures with Parameters

To execute a stored procedure with parameters, you must create and add a MySqlParameter object to the MySqlCommand.Parameters collection for each required parameter.  Also, parameter names in MySQL use a prefix of "?" which is similar to Microsoft SQL Server's use of "@."

// Hard-coding the Verse ID for example only
int verseID = 2;

// ...Code to create connection goes here...

// Create a SQL command object
string cmdText = "usp_Verse_Get_By_Id";
MySqlCommand cmd = new MySqlCommand(cmdText, cnx);

// Set the command type to StoredProcedure
cmd.CommandType = CommandType.StoredProcedure;

// Create the verse ID parameter
MySqlParameter param;
param = new MySqlParameter("?v_id", MySqlDbType.Int32);
param.Value = verseID;
param.Direction = ParameterDirection.Input;
cmd.Parameters.Add(param);

// ...Code to build DataSet goes here...


 

Of course, you would probably wrap all of this code in a method that takes a verseID as a parameter and returns a DataSet or DataRow.

Summary

In summary, you might think of stored procedures as a public method exposed by your database that encapsulates your SQL code. You can make any change to the stored procedure’s code, and as long as the name of the procedure and its parameters, if any, do not change (in other words, the “method signature” to continue our illustration), there would be no need to modify your ASP.NET application.

 

Hopefully this gives you a starting point for using MySQL stored procedures.  However, we have only begun to scratch the surface of this very powerful feature.  In the future, we will look at creating more complex stored procedures, and using stored procedures to insert, update, and delete data in your database.