/// <summary>
/// 将 <see cref="DataTable"/> 的数据批量插入到数据库中。
/// </summary>
/// <param name="database">提供给当前插件的 <see cref="IDatabase"/> 对象。</param>
/// <param name="dataTable">要批量插入的 <see cref="DataTable"/>。</param>
/// <param name="batchSize">每批次写入的数据量。</param>
/// <param name="completePercentage">已完成百分比的通知方法。</param>
public void Insert(IDatabase database, DataTable dataTable, int batchSize = 1000, Action <int> completePercentage = null)
{
if (!BatcherChecker.CheckDataTable(dataTable))
{
return;
}
try
{
database.Connection.TryOpen();
//给表名加上前后导符
var tableName = DbUtility.FormatByQuote(database.Provider.GetService <ISyntaxProvider>(), dataTable.TableName);
using (var bulk = new SqlBulkCopy((SqlConnection)database.Connection, SqlBulkCopyOptions.KeepIdentity, (SqlTransaction)database.Transaction)
{
DestinationTableName = tableName,
BatchSize = batchSize
})
using (var reader = new DataTableBatchReader(bulk, dataTable))
{
bulk.WriteToServer(reader);
}
}
catch (Exception exp)
{
throw new BatcherException(dataTable.Rows, exp);
}
}
/// <summary>
/// 批量插入集合中的数据。
/// </summary>
/// <param name="database">当前的 <see cref="IDatabase"/> 对象。</param>
/// <param name="collection">要插入的数据的集合。</param>
/// <param name="tableName">表的名称。</param>
/// <param name="mapping">名称和类型的映射字典。</param>
/// <param name="batchSize">每批次写入的数据量。</param>
/// <param name="completePercentage">已完成百分比的通知方法。</param>
private void BatchInsert(IDatabase database, ICollection collection, string tableName, IList <PropertyFieldMapping> mapping, int batchSize, Action <int> completePercentage)
{
//Oracle.DataAccess将每一列的数据构造成一个数组，然后使用参数进行插入
try
{
database.Connection.TryOpen();
using (var command = database.Provider.CreateCommand(database.Connection, database.Transaction, null))
{
var syntax = database.Provider.GetService <ISyntaxProvider>();
var sql = string.Format("INSERT INTO {0}({1}) VALUES({2})",
DbUtility.FormatByQuote(syntax, tableName),
#if NET35
string.Join(",", mapping.Select(s => DbUtility.FormatByQuote(syntax, s.FieldName)).ToArray()), string.Join(",", mapping.Select(s => syntax.ParameterPrefix + s).ToArray()));
#else
string.Join(",", mapping.Select(s => DbUtility.FormatByQuote(syntax, s.FieldName))), string.Join(",", mapping.Select(s => syntax.ParameterPrefix + s.FieldName)));
#endif
command.CommandText = sql;
var length = Math.Min(batchSize, collection.Count);
var count = collection.Count;
var data = InitArrayData(mapping.Count, length);
SetArrayBindCount(command, length);
BatchSplitData(collection, batchSize,
(index, batch, item) =>
{
if (mapping == null)
{
mapping = GetNameTypeMapping(item);
}
FillArrayData(mapping, item, data, batch);
},
(index, batch, surplus, lastBatch) =>
{
AddOrReplayParameters(syntax, mapping, command.Parameters, data,
() => database.Provider.DbProviderFactory.CreateParameter());
command.ExecuteNonQuery();
if (completePercentage != null)
{
completePercentage((int)(((index + 1.0) / count) * 100));
}
if (!lastBatch)
{
length = Math.Min(batchSize, surplus);
data = InitArrayData(mapping.Count, length);
SetArrayBindCount(command, length);
}
});
}
}
catch (Exception exp)
{
throw new BatcherException(collection, exp);
}
}
/// <summary>
/// 批量将一组实体对象插入到库中。
/// </summary>
/// <param name="entities">一组要插入实体对象。</param>
/// <param name="batchSize">每一个批次插入的实体数量。默认为 1000。</param>
/// <param name="completePercentage">已完成百分比的通知方法。</param>
public async Task BatchInsertAsync(IEnumerable <TEntity> entities, int batchSize = 1000, Action <int> completePercentage = null, CancellationToken cancellationToken = default)
{
var batcher = context.Database.Provider.GetService <IBatcherProvider>();
if (batcher == null)
{
throw new EntityPersistentException(SR.GetString(SRKind.NotSupportBatcher), null);
}
var syntax = context.Database.Provider.GetService <ISyntaxProvider>();
var rootType = typeof(TEntity).GetRootEntityType();
string tableName;
if (context.Environment != null)
{
tableName = DbUtility.FormatByQuote(syntax, context.Environment.GetVariableTableName(rootType));
}
else
{
var metadata = EntityMetadataUnity.GetEntityMetadata(rootType);
tableName = DbUtility.FormatByQuote(syntax, metadata.TableName);
}
await batcher.InsertAsync(context.Database, entities, tableName, batchSize, completePercentage, cancellationToken);
}
/// <summary>
/// 取参照实体的最大order值。
/// </summary>
/// <param name="bag"></param>
/// <param name="mode"></param>
/// <param name="offset"></param>
/// <param name="isolation"></param>
/// <returns></returns>
private int GetNewOrderNumber(EntityTreeUpdatingBag bag, EntityTreePosition mode, int offset = 0, Expression <Func <TEntity> > isolation = null)
{
if (bag == null)
{
return(GetNewOrderNumber(isolation));
}
switch (mode)
{
case EntityTreePosition.Children:
var sql = string.Format("SELECT MAX({0}) FROM {1} WHERE {2} LIKE {3}",
GetOrderExpression(),
DbUtility.FormatByQuote(syntax, metadata.TableName),
QuoteColumn(metaTree.InnerSign),
syntax.FormatParameter("pm"));
var innerId = bag.InnerId;
var parameters = new ParameterCollection {
{ "pm", innerId + new string('_', metaTree.SignLength) }
};
return(database.ExecuteScalar((SqlCommand)sql, parameters).To <int>() + 1 + offset);
case EntityTreePosition.Before:
return(bag.Order + offset);
case EntityTreePosition.After:
return(bag.Order + 1 + offset);
}
return(0);
}
/// <summary>
/// 批量将一组实体对象创建到库中。
/// </summary>
/// <param name="entities">一组要插入实体对象。</param>
/// <param name="batchSize">每一个批次写入的实体数量。默认为 1000。</param>
/// <param name="completePercentage">已完成百分比的通知方法。</param>
public virtual void BatchCreate(IEnumerable <TEntity> entities, int batchSize = 1000, Action <int> completePercentage = null)
{
Guard.ArgumentNull(entities, "entities");
var batcher = Database.Provider.GetService <IBatcherProvider>();
if (batcher == null)
{
throw new EntityPersistentException(SR.GetString(SRKind.NotSupportBatcher), null);
}
var syntax = Database.Provider.GetService <ISyntaxProvider>();
var rootType = GetEntityType().GetRootType();
var tableName = string.Empty;
if (Environment != null)
{
tableName = DbUtility.FormatByQuote(syntax, Environment.GetVariableTableName(rootType));
}
else
{
var metadata = EntityMetadataUnity.GetEntityMetadata(rootType);
tableName = DbUtility.FormatByQuote(syntax, metadata.TableName);
}
batcher.Insert(Database, entities, tableName, batchSize, completePercentage);
}
public void BatchInsert(IEnumerable <TEntity> entities, int batchSize = 1000, Action <int> completePercentage = null)
{
var batcher = context.Database.Provider.GetService <IBatcherProvider>();
if (batcher == null)
{
throw new EntityPersistentException(SR.GetString(SRKind.NotSupportBatcher), null);
}
var syntax = context.Database.Provider.GetService <ISyntaxProvider>();
var rootType = typeof(TEntity).GetRootType();
var tableName = string.Empty;
entities.ForEach(s => EntityPersistentSubscribePublisher.OnBeforeCreate(s));
//if (Environment != null)
{
// tableName = DbUtility.FormatByQuote(syntax, Environment.GetVariableTableName(rootType));
}
//else
{
var metadata = EntityMetadataUnity.GetEntityMetadata(rootType);
tableName = DbUtility.FormatByQuote(syntax, metadata.TableName);
}
batcher.Insert(context.Database, entities, tableName, batchSize, completePercentage);
}
private string QuoteColumn(IProperty property)
{
var sqc = property as SubqueryProperty;
if (sqc != null)
{
return("(" + sqc.Subquery.Replace("$", "T.") + ") " + DbUtility.FormatByQuote(m_syntax, sqc.Name));
}
return(DbUtility.FormatByQuote(m_syntax, property.Info.FieldName));
}
/// <summary>
/// 获取Level的表达式。
/// </summary>
/// <returns></returns>
private string GetLevelExpression()
{
//如果Level没有指定，则取InnerId的长度除以N
if (metaTree.Level == null)
{
return(syntax.String.Length(DbUtility.FormatByQuote(syntax, metaTree.InnerSign.Info.FieldName)) + " / " + metaTree.SignLength);
}
return(DbUtility.FormatByQuote(syntax, metaTree.Order.Info.FieldName));
}
/// <summary>
/// 获取Order的表达式。
/// </summary>
/// <returns></returns>
private string GetOrderExpression()
{
//如果Order没有指定，则取InnerId的后N位转成数字
if (metaTree.Order == null)
{
var field = DbUtility.FormatByQuote(syntax, metaTree.InnerSign.Info.FieldName);
return(syntax.Convert(
syntax.String.Substring(field, syntax.String.Length(field) + " + 1 - " + metaTree.SignLength,
metaTree.SignLength), DbType.Int32));
}
return(DbUtility.FormatByQuote(syntax, metaTree.Order.Info.FieldName));
}
private string QuoteTable()
{
var rootType = m_entityType.GetRootType();
if (m_environment != null)
{
return(DbUtility.FormatByQuote(m_syntax, m_environment.GetVariableTableName(rootType)));
}
else
{
var metadata = EntityMetadataUnity.GetEntityMetadata(rootType);
return(DbUtility.FormatByQuote(m_syntax, metadata.TableName));
}
}
private async Task BatchInsertAsync(IDatabase database, ICollection collection, string tableName, IList <PropertyFieldMapping> mapping, Func <IList <PropertyFieldMapping>, DbCommand, int, object, string> valueFunc, int batchSize, Action <int> completePercentage, CancellationToken cancellationToken = default)
{
//SQLite使用如 insert into table(f1, f2) values ('a1', 'b1'),('a2', 'b2'),('a3', 'b3') 方式批量插入
//但一次只能插入500条
batchSize = 500;
try
{
database.BeginTransaction();
using (var command = database.Provider.CreateCommand(database.Connection, database.Transaction, null))
{
var syntax = database.Provider.GetService <ISyntaxProvider>();
var valueSeg = new List <string>(batchSize);
var count = collection.Count;
BatchSplitData(collection, batchSize,
(index, batch, item) =>
{
if (mapping == null)
{
mapping = GetNameTypeMapping(item);
}
valueSeg.Add(string.Format("({0})", valueFunc(mapping, command, batch, item)));
},
(index, batch, surplus, lastBatch) =>
{
var sql = string.Format("INSERT INTO {0}({1}) VALUES {2}",
DbUtility.FormatByQuote(syntax, tableName),
string.Join(",", mapping.Select(s => DbUtility.FormatByQuote(syntax, s.FieldName))), string.Join(",", valueSeg));
command.CommandText = sql;
command.ExecuteNonQueryAsync(cancellationToken);
valueSeg.Clear();
command.Parameters.Clear();
completePercentage?.Invoke((int)(((index + 1.0) / count) * 100));
});
}
database.CommitTransaction();
}
catch (Exception exp)
{
database.RollbackTransaction();
throw new BatcherException(collection, exp);
}
}
private void BatchInsert(IDatabase database, ICollection collection, string tableName, IList <PropertyFieldMapping> mapping, Func <IList <PropertyFieldMapping>, DbCommand, int, object, string> valueFunc, int batchSize, Action <int> completePercentage)
{
//SQLite使用如 insert into table(f1, f2) values ('a1', 'b1'),('a2', 'b2'),('a3', 'b3') 方式批量插入
//但一次只能插入500条
batchSize = 500;
try
{
database.BeginTransaction();
using (var command = database.Provider.CreateCommand(database.Connection, database.Transaction, null))
{
var syntax = database.Provider.GetService <ISyntaxProvider>();
var valueSeg = new List <string>(batchSize);
var count = collection.Count;
BatchSplitData(collection, batchSize,
(index, batch, item) =>
{
if (mapping == null)
{
mapping = GetNameTypeMapping(item);
}
valueSeg.Add(string.Format("({0})", valueFunc(mapping, command, batch, item)));
},
(index, batch, surplus, lastBatch) =>
{
var sql = string.Format("INSERT INTO {0}({1}) VALUES {2}",
DbUtility.FormatByQuote(syntax, tableName),
#if NET35
string.Join(",", mapping.Select(s => DbUtility.FormatByQuote(syntax, s.FieldName)).ToArray()), string.Join(",", valueSeg.ToArray()));
#else
string.Join(",", mapping.Select(s => DbUtility.FormatByQuote(syntax, s.FieldName))), string.Join(",", valueSeg));
#endif
command.CommandText = sql;
command.ExecuteNonQuery();
valueSeg.Clear();
command.Parameters.Clear();
if (completePercentage != null)
{
completePercentage((int)(((index + 1.0) / count) * 100));
}
});
}
/// <summary>
/// 取顶层的最大order值。
/// </summary>
/// <returns></returns>
private int GetNewOrderNumber(Expression <Func <TEntity> > isolation = null)
{
var sql = string.Format("SELECT MAX({0}) FROM {1} WHERE {2} = {3}",
GetOrderExpression(),
DbUtility.FormatByQuote(syntax, metadata.TableName),
syntax.String.Length(QuoteColumn(metaTree.InnerSign)),
metaTree.SignLength);
if (isolation != null)
{
var condition = IsolationConditionBuilder.Build(isolation);
if (!string.IsNullOrEmpty(condition))
{
sql += " AND " + condition;
}
}
return(database.ExecuteScalar((SqlCommand)sql).To <int>() + 1);
}
protected string Quote(ISyntaxProvider syntax, string name)
{
return(DbUtility.FormatByQuote(syntax, name));
}
/// <summary>
/// 获取属性的对应的字段表达式，并在前后加上标识符。
/// </summary>
/// <param name="property"></param>
/// <returns></returns>
private string QuoteColumn(IProperty property)
{
return(DbUtility.FormatByQuote(syntax, property.Info.FieldName));
}
/// <summary>
/// 获取实体类所对应的表的名称。
/// </summary>
/// <returns></returns>
private string GetTableName()
{
return(DbUtility.FormatByQuote(syntax, metadata.TableName));
}
private string Quote(string name)
{
return(DbUtility.FormatByQuote(m_syntax, name));
}
