Creating an ADO.NET database factory for MySql database server

Author: Luis Ramirez.
January 12, 2008.

What is a database factory?

For the SqlNetFramework a database factory is any class that implements the SqlNetFramework.Data.IDbFactory interface. A database factory is used by the SqlNetFramework to interact with a particular .NET data provider. For example, the SqlClientDbFactory is used to interact with the Microsoft SQL Server .NET data provider. A database factory is also used to specify what information of a SQL command will be stored in the SQL data store and how to build a SQL command from the information in the SQL data store.

SqlNetFramework built-in database factories

The SqlNetFramework has a set of built-in database factories for two of the most used database servers: Microsoft SQL Server and Oracle. If your database server supports connection through the ODBC and/or OleDb .NET data providers, then you can use the Odbc and OleDb database factory to connect to your database server. If you cannot connect to your database server using one of the built-in database factories included with the SqlNetFramework, then you can create a database factory to connect to your database server. In the following article you will see how to create a database factory for the MySql database server.

Download the MySql database server

The MySql database server is a open source database server. You can download the MySql database server from http://dev.mysql.com/downloads/mysql/5.0.html.

Download the ADO.NET data provider for MySql database server

In order to connect to a MySql database server you will need a MySql .NET data provider. We will use the Connector/NET .NET data provider. You can download it from http://dev.mysql.com/downloads/connector/net/5.0.html.

Download the SqlNetFramework

Download the latest version of the SqlNetFramework from http://www.sqlnetframework.com/download/default.aspx.

Download the code of the ADO.NET database factory for MySql

Download the code used in this article from http://www.sqlnetframework.com/download/MySqlDbFactory.zip. The code for this example was written in C#.

File template for a database factory

As all the database factories share common elements we can create a file template. The DbFactoryTemplate.cs file template contains special tags that must be substituted by the values corresponding to the MySql ADO.NET data provider.

Replacing the tags in the database factory template with the MySql values

The table below explains you the special tags that the DbFactoryTemplate.cs has and the values that must be assigned to those tags.

Tag	Description	MySql value
<%=Namespace%>	Namespace for your database factory.	SqlNetFramework.Data.MySql
<%=ClassAcessor%>	Exposes the visibility of your database factory. Expected values: public, private, internal.	public
<%=DbFactoryPrefix%>	Used to create the names of the methods that the database factory will expose. For the MySql prefix the methos names would be CreateMySqlConnection, CreateMySqlCommand, CreateMySqlDataAdapter.	MySql
<%=DataProviderPrefix%>	The prefix used by your ADO.NET data provider to name its classes. For an ADO.NET data provider that has the following classes: MySqlConnection, MySqlParameter and MySqlCommand the DataProviderPrefix value would be MySql.	MySql
<%=ParameterTag%>	Character used by your ADO.NET data provider to specify a parameter name (e.g. @, :, ?). For the Connector/NET data provider the ParameterTag value is ?.	?
<%=DataProviderNamespace%>	Namespace where the connection, command, transaction (e.g. MySqlConnection, MySqlCommand, MySqlTransaction) classes of your ADO.NET data provider are declared.	MySql.Data.MySqlClient
<%=DataProviderDbTypeEnum%>	Enumeration used by your ADO.NET data provider to specify a database type (e.g. SqlDbType, OleDbType). For the Connector/NET data provider the DataProviderDbTypeEnum value is MySqlDbType.	MySqlDbType

You can use the text editor of your preference to replace the tags in the DbFactoryTemplate.cs file with the corresponding values. You also can use the DbFactoryTemplate.cst CodeSmith template to create your database factory and make the replacements.

MySql database factory after replace special tags

After substitute the special tags in the DbFactoryTemplate.cs file (MySqlDbFactoryAfterReplacement.cs) for the MySql values you have almost finish your database factory for MySql. What you need to do to finish the MySql database factory is to implement the functionality of the CreatePropertiesData, AssignPropertiesValues and CreateDummyValue methods.

Implementing the CreatePropertiesData method

