/// <summary>Executes and mapping objects by ColumnName - PropertyName.</summary>
/// <typeparam name="T">Mapping target Class.</typeparam>
/// <param name="query">SQL code.</param>
/// <param name="parameter">PropertyName parameterized to PropertyName. if null then no use parameter.</param>
/// <param name="commandType">Command Type.</param>
/// <returns>Mapped instances.</returns>
public IEnumerable <T> Select <T>(string query, object parameter = null, CommandType commandType = CommandType.Text) where T : new()
{
Contract.Requires <ArgumentException>(!String.IsNullOrEmpty(query));
Contract.Ensures(Contract.Result <IEnumerable <T> >() != null);
var accessors = AccessorCache.Lookup(typeof(T));
return(ExecuteReader(query, parameter, commandType, CommandBehavior.SequentialAccess)
.Select(dr =>
{
// if T is ValueType then can't set SetValue
// must be boxed
object result = new T();
for (int i = 0; i < dr.FieldCount; i++)
{
if (dr.IsDBNull(i))
{
continue;
}
var accessor = accessors[dr.GetName(i)];
if (accessor != null && accessor.IsWritable)
{
accessor.SetValueDirect(result, dr[i]);
}
}
return (T)result;
}));
}
/// <summary>If connection is not open then open and create command.</summary>
/// <param name="query">SQL code.</param>
/// <param name="commandType">Command Type.</param>
/// <param name="parameter">PropertyName parameterized to PropertyName. if null then no use parameter.</param>
/// <param name="extraParameter">CommandName set to __extra__PropertyName.</param>
/// <returns>Setuped IDbCommand.</returns>
protected IDbCommand PrepareExecute(string query, CommandType commandType, object parameter, object extraParameter = null)
{
Contract.Requires <ArgumentException>(!String.IsNullOrEmpty(query));
Contract.Ensures(Contract.Result <IDbCommand>() != null);
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
if (transaction == null && isUseTransaction)
{
transaction = connection.BeginTransaction(isolationLevel);
}
var command = connection.CreateCommand();
command.CommandText = query;
command.CommandType = commandType;
if (parameter != null)
{
foreach (var p in AccessorCache.Lookup(parameter.GetType()))
{
if (!p.IsReadable)
{
continue;
}
Contract.Assume(parameter != null);
var param = command.CreateParameter();
param.ParameterName = p.Name;
param.Value = p.GetValueDirect(parameter);
command.Parameters.Add(param);
}
}
if (extraParameter != null)
{
foreach (var p in AccessorCache.Lookup(extraParameter.GetType()))
{
if (!p.IsReadable)
{
continue;
}
Contract.Assume(extraParameter != null);
var param = command.CreateParameter();
param.ParameterName = "__extra__" + p.Name;
param.Value = p.GetValueDirect(extraParameter);
command.Parameters.Add(param);
}
}
if (transaction != null)
{
command.Transaction = transaction;
}
return(command);
}
/// <summary>Delete by object's PropertyName.</summary>
/// <param name="tableName">Target database's table.</param>
/// <param name="whereCondition">Where condition extracted from PropertyName.</param>
/// <returns>Rows affected.</returns>
public int Delete(string tableName, object whereCondition)
{
var where = string.Join(" and ", AccessorCache.Lookup(whereCondition.GetType())
.Select(p => p.Name + " = " + parameterSymbol + p.Name));
var query = string.Format("delete from {0} where {1}", tableName, where);
return(ExecuteNonQuery(query, whereCondition));
}
/// <summary>Insert by object's PropertyName.</summary>
/// <param name="tableName">Target database's table.</param>
/// <param name="insertItem">Table's column name extracted from PropertyName.</param>
/// <returns>Rows affected.</returns>
public int Insert(string tableName, object insertItem)
{
var propNames = AccessorCache.Lookup(insertItem.GetType())
.Where(p => p.IsReadable)
.ToArray();
var column = string.Join(", ", propNames.Select(p => p.Name));
var data = string.Join(", ", propNames.Select(p => parameterSymbol + p.Name));
var query = string.Format("insert into {0} ({1}) values ({2})", tableName, column, data);
return(ExecuteNonQuery(query, insertItem));
}
/// <summary>Delete by object's PropertyName.</summary>
/// <param name="tableName">Target database's table.</param>
/// <param name="whereCondition">Where condition extracted from PropertyName.</param>
/// <returns>Rows affected.</returns>
public int Delete(string tableName, object whereCondition)
{
Contract.Requires <ArgumentException>(!String.IsNullOrEmpty(tableName));
Contract.Requires <ArgumentNullException>(whereCondition != null);
var where = string.Join(" and ", AccessorCache.Lookup(whereCondition.GetType())
.Select(p => p.Name + " = " + parameterSymbol + p.Name));
var query = string.Format("delete from {0} where {1}", tableName, where);
Contract.Assume(query.Length > 0);
return(ExecuteNonQuery(query, whereCondition));
}
/// <summary>Insert by object's PropertyName.</summary>
/// <param name="tableName">Target database's table.</param>
/// <param name="insertItem">Table's column name extracted from PropertyName.</param>
/// <returns>Rows affected.</returns>
public int Insert(string tableName, object insertItem)
{
Contract.Requires <ArgumentException>(!String.IsNullOrEmpty(tableName));
Contract.Requires <ArgumentNullException>(insertItem != null);
var propNames = AccessorCache.Lookup(insertItem.GetType())
.Where(p => p.IsReadable)
.ToArray();
var column = string.Join(", ", propNames.Select(p => p.Name));
var data = string.Join(", ", propNames.Select(p => parameterSymbol + p.Name));
var query = string.Format("insert into {0} ({1}) values ({2})", tableName, column, data);
Contract.Assume(query.Length > 0);
return(ExecuteNonQuery(query, insertItem));
}
/// <summary>Update by object's PropertyName.</summary>
/// <param name="tableName">Target database's table.</param>
/// <param name="updateItem">Table's column name extracted from PropertyName.</param>
/// <param name="whereCondition">Where condition extracted from PropertyName.</param>
/// <returns>Rows affected.</returns>
public int Update(string tableName, object updateItem, object whereCondition)
{
var update = string.Join(", ", AccessorCache.Lookup(updateItem.GetType())
.Where(p => p.IsReadable)
.Select(p => p.Name + " = " + parameterSymbol + p.Name));
var where = string.Join(" and ", AccessorCache.Lookup(whereCondition.GetType())
.Select(p => p.Name + " = " + parameterSymbol + "__extra__" + p.Name));
var query = string.Format("update {0} set {1} where {2}", tableName, update, where);
using (var command = PrepareExecute(query, CommandType.Text, updateItem, whereCondition))
{
return(command.ExecuteNonQuery());
}
}
/// <summary>Update by object's PropertyName.</summary>
/// <param name="tableName">Target database's table.</param>
/// <param name="updateItem">Table's column name extracted from PropertyName.</param>
/// <param name="whereCondition">Where condition extracted from PropertyName.</param>
/// <returns>Rows affected.</returns>
public int Update(string tableName, object updateItem, object whereCondition)
{
Contract.Requires <ArgumentException>(!String.IsNullOrEmpty(tableName));
Contract.Requires <ArgumentNullException>(whereCondition != null);
Contract.Requires <ArgumentNullException>(updateItem != null);
var update = string.Join(", ", AccessorCache.Lookup(updateItem.GetType())
.Where(p => p.IsReadable)
.Select(p => p.Name + " = " + parameterSymbol + p.Name));
var where = string.Join(" and ", AccessorCache.Lookup(whereCondition.GetType())
.Select(p => p.Name + " = " + parameterSymbol + "__extra__" + p.Name));
var query = string.Format("update {0} set {1} where {2}", tableName, update, where);
Contract.Assume(query.Length > 0);
using (var command = PrepareExecute(query, CommandType.Text, updateItem, whereCondition))
{
return(command.ExecuteNonQuery());
}
}
public void StructTest()
{
var accessors = AccessorCache.Lookup(typeof(TestMockStruct))
.OrderBy(x => x.Name)
.ToArray();
accessors.Length.Is(10);
accessors.All(a => a.DeclaringType == typeof(TestMockStruct));
accessors.Take(8).All(a => a.IsReadable);
accessors.Take(8).All(a => a.IsWritable);
accessors.Select(a => a.Name).Is("Field1", "Field2", "Property1", "Property2", "Property3", "Property4", "Property5", "Property6", "PropertyReadOnly", "PropertySetOnly");
object target = new TestMockStruct();
accessors[0].SetValue(target, "a");
accessors[2].SetValue(target, "b");
accessors[4].SetValue(target, "c");
accessors[6].IsWritable.Is(false);
accessors[1].SetValue(target, 1);
accessors[3].SetValue(target, 2);
accessors[5].SetValue(target, 3);
accessors[7].IsWritable.Is(false);
accessors.Where(a => a.IsReadable).Select(a => a.GetValue(target))
.Is("a", 1, "b", 2, null, 0, null);
Enumerable.Repeat((TestMockStruct)target, 1)
.SelectMany(m => new object[] { m.Field1, m.Field2, m.Property1, m.Property2, m.Property5, m.Property6 })
.Is("a", 1, "b", 2, null, 0);
var read = accessors[8];
read.IsReadable.Is(true);
read.IsWritable.Is(false);
var write = accessors[9];
write.IsReadable.Is(false);
write.IsWritable.Is(true);
write.SetValue(target, "test");
Assert.AreEqual("test", ((TestMockStruct)target).AsDynamic().hiddenField);
}