Executing MySql stored procedure by using Enterprise Library

Once I needed a simple data access layer class to execute simple db commands. And I've come up with a class that wraps  Enterprise Library  Data Application classes and makes it even easier to call for me. This is my implementation ;

/// 

/// Facade interface to data access class 
/// 
public interface IDatabaseFacade
{
   int ExecuteNonQuery(string spName, List<object> parameters);
}
...
...

public class DatabaseFacade : IDatabaseFacade
{
   private Database database;

   public DatabaseFacade()
   {
      DatabaseFactory.SetDatabaseProviderFactory(new DatabaseProviderFactory(),false);
      this.database = DatabaseFactory.CreateDatabase();
   }

   public int ExecuteNonQuery(string spName, List<object>parameters)
   {
      using (DbCommand command = this.database.GetStoredProcCommand(spName, parameters.ToArray()))
      {
         return this.database.ExecuteNonQuery(command);
      }
   }
}

It is pretty straightforward. Sure it may have some design issues, i just implemented in hurry and used it right away and once it worked fine i didn't return back to it and checked my design. I've added this implementation to my tiny toolkit  to use it further.

This is just a simple class to execute a non-query store procedure.  Simple use is ;

DatabaseFacade dbFacade = new DatabaseFacade();
List<object> parameters = new List<object>()
{
   "Test",
   123,
   null,
   DateTime.Now,
   "Tester"
};
int result = dbFacade.ExecuteNonQuery("DbCommand_INS", parameters);

No need parameter name definition. You can just pass  stored procedure name with  parameters as a list of object. It worked fine. However, it turns out , this only works on Sql Server.  When i tried to execute mysql  stored procedure it produced following exception ;

System.NotSupportedException : Parameter discovery is not supported for connections using GenericDatabase. You must specify the parameters explicitly, or configure the connection to use a type deriving from Database that supports parameter discovery.

It seems that we cannot call MySql stored procedure without parameters.We have to pass them with names. So I had to pass parameters with their names. However, I didn't want to change my interface because the purpose of this implementation was to make data access as  simple as possible. So  I have changed the implementation of concrete class as following ;

public class DatabaseFacade : IDatabaseFacade
{
   private Database database;

   public DatabaseFacade()
   {
      DatabaseFactory.SetDatabaseProviderFactory(new DatabaseProviderFactory(), false);
      this.database = DatabaseFactory.CreateDatabase();
   }

   public int ExecuteNonQuery(string spName, List<object> parameters)
   {         
     if (database.DbProviderFactory is MySqlClientFactory)
     {
        string spStr = this.BuildMysqlSpString(spName, parameters);

        using (DbCommand command = this.database.GetSqlStringCommand(spStr))
        {
            DbCommand commandWithParameters = this.AddParameters(command, parameters);

            return this.database.ExecuteNonQuery(commandWithParameters);
        }
    }
    else {
       using (DbCommand command = this.database.GetStoredProcCommand(spName, parameters.ToArray()))
       {
          return this.database.ExecuteNonQuery(command);
       }
    }
   }
   #region MySql Functions
   private DbCommand AddParameters(DbCommand dbCommand, List<object> parameters)
   {
      for (int i = 0; i < parameters.Count; i++)
      {
         DbParameter parameter = dbCommand.CreateParameter();
         parameter.ParameterName = string.Format("@parameter{0}", i + 1);
         parameter.Value = parameters[i];

         dbCommand.Parameters.Add(parameter);
      }
      return dbCommand;
   }
   private string BuildMysqlSpString(string spName, List<object> parameters)
   {
      StringBuilder spBuilder = new StringBuilder();
      spBuilder.AppendFormat("call {0}(", spName);
      List dbParameters = new List();

      for (int i = 0; i < parameters.Count; i++)
      {
         spBuilder.AppendFormat("@parameter{0},", i + 1);
      }

      if (parameters.Count != 0)
         spBuilder.Length--;
      spBuilder.Append(")");

      return spBuilder.ToString();
   }
   #endregion
}

Design may look a bit ugly, yet it works for both MySql and Sql Server database ( haven't tested for others) , and you got the idea. To make it work for mysql , you have to build stored procedure call string and pass parameters with names.



Happy Coding!