public override int Update <TEntity>(Expression <Func <TEntity, bool> > condition, Expression <Func <TEntity, TEntity> > content, string table)
{
PublicHelper.CheckNull(condition);
PublicHelper.CheckNull(content);
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
Dictionary <MemberInfo, Expression> updateColumns = InitMemberExtractor.Extract(content);
DbTable explicitDbTable = null;
if (table != null)
{
explicitDbTable = new DbTable(table, typeDescriptor.Table.Schema);
}
DefaultExpressionParser expressionParser = typeDescriptor.GetExpressionParser(explicitDbTable);
DbExpression conditionExp = expressionParser.ParseFilterPredicate(condition);
DbUpdateExpression e = new DbUpdateExpression(explicitDbTable ?? typeDescriptor.Table, conditionExp);
foreach (var kv in updateColumns)
{
MemberInfo key = kv.Key;
PropertyDescriptor propertyDescriptor = typeDescriptor.TryGetPropertyDescriptor(key);
if (propertyDescriptor == null)
{
throw new ChloeException(string.Format("The member '{0}' does not map any column.", key.Name));
}
if (propertyDescriptor.IsPrimaryKey)
{
throw new ChloeException(string.Format("Could not update the primary key '{0}'.", propertyDescriptor.Column.Name));
}
bool hasSequence = propertyDescriptor.HasSequence();
if (hasSequence)
{
throw new ChloeException(string.Format("Could not update the column '{0}', because it's mapping member has define a sequence.", propertyDescriptor.Column.Name));
}
e.UpdateColumns.Add(propertyDescriptor.Column, expressionParser.Parse(kv.Value));
}
if (e.UpdateColumns.Count == 0)
{
return(0);
}
return(this.ExecuteSqlCommand(e));
}
string AppendInsertRangeSqlTemplate(DbTable table, List <PropertyDescriptor> mappingPropertyDescriptors, bool keepIdentity)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("INSERT INTO ");
sqlBuilder.Append(this.AppendTableName(table));
sqlBuilder.Append("(");
for (int i = 0; i < mappingPropertyDescriptors.Count; i++)
{
PropertyDescriptor mappingPropertyDescriptor = mappingPropertyDescriptors[i];
if (i > 0)
{
sqlBuilder.Append(",");
}
sqlBuilder.Append(this.QuoteName(mappingPropertyDescriptor.Column.Name));
}
sqlBuilder.Append(")");
if (keepIdentity == false)
{
sqlBuilder.Append(" SELECT ");
for (int i = 0; i < mappingPropertyDescriptors.Count; i++)
{
PropertyDescriptor mappingPropertyDescriptor = mappingPropertyDescriptors[i];
if (i > 0)
{
sqlBuilder.Append(",");
}
if (mappingPropertyDescriptor.HasSequence())
{
sqlBuilder.AppendFormat("{0}.{1}", this.QuoteName(mappingPropertyDescriptor.Definition.SequenceName), this.QuoteName("NEXTVAL"));
}
else
{
sqlBuilder.Append("C").Append(i.ToString());
}
}
sqlBuilder.Append(" FROM (");
}
string sqlTemplate = sqlBuilder.ToString();
return(sqlTemplate);
}
public override void InsertRange <TEntity>(List <TEntity> entities, bool keepIdentity = false, string table = null)
{
/*
* 将 entities 分批插入数据库
* 每批生成 insert into TableName(...) select ... from dual union all select ... from dual...
* 对于 oracle，貌似速度提升不了...- -
* #期待各码友的优化建议#
*/
PublicHelper.CheckNull(entities);
if (entities.Count == 0)
{
return;
}
int maxParameters = 1000;
int batchSize = 40; /* 每批实体大小，此值通过测试得出相对插入速度比较快的一个值 */
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
List <PropertyDescriptor> mappingPropertyDescriptors = typeDescriptor.PropertyDescriptors.ToList();
int maxDbParamsCount = maxParameters - mappingPropertyDescriptors.Count; /* 控制一个 sql 的参数个数 */
DbTable dbTable = string.IsNullOrEmpty(table) ? typeDescriptor.Table : new DbTable(table, typeDescriptor.Table.Schema);
string sqlTemplate = AppendInsertRangeSqlTemplate(dbTable, mappingPropertyDescriptors, keepIdentity);
Action insertAction = () =>
{
int batchCount = 0;
List <DbParam> dbParams = new List <DbParam>();
StringBuilder sqlBuilder = new StringBuilder();
for (int i = 0; i < entities.Count; i++)
{
var entity = entities[i];
if (batchCount > 0)
{
sqlBuilder.Append(" UNION ALL ");
}
sqlBuilder.Append(" SELECT ");
for (int j = 0; j < mappingPropertyDescriptors.Count; j++)
{
if (j > 0)
{
sqlBuilder.Append(",");
}
PropertyDescriptor mappingPropertyDescriptor = mappingPropertyDescriptors[j];
if (keepIdentity == false && mappingPropertyDescriptor.HasSequence())
{
sqlBuilder.AppendFormat("1");
continue;
}
object val = mappingPropertyDescriptor.GetValue(entity);
if (val == null)
{
sqlBuilder.Append("NULL");
sqlBuilder.Append(" C").Append(j.ToString());
continue;
}
Type valType = val.GetType();
if (valType.IsEnum)
{
val = Convert.ChangeType(val, Enum.GetUnderlyingType(valType));
valType = val.GetType();
}
if (Utils.IsToStringableNumericType(valType))
{
sqlBuilder.Append(val.ToString());
}
else if (val is bool)
{
if ((bool)val == true)
{
sqlBuilder.AppendFormat("1");
}
else
{
sqlBuilder.AppendFormat("0");
}
}
else
{
string paramName = UtilConstants.ParameterNamePrefix + dbParams.Count.ToString();
DbParam dbParam = new DbParam(paramName, val)
{
DbType = mappingPropertyDescriptor.Column.DbType
};
dbParams.Add(dbParam);
sqlBuilder.Append(paramName);
}
sqlBuilder.Append(" C").Append(j.ToString());
}
sqlBuilder.Append(" FROM DUAL");
batchCount++;
if ((batchCount >= 20 && dbParams.Count >= 400 /*参数个数太多也会影响速度*/) || dbParams.Count >= maxDbParamsCount || batchCount >= batchSize || (i + 1) == entities.Count)
{
sqlBuilder.Insert(0, sqlTemplate);
if (keepIdentity == false)
{
sqlBuilder.Append(") T");
}
string sql = sqlBuilder.ToString();
this.Session.ExecuteNonQuery(sql, dbParams.ToArray());
sqlBuilder.Clear();
dbParams.Clear();
batchCount = 0;
}
}
};
Action fAction = insertAction;
if (this.Session.IsInTransaction)
{
fAction();
}
else
{
/* 因为分批插入，所以需要开启事务保证数据一致性 */
this.Session.BeginTransaction();
try
{
fAction();
this.Session.CommitTransaction();
}
catch
{
if (this.Session.IsInTransaction)
{
this.Session.RollbackTransaction();
}
throw;
}
}
}
public override object Insert <TEntity>(Expression <Func <TEntity> > content, string table)
{
PublicHelper.CheckNull(content);
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
if (typeDescriptor.PrimaryKeys.Count > 1)
{
/* 对于多主键的实体，暂时不支持调用这个方法进行插入 */
throw new NotSupportedException(string.Format("Can not call this method because entity '{0}' has multiple keys.", typeDescriptor.Definition.Type.FullName));
}
PropertyDescriptor keyPropertyDescriptor = typeDescriptor.PrimaryKeys.FirstOrDefault();
Dictionary <MemberInfo, Expression> insertColumns = InitMemberExtractor.Extract(content);
DbTable explicitDbTable = null;
if (table != null)
{
explicitDbTable = new DbTable(table, typeDescriptor.Table.Schema);
}
DefaultExpressionParser expressionParser = typeDescriptor.GetExpressionParser(explicitDbTable);
DbInsertExpression insertExp = new DbInsertExpression(explicitDbTable ?? typeDescriptor.Table);
object keyVal = null;
foreach (var kv in insertColumns)
{
MemberInfo key = kv.Key;
PropertyDescriptor propertyDescriptor = typeDescriptor.TryGetPropertyDescriptor(key);
if (propertyDescriptor == null)
{
throw new ChloeException(string.Format("The member '{0}' does not map any column.", key.Name));
}
if (propertyDescriptor.IsAutoIncrement)
{
throw new ChloeException(string.Format("Could not insert value into the auto increment column '{0}'.", propertyDescriptor.Column.Name));
}
if (propertyDescriptor.HasSequence())
{
throw new ChloeException(string.Format("Can not insert value into the column '{0}', because it's mapping member has define a sequence.", propertyDescriptor.Column.Name));
}
if (propertyDescriptor.IsPrimaryKey)
{
object val = ExpressionEvaluator.Evaluate(kv.Value);
if (val == null)
{
throw new ChloeException(string.Format("The primary key '{0}' could not be null.", propertyDescriptor.Property.Name));
}
else
{
keyVal = val;
insertExp.InsertColumns.Add(propertyDescriptor.Column, DbExpression.Parameter(keyVal, propertyDescriptor.PropertyType, propertyDescriptor.Column.DbType));
continue;
}
}
insertExp.InsertColumns.Add(propertyDescriptor.Column, expressionParser.Parse(kv.Value));
}
foreach (PropertyDescriptor propertyDescriptor in typeDescriptor.PropertyDescriptors)
{
if (propertyDescriptor.IsAutoIncrement && propertyDescriptor.IsPrimaryKey)
{
insertExp.Returns.Add(propertyDescriptor.Column);
continue;
}
if (propertyDescriptor.HasSequence())
{
DbMethodCallExpression getNextValueForSequenceExp = PublicHelper.MakeNextValueForSequenceDbExpression(propertyDescriptor);
insertExp.InsertColumns.Add(propertyDescriptor.Column, getNextValueForSequenceExp);
if (propertyDescriptor.IsPrimaryKey)
{
insertExp.Returns.Add(propertyDescriptor.Column);
}
continue;
}
}
if (keyPropertyDescriptor != null)
{
//主键为空并且主键又不是自增列
if (keyVal == null && !keyPropertyDescriptor.IsAutoIncrement && !keyPropertyDescriptor.HasSequence())
{
throw new ChloeException(string.Format("The primary key '{0}' could not be null.", keyPropertyDescriptor.Property.Name));
}
}
List <DbParam> parameters;
this.ExecuteNonQuery(insertExp, out parameters);
if (keyPropertyDescriptor != null && (keyPropertyDescriptor.IsAutoIncrement || keyPropertyDescriptor.HasSequence()))
{
string outputColumnName = Utils.GenOutputColumnParameterName(keyPropertyDescriptor.Column.Name);
DbParam outputParam = parameters.Where(a => a.Direction == ParamDirection.Output && a.Name == outputColumnName).First();
keyVal = PublicHelper.ConvertObjType(outputParam.Value, keyPropertyDescriptor.PropertyType);
}
return(keyVal); /* It will return null if an entity does not define primary key. */
}
public override object Insert <TEntity>(Expression <Func <TEntity> > content, string table)
{
PublicHelper.CheckNull(content);
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
if (typeDescriptor.PrimaryKeys.Count > 1)
{
/* 对于多主键的实体，暂时不支持调用这个方法进行插入 */
throw new NotSupportedException(string.Format("Can not call this method because entity '{0}' has multiple keys.", typeDescriptor.Definition.Type.FullName));
}
PropertyDescriptor keyPropertyDescriptor = typeDescriptor.PrimaryKeys.FirstOrDefault();
Dictionary <MemberInfo, Expression> insertColumns = InitMemberExtractor.Extract(content);
DbTable explicitDbTable = null;
if (table != null)
{
explicitDbTable = new DbTable(table, typeDescriptor.Table.Schema);
}
DefaultExpressionParser expressionParser = typeDescriptor.GetExpressionParser(explicitDbTable);
DbInsertExpression insertExp = new DbInsertExpression(explicitDbTable ?? typeDescriptor.Table);
object keyVal = null;
foreach (var kv in insertColumns)
{
MemberInfo key = kv.Key;
PropertyDescriptor propertyDescriptor = typeDescriptor.TryGetPropertyDescriptor(key);
if (propertyDescriptor == null)
{
throw new ChloeException(string.Format("The member '{0}' does not map any column.", key.Name));
}
if (propertyDescriptor.IsAutoIncrement)
{
throw new ChloeException(string.Format("Could not insert value into the identity column '{0}'.", propertyDescriptor.Column.Name));
}
if (propertyDescriptor.HasSequence())
{
throw new ChloeException(string.Format("Can not insert value into the column '{0}', because it's mapping member has define a sequence.", propertyDescriptor.Column.Name));
}
if (propertyDescriptor.IsPrimaryKey)
{
object val = ExpressionEvaluator.Evaluate(kv.Value);
if (val == null)
{
throw new ChloeException(string.Format("The primary key '{0}' could not be null.", propertyDescriptor.Property.Name));
}
else
{
keyVal = val;
insertExp.InsertColumns.Add(propertyDescriptor.Column, DbExpression.Parameter(keyVal, propertyDescriptor.PropertyType, propertyDescriptor.Column.DbType));
continue;
}
}
insertExp.InsertColumns.Add(propertyDescriptor.Column, expressionParser.Parse(kv.Value));
}
foreach (var item in typeDescriptor.PropertyDescriptors.Where(a => a.HasSequence()))
{
DbMethodCallExpression getNextValueForSequenceExp = PublicHelper.MakeNextValueForSequenceDbExpression(item);
insertExp.InsertColumns.Add(item.Column, getNextValueForSequenceExp);
}
if (keyPropertyDescriptor != null)
{
//主键为空并且主键又不是自增列
if (keyVal == null && !keyPropertyDescriptor.IsAutoIncrement && !keyPropertyDescriptor.HasSequence())
{
throw new ChloeException(string.Format("The primary key '{0}' could not be null.", keyPropertyDescriptor.Property.Name));
}
}
if (keyPropertyDescriptor == null)
{
this.ExecuteSqlCommand(insertExp);
return(keyVal); /* It will return null if an entity does not define primary key. */
}
if (!keyPropertyDescriptor.IsAutoIncrement && !keyPropertyDescriptor.HasSequence())
{
this.ExecuteSqlCommand(insertExp);
return(keyVal);
}
IDbExpressionTranslator translator = this.DatabaseProvider.CreateDbExpressionTranslator();
List <DbParam> parameters;
string sql = translator.Translate(insertExp, out parameters);
if (keyPropertyDescriptor.IsAutoIncrement)
{
/* 自增 id 不能用 output inserted.Id 输出，因为如果表设置了触发器的话会报错 */
sql = string.Concat(sql, ";", this.GetSelectLastInsertIdClause());
}
else if (keyPropertyDescriptor.HasSequence())
{
insertExp.Returns.Add(keyPropertyDescriptor.Column);
}
object ret = this.Session.ExecuteScalar(sql, parameters.ToArray());
if (ret == null || ret == DBNull.Value)
{
throw new ChloeException("Unable to get the identity/sequence value.");
}
ret = PublicHelper.ConvertObjType(ret, typeDescriptor.AutoIncrement.PropertyType);
return(ret);
}
public override void InsertRange <TEntity>(List <TEntity> entities, string table)
{
/*
* 将 entities 分批插入数据库
* 每批生成 insert into TableName(...) values(...),(...)...
* 该方法相对循环一条一条插入，速度提升 2/3 这样
*/
PublicHelper.CheckNull(entities);
if (entities.Count == 0)
{
return;
}
int maxParameters = 2100;
int batchSize = 50; /* 每批实体大小，此值通过测试得出相对插入速度比较快的一个值 */
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
List <PropertyDescriptor> mappingPropertyDescriptors = typeDescriptor.PropertyDescriptors.Where(a => a.IsAutoIncrement == false).ToList();
int maxDbParamsCount = maxParameters - mappingPropertyDescriptors.Count; /* 控制一个 sql 的参数个数 */
DbTable dbTable = string.IsNullOrEmpty(table) ? typeDescriptor.Table : new DbTable(table, typeDescriptor.Table.Schema);
string sqlTemplate = this.AppendInsertRangeSqlTemplate(dbTable, mappingPropertyDescriptors);
Action insertAction = () =>
{
int batchCount = 0;
List <DbParam> dbParams = new List <DbParam>();
StringBuilder sqlBuilder = new StringBuilder();
for (int i = 0; i < entities.Count; i++)
{
var entity = entities[i];
if (batchCount > 0)
{
sqlBuilder.Append(",");
}
sqlBuilder.Append("(");
for (int j = 0; j < mappingPropertyDescriptors.Count; j++)
{
if (j > 0)
{
sqlBuilder.Append(",");
}
PropertyDescriptor mappingPropertyDescriptor = mappingPropertyDescriptors[j];
if (mappingPropertyDescriptor.HasSequence())
{
sqlBuilder.Append("nextval(");
sqlBuilder.Append(mappingPropertyDescriptor.Definition.SequenceName);
sqlBuilder.Append(")");
continue;
}
object val = mappingPropertyDescriptor.GetValue(entity);
if (val == null)
{
sqlBuilder.Append("NULL");
continue;
}
Type valType = val.GetType();
if (valType.IsEnum)
{
val = Convert.ChangeType(val, Enum.GetUnderlyingType(valType));
valType = val.GetType();
}
if (Utils.IsToStringableNumericType(valType))
{
sqlBuilder.Append(val.ToString());
continue;
}
if (val is bool)
{
if ((bool)val == true)
{
sqlBuilder.AppendFormat("true");
}
else
{
sqlBuilder.AppendFormat("false");
}
continue;
}
string paramName = UtilConstants.ParameterNamePrefix + dbParams.Count.ToString();
DbParam dbParam = new DbParam(paramName, val)
{
DbType = mappingPropertyDescriptor.Column.DbType
};
dbParams.Add(dbParam);
sqlBuilder.Append(paramName);
}
sqlBuilder.Append(")");
batchCount++;
if ((batchCount >= 20 && dbParams.Count >= 120 /*参数个数太多也会影响速度*/) || dbParams.Count >= maxDbParamsCount || batchCount >= batchSize || (i + 1) == entities.Count)
{
sqlBuilder.Insert(0, sqlTemplate);
string sql = sqlBuilder.ToString();
this.Session.ExecuteNonQuery(sql, dbParams.ToArray());
sqlBuilder.Clear();
dbParams.Clear();
batchCount = 0;
}
}
};
Action fAction = () =>
{
insertAction();
};
if (this.Session.IsInTransaction)
{
fAction();
}
else
{
/* 因为分批插入，所以需要开启事务保证数据一致性 */
this.Session.BeginTransaction();
try
{
fAction();
this.Session.CommitTransaction();
}
catch
{
if (this.Session.IsInTransaction)
{
this.Session.RollbackTransaction();
}
throw;
}
}
}
public override object Insert <TEntity>(Expression <Func <TEntity> > content, string table)
{
PublicHelper.CheckNull(content);
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
if (typeDescriptor.PrimaryKeys.Count > 1)
{
/* 对于多主键的实体，暂时不支持调用这个方法进行插入 */
throw new NotSupportedException(string.Format("Can not call this method because entity '{0}' has multiple keys.", typeDescriptor.Definition.Type.FullName));
}
PropertyDescriptor keyPropertyDescriptor = typeDescriptor.PrimaryKeys.FirstOrDefault();
Dictionary <MemberInfo, Expression> insertColumns = InitMemberExtractor.Extract(content);
DbTable explicitDbTable = null;
if (table != null)
{
explicitDbTable = new DbTable(table, typeDescriptor.Table.Schema);
}
DefaultExpressionParser expressionParser = typeDescriptor.GetExpressionParser(explicitDbTable);
DbInsertExpression insertExp = new DbInsertExpression(explicitDbTable ?? typeDescriptor.Table);
object keyVal = null;
foreach (var kv in insertColumns)
{
MemberInfo key = kv.Key;
PropertyDescriptor propertyDescriptor = typeDescriptor.TryGetPropertyDescriptor(key);
if (propertyDescriptor == null)
{
throw new ChloeException(string.Format("The member '{0}' does not map any column.", key.Name));
}
if (propertyDescriptor.HasSequence())
{
throw new ChloeException(string.Format("Can not insert value into the column '{0}', because it's mapping member has define a sequence.", propertyDescriptor.Column.Name));
}
if (propertyDescriptor.IsPrimaryKey)
{
object val = ExpressionEvaluator.Evaluate(kv.Value);
if (val == null)
{
throw new ChloeException(string.Format("The primary key '{0}' could not be null.", propertyDescriptor.Property.Name));
}
else
{
keyVal = val;
insertExp.InsertColumns.Add(propertyDescriptor.Column, DbExpression.Parameter(keyVal, propertyDescriptor.PropertyType, propertyDescriptor.Column.DbType));
continue;
}
}
insertExp.InsertColumns.Add(propertyDescriptor.Column, expressionParser.Parse(kv.Value));
}
Dictionary <PropertyDescriptor, object> sequenceValues = this.GetSequenceValues(typeDescriptor.PropertyDescriptors.Where(a => a.HasSequence()).ToList());
foreach (var kv in sequenceValues)
{
insertExp.InsertColumns.Add(kv.Key.Column, DbExpression.Parameter(kv.Value));
if (kv.Key.IsPrimaryKey)
{
keyVal = kv.Value;
}
}
if (keyPropertyDescriptor != null && keyVal == null)
{
throw new ChloeException(string.Format("The primary key '{0}' could not be null.", keyPropertyDescriptor.Property.Name));
}
this.ExecuteSqlCommand(insertExp);
return(keyVal); /* It will return null if an entity does not define primary key. */
}