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