The need came up the other day to be able to easily switch back and forth between making IBM DB2 queries and MS SQL queries. I didn't want to mess with NHibernate as I was on a time crunch, and I also didn't want to have a million connection strings. Lastly I learned a while ago that for classes that needed to be used a specific way could be wrapped in a method with a Action with that type as a parameter. This way you can use it as needed in the base class, and in the derived classes you can just use it.
One known "issue" is that whatever provider you want to use it has to be installed and able to be instantiated by System.Data.DbProviderFactory. So I made IBaseDao in a futile attempt to be able to replace this with a mock... it might need some tweaking, but here it is for reference
public interface IBaseDao
{
void AddParameter(string parameterName, object parameterValue);
void ExecuteNonQuery();
void ReadAll(Action<DynamicDataReader> readerActionBlock);
void SetSqlText(string sql);
}
Now I did a few things in BaseDao that I'm unsure if it is bad practice or not. It mainly comes from unfamiliarity with the interfaces and implementations from System.Data. One is to make public the DbProviderFactory, and the other is to make the IDbCommand public.
public abstract class BaseDao : IBaseDao
{
public DbProviderFactory Factory { get { return _factory; } }
public IDbCommand Command { get; private set; }
private readonly DbProviderFactory _factory;
protected BaseDao(string providerName)
: this(DbProviderFactories.GetFactory(providerName))
{
}
protected BaseDao(DbProviderFactory factory)
{
_factory = factory;
}
public void ReadAll(Action<DynamicDataReader> readerActionBlock)
{
using (var dynamicReader = new DynamicDataReader(Command.ExecuteReader()))
{
while (dynamicReader.Read())
{
readerActionBlock(dynamicReader);
}
}
}
public void SetSqlText(string sql)
{
Command.CommandText = sql;
}
public void AddParameter(string parameterName, object parameterValue)
{
var parameter = Command.CreateParameter();
parameter.ParameterName = parameterName;
parameter.Value = parameterValue;
Command.Parameters.Add(parameter);
}
public void ExecuteNonQuery()
{
int rowsAffected = Command.ExecuteNonQuery();
if (rowsAffected < 1)
{
throw new Exception("No Rows Affected");
}
}
internal void SafelyUse(Action<BaseDao> commandBlock)
{
UsingConnection((con) =>
{
using (Command = con.CreateCommand())
{
Command.CommandTimeout = 5 * 60;
Command.CommandType = CommandType.Text;
try
{
commandBlock(this);
}
catch (Exception ex)
{
throw new Exception("An exception occured when Creating a command for a database", ex);
}
}
});
}
private void UsingConnection(Action<IDbConnection> connectionBlock)
{
try
{
var connectionstringBuilder = Factory.CreateConnectionStringBuilder();
AdjustConnectionString(connectionstringBuilder);
using (var connection = Factory.CreateConnection())
{
if (connection == null) throw new Exception(string.Format("No connection could be made with the connection string {0}", connectionstringBuilder.ConnectionString));
connection.ConnectionString = connectionstringBuilder.ConnectionString;
connection.Open();
connectionBlock(connection);
connection.Close();
}
}
catch (Exception ex)
{
throw new Exception("An exception was made when creating a connection to a database", ex);
}
}
protected abstract void AdjustConnectionString(DbConnectionStringBuilder connectionStringBuilder);
}
This class uses the Action type of pattern (not sure what to call it) exclusivly. So a person could just use the base class by itself, but I had a need for IBM DB2 and MS SQL so I made 2 preconfigured versions.
internal class IbmDb2Dao : BaseDao
{
private readonly string _databaseName;
private readonly string _serverName;
internal IbmDb2Dao(string serverName, string databaseName)
: base("IBM.Data.DB2")
{
_serverName = serverName;
_databaseName = databaseName;
}
protected override void AdjustConnectionString(DbConnectionStringBuilder connectionStringBuilder)
{
connectionStringBuilder.Add("Server", _serverName);
connectionStringBuilder.Add("Database", _databaseName);
connectionStringBuilder.Add("CurrentSchema", "userid");
}
}
internal class MsSqlDao : BaseDao
{
private readonly string _databaseName;
private readonly string _serverName;
internal MsSqlDao(string serverName, string databaseName)
: base("System.Data.SqlClient")
{
_serverName = serverName;
_databaseName = databaseName;
}
protected override void AdjustConnectionString(DbConnectionStringBuilder connectionStringBuilder)
{
connectionStringBuilder.Add("Data Source", _serverName);
connectionStringBuilder.Add("Initial Catalog", _databaseName);
connectionStringBuilder.Add("Integrated Security", "SSPI");
}
}
which then I made a public facing way of getting those. Granted.. it's not dynamic, but I have no other need for adding or removing DB types (as it is dictated by Business, and our DB Teams)
public static class PreconfiguredDao
{
public static void MsSqlDao(DaoConnector connector, Action<BaseDao> actionBase)
{
var dao = new MsSqlDao(connector.ServerName, connector.Database);
dao.SafelyUse(actionBase);
}
public static void IbmDb2Dao(DaoConnector connector, Action<BaseDao> actionBase)
{
var dao = new IbmDb2Dao(connector.ServerName, connector.Database);
dao.SafelyUse(actionBase);
}
}
I suppose this particular area could stand to be more dynamic...
public class DaoConnector
{
public static DaoConnector P_INFO { get { return new DaoConnector("like i'm gonna show you", "info"); } }
public static DaoConnector P_SLED { get { return new DaoConnector("yeah right", "sled"); } }
public static DaoConnector P_SLED_Service(string D_)
{
return new DaoConnector("my fancy server:50000", D_);
}
public string ServerName { get; private set; }
public string Database { get; private set; }
public DaoConnector(string serverName, string database)
{
ServerName = serverName;
Database = database;
}
}
Now that all the setup is done, I can easily use it:
private static IEnumerable<object> GetAllEmployees(int employeeId)
{
var employeeExample = new List<object>();
PreconfiguredDao.IbmDb2Dao(DaoConnector.P_INFO, dao =>
{
dao.SetSqlText(@"select EMP_NAME, PHONE from Employee e where e.Id = @EMPID with ur; ");
dao.AddParameter("@EMPID", employeeId);
dao.ReadAll(reader =>
{
//for example, add anonymous type
employeeExample.Add(new
{
Name = reader.GetString("EMP_NAME"),
Phone = reader.GetString("PHONE")
});
});
});
return employeeExample;
}
I'm happy with this code because it's easy for me to easily and quickly take some of my common queries that I use over and over again and put them in a program, but I'm curious what you guys think of it. If it was a nuget package (and easier to add your own installed providers) would you use it?
