Oct 3, 2011

MY SQL PART 2


MySQL Stored Procedures

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();

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);

Calling MySQL Stored Procedures from ASP.NET

// 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 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" />
</add>
</appsettings>

0 comments:

Post a Comment

Nam Le © 2014 - Designed by Templateism.com, Distributed By Templatelib