As you know the SqlNetFramework saves the SQL code to a SQL data store, it is a repository (XML, database) containing all your SQL code. If you create a SQL command using the SQL Editor all the information in the SQL command will be saved to a SQL data store. Each parameter in the SQL command is saved to the SQL data store including its property values.

The CreatePropertiesData method receives a parameter as argument. Its function is to return a PropertyData array containing the property values of the parameter. Each PropertyData item in the array contains a property name and its value. The CreatePropertiesData method implementation is as follow:

For each parameter property a PropertyData item is created. The point 2 will explain the cases where a PropertyData item is not created for a parameter property.
Cases where a PropertyData item is not created:
- If the value assigned to the parameter property is its default value. You only need to create a PropertyData item for those properties whose value is not the default value.
- A PropertyData is not created for the following properties: DbType, ParameterName, SourceColumn, SourceColumnNullMapping, SourceVersion.

The following listing shows the implementation of the CreatePropertiesData method for the MySql ADO.NET data provider.

protected virtual PropertyData[] CreatePropertiesData(MySqlParameter parameter)
{
    ArrayList properties = new ArrayList(7);

    if (parameter.Direction != ParameterDirection.Input)
        properties.Add(new PropertyData("Direction", parameter.Direction.ToString()));
    if (parameter.IsNullable)
        properties.Add(new PropertyData("IsNullable", Boolean.TrueString));

    properties.Add(new PropertyData("MySqlDbType", parameter.MySqlDbType.ToString()));

    if (parameter.Precision != 0)
        properties.Add(new PropertyData("Precision", parameter.Precision.ToString()));
    if (parameter.Scale != 0)
        properties.Add(new PropertyData("Scale", parameter.Scale.ToString()));
    if (parameter.Size != 0)
        properties.Add(new PropertyData("Size", parameter.Size.ToString()));
    if (parameter.Value != null && parameter.Value != DBNull.Value && (!(parameter.Value is string) || ((parameter.Value is string) && (!StringHelper.IsEmpty((string)parameter.Value)))))
        properties.Add(new PropertyData("Value", parameter.Value.ToString()));

    PropertyData[] propertiesData = new PropertyData[properties.Count];
    properties.CopyTo(propertiesData);

    return propertiesData;
}

Implementing the AssignPropertiesValues method

The AssignPropertiesValues method receives a parameter and a PropertyData array as argument. Its function is to initialize the values of the parameter properties using the values in the PropertyData array.

The following listing shows the implementation of the AssignPropertiesValues method for the MySql ADO.NET data provider.

protected virtual void AssignPropertiesValues(MySqlParameter parameter, PropertyData[] propertiesData)
{
    HybridDictionary properties = new HybridDictionary(propertiesData.Length, true);

    for (int i = 0; i < propertiesData.Length; i++)
    {
        PropertyData propData = propertiesData[i];
        properties.Add(propData.PropertyName, propData);
    }

    try
    {
        PropertyData propData;

        if ((propData = (PropertyData)properties["Direction"]) != null)
            parameter.Direction = (ParameterDirection)Enum.Parse(typeof(ParameterDirection), propData.PropertyValue);
        if ((propData = (PropertyData)properties["IsNullable"]) != null)
            parameter.IsNullable = Boolean.Parse(propData.PropertyValue);
        if ((propData = (PropertyData)properties["MySqlDbType"]) != null)
            parameter.MySqlDbType = (MySqlDbType)Enum.Parse(typeof(MySqlDbType), propData.PropertyValue);
        if ((propData = (PropertyData)properties["Precision"]) != null)
            parameter.Precision = Byte.Parse(propData.PropertyValue);
        if ((propData = (PropertyData)properties["Scale"]) != null)
            parameter.Scale = Byte.Parse(propData.PropertyValue);
        if ((propData = (PropertyData)properties["Size"]) != null)
            parameter.Size = Int32.Parse(propData.PropertyValue);
        if ((propData = (PropertyData)properties["Value"]) != null)
            parameter.Value = propData.PropertyValue;
    }
    catch (Exception ex)
    {
        throw new SqlNetFrameworkException(string.Format("Cannot create IDataParameter from text values. {0}", ex.Message), ex);
    }
}

