Post date: Feb 9, 2012 5:14:10 PM
This class is used for creating provider agnostic DbCommands, instead of hardcoding SqlClient as your provider.
The methods will create DbCommands and DbParameters based on the configured provider client. It is meant to be as close to as easy to use as you would regularly use the SqlCommand.
In this implementation I require that the caller is sending the connectionstring to the methods, but that can be removed and included in the ConnectionFactory.Create() method.
The implementation is straight forward. It uses the DbProviderFactory class as the entry point of creating the DbCommand/DbConnection/DbParameter objects. The DbProviderFactory is created from DbProviderFactories which takes as its argument the InvariantName of the provider. This means that you can change the provider using a configuration file or even using some runtime decision (if a then SqlClient else MySQLClient).
To use it you need this in your .config file. Modify it to use MySqlClient or OracleClient or what you want to use.
<appSettings> <add key="DbConnFactory" value="System.Data.SqlClient" /> </appSettings>
Example of how you could use it:
As you can see, you do the coding almost as how you normally would using SqlClient objects. The only difference is the creation of the parameters.
using (DbCommand cmd = CommandFactory.Create(sql, connStr)) { // If we are requesting wikimarkup for a specific stored procedure then this sql code need to be included in the search clause. if (spMetadata != null) { cmd.Parameters.Add(CommandFactory.CreateParameter("SPName", spMetadata.Name)); cmd.Parameters.Add(CommandFactory.CreateParameter("SPSchema", spMetadata.Schema)); } else { cmd.Parameters.Add(CommandFactory.CreateParameter("SPName", DBNull.Value)); cmd.Parameters.Add(CommandFactory.CreateParameter("SPSchema", DBNull.Value)); } cmd.Connection.Open(); DbDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { sb.AppendLine(reader[0].ToString()); } }
using System.Data;using System.Data.Common; namespace DataAccess { public static class CommandFactory { /// <summary> /// Create DbCommand using the configured DbFactory, the commandType will be CommandType.Text /// </summary> /// <param name="sqlQuery">the query to use in the command</param> /// <param name="connStr">the connection string to use in the connection of the command</param> /// <returns>A DbCommand with a closed connection</returns> public static DbCommand Create(string sqlQuery, string connStr) { return Create(sqlQuery, connStr, CommandType.Text); } /// <summary> /// Create DbCommand using the configured DbFactory /// </summary> /// <param name="sqlQuery">the query to use in the command</param> /// <param name="connStr">the connection string to use in the connection of the command</param> /// <param name="commandType">the type of command this should be</param> /// <returns>A DbCommand with a closed connection</returns> public static DbCommand Create(string sqlQuery, string connStr, CommandType commandType) { DbConnection conn = ConnectionFactory.Create(connStr); DbCommand cmd = conn.CreateCommand(); cmd.CommandText = sqlQuery; cmd.CommandType = commandType; return cmd; } /// <summary> /// Create DbParameter using the configured DbFactory /// </summary> /// <param name="name"></param> /// <param name="value"></param> /// <returns></returns> public static DbParameter CreateParameter(string name, object value) { DbParameter param = DbProviderFactoryFactory.Create().CreateParameter(); param.Value = value; param.ParameterName = name; return param; } /// <summary> /// Factory to create DbConnections /// </summary> private static class ConnectionFactory { public static DbConnection Create(string connStr) { DbProviderFactory factory = DbProviderFactoryFactory.Create(); DbConnection conn = factory.CreateConnection(); conn.ConnectionString = connStr; return conn; } } /// <summary> /// Singleton DbProviderFactory /// </summary> private static class DbProviderFactoryFactory { private static DbProviderFactory factory = null; public static DbProviderFactory Create() { if (factory == null) { // http://msdn.microsoft.com/en-us/library/dd0w4a2z.aspx // This configuration is needed if you are going to use SqlClient. // If you want to use any other client like MySql then you need to modify this setting. //<appSettings> // <add key="DbConnFactory" value="System.Data.SqlClient" /> //</appSettings> factory = DbProviderFactories.GetFactory(System.Configuration.ConfigurationManager.AppSettings["DbConnFactory"]); } return factory; } } }}