protected override async Task <TEntity> Insert <TEntity>(TEntity entity, string table, bool @async)
{
PublicHelper.CheckNull(entity);
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
DbTable dbTable = PublicHelper.CreateDbTable(typeDescriptor, table);
Dictionary <PrimitivePropertyDescriptor, object> keyValueMap = PrimaryKeyHelper.CreateKeyValueMap(typeDescriptor);
Dictionary <PrimitivePropertyDescriptor, DbExpression> insertColumns = new Dictionary <PrimitivePropertyDescriptor, DbExpression>();
foreach (PrimitivePropertyDescriptor propertyDescriptor in typeDescriptor.PrimitivePropertyDescriptors)
{
if (propertyDescriptor.IsAutoIncrement)
{
continue;
}
if (propertyDescriptor.HasSequence())
{
DbMethodCallExpression getNextValueForSequenceExp = PublicHelper.MakeNextValueForSequenceDbExpression(propertyDescriptor, dbTable.Schema);
insertColumns.Add(propertyDescriptor, getNextValueForSequenceExp);
continue;
}
object val = propertyDescriptor.GetValue(entity);
PublicHelper.NotNullCheck(propertyDescriptor, val);
if (propertyDescriptor.IsPrimaryKey)
{
keyValueMap[propertyDescriptor] = val;
}
DbParameterExpression valExp = DbExpression.Parameter(val, propertyDescriptor.PropertyType, propertyDescriptor.Column.DbType);
insertColumns.Add(propertyDescriptor, valExp);
}
PrimitivePropertyDescriptor nullValueKey = keyValueMap.Where(a => a.Value == null && !a.Key.IsAutoIncrement).Select(a => a.Key).FirstOrDefault();
if (nullValueKey != null)
{
/* 主键为空并且主键又不是自增列 */
throw new ChloeException(string.Format("The primary key '{0}' could not be null.", nullValueKey.Property.Name));
}
DbInsertExpression insertExp = new DbInsertExpression(dbTable);
foreach (var kv in insertColumns)
{
insertExp.InsertColumns.Add(kv.Key.Column, kv.Value);
}
List <Action <TEntity, IDataReader> > mappers = new List <Action <TEntity, IDataReader> >();
foreach (var item in typeDescriptor.PrimitivePropertyDescriptors.Where(a => a.IsAutoIncrement || a.HasSequence()))
{
mappers.Add(GetMapper <TEntity>(item, insertExp.Returns.Count));
insertExp.Returns.Add(item.Column);
}
if (mappers.Count == 0)
{
await this.ExecuteNonQuery(insertExp, @async);
return(entity);
}
IDbExpressionTranslator translator = this.DatabaseProvider.CreateDbExpressionTranslator();
DbCommandInfo dbCommandInfo = translator.Translate(insertExp);
IDataReader dataReader = this.Session.ExecuteReader(dbCommandInfo.CommandText, dbCommandInfo.GetParameters());
using (dataReader)
{
dataReader.Read();
foreach (var mapper in mappers)
{
mapper(entity, dataReader);
}
}
return(entity);
}
protected override async Task <object> Insert <TEntity>(Expression <Func <TEntity> > content, string table, bool @async)
{
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));
}
PrimitivePropertyDescriptor keyPropertyDescriptor = typeDescriptor.PrimaryKeys.FirstOrDefault();
Dictionary <MemberInfo, Expression> insertColumns = InitMemberExtractor.Extract(content);
DbTable dbTable = PublicHelper.CreateDbTable(typeDescriptor, table);
DefaultExpressionParser expressionParser = typeDescriptor.GetExpressionParser(dbTable);
DbInsertExpression insertExp = new DbInsertExpression(dbTable);
object keyVal = null;
foreach (var kv in insertColumns)
{
MemberInfo key = kv.Key;
PrimitivePropertyDescriptor propertyDescriptor = typeDescriptor.GetPrimitivePropertyDescriptor(key);
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));
continue;
}
}
insertExp.InsertColumns.Add(propertyDescriptor.Column, expressionParser.Parse(kv.Value));
}
foreach (var item in typeDescriptor.PrimitivePropertyDescriptors.Where(a => a.HasSequence()))
{
DbMethodCallExpression getNextValueForSequenceExp = PublicHelper.MakeNextValueForSequenceDbExpression(item, dbTable.Schema);
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)
{
await this.ExecuteNonQuery(insertExp, @async);
return(keyVal); /* It will return null if an entity does not define primary key. */
}
if (!keyPropertyDescriptor.IsAutoIncrement && !keyPropertyDescriptor.HasSequence())
{
await this.ExecuteNonQuery(insertExp, @async);
return(keyVal);
}
insertExp.Returns.Add(keyPropertyDescriptor.Column);
IDbExpressionTranslator translator = this.DatabaseProvider.CreateDbExpressionTranslator();
DbCommandInfo dbCommandInfo = translator.Translate(insertExp);
object ret = this.Session.ExecuteScalar(dbCommandInfo.CommandText, dbCommandInfo.GetParameters());
if (ret == null || ret == DBNull.Value)
{
throw new ChloeException("Unable to get the identity/sequence value.");
}
ret = PublicHelper.ConvertObjectType(ret, typeDescriptor.AutoIncrement.PropertyType);
return(ret);
}
protected override async Task InsertRange <TEntity>(List <TEntity> entities, string table, bool @async)
{
/*
* 将 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 <PrimitivePropertyDescriptor> mappingPropertyDescriptors = typeDescriptor.PrimitivePropertyDescriptors.Where(a => a.IsAutoIncrement == false).ToList();
int maxDbParamsCount = maxParameters - mappingPropertyDescriptors.Count; /* 控制一个 sql 的参数个数 */
DbTable dbTable = PublicHelper.CreateDbTable(typeDescriptor, table);
string sqlTemplate = this.AppendInsertRangeSqlTemplate(dbTable, mappingPropertyDescriptors);
Func <Task> insertAction = async() =>
{
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(",");
}
PrimitivePropertyDescriptor mappingPropertyDescriptor = mappingPropertyDescriptors[j];
if (mappingPropertyDescriptor.HasSequence())
{
string sequenceSchema = mappingPropertyDescriptor.Definition.SequenceSchema;
sequenceSchema = string.IsNullOrEmpty(sequenceSchema) ? dbTable.Schema : sequenceSchema;
sqlBuilder.Append("nextval('");
if (!string.IsNullOrEmpty(sequenceSchema))
{
sqlBuilder.Append(sequenceSchema).Append(".");
}
sqlBuilder.Append(mappingPropertyDescriptor.Definition.SequenceName);
sqlBuilder.Append("')");
continue;
}
object val = mappingPropertyDescriptor.GetValue(entity);
PublicHelper.NotNullCheck(mappingPropertyDescriptor, val);
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();
await this.Session.ExecuteNonQuery(sql, dbParams.ToArray(), @async);
sqlBuilder.Clear();
dbParams.Clear();
batchCount = 0;
}
}
};
Func <Task> fAction = insertAction;
if (this.Session.IsInTransaction)
{
await fAction();
return;
}
/* 因为分批插入，所以需要开启事务保证数据一致性 */
using (var tran = this.BeginTransaction())
{
await fAction();
tran.Commit();
}
}
protected override async Task <TEntity> Insert <TEntity>(TEntity entity, string table, bool @async)
{
PublicHelper.CheckNull(entity);
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
DbTable dbTable = PublicHelper.CreateDbTable(typeDescriptor, table);
List <PrimitivePropertyDescriptor> outputColumns = new List <PrimitivePropertyDescriptor>();
Dictionary <PrimitivePropertyDescriptor, DbExpression> insertColumns = new Dictionary <PrimitivePropertyDescriptor, DbExpression>();
foreach (PrimitivePropertyDescriptor propertyDescriptor in typeDescriptor.PrimitivePropertyDescriptors)
{
if (propertyDescriptor.IsAutoIncrement)
{
outputColumns.Add(propertyDescriptor);
continue;
}
if (propertyDescriptor.HasSequence())
{
DbMethodCallExpression getNextValueForSequenceExp = PublicHelper.MakeNextValueForSequenceDbExpression(propertyDescriptor, dbTable.Schema);
insertColumns.Add(propertyDescriptor, getNextValueForSequenceExp);
outputColumns.Add(propertyDescriptor);
continue;
}
object val = propertyDescriptor.GetValue(entity);
PublicHelper.NotNullCheck(propertyDescriptor, val);
DbExpression valExp = DbExpression.Parameter(val, propertyDescriptor.PropertyType, propertyDescriptor.Column.DbType);
insertColumns.Add(propertyDescriptor, valExp);
}
DbInsertExpression e = new DbInsertExpression(dbTable);
foreach (var kv in insertColumns)
{
e.InsertColumns.Add(kv.Key.Column, kv.Value);
}
e.Returns.AddRange(outputColumns.Select(a => a.Column));
DbCommandInfo dbCommandInfo = this.Translate(e);
await this.ExecuteNonQuery(dbCommandInfo, @async);
List <DbParam> outputParams = dbCommandInfo.Parameters.Where(a => a.Direction == ParamDirection.Output).ToList();
for (int i = 0; i < outputColumns.Count; i++)
{
PrimitivePropertyDescriptor propertyDescriptor = outputColumns[i];
string putputColumnName = Utils.GenOutputColumnParameterName(propertyDescriptor.Column.Name);
DbParam outputParam = outputParams.Where(a => a.Name == putputColumnName).First();
var outputValue = PublicHelper.ConvertObjectType(outputParam.Value, propertyDescriptor.PropertyType);
outputColumns[i].SetValue(entity, outputValue);
}
return(entity);
}
public override void InsertRange <TEntity>(List <TEntity> entities, string table)
{
/*
* 将 entities 分批插入数据库
* 每批生成 insert into TableName(...) select ... union all select ...
* 该方法相对循环一条一条插入，速度提升 1/2 这样
*/
PublicHelper.CheckNull(entities);
if (entities.Count == 0)
{
return;
}
int maxParameters = 1000;
int batchSize = 30; /* 每批实体大小，此值通过测试得出相对插入速度比较快的一个值 */
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
List <PrimitivePropertyDescriptor> mappingPropertyDescriptors = typeDescriptor.PrimitivePropertyDescriptors.Where(a => a.IsAutoIncrement == false).ToList();
int maxDbParamsCount = maxParameters - mappingPropertyDescriptors.Count; /* 控制一个 sql 的参数个数 */
DbTable dbTable = PublicHelper.CreateDbTable(typeDescriptor, table);
string sqlTemplate = 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(" UNION ALL");
}
sqlBuilder.Append(" SELECT ");
for (int j = 0; j < mappingPropertyDescriptors.Count; j++)
{
if (j > 0)
{
sqlBuilder.Append(",");
}
PrimitivePropertyDescriptor mappingPropertyDescriptor = mappingPropertyDescriptors[j];
object val = mappingPropertyDescriptor.GetValue(entity);
PublicHelper.NotNullCheck(mappingPropertyDescriptor, val);
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("1");
}
else
{
sqlBuilder.AppendFormat("0");
}
continue;
}
string paramName = UtilConstants.ParameterNamePrefix + dbParams.Count.ToString();
DbParam dbParam = new DbParam(paramName, val)
{
DbType = mappingPropertyDescriptor.Column.DbType
};
dbParams.Add(dbParam);
sqlBuilder.Append(paramName);
}
batchCount++;
if ((batchCount >= 20 && dbParams.Count >= 200 /*参数个数太多也会影响速度*/) || 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;
}
}
}
string GetMappedDbTypeName(PrimitivePropertyDescriptor propertyDescriptor)
{
Type type = propertyDescriptor.PropertyType.GetUnderlyingType();
if (type.IsEnum)
{
type = type.GetEnumUnderlyingType();
}
if (type == typeof(string))
{
int stringLength = propertyDescriptor.Column.Size ?? 4000;
return($"NVARCHAR({stringLength})");
}
if (type == typeof(int))
{
return("int");
}
if (type == typeof(byte))
{
return("tinyint");
}
if (type == typeof(Int16))
{
return("smallint");
}
if (type == typeof(long))
{
return("bigint");
}
if (type == typeof(float))
{
return("real");
}
if (type == typeof(double))
{
return("float");
}
if (type == typeof(decimal))
{
int scale = propertyDescriptor.Column.Scale ?? 18;
int precision = propertyDescriptor.Column.Precision ?? 2;
return($"decimal({scale},{precision})");
}
if (type == typeof(bool))
{
return("bit");
}
if (type == typeof(DateTime))
{
return("datetime");
}
if (type == typeof(Guid))
{
return("uniqueidentifier");
}
throw new NotSupportedException(type.FullName);
}
string GetMappedDbTypeName(PrimitivePropertyDescriptor propertyDescriptor)
{
Type type = propertyDescriptor.PropertyType.GetUnderlyingType();
if (type.IsEnum)
{
type = type.GetEnumUnderlyingType();
}
if (type == typeof(string))
{
int stringLength = propertyDescriptor.Column.Size ?? 4000;
return($"varchar({stringLength})");
}
if (type == typeof(int))
{
if (propertyDescriptor.IsAutoIncrement)
{
return("serial4");
}
return("int4");
}
if (type == typeof(byte))
{
return("int2");
}
if (type == typeof(Int16))
{
return("int2");
}
if (type == typeof(long))
{
if (propertyDescriptor.IsAutoIncrement)
{
return("serial8");
}
return("int8");
}
if (type == typeof(float))
{
return("float4");
}
if (type == typeof(double))
{
return("float8");
}
if (type == typeof(decimal))
{
return("decimal(18,4)");
}
if (type == typeof(bool))
{
return("boolean");
}
if (type == typeof(DateTime))
{
return("timestamp");
}
if (type == typeof(Guid))
{
return("uuid");
}
throw new NotSupportedException(type.FullName);
}
public virtual TEntity Insert <TEntity>(TEntity entity, string table)
{
Utils.CheckNull(entity);
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
Dictionary <PrimitivePropertyDescriptor, object> keyValueMap = PrimaryKeyHelper.CreateKeyValueMap(typeDescriptor);
Dictionary <PrimitivePropertyDescriptor, DbExpression> insertColumns = new Dictionary <PrimitivePropertyDescriptor, DbExpression>();
foreach (PrimitivePropertyDescriptor propertyDescriptor in typeDescriptor.PrimitivePropertyDescriptors)
{
if (propertyDescriptor.IsAutoIncrement)
{
continue;
}
object val = propertyDescriptor.GetValue(entity);
if (propertyDescriptor.IsPrimaryKey)
{
keyValueMap[propertyDescriptor] = val;
}
PublicHelper.NotNullCheck(propertyDescriptor, val);
DbParameterExpression valExp = DbExpression.Parameter(val, propertyDescriptor.PropertyType, propertyDescriptor.Column.DbType);
insertColumns.Add(propertyDescriptor, valExp);
}
PrimitivePropertyDescriptor nullValueKey = keyValueMap.Where(a => a.Value == null && !a.Key.IsAutoIncrement).Select(a => a.Key).FirstOrDefault();
if (nullValueKey != null)
{
/* 主键为空并且主键又不是自增列 */
throw new ChloeException(string.Format("The primary key '{0}' could not be null.", nullValueKey.Property.Name));
}
DbTable dbTable = PublicHelper.CreateDbTable(typeDescriptor, table);
DbInsertExpression e = new DbInsertExpression(dbTable);
foreach (var kv in insertColumns)
{
e.InsertColumns.Add(kv.Key.Column, kv.Value);
}
PrimitivePropertyDescriptor autoIncrementPropertyDescriptor = typeDescriptor.AutoIncrement;
if (autoIncrementPropertyDescriptor == null)
{
this.ExecuteNonQuery(e);
return(entity);
}
IDbExpressionTranslator translator = this.DatabaseProvider.CreateDbExpressionTranslator();
List <DbParam> parameters;
string sql = translator.Translate(e, out parameters);
sql = string.Concat(sql, ";", this.GetSelectLastInsertIdClause());
//SELECT @@IDENTITY 返回的是 decimal 类型
object retIdentity = this.Session.ExecuteScalar(sql, parameters.ToArray());
if (retIdentity == null || retIdentity == DBNull.Value)
{
throw new ChloeException("Unable to get the identity value.");
}
retIdentity = PublicHelper.ConvertObjectType(retIdentity, autoIncrementPropertyDescriptor.PropertyType);
autoIncrementPropertyDescriptor.SetValue(entity, retIdentity);
return(entity);
}
public virtual object Insert <TEntity>(Expression <Func <TEntity> > content, string table)
{
Utils.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));
}
PrimitivePropertyDescriptor 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 e = new DbInsertExpression(explicitDbTable ?? typeDescriptor.Table);
object keyVal = null;
foreach (var kv in insertColumns)
{
MemberInfo key = kv.Key;
PrimitivePropertyDescriptor propertyDescriptor = typeDescriptor.GetPrimitivePropertyDescriptor(key);
if (propertyDescriptor.IsAutoIncrement)
{
throw new ChloeException(string.Format("Could not insert value into the identity column '{0}'.", 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;
e.InsertColumns.Add(propertyDescriptor.Column, DbExpression.Parameter(keyVal, propertyDescriptor.PropertyType, propertyDescriptor.Column.DbType));
continue;
}
}
e.InsertColumns.Add(propertyDescriptor.Column, expressionParser.Parse(kv.Value));
}
if (keyPropertyDescriptor != null)
{
//主键为空并且主键又不是自增列
if (keyVal == null && !keyPropertyDescriptor.IsAutoIncrement)
{
throw new ChloeException(string.Format("The primary key '{0}' could not be null.", keyPropertyDescriptor.Property.Name));
}
}
if (keyPropertyDescriptor == null || !keyPropertyDescriptor.IsAutoIncrement)
{
this.ExecuteNonQuery(e);
return(keyVal); /* It will return null if an entity does not define primary key. */
}
IDbExpressionTranslator translator = this.DatabaseProvider.CreateDbExpressionTranslator();
List <DbParam> parameters;
string sql = translator.Translate(e, out parameters);
sql = string.Concat(sql, ";", this.GetSelectLastInsertIdClause());
//SELECT @@IDENTITY 返回的是 decimal 类型
object retIdentity = this.Session.ExecuteScalar(sql, parameters.ToArray());
if (retIdentity == null || retIdentity == DBNull.Value)
{
throw new ChloeException("Unable to get the identity value.");
}
retIdentity = PublicHelper.ConvertObjectType(retIdentity, typeDescriptor.AutoIncrement.PropertyType);
return(retIdentity);
}
string GetMappedDbTypeName(PrimitivePropertyDescriptor propertyDescriptor)
{
Type type = propertyDescriptor.PropertyType.GetUnderlyingType();
if (type.IsEnum)
{
type = type.GetEnumUnderlyingType();
}
if (type == typeof(string))
{
int stringLength = propertyDescriptor.Column.Size ?? 4000;
return($"varchar({stringLength})");
}
if (type == typeof(int))
{
return("int(11)");
}
if (type == typeof(byte))
{
return("int(11)");
}
if (type == typeof(Int16))
{
return("int(11)");
}
if (type == typeof(long))
{
return("bigint");
}
if (type == typeof(float))
{
return("float(10, 4)");
}
if (type == typeof(double))
{
return("double(10, 4)");
}
if (type == typeof(decimal))
{
return("decimal(10, 4)");
}
if (type == typeof(bool))
{
return("int(11)");
}
if (type == typeof(DateTime))
{
return("datetime(0)");
}
if (type == typeof(Guid))
{
return("varchar(50)");
}
throw new NotSupportedException(type.FullName);
}
protected override async Task <int> Update <TEntity>(TEntity entity, string table, bool @async)
{
PublicHelper.CheckNull(entity);
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
PublicHelper.EnsureHasPrimaryKey(typeDescriptor);
PairList <PrimitivePropertyDescriptor, object> keyValues = new PairList <PrimitivePropertyDescriptor, object>(typeDescriptor.PrimaryKeys.Count);
IEntityState entityState = this.TryGetTrackedEntityState(entity);
Dictionary <PrimitivePropertyDescriptor, DbExpression> updateColumns = new Dictionary <PrimitivePropertyDescriptor, DbExpression>();
foreach (PrimitivePropertyDescriptor propertyDescriptor in typeDescriptor.PrimitivePropertyDescriptors)
{
if (propertyDescriptor.IsPrimaryKey)
{
var keyValue = propertyDescriptor.GetValue(entity);
PrimaryKeyHelper.KeyValueNotNull(propertyDescriptor, keyValue);
keyValues.Add(propertyDescriptor, keyValue);
continue;
}
if (propertyDescriptor.CannotUpdate())
{
continue;
}
object val = propertyDescriptor.GetValue(entity);
PublicHelper.NotNullCheck(propertyDescriptor, val);
if (entityState != null && !entityState.HasChanged(propertyDescriptor, val))
{
continue;
}
DbExpression valExp = DbExpression.Parameter(val, propertyDescriptor.PropertyType, propertyDescriptor.Column.DbType);
updateColumns.Add(propertyDescriptor, valExp);
}
PrimitivePropertyDescriptor rowVersionDescriptor = null;
object rowVersionValue = null;
object rowVersionNewValue = null;
if (typeDescriptor.HasRowVersion())
{
rowVersionDescriptor = typeDescriptor.RowVersion;
if (rowVersionDescriptor.IsTimestamp())
{
rowVersionValue = rowVersionDescriptor.GetValue(entity);
this.EnsureRowVersionValueIsNotNull(rowVersionValue);
keyValues.Add(rowVersionDescriptor, rowVersionValue);
}
else
{
rowVersionValue = rowVersionDescriptor.GetValue(entity);
rowVersionNewValue = PublicHelper.IncreaseRowVersionNumber(rowVersionValue);
updateColumns.Add(rowVersionDescriptor, DbExpression.Parameter(rowVersionNewValue, rowVersionDescriptor.PropertyType, rowVersionDescriptor.Column.DbType));
keyValues.Add(rowVersionDescriptor, rowVersionValue);
}
}
if (updateColumns.Count == 0)
{
return(0);
}
DbTable dbTable = PublicHelper.CreateDbTable(typeDescriptor, table);
DbExpression conditionExp = PublicHelper.MakeCondition(keyValues, dbTable);
DbUpdateExpression e = new DbUpdateExpression(dbTable, conditionExp);
foreach (var item in updateColumns)
{
e.UpdateColumns.Add(item.Key.Column, item.Value);
}
int rowsAffected = 0;
if (rowVersionDescriptor == null)
{
rowsAffected = await this.ExecuteNonQuery(e, @async);
if (entityState != null)
{
entityState.Refresh();
}
return(rowsAffected);
}
if (rowVersionDescriptor.IsTimestamp())
{
List <Action <TEntity, IDataReader> > mappers = new List <Action <TEntity, IDataReader> >();
mappers.Add(GetMapper <TEntity>(rowVersionDescriptor, e.Returns.Count));
e.Returns.Add(rowVersionDescriptor.Column);
IDataReader dataReader = await this.ExecuteReader(e, @async);
using (dataReader)
{
while (dataReader.Read())
{
rowsAffected++;
foreach (var mapper in mappers)
{
mapper(entity, dataReader);
}
}
}
PublicHelper.CauseErrorIfOptimisticUpdateFailed(rowsAffected);
}
else
{
rowsAffected = await this.ExecuteNonQuery(e, @async);
PublicHelper.CauseErrorIfOptimisticUpdateFailed(rowsAffected);
rowVersionDescriptor.SetValue(entity, rowVersionNewValue);
}
if (entityState != null)
{
entityState.Refresh();
}
return(rowsAffected);
}
string GetMappedDbTypeName(PrimitivePropertyDescriptor propertyDescriptor)
{
Type type = propertyDescriptor.PropertyType.GetUnderlyingType();
if (type.IsEnum)
{
type = type.GetEnumUnderlyingType();
}
if (type == typeof(string))
{
int stringLength = propertyDescriptor.Column.Size ?? 2000;
return($"NVARCHAR2({stringLength})");
}
if (type == typeof(int))
{
return("NUMBER(9,0)");
}
if (type == typeof(byte))
{
return("NUMBER(3,0)");
}
if (type == typeof(Int16))
{
return("NUMBER(4,0)");
}
if (type == typeof(long))
{
return("NUMBER(18,0)");
}
if (type == typeof(float))
{
return("BINARY_FLOAT");
}
if (type == typeof(double))
{
return("BINARY_DOUBLE");
}
if (type == typeof(decimal))
{
return("NUMBER");
}
if (type == typeof(bool))
{
return("NUMBER(9,0)");
}
if (type == typeof(DateTime))
{
return("DATE");
}
if (type == typeof(Guid))
{
return("BLOB");
}
throw new NotSupportedException(type.FullName);
}
public override TEntity Insert <TEntity>(TEntity entity, string table)
{
PublicHelper.CheckNull(entity);
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
DbTable dbTable = PublicHelper.CreateDbTable(typeDescriptor, table);
Dictionary <PrimitivePropertyDescriptor, object> keyValueMap = PrimaryKeyHelper.CreateKeyValueMap(typeDescriptor);
Dictionary <PrimitivePropertyDescriptor, DbExpression> insertColumns = new Dictionary <PrimitivePropertyDescriptor, DbExpression>();
List <PrimitivePropertyDescriptor> outputColumns = new List <PrimitivePropertyDescriptor>();
foreach (PrimitivePropertyDescriptor propertyDescriptor in typeDescriptor.PrimitivePropertyDescriptors)
{
if (propertyDescriptor.IsAutoIncrement || propertyDescriptor.IsTimestamp())
{
outputColumns.Add(propertyDescriptor);
continue;
}
if (propertyDescriptor.HasSequence())
{
DbMethodCallExpression getNextValueForSequenceExp = PublicHelper.MakeNextValueForSequenceDbExpression(propertyDescriptor, dbTable.Schema);
insertColumns.Add(propertyDescriptor, getNextValueForSequenceExp);
outputColumns.Add(propertyDescriptor);
continue;
}
object val = propertyDescriptor.GetValue(entity);
PublicHelper.NotNullCheck(propertyDescriptor, val);
if (propertyDescriptor.IsPrimaryKey)
{
keyValueMap[propertyDescriptor] = val;
}
DbExpression valExp = DbExpression.Parameter(val, propertyDescriptor.PropertyType, propertyDescriptor.Column.DbType);
insertColumns.Add(propertyDescriptor, valExp);
}
PrimitivePropertyDescriptor nullValueKey = keyValueMap.Where(a => a.Value == null && !a.Key.IsAutoIncrement).Select(a => a.Key).FirstOrDefault();
if (nullValueKey != null)
{
/* 主键为空并且主键又不是自增列 */
throw new ChloeException(string.Format("The primary key '{0}' could not be null.", nullValueKey.Property.Name));
}
DbInsertExpression insertExp = new DbInsertExpression(dbTable);
foreach (var kv in insertColumns)
{
insertExp.InsertColumns.Add(kv.Key.Column, kv.Value);
}
if (outputColumns.Count == 0)
{
this.ExecuteNonQuery(insertExp);
return(entity);
}
List <Action <TEntity, IDataReader> > mappers = new List <Action <TEntity, IDataReader> >();
IDbExpressionTranslator translator = this.DatabaseProvider.CreateDbExpressionTranslator();
List <DbParam> parameters;
string sql = null;
if (outputColumns.Count == 1 && outputColumns[0].IsAutoIncrement)
{
sql = translator.Translate(insertExp, out parameters);
/* 自增 id 不能用 output inserted.Id 输出，因为如果表设置了触发器的话会报错 */
sql = string.Concat(sql, ";", this.GetSelectLastInsertIdClause());
mappers.Add(GetMapper <TEntity>(outputColumns[0], 0));
}
else
{
foreach (PrimitivePropertyDescriptor outputColumn in outputColumns)
{
mappers.Add(GetMapper <TEntity>(outputColumn, insertExp.Returns.Count));
insertExp.Returns.Add(outputColumn.Column);
}
sql = translator.Translate(insertExp, out parameters);
}
IDataReader dataReader = this.Session.ExecuteReader(sql, parameters.ToArray());
using (dataReader)
{
dataReader.Read();
foreach (var mapper in mappers)
{
mapper(entity, dataReader);
}
}
return(entity);
}
public static Expression <Func <TEntity, bool> > BuildCondition <TEntity>(object key)
{
/*
* key:
* 如果实体是单一主键，可以传入的 key 与主键属性类型相同的值，亦可以传一个包含了与实体主键类型相同的属性的对象，如：new { Id = 1 }
* 如果实体是多主键，则传入的 key 须是包含了与实体主键类型相同的属性的对象，如：new { Key1 = "1", Key2 = "2" }
*/
PublicHelper.CheckNull(key);
Type entityType = typeof(TEntity);
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(entityType);
PublicHelper.EnsureHasPrimaryKey(typeDescriptor);
ParameterExpression parameter = Expression.Parameter(entityType, "a");
Expression conditionBody = null;
Type keyType = key.GetType();
if (typeDescriptor.PrimaryKeys.Count == 1 && MappingTypeSystem.IsMappingType(keyType))
{
/* a => a.Key == key */
PrimitivePropertyDescriptor keyDescriptor = typeDescriptor.PrimaryKeys[0];
Expression propOrField = Expression.PropertyOrField(parameter, keyDescriptor.Property.Name);
Expression wrappedValue = ExpressionExtension.MakeWrapperAccess(key, keyDescriptor.PropertyType);
conditionBody = Expression.Equal(propOrField, wrappedValue);
}
else
{
/*
* key: new { Key1 = "1", Key2 = "2" }
*/
/* a => a.Key1 == key.Key1 && a.Key2 == key.Key2 */
Type keyObjectType = keyType;
ConstantExpression keyConstantExp = Expression.Constant(key);
if (keyObjectType == entityType)
{
foreach (PrimitivePropertyDescriptor primaryKey in typeDescriptor.PrimaryKeys)
{
Expression propOrField = Expression.PropertyOrField(parameter, primaryKey.Property.Name);
Expression keyValue = Expression.MakeMemberAccess(keyConstantExp, primaryKey.Property);
Expression e = Expression.Equal(propOrField, keyValue);
conditionBody = conditionBody == null ? e : Expression.AndAlso(conditionBody, e);
}
}
else
{
for (int i = 0; i < typeDescriptor.PrimaryKeys.Count; i++)
{
PrimitivePropertyDescriptor keyPropertyDescriptor = typeDescriptor.PrimaryKeys[i];
MemberInfo keyMember = keyPropertyDescriptor.Property;
MemberInfo inputKeyMember = keyObjectType.GetMember(keyMember.Name).FirstOrDefault();
if (inputKeyMember == null)
{
throw new ArgumentException(string.Format("The input object does not define property for key '{0}'.", keyMember.Name));
}
Expression propOrField = Expression.PropertyOrField(parameter, keyMember.Name);
Expression keyValueExp = Expression.MakeMemberAccess(keyConstantExp, inputKeyMember);
Type keyMemberType = keyMember.GetMemberType();
if (inputKeyMember.GetMemberType() != keyMemberType)
{
keyValueExp = Expression.Convert(keyValueExp, keyMemberType);
}
Expression e = Expression.Equal(propOrField, keyValueExp);
conditionBody = conditionBody == null ? e : Expression.AndAlso(conditionBody, e);
}
}
}
Expression <Func <TEntity, bool> > condition = Expression.Lambda <Func <TEntity, bool> >(conditionBody, parameter);
return(condition);
}
string GetMappedDbTypeName(PrimitivePropertyDescriptor propertyDescriptor)
{
Type type = propertyDescriptor.PropertyType.GetUnderlyingType();
if (type.IsEnum)
{
type = type.GetEnumUnderlyingType();
}
if (type == typeof(string))
{
int stringLength = propertyDescriptor.Column.Size ?? 4000;
return($"NVARCHAR({stringLength})");
}
if (type == typeof(int))
{
if (propertyDescriptor.IsAutoIncrement)
{
return("INTEGER");
}
return("INT");
}
if (type == typeof(byte))
{
return("TINYINT");
}
if (type == typeof(Int16))
{
return("SMALLINT");
}
if (type == typeof(long))
{
return("INT64");
}
if (type == typeof(float))
{
return("FLOAT");
}
if (type == typeof(double))
{
return("DOUBLE");
}
if (type == typeof(decimal))
{
return("NUMERIC");
}
if (type == typeof(bool))
{
return("BOOL");
}
if (type == typeof(DateTime))
{
return("DATETIME");
}
if (type == typeof(Guid))
{
return("GUID");
}
throw new NotSupportedException(type.FullName);
}
/// <summary>
/// 判断字段是否为 timestamp 类型
/// </summary>
/// <param name="propertyDescriptor"></param>
/// <returns></returns>
public static bool IsTimestamp(this PrimitivePropertyDescriptor propertyDescriptor)
{
return(propertyDescriptor.IsRowVersion && propertyDescriptor.PropertyType == PublicConstants.TypeOfByteArray);
}