Implementing the CreateDummyValue method

The CreateDummyValue method receives a database type as argument. Its function is to create a dummy value depending on the database type given as argument. This method is used internally by the SqlNetFramework when a SQL command needs to be executed and its parameter values has not been specified.

The following listing shows the implementation of the CreateDummyValue method for the MySql ADO.NET data provider.

private object CreateDummyValue(MySqlDbType dbType)
{
    if (dbType == MySqlDbType.Decimal || dbType == MySqlDbType.Byte || dbType == MySqlDbType.Int16 || dbType == MySqlDbType.Int24 || dbType == MySqlDbType.Int32 || dbType == MySqlDbType.Int64 || dbType == MySqlDbType.Float || dbType == MySqlDbType.Double || dbType == MySqlDbType.UInt16 || dbType == MySqlDbType.UInt24 || dbType == MySqlDbType.UInt32 || dbType == MySqlDbType.UInt64)
        return 1;
    else if (dbType == MySqlDbType.Timestamp)
        return TimeSpan.Zero;
    else if (dbType == MySqlDbType.Datetime || dbType == MySqlDbType.Newdate)
        return DateTime.Today;
    else if (dbType == MySqlDbType.VarString || dbType == MySqlDbType.TinyBlob || dbType == MySqlDbType.VarChar || dbType == MySqlDbType.String || dbType == MySqlDbType.TinyText || dbType == MySqlDbType.MediumText || dbType == MySqlDbType.LongText || dbType == MySqlDbType.Text)
        return "a";
    else if (dbType == MySqlDbType.MediumBlob || dbType == MySqlDbType.LongBlob || dbType == MySqlDbType.Blob)
        return new Byte[0];
    else if (dbType == MySqlDbType.Date)
        return "2007-01-01";
    else if (dbType == MySqlDbType.Time)
        return "01:01:01";
    else if (dbType == MySqlDbType.Bit || dbType == MySqlDbType.NewDecimal || dbType == MySqlDbType.UByte)
        return 1;
    else if (dbType == MySqlDbType.Enum || dbType == MySqlDbType.Set)
        return "a";
    else if (dbType == MySqlDbType.Geometry)
        return System.DBNull.Value;
    else if (dbType == MySqlDbType.Binary || dbType == MySqlDbType.VarBinary)
        return new byte[0];

    throw new ArgumentException("dbType", "Invalid MySqlDbType value.");
}

ADO.NET database factory for MySql database server

After replace the special tags in the database factory template and implement the CreatePropertiesData, AssignPropertiesValues and CreateDummyValue methods the ADO.NET database factory for MySql has been finished. Yu can take a look at MySqlDbFactory.cs file to see the final result.

Compiling the ADO.NET database factory for MySql database server

Now that we have our ADO.NET database factory implementation for MySql we need to create an assembly to start creating database applications using our ADO.NET MySql database factory. You need to create a Visual Studio 2005 C# Class Library project. Add the MySqlDbFactory.cs file to your project. Add a reference to the SqlNetFramework.Core.dll and SqlNetFramework.Shared.dll assemblies, both assemblies at located at [Program Files]/SqlNetFramework/SqlNetFramework 1.0/bin. Add a reference to the MySql.Data assembly, it must be located at [Program Files]/mysql/MySQL Connector Net 5.0.8.1/Binaries/.NET 2.0. Now just compile your project to generate the assembly file.

Using the MySql ADO.NET database factory to create database applications

Now you can use your MySql ADO.NET database factory and the SqlNetFramework to create database application for MySql database server. The http://www.sqlnetframework.com/download/MySqlDbFactory.zip file contains a demo project that uses the MySql ADO.NET database factory.

Conclusion

To create a database factory for a custom database server is a simple task. All you need to do is to replace some values in the database factory template and then implement the CreatePropertiesData, AssignPropertiesValues and CreateDummyValue methods.

$ 75 USD

Download ADO.NET Accelerator

Buy ADO.NET Accelerator Now

Product details

Friend sites