Sep 25 2006

MySQL DAL stored procedures support - howto part 14

Posted by admin under In practice

 Please read this article serie from the beginning

The DAL we created in part 13 might be good enough - I would not be ashamed over code like that - however there are some more things to do. As you might know MySQL does (since version 5.0) support stored procedures. Please read the related articles and the numbers you see will sure be a reason enought as to why to use SP:s whenever possible. MySQL without SP is roughly half as fast as MySQL with SP:s - according to my tests on that specific scenario. 

However, we do have a problem. We still want people to be able to use our extremly useful Customer application even if they only have MySQL 4, right?

So we can't just overwrite the DAL code from part 13. Instead we create an extra class in that project:

So our CustomerDAL_MySQL will contain two classes - DataAccessSP (which we leave untouched) and DataAccessSP - which we will implement now.

First lets look at the SP:s

 



DELIMITER $$

DROP PROCEDURE IF EXISTS `demoupload`.`cust_DeleteFile`$$
CREATE PROCEDURE  `demoupload`.`cust_DeleteFile`(in_id integer)
BEGIN
  delete from cust_file where id=in_id;
END $$

DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `demoupload`.`cust_GetBinaryData`$$
CREATE PROCEDURE  `demoupload`.`cust_GetBinaryData`(in_id integer)
BEGIN
  select filedata from cust_file where id=in_id;
END $$

DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `demoupload`.`cust_ListAllCustomers`$$
CREATE PROCEDURE  `demoupload`.`cust_ListAllCustomers`()
BEGIN
  select * from cust_customer;
END $$

DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `demoupload`.`cust_ListFiles`$$
CREATE PROCEDURE  `demoupload`.`cust_ListFiles`(in_id integer)
BEGIN
  select id, customer_id, filename, contentype as contenttype, length from cust_file where customer_id=in_id;
END $$

DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `demoupload`.`cust_OpenCustomer`$$
CREATE PROCEDURE  `demoupload`.`cust_OpenCustomer`(in_id integer)
BEGIN
  select * from cust_customer where id=in_id;
END $$

DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `demoupload`.`cust_OpenFile`$$
CREATE PROCEDURE  `demoupload`.`cust_OpenFile`(in_id integer)
BEGIN
  select id, customer_id, filename, contentype as contenttype, length from cust_file where id=in_id;
END $$

DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `demoupload`.`cust_SaveCustomer`$$
CREATE PROCEDURE  `demoupload`.`cust_SaveCustomer`(in_id integer, in_custname varchar(255))
BEGIN
  IF in_id = -1 THEN
    insert into cust_customer(custname) values( in_custname );
    select  LAST_INSERT_ID();
  ELSE
    update cust_customer set custname=in_custname where id=in_id;
    select  in_id;
  END IF;
END $$

DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `demoupload`.`cust_SaveFile`$$
CREATE PROCEDURE  `demoupload`.`cust_SaveFile`(in_customer_id integer, in_filename varchar(255), in_filedata Blob, in_contentype varchar(255), in_length integer )
BEGIN
  insert into cust_file(customer_id, filename, filedata, contentype, length) values( in_customer_id, in_filename, in_filedata, in_contentype, in_length);

END $$

DELIMITER ;

Now - as for our code - it's pretty much like coding for stored procedures in MS SQL Server:



        public System.Data.DataSet Customer_Open(string sConnString, long ld)
        {
            using (MySqlConnection oConn = new MySqlConnection(sConnString))
            {
                oConn.Open();

                MySqlCommand oCommand = oConn.CreateCommand();
                oCommand.CommandText = "cust_OpenCustomer";
                oCommand.CommandType = CommandType.StoredProcedure;

                MySqlParameter oParam = oCommand.Parameters.Add("in_id", MySqlDbType.Int32);
                oParam.Value = ld;

                oCommand.Connection = oConn;
                DataSet oDataSet = new DataSet();
                MySqlDataAdapter oAdapter = new MySqlDataAdapter();
                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(oDataSet);
                oConn.Close();
                return oDataSet;
            }
        }

For more details - please read the articles on MySQL and C# Stored procedures and Insert binary object with stored procedure with MySQL and C#

 

 So to sum it up - one MySQL DAL dll - two drivers. One using stored procedures and one without. To use the one with stored procedure support make this change to web.config:



  <appSettings>
    <add key="DataAccessComponent" value="CustomerDAL_MySQL.DataAccessSP, CustomerDAL_MySQL"/>
  </appSettings>