// Jeffrey Becker Jan 2012
// Released to the public domain, use at your own risk!
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using System.Text.RegularExpressions;
namespace CrapyDataAccess
{
public class Db : IDisposable
{
private readonly DbConnection _connection;
private readonly HydraterFactory _hydraterFactory = new HydraterFactory();
private DbTransaction _transaction;
private readonly DbProviderFactory _factory;
public Db(string connectionStringName)
{
var css = ConfigurationManager.ConnectionStrings[connectionStringName];
if (css == null)
throw new InvalidOperationException(String.Format("No ConnectionString {0} defined.",
connectionStringName));
if (String.IsNullOrEmpty(css.ConnectionString))
throw new InvalidOperationException(
String.Format("The ConnectionString {0} doesn't have a connectionString value defined.",
connectionStringName));
if (String.IsNullOrEmpty(css.ProviderName))
throw new InvalidOperationException(
String.Format("The ConnectionString {0} doesn't have a providerName defined.", connectionStringName));
_factory = DbProviderFactories.GetFactory(css.ProviderName);
if(_factory == null)
_connection = _factory.CreateConnection();
_connection.ConnectionString = css.ConnectionString;
_connection.Open();
}
public void Dispose()
{
if (_transaction != null)
{
_transaction.Commit();
_transaction.Dispose();
}
_connection.Dispose();
}
public IEnumerable<T> Execute<T>(string sql, object args = null)
{
using (var cmd = CreateCommand(sql, args))
using (var reader = cmd.ExecuteReader())
{
var hydrater = _hydraterFactory.GetHydrater<T>();
while (reader.Read())
{
yield return hydrater.Hydrate(reader);
}
}
}
public T Scalar<T>(string sql, object args = null)
{
return Execute<T>(sql, args).First();
}
public void Run(string sql, object args = null)
{
using (var cmd = CreateCommand(sql, args))
{
cmd.ExecuteNonQuery();
}
}
public void Begin()
{
if (_transaction == null)
{
_transaction = _connection.BeginTransaction();
}
}
public void Commit()
{
if (_transaction != null)
{
_transaction.Commit();
_transaction.Dispose();
_transaction = null;
}
}
public void Rollback()
{
if (_transaction != null)
{
_transaction.Rollback();
_transaction.Dispose();
_transaction = null;
}
}
private DbCommand CreateCommand(string sql, object args)
{
if (String.IsNullOrEmpty(sql))
throw new ArgumentException("Sql must not be null or empty");
var cmd = _connection.CreateCommand();
cmd.CommandText = sql;
if (_transaction != null)
{
cmd.Transaction = _transaction;
}
if (args != null)
{
var paramsToAdd = args.GetType()
.GetProperties()
.Select(p =>
{
var param = cmd.CreateParameter();
param.DbType = TypeConverter.ToDbType(p.PropertyType);
param.ParameterName = "@" + p.Name;
param.Value = p.GetValue(args, null) ?? DBNull.Value;
param.Direction = ParameterDirection.Input;
return param;
});
foreach (var p in paramsToAdd)
{
if (Regex.IsMatch(sql, "\\b" + p.ParameterName + "\\b",
RegexOptions.IgnoreCase | RegexOptions.CultureInvariant))
{
cmd.Parameters.Add(p);
}
}
}
return cmd;
}
#region Nested type: ColumnInfo
private class ColumnInfo
{
private readonly bool _isNullable;
private readonly Type _parentType;
private readonly PropertyInfo _propertyInfo;
public ColumnInfo(Type parentType, PropertyInfo propertyInfo)
{
_parentType = parentType;
_propertyInfo = propertyInfo;
Name = _propertyInfo.Name;
_isNullable = TypeConverter.IsNullable(_propertyInfo.PropertyType);
}
public string Name { get; protected set; }
public void AssignValue(object o, object instance)
{
try
{
if (_isNullable && o == DBNull.Value)
o = null;
_propertyInfo.SetValue(instance, o, null);
}
catch (ArgumentException ex)
{
throw new InvalidOperationException(
String.Format("Error hydrating {0}.{1}: \"{2}\"", _parentType.Name, Name, ex.Message), ex);
}
}
}
#endregion
#region Nested type: EntityHydrater
private class EntityHydrater<T> : IHydrater<T>
{
private readonly EntityInfo _entityInfo;
private IDictionary<int, ColumnInfo> _lookup;
public EntityHydrater(EntityInfo entityInfo)
{
_entityInfo = entityInfo;
}
#region IHydrater<T> Members
public T Hydrate(IDataRecord dr)
{
_lookup = _lookup ?? _entityInfo.BuildLookup(dr);
object instance = Activator.CreateInstance(typeof (T));
for (int i = 0; i < dr.FieldCount; i++)
{
if (_lookup.ContainsKey(i))
{
_lookup[i].AssignValue(dr.GetValue(i), instance);
}
}
return (T) instance;
}
#endregion
}
#endregion
#region Nested type: EntityInfo
private class EntityInfo
{
private readonly ICollection<ColumnInfo> _columns;
private readonly Type _type;
public EntityInfo(Type type)
{
_type = type;
bool hasParameterlessConstructor = _type.GetConstructors()
.Any(c => !c.GetParameters().Any());
if (!hasParameterlessConstructor)
throw new InvalidOperationException(
String.Format("{0} does not have a public parameterless constructor.", _type.Name));
_columns = _type.GetProperties()
.Where(p => p.CanWrite)
.Select(p => new ColumnInfo(_type, p))
.ToList();
}
public object CreateInstance()
{
return Activator.CreateInstance(_type);
}
public IDictionary<int, ColumnInfo> BuildLookup(IDataRecord dr)
{
return
Enumerable.Range(0, dr.FieldCount)
.Select(i =>
new
{
Index = i,
Column =
_columns.FirstOrDefault(c => string.Compare(c.Name, dr.GetName(i), true) == 0)
})
.Where(x => x.Column != null)
.ToDictionary(x => x.Index, x => x.Column);
}
}
#endregion
#region Nested type: HydraterFactory
private class HydraterFactory
{
private readonly Dictionary<Type, EntityInfo> _entityInfos = new Dictionary<Type, EntityInfo>();
public IHydrater<T> GetHydrater<T>()
{
Type type = typeof (T);
if (TypeConverter.IsScalarType(type))
return new ScalarHydrater<T>();
if (!_entityInfos.ContainsKey(type))
{
_entityInfos.Add(type, new EntityInfo(type));
}
return new EntityHydrater<T>(_entityInfos[type]);
}
}
#endregion
#region Nested type: IHydrater
private interface IHydrater<T>
{
T Hydrate(IDataRecord dr);
}
#endregion
#region Nested type: ScalarHydrater
private class ScalarHydrater<T> : IHydrater<T>
{
private readonly bool _isNullable;
private readonly Type _type;
public ScalarHydrater()
{
Type type = typeof (T);
_isNullable = TypeConverter.IsNullable(type);
_type = _isNullable ? TypeConverter.GetNonNullable(type) : type;
}
#region IHydrater<T> Members
public T Hydrate(IDataRecord dr)
{
if (dr.FieldCount == 0)
throw new InvalidOperationException("No fields were returned");
object obj = dr.GetValue(0);
if (obj == DBNull.Value)
{
if (!_isNullable)
{
throw new InvalidOperationException(String.Format("The Column {0} is NULL", dr.GetName(0)));
}
else
return (T) (object) null;
}
else
return (T) Convert.ChangeType(obj, _type);
}
#endregion
}
#endregion
#region Nested type: TypeConverter
private static class TypeConverter
{
private static readonly Dictionary<Type, DbType> TypeToDbType
= new Dictionary<Type, DbType>
{
{typeof (string), DbType.String}, {typeof (DateTime), DbType.DateTime}, {typeof (DateTime?), DbType.DateTime },
{typeof (int), DbType.Int32}, {typeof (int?), DbType.Int32}, {typeof (long), DbType.Int64},
{typeof (long?), DbType.Int64}, {typeof (bool), DbType.Boolean}, {typeof (bool?), DbType.Boolean},
{typeof (byte[]), DbType.Binary}, {typeof (decimal), DbType.Decimal}, {typeof (decimal?), DbType.Decimal},
{typeof (double), DbType.Double}, {typeof (double?), DbType.Double}, {typeof (float), DbType.Single},
{typeof (float?), DbType.Single}, {typeof (Guid), DbType.Guid}, {typeof (Guid?), DbType.Guid}
};
public static bool IsScalarType(Type type)
{
return TypeToDbType.ContainsKey(type);
}
public static bool IsNullable(Type type)
{
return !type.IsValueType ||
(type.IsGenericType && type.GetGenericTypeDefinition() == typeof (Nullable<>));
}
public static Type GetNonNullable(Type type)
{
return type.GetGenericArguments()[0];
}
public static DbType ToDbType(Type type)
{
if (!TypeToDbType.ContainsKey(type))
{
throw new InvalidOperationException(
string.Format("Type {0} doesn't have a matching DbType configured", type.FullName));
}
return TypeToDbType[type];
}
}
#endregion
}
}