Implementing MySQL DAL - howto part 13

Sep 22 2006

Implementing MySQL DAL - howto part 13

Posted by admin under In practice

Please read this article serie from the beginning

First step is of course to create a MySQL database - with the same columns as the MSSQL. I used a database called DemoUpload and ran this script




DROP TABLE IF EXISTS `demoupload`.`cust_file`;
CREATE TABLE  `demoupload`.`cust_file` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `customer_id` int(10) unsigned NOT NULL default '0',
  `filename` varchar(255) NOT NULL default '',
  `filedata` blob,
  `contentype` varchar(255) NOT NULL default '',
  `length` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `demoupload`.`cust_customer`;
CREATE TABLE  `demoupload`.`cust_customer` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `custname` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

To store the binary files I use a BLOB field as you can see.

Now create a new dll project, we call it CustomerDAL_MySQL:

I have created a class DataAccessNoSP



namespace CustomerDAL_MySQL
{
    public class DataAccessNoSP : CustomerInterfaces.DALInterface
    {
        #region DALInterface Members

        public long Customer_Save(string sConnString, long lId, string sName)

Now the beauty of it. All we need to create this driver is the CustomerInterfaces.dll. Some other party could for example develop it - with NO knowledge of our web application - all the DAL driver developer needs is the interface.

So we continue to develop all interface functions:



        public System.Data.DataSet Customer_ListAll(string sConnString)
        {
            
            using (MySqlConnection oConn = new MySqlConnection(sConnString))
            {
                oConn.Open();

                MySqlCommand oCommand = oConn.CreateCommand();
                oCommand.CommandText = "select * from cust_customer";
                oCommand.Connection = oConn;
                DataSet oDataSet = new DataSet();
                MySqlDataAdapter oAdapter = new MySqlDataAdapter();
                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(oDataSet);
                oConn.Close();
                return oDataSet;
            }
        }

...
etc
...
...

And when done - lets see how to configure the DemoUpload web application to use it:



<configuration>
  <appSettings>
    <add key="DataAccessComponent" value="CustomerDAL_MySQL.DataAccessNoSP, CustomerDAL_MySQL"/>
  </appSettings>
	<connectionStrings>
		<add name="mainConn" connectionString="Database=demoupload;Data Source=localhost;User id=root;Password=stefan"/>
	</connectionStrings>

Of course we need to copy the driver dll into the web applications bin directory, but after that all we need to do is to change the connectionstring to point to the MySQL database - and change the DataAccessComponent entry so our new driver is loaded!

As for implementation details - please read the related articles - they are informative for parameterized queries etc in MySQL.

However one last thing: Did you notice my spelling mistake when creating the MySQL cust_file table:

contentype - was called contenttype in MSSQL.

This gave me an error when my BLL tried to read the data, expecting contenttype. While I first thought - lets change the MySQL column name - but then I thought it would make an excellent point about loosly coupled layers:

Data storage details shouldn't simply matter. If I want to I should be able to store my data in the very special AcmeSpecialDonaldDuck database engine (havn't heard of it?) - which when built in 1948 prefixes all columns with hhhjhfdsjkhqw. So the column "id" is "hhhjhfdsjkhqw_id".

No, seriously, I am of course making that database engine up - but you get the point. So the right way to fix it is to make the MySQL DAL driver return the column contentype as contenttype - while still being stored as contentype in the database.

So the easiest - and correct way to fix it :



oCommand.CommandText = "select id, customer_id, filename, 
contentype as contenttype, 
length from cust_file where customer_id=?id";

As you understand - now I should be able to just attach the MySQL driver to this article and if you download it you should be able to plug it into your current project from part 11. And while true, the download contains ALL code as a convinience.

ASPCode.net

Free asp.net and asp scripts

Implementing MySQL DAL - howto part 13