public static Func <TTable, TTable> PrimaryKeyClone <TTable>() { if (PrimaryKeyCloneExpressionCache.TryGetValue(typeof(TTable), out Delegate resDelegate)) { return((Func <TTable, TTable>)resDelegate); } // Instance type of target entity class // e.g.: TTable record ParameterExpression instanceParamLeft = Expression.Variable(typeof(TTable)); // Create and assign new TTable to variable. Ex. var instance = new TTable(); // e.g.: record = new TTable BinaryExpression createInstanceLeft = Expression.Assign(instanceParamLeft, Expression.New(typeof(TTable))); // List of statements in our dynamic method var statements = new List <Expression> { createInstanceLeft }; // Parameter for the TTable object ParameterExpression instanceParamRight = Expression.Parameter(typeof(TTable)); List <FieldSchema> fieldsSchema = TableSchemaResolver.GetTableSchema(typeof(TTable)).FieldsSchema; foreach (var field in fieldsSchema) { if (!field.IsPrimaryKey && !field.IsUpdCount) { continue; } // instance.Property // e.g.: TTable.Property property MemberExpression getPropertyLeft = Expression.Property(instanceParamLeft, field.PropertyInfo); MemberExpression getPropertyRight = Expression.Property(instanceParamRight, field.PropertyInfo); // e.g.: property = value var assignProperty = Expression.Assign(getPropertyLeft, getPropertyRight); statements.Add(assignProperty); } // e.g.: return record var returnStatement = instanceParamLeft; statements.Add(returnStatement); var body = Expression.Block(instanceParamLeft.Type, new[] { instanceParamLeft }, statements.ToArray()); var lambda = Expression.Lambda <Func <TTable, TTable> >(body, instanceParamRight); resDelegate = lambda.Compile(); // Cache the dynamic method into ExpressionCache dictionary PrimaryKeyCloneExpressionCache[typeof(TTable)] = resDelegate; return((Func <TTable, TTable>)resDelegate); }
public List <TTable> Select(int count = -1) { TableSchema tableSchema = TableSchemaResolver.GetTableSchema(typeof(TTable)); _sqlBuilder.From(tableSchema.PrimaryTable); _sqlBuilder.Top(count); for (int i = 0, fieldCount = tableSchema.FieldsSchema.Count; i < fieldCount; i++) { var field = tableSchema.FieldsSchema[i]; if (this._fieldsUsable[i] == true) { string column = field.ToString(); if (string.IsNullOrEmpty(field.AliasName) == false) { column = string.Format("{0} AS {1}", column, field.AliasName); } _sqlBuilder.Column(column); } } this.Join(tableSchema); _recordsCache = _excuteQuery.Excute <TTable>(_sqlBuilder.Query(), _parameters, _fieldsUsable); if (_recordsCache == null) { this._dbError.Code = _excuteQuery.dbError.Code; this._dbError.Text = _excuteQuery.dbError.Text; return(null); } return(_recordsCache.Select(x => x.recordOut).ToList()); }
internal void SetFieldUsable <T>(Expression <T> predicate, ClauseType type) { try { var result = _translateQuery.Translate(predicate, type); string commandText = TranslateToNativeColumn(result.CommandText); TableSchema tableSchema = TableSchemaResolver.GetTableSchema(typeof(TTable)); var fieldIndex = tableSchema.FieldsSchema.FindIndex(x => x.ToString() == commandText); if (fieldIndex != -1) { var field = tableSchema.FieldsSchema[fieldIndex]; if (field.IsPrimaryKey || field.IsUpdCount) { this._fieldsUsable[fieldIndex] = true; } else { this._fieldsUsable[fieldIndex] = type == ClauseType.Take; } } } catch (Exception e) { this._dbError.Code = ErrorCode.InvalidOperation; this._dbError.Text = e.Message; } }
private TranslateResult TranslateValues(object instance, Type type, List <Parameter> parameters) { TableSchema tableSchema = TableSchemaResolver.GetTableSchema(type); for (int i = 0, count = tableSchema.FieldsSchema.Count; i < count; i++) { var field = tableSchema.FieldsSchema[i]; object value = ExpressionFunc.GetPropertyValue(instance, type, field.PropertyInfo); if (value == null && field.PropertyInfo.PropertyType.Name == "String") { value = ""; } if (parameters == null) { clauseBuilder.AppendClause(parameterBuilder.GenerateParameterName(), value); } else { clauseBuilder.AppendClause(parameters[i].Name, value); } } return(new TranslateResult { CommandText = clauseBuilder.GetExpressionText(","), // INSERT ... VALUES(@value1,@value2,@value3,...) Parameters = clauseBuilder.GetExpressionParameters(), }); }
public bool AddNew(List <TTable> records) { if (records == null || records.Count == 0) { return(false); } List <List <Parameter> > listParameters = new List <List <Parameter> >(); TranslateResult resultFirst = null; TranslateResult resultNext = null; for (int i = 0, count = records.Count; i < count; i++) { var record = records[i]; if (i == 0) { resultFirst = PreAddNew(record, null); listParameters.Add(resultFirst.Parameters); } else { resultNext = PreAddNew(record, resultFirst.Parameters); listParameters.Add(resultNext.Parameters); } } TableSchema tableSchema = TableSchemaResolver.GetTableSchema(typeof(TTable)); _sqlBuilder.From(tableSchema.PrimaryTable); for (int i = 0, fieldCount = tableSchema.FieldsSchema.Count; i < fieldCount; i++) { _sqlBuilder.Column(tableSchema.FieldsSchema[i].FieldName); } _dbSet.BeginTransaction(); if (listParameters.Count == 1) { this._dbError = _excuteUpdate.Excute <TTable>(_sqlBuilder.Insert(), listParameters[0], _dbSet.Transaction); } else { this._dbError = _excuteUpdate.ExcuteBatch <TTable>(_sqlBuilder.Insert(), listParameters, _dbSet.Transaction); } if (this._dbError.Code == ErrorCode.Success) { records.ForEach(rec => InsertRecordToCache(rec)); } else { _dbSet.Cancel(); } return(this._dbError.Code == ErrorCode.Success); }
private TranslateResult TranslateColumnValue(object instance, Type type, List <bool> fieldsUsable, List <Parameter> parameters) { bool isFirst = true; TableSchema tableSchema = TableSchemaResolver.GetTableSchema(type); for (int i = 0, count = tableSchema.FieldsSchema.Count; i < count; i++) { var field = tableSchema.FieldsSchema[i]; if (fieldsUsable[i] && field.IsPrimaryKey == false) { object value = ExpressionFunc.GetPropertyValue(instance, type, field.PropertyInfo); if (value == null && field.PropertyInfo.PropertyType.Name == "String") { value = ""; } if (value == null) { throw new NotSupportedException(string.Format("The field '{0}' is not supported", field.FieldName)); } if (!isFirst) { clauseBuilder.AppendClause(","); } clauseBuilder.AppendClause(field.FieldName); clauseBuilder.AppendClause("="); if (field.IsUpdCount) { int iValue = Convert.ToInt32(value); if (iValue == short.MaxValue) { iValue = 1; } else { iValue++; } value = Convert.ChangeType(iValue, field.PropertyInfo.PropertyType); ExpressionFunc.SetPropertyValue(instance, type, field.PropertyInfo, value); } clauseBuilder.AppendClause(parameterBuilder.GenerateParameterName(), value); isFirst = false; } } return(new TranslateResult { CommandText = clauseBuilder.GetExpressionText(), // UPDATE ... column1=@value1,column2=@value2,... Parameters = clauseBuilder.GetExpressionParameters(), }); }
//private void Join<TPrimary, TSecondary>(Expression<Func<TPrimary, TSecondary, bool>> predicate, JOINTYPE type) //{ // try // { // var result = _translateQuery.Translate(predicate, ClauseType.Join); // TableSchema tableSchema = TableSchemaResolver.GetTableSchema(typeof(TSecondary)); // _sqlBuilder.Join(tableSchema.PrimaryTable, result.CommandText, type); // } // catch(Exception e) // { // this._dbError.Code = ErrorCode.InvalidOperation; // this._dbError.Text = e.Message; // } //} //public IQueryJoinable<TTable> Join<TPrimary, TSecondary>(Expression<Func<TPrimary, TSecondary, bool>> predicate) //{ // this.Join(predicate, JOINTYPE.INNER); // return this; //} //public IQueryJoinable<TTable> LeftJoin<TPrimary, TSecondary>(Expression<Func<TPrimary, TSecondary, bool>> predicate) //{ // this.Join(predicate, JOINTYPE.LEFT); // return this; //} //public IQueryJoinable<TTable> RightJoin<TPrimary, TSecondary>(Expression<Func<TPrimary, TSecondary, bool>> predicate) //{ // this.Join(predicate, JOINTYPE.RIGHT); // return this; //} protected override string TranslateToNativeColumn(string commandText) { TableSchema tableSchema = TableSchemaResolver.GetTableSchema(typeof(TTable)); for (int i = 0, count = tableSchema.FieldsSchema.Count; i < count; i++) { var field = tableSchema.FieldsSchema[i]; string column = string.Format("{0}.{1}", field.PropertyInfo.DeclaringType.Name, field.PropertyInfo.Name); if (commandText.IndexOf(column) != -1) { commandText = commandText.Replace(column, field.ToString()); } } return(commandText); }
private void SetFieldUsable(bool usable) { TableSchema tableSchema = TableSchemaResolver.GetTableSchema(typeof(TTable)); for (int i = 0, fieldCount = this._fieldsUsable.Count; i < fieldCount; i++) { var field = tableSchema.FieldsSchema[i]; if (field.IsPrimaryKey || field.IsUpdCount) { this._fieldsUsable[i] = true; } else { this._fieldsUsable[i] = usable; } } }
public bool Delete() { TableSchema tableSchema = TableSchemaResolver.GetTableSchema(typeof(TTable)); _sqlBuilder.From(tableSchema.PrimaryTable); _dbSet.BeginTransaction(); this._dbError = _excuteUpdate.Excute <TTable>(_sqlBuilder.DeleteAll(), null, _dbSet.Transaction); if (this._dbError.Code != ErrorCode.Success) { _dbSet.Cancel(); } return(this._dbError.Code == ErrorCode.Success); }
public TableQuery(DbSet dbSet) { this._dbSet = dbSet; this._dbError = new DbError(); this._translateQuery = new QueryTranslator(); this._sqlBuilder = new SqlBuilder(); this._parameters = new List <Parameter>(); this._excuteQuery = new Query(dbSet.DbConnection); this._fieldsUsable = new List <bool>(); TableSchema tableSchema = TableSchemaResolver.GetTableSchema(typeof(TTable)); for (int i = 0, count = tableSchema.FieldsSchema.Count; i < count; i++) { this._fieldsUsable.Add(true); } }
private TranslateResult TranslateWhere(object instance, Type type, List <Parameter> parameters) { bool isFirst = true; TableSchema tableSchema = TableSchemaResolver.GetTableSchema(type); int paramIndex = 0; for (int i = 0, count = tableSchema.FieldsSchema.Count; i < count; i++) { var field = tableSchema.FieldsSchema[i]; if (field.IsPrimaryKey || field.IsUpdCount) { object value = ExpressionFunc.GetPropertyValue(instance, type, field.PropertyInfo); if (value == null) { throw new NotSupportedException(string.Format("The field '{0}' is not supported", field.FieldName)); } if (!isFirst) { clauseBuilder.AppendClause(" And "); } clauseBuilder.AppendClause(field.FieldName); clauseBuilder.AppendClause("="); if (parameters == null) { clauseBuilder.AppendClause(parameterBuilder.GenerateParameterName(), value); } else { clauseBuilder.AppendClause(parameters[paramIndex++].Name, value); } isFirst = false; } } return(new TranslateResult { CommandText = clauseBuilder.GetExpressionText(), // UPDATE ...Where key_column=some_value And UpdCount=some_value // DELETE ...Where key_column=some_value And UpdCount=some_value Parameters = clauseBuilder.GetExpressionParameters(), }); }
public bool Delete(List <TTable> records) { if (records == null || records.Count == 0) { return(false); } Action <TTable, TTable> copyAction = ExpressionFunc.PrimaryKeyCopy <TTable>(); List <List <Parameter> > listParameters = new List <List <Parameter> >(); TranslateResult resultFirst = null; TranslateResult resultNext = null; for (int i = 0, count = records.Count; i < count; i++) { var record = records[i]; if (IsValidRecord(record) == false) { return(false); } copyAction(record, GetCacheRecord(record)); if (i == 0) { resultFirst = _translate.Translator(record, typeof(TTable), _fieldsUsable, ClauseType.SaveWhere); _sqlBuilder.SaveWhere(resultFirst.CommandText); listParameters.Add(resultFirst.Parameters); } else { resultNext = _translate.Translator(record, typeof(TTable), _fieldsUsable, ClauseType.SaveWhere, resultFirst.Parameters); listParameters.Add(resultNext.Parameters); } } TableSchema tableSchema = TableSchemaResolver.GetTableSchema(typeof(TTable)); _sqlBuilder.From(tableSchema.PrimaryTable); _dbSet.BeginTransaction(); if (listParameters.Count == 1) { this._dbError = _excuteUpdate.Excute <TTable>(_sqlBuilder.Delete(), listParameters[0], _dbSet.Transaction); } else { this._dbError = _excuteUpdate.ExcuteBatch <TTable>(_sqlBuilder.Delete(), listParameters, _dbSet.Transaction); } if (this._dbError.Code == ErrorCode.Success) { records.ForEach(rec => DeleteRecordFromCache(rec)); } else { _dbSet.Cancel(); } return(this._dbError.Code == ErrorCode.Success); }
// Cache store for memorizing the delegate for later use //ConcurrentDictionary<Type, Delegate> ExpressionCache = new ConcurrentDictionary<Type, Delegate>(); // Method for creating the dynamic funtion for setting entity properties public static Func <DbDataReader, TTable> GetReader <TTable>(List <bool> fieldsUsable) { //if (ExpressionCache.TryGetValue(typeof(TTable), out Delegate resDelegate)) //{ // return (Func<DbDataReader, TTable>)resDelegate; //} List <FieldSchema> fieldsSchema = TableSchemaResolver.GetTableSchema(typeof(TTable)).FieldsSchema; if (fieldsUsable != null && fieldsUsable.Count != fieldsSchema.Count) { return(null); } // Get the indexer property of DbDataReader // e.g.: dbReader["name"] var indexerProperty = typeof(DbDataReader).GetProperty("Item", new[] { typeof(string) }); // Instance type of target entity class // e.g.: TTable record ParameterExpression instanceParam = Expression.Variable(typeof(TTable)); // Create and assign new TTable to variable. Ex. var instance = new TTable(); // e.g.: record = new TTable BinaryExpression createInstance = Expression.Assign(instanceParam, Expression.New(typeof(TTable))); // Parameter for the DbDataReader object ParameterExpression readerParam = Expression.Parameter(typeof(DbDataReader)); // List of statements in our dynamic method var statements = new List <Expression> { createInstance }; for (int i = 0, fieldCount = fieldsSchema.Count; i < fieldCount; i++) //foreach (var field in fieldsSchema) { var field = fieldsSchema[i]; if (fieldsUsable != null && fieldsUsable[i] == false) { continue; } var property = field.PropertyInfo; string columnName = field.FieldName; if (string.IsNullOrEmpty(field.AliasName) == false) { columnName = field.AliasName; } var subStatements = new List <Expression>(); // instance.Property // e.g.: TTable.Property property MemberExpression getProperty = Expression.Property(instanceParam, property); // row[property] The assumption is, column names are the // same as PropertyInfo names of TTable IndexExpression readValue = Expression.MakeIndex(readerParam, indexerProperty, new[] { Expression.Constant(columnName) }); // e.g.: property = value var assignProperty = Expression.Assign(getProperty, Expression.Convert(readValue, property.PropertyType)); subStatements.Add(assignProperty); if (property.PropertyType.Name == "String") { var call = Expression.Call(null, typeof(ExpressionFunc).GetMethod("TrimEnd", new[] { typeof(string) }), getProperty); subStatements.Add(Expression.Assign(getProperty, call)); } // if the column name dose not exist or data is DBNull TryExpression tryCatchExpr = Expression.TryCatch( Expression.Block(subStatements.ToArray()), Expression.Catch(typeof(Exception), Expression.Default(property.PropertyType)) ); statements.Add(tryCatchExpr); } // e.g.: return record var returnStatement = instanceParam; statements.Add(returnStatement); var body = Expression.Block(instanceParam.Type, new[] { instanceParam }, statements.ToArray()); var lambda = Expression.Lambda <Func <DbDataReader, TTable> >(body, readerParam); //Func<DbDataReader, TTable> resDelegate = lambda.Compile(); // Cache the dynamic method into ExpressionCache dictionary //ExpressionCache[typeof(TTable)] = resDelegate; return(lambda.Compile()); }