/// <summary>
/// 利用 SqlBulkCopy 批量插入数据。
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="entities"></param>
/// <param name="table"></param>
/// <param name="batchSize">设置 SqlBulkCopy.BatchSize 的值</param>
/// <param name="bulkCopyTimeout">设置 SqlBulkCopy.BulkCopyTimeout 的值</param>
/// <param name="keepIdentity">是否保留源自增值。false 由数据库分配自增值</param>
public virtual void BulkInsert <TEntity>(List <TEntity> entities, string table = null, int?batchSize = null, int?bulkCopyTimeout = null, bool keepIdentity = false)
{
PublicHelper.CheckNull(entities);
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
DataTable dtToWrite = ConvertToSqlBulkCopyDataTable(entities, typeDescriptor);
bool shouldCloseConnection = false;
SqlConnection conn = this.Session.CurrentConnection as SqlConnection;
try
{
SqlTransaction externalTransaction = null;
if (this.Session.IsInTransaction)
{
externalTransaction = this.Session.CurrentTransaction as SqlTransaction;
}
SqlBulkCopyOptions sqlBulkCopyOptions = SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.KeepNulls | SqlBulkCopyOptions.FireTriggers;
if (keepIdentity)
{
sqlBulkCopyOptions = SqlBulkCopyOptions.KeepIdentity | sqlBulkCopyOptions;
}
SqlBulkCopy sbc = new SqlBulkCopy(conn, sqlBulkCopyOptions, externalTransaction);
using (sbc)
{
for (int i = 0; i < dtToWrite.Columns.Count; i++)
{
var column = dtToWrite.Columns[i];
sbc.ColumnMappings.Add(column.ColumnName, column.ColumnName);
}
if (batchSize != null)
{
sbc.BatchSize = batchSize.Value;
}
DbTable dbTable = string.IsNullOrEmpty(table) ? typeDescriptor.Table : new DbTable(table, typeDescriptor.Table.Schema);
sbc.DestinationTableName = AppendTableName(dbTable);
if (bulkCopyTimeout != null)
{
sbc.BulkCopyTimeout = bulkCopyTimeout.Value;
}
if (conn.State != ConnectionState.Open)
{
conn.Open();
shouldCloseConnection = true;
}
sbc.WriteToServer(dtToWrite);
}
}
finally
{
if (conn != null)
{
if (shouldCloseConnection && conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
}
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 && !a.IsTimestamp()).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);
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("NEXT VALUE FOR ");
sqlBuilder.Append(mappingPropertyDescriptor.Definition.SequenceName);
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("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);
}
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;
}
}
};
if (this.Session.IsInTransaction)
{
insertAction();
}
else
{
/* 因为分批插入，所以需要开启事务保证数据一致性 */
this.Session.BeginTransaction();
try
{
insertAction();
this.Session.CommitTransaction();
}
catch
{
if (this.Session.IsInTransaction)
{
this.Session.RollbackTransaction();
}
throw;
}
}
}
public override TEntity Insert <TEntity>(TEntity entity, string table)
{
PublicHelper.CheckNull(entity);
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
Dictionary <PropertyDescriptor, object> keyValueMap = PrimaryKeyHelper.CreateKeyValueMap(typeDescriptor);
Dictionary <PropertyDescriptor, DbExpression> insertColumns = new Dictionary <PropertyDescriptor, DbExpression>();
List <PropertyDescriptor> outputColumns = new List <PropertyDescriptor>();
foreach (PropertyDescriptor propertyDescriptor in typeDescriptor.PropertyDescriptors)
{
if (propertyDescriptor.IsAutoIncrement || propertyDescriptor.IsTimestamp())
{
outputColumns.Add(propertyDescriptor);
continue;
}
if (propertyDescriptor.HasSequence())
{
DbMethodCallExpression getNextValueForSequenceExp = PublicHelper.MakeNextValueForSequenceDbExpression(propertyDescriptor);
insertColumns.Add(propertyDescriptor, getNextValueForSequenceExp);
outputColumns.Add(propertyDescriptor);
continue;
}
object val = propertyDescriptor.GetValue(entity);
if (propertyDescriptor.IsPrimaryKey)
{
keyValueMap[propertyDescriptor] = val;
}
DbExpression valExp = DbExpression.Parameter(val, propertyDescriptor.PropertyType, propertyDescriptor.Column.DbType);
insertColumns.Add(propertyDescriptor, valExp);
}
PropertyDescriptor 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 = table == null ? typeDescriptor.Table : new DbTable(table, typeDescriptor.Table.Schema);
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 (PropertyDescriptor 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 OracleContext(IDbConnectionFactory dbConnectionFactory)
{
PublicHelper.CheckNull(dbConnectionFactory);
this._databaseProvider = new DatabaseProvider(dbConnectionFactory, this);
}
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.IsAutoIncrement || propertyDescriptor.HasSequence() || propertyDescriptor.IsRowVersion)
{
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);
}
object rowVersionNewValue = null;
if (typeDescriptor.HasRowVersion())
{
var rowVersionDescriptor = typeDescriptor.RowVersion;
var rowVersionOldValue = rowVersionDescriptor.GetValue(entity);
rowVersionNewValue = PublicHelper.IncreaseRowVersionNumber(rowVersionOldValue);
updateColumns.Add(rowVersionDescriptor, DbExpression.Parameter(rowVersionNewValue, rowVersionDescriptor.PropertyType, rowVersionDescriptor.Column.DbType));
keyValues.Add(rowVersionDescriptor, rowVersionOldValue);
}
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 = await this.ExecuteNonQuery(e, @async);
if (typeDescriptor.HasRowVersion())
{
PublicHelper.CauseErrorIfOptimisticUpdateFailed(rowsAffected);
typeDescriptor.RowVersion.SetValue(entity, rowVersionNewValue);
}
if (entityState != null)
{
entityState.Refresh();
}
return(rowsAffected);
}
public IJoinQuery <T, TOther> Join <TOther>(IQuery <TOther> q, JoinType joinType, Expression <Func <T, TOther, bool> > on)
{
PublicHelper.CheckNull(q);
PublicHelper.CheckNull(on);
return(new JoinQuery <T, TOther>(this, (Query <TOther>)q, joinType, on));
}
public DbCommandDecorator(IDbCommand dbCommand)
{
PublicHelper.CheckNull(dbCommand);
this._dbCommand = dbCommand;
}
public async Task <int> ExecuteNonQueryAsync(string cmdText, CommandType cmdType, params DbParam[] parameters)
{
PublicHelper.CheckNull(cmdText, "cmdText");
return(await this._dbContext.AdoSession.ExecuteNonQueryAsync(cmdText, parameters, cmdType));
}
public object ExecuteScalar(string cmdText, CommandType cmdType, params DbParam[] parameters)
{
PublicHelper.CheckNull(cmdText, "cmdText");
return(this._dbContext.AdoSession.ExecuteScalar(cmdText, parameters, cmdType));
}
public override int Update <TEntity>(TEntity entity, string table)
{
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.IsAutoIncrement || propertyDescriptor.HasSequence() || propertyDescriptor.IsRowVersion)
{
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 = this.ExecuteNonQuery(e);
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 = this.ExecuteReader(e);
using (dataReader)
{
while (dataReader.Read())
{
rowsAffected++;
foreach (var mapper in mappers)
{
mapper(entity, dataReader);
}
}
}
PublicHelper.CauseErrorIfOptimisticUpdateFailed(rowsAffected);
}
else
{
rowsAffected = this.ExecuteNonQuery(e);
PublicHelper.CauseErrorIfOptimisticUpdateFailed(rowsAffected);
rowVersionDescriptor.SetValue(entity, rowVersionNewValue);
}
if (entityState != null)
{
entityState.Refresh();
}
return(rowsAffected);
}
public void RemoveInterceptor(IDbCommandInterceptor interceptor)
{
PublicHelper.CheckNull(interceptor, "interceptor");
this._dbContext.AdoSession.SessionInterceptors.Remove(interceptor);
}
public DefaultDbConnectionFactory(string connString)
{
PublicHelper.CheckNull(connString, "connString");
this._connString = connString;
}
public DbConnectionDecorator(IDbConnection dbConnection)
{
PublicHelper.CheckNull(dbConnection);
this._dbConnection = dbConnection;
}
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 int Update <TEntity>(TEntity entity, string table)
{
PublicHelper.CheckNull(entity);
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
PublicHelper.EnsureHasPrimaryKey(typeDescriptor);
Dictionary <PropertyDescriptor, object> keyValueMap = PrimaryKeyHelper.CreateKeyValueMap(typeDescriptor);
IEntityState entityState = this.TryGetTrackedEntityState(entity);
Dictionary <PropertyDescriptor, DbExpression> updateColumns = new Dictionary <PropertyDescriptor, DbExpression>();
PropertyDescriptor timestampProperty = null;
object timestampValue = null;
foreach (PropertyDescriptor propertyDescriptor in typeDescriptor.PropertyDescriptors)
{
if (propertyDescriptor.IsPrimaryKey)
{
keyValueMap[propertyDescriptor] = propertyDescriptor.GetValue(entity);
continue;
}
if (propertyDescriptor.IsAutoIncrement || propertyDescriptor.HasSequence())
{
continue;
}
if (propertyDescriptor.IsTimestamp())
{
timestampProperty = propertyDescriptor;
timestampValue = propertyDescriptor.GetValue(entity);
continue;
}
object val = propertyDescriptor.GetValue(entity);
if (entityState != null && !entityState.HasChanged(propertyDescriptor, val))
{
continue;
}
DbExpression valExp = DbExpression.Parameter(val, propertyDescriptor.PropertyType, propertyDescriptor.Column.DbType);
updateColumns.Add(propertyDescriptor, valExp);
}
if (updateColumns.Count == 0)
{
return(0);
}
DbTable dbTable = table == null ? typeDescriptor.Table : new DbTable(table, typeDescriptor.Table.Schema);
if (timestampValue != null)
{
keyValueMap[timestampProperty] = timestampValue;
}
DbExpression conditionExp = PrimaryKeyHelper.MakeCondition(keyValueMap, dbTable);
DbUpdateExpression e = new DbUpdateExpression(dbTable, conditionExp);
foreach (var item in updateColumns)
{
e.UpdateColumns.Add(item.Key.Column, item.Value);
}
int rowsAffected = 0;
if (timestampValue == null)
{
rowsAffected = this.ExecuteNonQuery(e);
if (entityState != null)
{
entityState.Refresh();
}
return(rowsAffected);
}
List <Action <TEntity, IDataReader> > mappers = new List <Action <TEntity, IDataReader> >();
mappers.Add(GetMapper <TEntity>(timestampProperty, e.Returns.Count));
e.Returns.Add(timestampProperty.Column);
IDataReader dataReader = this.ExecuteReader(e);
using (dataReader)
{
while (dataReader.Read())
{
rowsAffected++;
foreach (var mapper in mappers)
{
mapper(entity, dataReader);
}
}
}
if (entityState != null)
{
entityState.Refresh();
}
return(rowsAffected);
}
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>();
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)
{
this.ExecuteNonQuery(insertExp);
return(entity);
}
IDbExpressionTranslator translator = this.DatabaseProvider.CreateDbExpressionTranslator();
List <DbParam> parameters;
string 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 IGroupingQuery <T> GroupBy <K>(Expression <Func <T, K> > keySelector)
{
PublicHelper.CheckNull(keySelector);
return(new GroupingQuery <T>(this, keySelector));
}
public DataReaderDecorator(IDataReader reader)
{
PublicHelper.CheckNull(reader);
this._reader = reader;
}
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 = 1000;
int batchSize = 60; /* 每批实体大小，此值通过测试得出相对插入速度比较快的一个值 */
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);
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];
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);
}
sqlBuilder.Append(")");
batchCount++;
if ((batchCount >= 20 && dbParams.Count >= 400 /*参数个数太多也会影响速度*/) || 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();
}
}
public ChloeMySqlConnection(IDbConnection dbConnection)
{
PublicHelper.CheckNull(dbConnection);
this._dbConnection = dbConnection;
}
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 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.ExecuteNonQuery(insertExp);
return(keyVal); /* It will return null if an entity does not define primary key. */
}
if (!keyPropertyDescriptor.IsAutoIncrement && !keyPropertyDescriptor.HasSequence())
{
this.ExecuteNonQuery(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 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));
}
var sequencePropertyDescriptors = typeDescriptor.PropertyDescriptors.Where(a => a.HasSequence());
foreach (PropertyDescriptor sequencePropertyDescriptor in sequencePropertyDescriptors)
{
DbMethodCallExpression getNextValueForSequenceExp = PublicHelper.MakeNextValueForSequenceDbExpression(sequencePropertyDescriptor);
insertExp.InsertColumns.Add(sequencePropertyDescriptor.Column, getNextValueForSequenceExp);
if (sequencePropertyDescriptor.IsPrimaryKey)
{
insertExp.Returns.Add(sequencePropertyDescriptor.Column);
}
}
if (keyPropertyDescriptor != null && !keyPropertyDescriptor.HasSequence() && keyVal == null)
{
throw new ChloeException(string.Format("The primary key '{0}' could not be null.", keyPropertyDescriptor.Property.Name));
}
List <DbParam> parameters;
this.ExecuteSqlCommand(insertExp, out parameters);
if (keyPropertyDescriptor != null && keyPropertyDescriptor.HasSequence())
{
string putputColumnName = Utils.GenOutputColumnParameterName(keyPropertyDescriptor.Column.Name);
DbParam outputParam = parameters.Where(a => a.Direction == ParamDirection.Output && a.Name == putputColumnName).First();
keyVal = PublicHelper.ConvertObjType(outputParam.Value, keyPropertyDescriptor.PropertyType);
}
return(keyVal); /* It will return null if an entity does not define primary key. */
}
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 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 (PrimitivePropertyDescriptor propertyDescriptor in typeDescriptor.PrimitivePropertyDescriptors)
{
if (propertyDescriptor.IsAutoIncrement && propertyDescriptor.IsPrimaryKey)
{
insertExp.Returns.Add(propertyDescriptor.Column);
continue;
}
if (propertyDescriptor.HasSequence())
{
DbMethodCallExpression getNextValueForSequenceExp = PublicHelper.MakeNextValueForSequenceDbExpression(propertyDescriptor, dbTable.Schema);
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));
}
}
DbCommandInfo dbCommandInfo = this.Translate(insertExp);
await this.ExecuteNonQuery(dbCommandInfo, @async);
if (keyPropertyDescriptor != null && (keyPropertyDescriptor.IsAutoIncrement || keyPropertyDescriptor.HasSequence()))
{
string outputColumnName = Utils.GenOutputColumnParameterName(keyPropertyDescriptor.Column.Name);
DbParam outputParam = dbCommandInfo.Parameters.Where(a => a.Direction == ParamDirection.Output && a.Name == outputColumnName).First();
keyVal = PublicHelper.ConvertObjectType(outputParam.Value, keyPropertyDescriptor.PropertyType);
}
return(keyVal); /* It will return null if an entity does not define primary key. */
}
