/// <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); }