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