When you working i'ts not always possible to just stop what your doing and blog and Idea or a train of thought. This post is random in it's creation with snippits of code, no explanations and no real flow. However, this is where the useful bits start off - so if it's in here, at some point in the future it will become a blog.
Data Access Layer
A way of creating a common DAL for use in many project where ADO crud operations are used instead of Entity framework or NHibernate.
using System.Collections.Generic;
using System.Data;
namespace Jannersoft.Common.DAL
{
public interface IDbManager
{
DataProvider ProviderType { get; set; }
string ConnectionString { get; set; }
IDbConnection Connection { get; }
IDbTransaction Transaction { get; }
IDataReader DataReader { get; }
IDbCommand Command { get; }
List<IDbDataParameter> ParameterList { get; }
void Open();
void BeginTransaction();
void CommitTransaction();
void AddParameters(IDbDataParameter param);
IDataReader ExecuteReader(CommandType commandType, string commandText);
DataSet ExecuteDataSet(CommandType commandType, string commandText);
object ExecuteScalar(CommandType commandType, string commandText);
int ExecuteNonQuery(CommandType commandType, string commandText);
void CloseReader();
void Close();
void Dispose();
}
}
using System;
using System.Data;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace Jannersoft.Common.DAL
{
public static class DbManagerFactory
{
public static IDbConnection GetConnection(DataProvider providerType)
{
IDbConnection iDbConnection;
switch (providerType)
{
case DataProvider.SqlServer:
iDbConnection = new SqlConnection();
break;
case DataProvider.OleDb:
iDbConnection = new OleDbConnection();
break;
case DataProvider.Odbc:
iDbConnection = new OdbcConnection();
break;
case DataProvider.Oracle:
throw new NotImplementedException();
default:
return null;
}
return iDbConnection;
}
public static IDbCommand GetCommand(DataProvider providerType)
{
switch (providerType)
{
case DataProvider.SqlServer:
return new SqlCommand();
case DataProvider.OleDb:
return new OleDbCommand();
case DataProvider.Odbc:
return new OdbcCommand();
case DataProvider.Oracle:
throw new NotImplementedException();
default:
return null;
}
}
public static IDbDataAdapter GetDataAdapter(DataProvider providerType)
{
switch (providerType)
{
case DataProvider.SqlServer:
return new SqlDataAdapter();
case DataProvider.OleDb:
return new OleDbDataAdapter();
case DataProvider.Odbc:
return new OdbcDataAdapter();
case DataProvider.Oracle:
throw new NotImplementedException();
default:
return null;
}
}
public static IDbTransaction GetTransaction(DataProvider providerType)
{
IDbConnection iDbConnection = GetConnection(providerType);
IDbTransaction iDbTransaction = iDbConnection.BeginTransaction();
return iDbTransaction;
}
public static IDataParameter GetParameter(DataProvider providerType)
{
IDataParameter iDataParameter = null;
switch (providerType)
{
case DataProvider.SqlServer:
iDataParameter = new SqlParameter();
break;
case DataProvider.OleDb:
iDataParameter = new OleDbParameter();
break;
case DataProvider.Odbc:
iDataParameter = new OdbcParameter();
break;
case DataProvider.Oracle:
throw new NotImplementedException();
}
return iDataParameter;
}
}
}
using System;
using System.Collections.Generic;
using System.Data;
namespace Jannersoft.Common.DAL
{
public sealed class DbManager : IDbManager, IDisposable
{
private IDbCommand idbCommand;
private DataProvider providerType;
private IDbTransaction idbTransaction;
public DbManager(DataProvider providerType)
{
this.providerType = providerType;
}
public DbManager(DataProvider providerType, string connectionString)
{
this.providerType = providerType;
ConnectionString = connectionString;
}
public IDbConnection Connection { get; private set; }
public IDataReader DataReader { get; set; }
public DataProvider ProviderType
{
get { return providerType; }
set { providerType = value; }
}
public string ConnectionString { get; set; }
public IDbCommand Command
{
get { return idbCommand; }
}
public IDbTransaction Transaction
{
get { return idbTransaction; }
}
public void Open()
{
Connection =
DbManagerFactory.GetConnection(providerType);
Connection.ConnectionString = ConnectionString;
if (Connection.State != ConnectionState.Open)
Connection.Open();
idbCommand = DbManagerFactory.GetCommand(ProviderType);
}
public void Close()
{
if (Connection.State != ConnectionState.Closed)
Connection.Close();
}
public void Dispose()
{
GC.SuppressFinalize(this);
Close();
idbCommand = null;
idbTransaction = null;
Connection = null;
}
public void AddParameters(IDbDataParameter param)
{
ParameterList.Add(param);
}
public void BeginTransaction()
{
if (idbTransaction == null)
idbTransaction =
DbManagerFactory.GetTransaction(ProviderType);
idbCommand.Transaction = idbTransaction;
}
public void CommitTransaction()
{
if (idbTransaction != null)
idbTransaction.Commit();
idbTransaction = null;
}
public IDataReader ExecuteReader(CommandType commandType, string commandText)
{
idbCommand = DbManagerFactory.GetCommand(ProviderType);
idbCommand.Connection = Connection;
PrepareCommand(idbCommand, Connection, Transaction,
commandType,
commandText, ParameterList);
DataReader = idbCommand.ExecuteReader();
idbCommand.Parameters.Clear();
return DataReader;
}
public void CloseReader()
{
if (DataReader != null)
DataReader.Close();
}
private static void AttachParameters(IDbCommand command, IEnumerable<IDbDataParameter> commandParameters)
{
foreach (var idbParameter in commandParameters)
{
if ((idbParameter.Direction == ParameterDirection.InputOutput)
&&
(idbParameter.Value == null))
{
idbParameter.Value = DBNull.Value;
}
command.Parameters.Add(idbParameter);
}
}
private static void PrepareCommand(IDbCommand command, IDbConnection connection, IDbTransaction transaction, CommandType commandType, string commandText,
IEnumerable<IDbDataParameter> commandParameters)
{
command.Connection = connection;
command.CommandText = commandText;
command.CommandType = commandType;
if (transaction != null)
{
command.Transaction = transaction;
}
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
}
public int ExecuteNonQuery(CommandType commandType, string commandText)
{
idbCommand = DbManagerFactory.GetCommand(ProviderType);
PrepareCommand(idbCommand, Connection, Transaction,
commandType, commandText, ParameterList);
var returnValue = idbCommand.ExecuteNonQuery();
idbCommand.Parameters.Clear();
return returnValue;
}
public object ExecuteScalar(CommandType commandType, string commandText)
{
idbCommand = DbManagerFactory.GetCommand(ProviderType);
PrepareCommand(idbCommand, Connection, Transaction,
commandType,
commandText, ParameterList);
var returnValue = idbCommand.ExecuteScalar();
idbCommand.Parameters.Clear();
return returnValue;
}
public DataSet ExecuteDataSet(CommandType commandType, string commandText)
{
idbCommand = DbManagerFactory.GetCommand(ProviderType);
PrepareCommand(idbCommand, Connection, Transaction,
commandType,
commandText, ParameterList);
var dataAdapter = DbManagerFactory.GetDataAdapter(ProviderType);
dataAdapter.SelectCommand = idbCommand;
var dataSet = new DataSet();
dataAdapter.Fill(dataSet);
idbCommand.Parameters.Clear();
return dataSet;
}
public List<IDbDataParameter> ParameterList
{
get { return parameterList ??
(parameterList = new List<IDbDataParameter>()); }
}
private List<IDbDataParameter> parameterList;
}
}
namespace Jannersoft.Common.DAL
{
public enum DataProvider
{
Oracle,
SqlServer,
OleDb,
Odbc
}
}
Implementation
public class TestHarness
{
public IEnumerable<IPerson> GetAllPeople()
{
var dbManager = new DbManager(DataProvider.SqlServer);
dbManager.ConnectionString = Properties.Settings.Default.Connection;
try
{
dbManager.Open();
dbManager.ExecuteReader(CommandType.StoredProcedure, "sp_GetPersons");
while (dbManager.DataReader.Read())
{
Mapper.CreateMap<IDataReader, Person>();
var persons = Mapper.Map<IDataReader,IEnumerable<Person>>(dbManager.DataReader);
return persons;
}
}
finally
{
dbManager.Dispose();
}
return null;
}
}
Testing
[TestFixture]
public class TestHarnesTests
{
private Jannersoft.common.DALTestHarnes.TestHarness harness;
[SetUp]
public void SetUp()
{
harness = new TestHarness();
}
[TearDown]
public void TearDown()
{
harness = null;
}
[Test]
public void GetAllPeople_Return_ListOfPeople()
{
//Arrange
//Act
var output = harness.GetAllPeople();
//Assert
Assert.GreaterOrEqual(output.Count(), 0);
}
}
Enum Extension
public static class AttributesHelperExtensions
{
public static string GetStringValue<TEnum>(this TEnum value)
{
var type = value.GetType();
var fieldInfo = type.GetField(value.ToString());
var attribs = fieldInfo.GetCustomAttributes(
typeof(StringValueAttribute), false) as StringValueAttribute[];
return attribs.Length > 0 ? attribs[0].StringValue : null;
}
}
