.NET and MySQL

I've done a fair bit of work on the database interface for AllDay DJ 3. One of the decisions taken very early in the design process was to make the program able to operate over a number of database back ends.

To allow for this, I've developed a standard base class that has all of the calls to the Idb... interfaces .These are generic, no mater the database connection used in a .NET program.

In a bit of a strange move and allowing for the different SQL syntaxes, I store the commands in a map that is looked up by the generic class, but loaded by the database specific class. This combined with prepared statements and the factory pattern allows for adding new database back ends easily.

Talking about prepared statements. I had to to a bit of digging around to find out how to do it in C# with a generic interface. Unfortunately, it's not as nice as the MSSQL specific calls. So, I've thrown together a method for adding a parameter to an IDbCommand:

        /// <summary>
        /// Creates and assigns a parameter.
        /// </summary>
        /// <param name="command">The command to add the parameter to.</param>
        /// <param name="param_name">The name of the parameter. E.g. "@ID"</param>
        /// <param name="type">The type the parameter accepted. E.g. string, int</param>
        /// <param name="value">The value to enter into the parameter</param>

        private void assignParameter(IDbCommand command, String param_name, DbType type, Object value)
        {

            IDbDataParameter param = command.CreateParameter();
            param.ParameterName = param_name;
            param.DbType = type;
            param.Value = value;
            command.Parameters.Add(param);

        }


Not bad, eh? You can have it for free.

Now, so far I've talked about generic database access but have MySQL in the title. Why?

I've been making use of the ByteFX library. Using it with .NET 3 has proven a bit of an issue. For one, you have to use the OLD_PASSWORD() call when creating users for the ByteFX library on the latest MySQL builds. So you may want to create a .NET only user.

Secondly, the ByteFX MySQL objects don't directly map to the MSSQL calls. Thus the rather generic interfaces. Still feeling generous at the moment, I'll give you the method I use to connect to MySQL:

        /// <summary>
        /// MySQL specific connection
        /// </summary>
        /// <returns></returns>

        protected override IDbConnection connect()
        {
            IDbConnection conn = new ByteFX.Data.MySqlClient.MySqlConnection(this.connection_string);
            conn.Open();
            return conn;
        }

Simple enough to use. I'm assuming this.connection_string contains your connection string.

So I think I'll wrap it up by saying I'll post more interesting code from AllDay DJ 3 in the future. Though, for obvious reasons, not the "trade secret" parts.