protected override BulkCopyRowsCopied MultipleRowsCopy <T>(ITable <T> table, BulkCopyOptions options, IEnumerable <T> source)
{
return(MultipleRowsCopy1(table, options, source));
}
BulkCopyRowsCopied IDataProvider.BulkCopy <T>(DataConnection dataConnection, BulkCopyOptions options, IEnumerable <T> source)
{
throw new NotImplementedException();
}
protected virtual BulkCopyRowsCopied RowByRowCopy <T>(DataConnection dataConnection, BulkCopyOptions options, IEnumerable <T> source)
{
var rowsCopied = new BulkCopyRowsCopied();
foreach (var item in source)
{
dataConnection.Insert(item, options.TableName, options.DatabaseName, options.SchemaName);
rowsCopied.RowsCopied++;
if (options.NotifyAfter != 0 && options.RowsCopiedCallback != null && rowsCopied.RowsCopied % options.NotifyAfter == 0)
{
options.RowsCopiedCallback(rowsCopied);
if (rowsCopied.Abort)
{
break;
}
}
}
return(rowsCopied);
}
protected override BulkCopyRowsCopied MultipleRowsCopy <T>(
DataConnection dataConnection, BulkCopyOptions options, IEnumerable <T> source)
{
return(MultipleRowsCopy2(dataConnection, options, false, source, ""));
}
public virtual BulkCopyRowsCopied BulkCopy <T>(BulkCopyType bulkCopyType, DataConnection dataConnection, BulkCopyOptions options, IEnumerable <T> source)
{
switch (bulkCopyType)
{
case BulkCopyType.MultipleRows: return(MultipleRowsCopy(dataConnection, options, source));
case BulkCopyType.RowByRow: return(RowByRowCopy(dataConnection, options, source));
default: return(ProviderSpecificCopy(dataConnection, options, source));
}
}
protected virtual BulkCopyRowsCopied MultipleRowsCopy <T>(DataConnection dataConnection, BulkCopyOptions options, IEnumerable <T> source)
{
return(RowByRowCopy(dataConnection, options, source));
}
private async Task <BulkCopyRowsCopied> ProviderSpecificCopyInternal <T>(
ProviderConnections providerConnections,
ITable <T> table,
BulkCopyOptions options,
Func <List <Mapping.ColumnDescriptor>, BulkCopyReader <T> > createDataReader,
bool runAsync,
CancellationToken cancellationToken)
{
var dataConnection = providerConnections.DataConnection;
var connection = providerConnections.ProviderConnection;
var transaction = providerConnections.ProviderTransaction;
var ed = dataConnection.MappingSchema.GetEntityDescriptor(typeof(T));
var columns = ed.Columns.Where(c => !c.SkipOnInsert || options.KeepIdentity == true && c.IsIdentity).ToList();
var sb = _provider.CreateSqlBuilder(dataConnection.MappingSchema);
var rd = createDataReader(columns);
var sqlopt = SqlServerProviderAdapter.SqlBulkCopyOptions.Default;
var rc = new BulkCopyRowsCopied();
if (options.CheckConstraints == true)
{
sqlopt |= SqlServerProviderAdapter.SqlBulkCopyOptions.CheckConstraints;
}
if (options.KeepIdentity == true)
{
sqlopt |= SqlServerProviderAdapter.SqlBulkCopyOptions.KeepIdentity;
}
if (options.TableLock == true)
{
sqlopt |= SqlServerProviderAdapter.SqlBulkCopyOptions.TableLock;
}
if (options.KeepNulls == true)
{
sqlopt |= SqlServerProviderAdapter.SqlBulkCopyOptions.KeepNulls;
}
if (options.FireTriggers == true)
{
sqlopt |= SqlServerProviderAdapter.SqlBulkCopyOptions.FireTriggers;
}
if (options.UseInternalTransaction == true)
{
sqlopt |= SqlServerProviderAdapter.SqlBulkCopyOptions.UseInternalTransaction;
}
using (var bc = _provider.Adapter.CreateBulkCopy(connection, sqlopt, transaction))
{
if (options.NotifyAfter != 0 && options.RowsCopiedCallback != null)
{
bc.NotifyAfter = options.NotifyAfter;
bc.SqlRowsCopied += (sender, args) =>
{
rc.RowsCopied = args.RowsCopied;
options.RowsCopiedCallback(rc);
if (rc.Abort)
{
args.Abort = true;
}
};
}
if (options.MaxBatchSize.HasValue)
{
bc.BatchSize = options.MaxBatchSize.Value;
}
if (options.BulkCopyTimeout.HasValue)
{
bc.BulkCopyTimeout = options.BulkCopyTimeout.Value;
}
else if (Common.Configuration.Data.BulkCopyUseConnectionCommandTimeout)
{
bc.BulkCopyTimeout = connection.ConnectionTimeout;
}
var tableName = GetTableName(sb, options, table);
bc.DestinationTableName = tableName;
for (var i = 0; i < columns.Count; i++)
{
bc.ColumnMappings.Add(_provider.Adapter.CreateBulkCopyColumnMapping(i, sb.ConvertInline(columns[i].ColumnName, ConvertType.NameToQueryField)));
}
await TraceActionAsync(
dataConnection,
() => (runAsync ? "INSERT ASYNC BULK " : "INSERT BULK ") + tableName + "(" + string.Join(", ", columns.Select(x => x.ColumnName)) + ")" + Environment.NewLine,
async() => {
if (runAsync)
{
await bc.WriteToServerAsync(rd, cancellationToken).ConfigureAwait(Common.Configuration.ContinueOnCapturedContext);
}
else
{
bc.WriteToServer(rd);
}
return(rd.Count);
}).ConfigureAwait(Common.Configuration.ContinueOnCapturedContext);
}
if (rc.RowsCopied != rd.Count)
{
rc.RowsCopied = rd.Count;
if (options.NotifyAfter != 0 && options.RowsCopiedCallback != null)
{
options.RowsCopiedCallback(rc);
}
}
return(rc);
}
protected virtual BulkCopyRowsCopied RowByRowCopy <T>(DataConnection dataConnection, BulkCopyOptions options, IEnumerable <T> source)
{
// This limitation could be lifted later for some providers that supports identity insert if we will get such request
// It will require support from DataConnection.Insert
if (options.KeepIdentity == true)
{
throw new LinqToDBException($"{nameof(BulkCopyOptions)}.{nameof(BulkCopyOptions.KeepIdentity)} = true is not supported by {nameof(BulkCopyType)}.{nameof(BulkCopyType.RowByRow)} mode");
}
var rowsCopied = new BulkCopyRowsCopied();
foreach (var item in source)
{
dataConnection.Insert(item, options.TableName, options.DatabaseName, options.SchemaName);
rowsCopied.RowsCopied++;
if (options.NotifyAfter != 0 && options.RowsCopiedCallback != null && rowsCopied.RowsCopied % options.NotifyAfter == 0)
{
options.RowsCopiedCallback(rowsCopied);
if (rowsCopied.Abort)
{
break;
}
}
}
return(rowsCopied);
}
/// <summary>
/// SqlServer大数据复制
/// </summary>
/// <param name="dr">数据源</param>
/// <param name="tableName">对应的表名</param>
/// <param name="dbkey">数据库</param>
/// <param name="options">选项 默认Default</param>
/// <param name="isTran">是否使用事务 默认false</param>
/// <param name="timeout">超时时间7200 2小时</param>
/// <param name="batchSize">每一批次中的行数</param>
/// <param name="error">错误处理</param>
/// <returns>true/false</returns>
public bool DataBulkCopy(IDataReader dr, string tableName, string dbkey = "", BulkCopyOptions options = BulkCopyOptions.Default, bool isTran = false, int timeout = 7200, int batchSize = 10000, Action<Exception> error = null) {
if (Data.Pool(dbkey).DBType != "SqlServer") return false;
SqlTransaction tran = null;
using(SqlConnection conn = new SqlConnection(Data.Pool(dbkey).ConnString)) {
if (isTran) tran = conn.BeginTransaction();
using (System.Data.SqlClient.SqlBulkCopy bc = new System.Data.SqlClient.SqlBulkCopy(conn, ((int)options).ToEnum<SqlBulkCopyOptions>(), tran)) {
bc.BulkCopyTimeout = timeout;
bc.BatchSize = batchSize;
bc.DestinationTableName = tableName;
try {
bc.WriteToServer(dr);
if (isTran) tran.Commit();
} catch(Exception ex) {
if (isTran) tran.Rollback();
if (error.IsNotNull()) error(ex);
return false;
}
}
}
return true;
}
protected override BulkCopyRowsCopied ProviderSpecificCopy <T>(
[JetBrains.Annotations.NotNull] ITable <T> table,
BulkCopyOptions options,
IEnumerable <T> source)
{
if (!(table?.DataContext is DataConnection dataConnection))
{
throw new ArgumentNullException(nameof(dataConnection));
}
if (dataConnection.Transaction == null)
{
var sqlBuilder = dataConnection.DataProvider.CreateSqlBuilder(dataConnection.MappingSchema);
var descriptor = dataConnection.MappingSchema.GetEntityDescriptor(typeof(T));
var tableName = GetTableName(sqlBuilder, options, table);
if (_bulkCopyCreator == null)
{
var bulkCopyType = _connectionType.AssemblyEx().GetType("IBM.Data.DB2.DB2BulkCopy", false);
var bulkCopyOptionType = _connectionType.AssemblyEx().GetType("IBM.Data.DB2.DB2BulkCopyOptions", false);
var columnMappingType = _connectionType.AssemblyEx().GetType("IBM.Data.DB2.DB2BulkCopyColumnMapping", false);
if (bulkCopyType != null)
{
_bulkCopyCreator = CreateBulkCopyCreator(_connectionType, bulkCopyType, bulkCopyOptionType);
_columnMappingCreator = CreateColumnMappingCreator(columnMappingType);
}
}
if (_bulkCopyCreator != null)
{
var columns = descriptor.Columns.Where(c => !c.SkipOnInsert || options.KeepIdentity == true && c.IsIdentity).ToList();
var rd = new BulkCopyReader(dataConnection.DataProvider, dataConnection.MappingSchema, columns, source);
var rc = new BulkCopyRowsCopied();
var bcOptions = 0; // Default
if (options.KeepIdentity == true)
{
bcOptions |= 1; // KeepIdentity = 1, TableLock = 2, Truncate = 4,
}
if (options.TableLock == true)
{
bcOptions |= 2;
}
using (var bc = _bulkCopyCreator(Proxy.GetUnderlyingObject((DbConnection)dataConnection.Connection), bcOptions))
{
dynamic dbc = bc;
var notifyAfter = options.NotifyAfter == 0 && options.MaxBatchSize.HasValue ?
options.MaxBatchSize.Value : options.NotifyAfter;
if (notifyAfter != 0 && options.RowsCopiedCallback != null)
{
if (_bulkCopySubscriber == null)
{
_bulkCopySubscriber = CreateBulkCopySubscriber(bc, "DB2RowsCopied");
}
dbc.NotifyAfter = notifyAfter;
_bulkCopySubscriber(bc, arg =>
{
dynamic darg = arg;
rc.RowsCopied = darg.RowsCopied;
options.RowsCopiedCallback(rc);
if (rc.Abort)
{
darg.Abort = true;
}
});
}
if (options.BulkCopyTimeout.HasValue)
{
dbc.BulkCopyTimeout = options.BulkCopyTimeout.Value;
}
dbc.DestinationTableName = tableName;
for (var i = 0; i < columns.Count; i++)
{
dbc.ColumnMappings.Add((dynamic)_columnMappingCreator(i, columns[i].ColumnName));
}
TraceAction(
dataConnection,
() => "INSERT BULK " + tableName + Environment.NewLine,
() => { dbc.WriteToServer(rd); return(rd.Count); });
}
if (rc.RowsCopied != rd.Count)
{
rc.RowsCopied = rd.Count;
if (options.NotifyAfter != 0 && options.RowsCopiedCallback != null)
{
options.RowsCopiedCallback(rc);
}
}
return(rc);
}
}
return(MultipleRowsCopy(table, options, source));
}
/// <summary>
/// 大数据复制
/// </summary>
/// <param name="conn">连接源</param>
/// <param name="tran">事务</param>
/// <param name="dt">数据源 dt.TableName一定要和数据库表名对应</param>
/// <param name="options">选项 默认Default</param>
/// <param name="timeout">超时时间7200 2小时</param>
/// <param name="batchSize">每一批次中的行数</param>
/// <param name="error">错误处理</param>
/// <returns></returns>
public bool DataBulkCopy(IDbConnection conn, IDbTransaction tran, DataTable dt, BulkCopyOptions options = BulkCopyOptions.Default, int timeout = 7200, int batchSize = 10000, Action<Exception> error = null) {
using (System.Data.SqlClient.SqlBulkCopy bc = new System.Data.SqlClient.SqlBulkCopy((SqlConnection)conn, ((int)options).ToEnum<SqlBulkCopyOptions>(), (SqlTransaction)tran)) {
bc.BulkCopyTimeout = timeout;
bc.BatchSize = batchSize;
bc.DestinationTableName = dt.TableName;
try {
bc.WriteToServer(dt);
} catch(Exception ex) {
if (error.IsNotNull()) error(ex);
return false;
}
}
return true;
}
protected override BulkCopyRowsCopied MultipleRowsCopy <T>(DataConnection dataConnection, BulkCopyOptions options, IEnumerable <T> source)
{
var sqlBuilder = _dataProvider.CreateSqlBuilder();
var descriptor = dataConnection.MappingSchema.GetEntityDescriptor(typeof(T));
var tableName = GetTableName(sqlBuilder, descriptor);
var iszOS = _dataProvider.Version == DB2Version.zOS;
var rowsCopied = new BulkCopyRowsCopied();
var sb = new StringBuilder();
var buildValue = BasicSqlBuilder.GetBuildValue(sqlBuilder, sb);
var columns = descriptor.Columns.Where(c => !c.SkipOnInsert).ToArray();
var pname = sqlBuilder.Convert("p", ConvertType.NameToQueryParameter).ToString();
sb
.AppendFormat("INSERT INTO {0}", tableName).AppendLine()
.Append("(");
foreach (var column in columns)
{
sb
.AppendLine()
.Append("\t")
.Append(sqlBuilder.Convert(column.ColumnName, ConvertType.NameToQueryField))
.Append(",");
}
sb.Length--;
sb
.AppendLine()
.Append(")");
if (!iszOS)
{
sb
.AppendLine()
.Append("VALUES");
}
var headerLen = sb.Length;
var currentCount = 0;
var batchSize = options.MaxBatchSize ?? 1000;
if (batchSize <= 0)
{
batchSize = 1000;
}
var parms = new List <DataParameter>();
var pidx = 0;
foreach (var item in source)
{
sb
.AppendLine()
.Append(iszOS ? "SELECT " : "(");
foreach (var column in columns)
{
var value = column.GetValue(item);
if (value == null)
{
sb.Append("NULL");
}
else
{
switch (Type.GetTypeCode(value.GetType()))
{
case TypeCode.DBNull:
sb.Append("NULL");
break;
case TypeCode.String:
var isString = false;
switch (column.DataType)
{
case DataType.NVarChar:
case DataType.Char:
case DataType.VarChar:
case DataType.NChar:
case DataType.Undefined:
isString = true;
break;
}
if (isString)
{
goto case TypeCode.Int32;
}
goto default;
case TypeCode.Boolean:
case TypeCode.Char:
case TypeCode.SByte:
case TypeCode.Byte:
case TypeCode.Int16:
case TypeCode.UInt16:
case TypeCode.Int32:
case TypeCode.UInt32:
case TypeCode.Int64:
case TypeCode.UInt64:
case TypeCode.Single:
case TypeCode.Double:
case TypeCode.Decimal:
case TypeCode.DateTime:
//SetParameter(dataParam, "", column.DataType, value);
buildValue(value);
break;
default:
var name = pname + ++pidx;
sb.Append(name);
parms.Add(new DataParameter("p" + pidx, value, column.DataType));
break;
}
}
sb.Append(",");
}
sb.Length--;
sb.Append(iszOS ? " FROM SYSIBM.SYSDUMMY1 UNION ALL" : "),");
rowsCopied.RowsCopied++;
currentCount++;
if (currentCount >= batchSize || parms.Count > 100000 || sb.Length > 100000)
{
if (iszOS)
{
sb.Length -= " UNION ALL".Length;
}
else
{
sb.Length--;
}
dataConnection.Execute(sb.AppendLine().ToString(), parms.ToArray());
if (options.RowsCopiedCallback != null)
{
options.RowsCopiedCallback(rowsCopied);
if (rowsCopied.Abort)
{
return(rowsCopied);
}
}
parms.Clear();
pidx = 0;
currentCount = 0;
sb.Length = headerLen;
}
}
if (currentCount > 0)
{
if (iszOS)
{
sb.Length -= " UNION ALL".Length;
}
else
{
sb.Length--;
}
dataConnection.Execute(sb.ToString(), parms.ToArray());
sb.Length = headerLen;
if (options.RowsCopiedCallback != null)
{
options.RowsCopiedCallback(rowsCopied);
}
}
return(rowsCopied);
}
public virtual BulkCopyRowsCopied BulkCopy <T>(DataConnection dataConnection, BulkCopyOptions options, IEnumerable <T> source)
{
return(new BasicBulkCopy().BulkCopy(options.BulkCopyType, dataConnection, options, source));
}
protected override BulkCopyRowsCopied ProviderSpecificCopy <T>(
DataConnection dataConnection,
BulkCopyOptions options,
IEnumerable <T> source)
{
if (dataConnection == null)
{
throw new ArgumentNullException("dataConnection");
}
if (dataConnection.Transaction == null)
{
var sqlBuilder = dataConnection.DataProvider.CreateSqlBuilder();
var descriptor = dataConnection.MappingSchema.GetEntityDescriptor(typeof(T));
var tableName = GetTableName(sqlBuilder, options, descriptor);
if (_bulkCopyCreator == null)
{
throw new NotImplementedException("Not able to do bulk copy in DB2iSeries Provider.");
}
if (_bulkCopyCreator != null)
{
var columns = descriptor.Columns.Where(c => !c.SkipOnInsert || options.KeepIdentity == true && c.IsIdentity).ToList();
var rd = new BulkCopyReader(dataConnection.DataProvider, dataConnection.MappingSchema, columns, source);
var rc = new BulkCopyRowsCopied();
var bcOptions = 0; // Default
if (options.KeepIdentity == true)
{
bcOptions |= 1; // KeepIdentity = 1, TableLock = 2, Truncate = 4,
}
if (options.TableLock == true)
{
bcOptions |= 2;
}
using (var bc = _bulkCopyCreator(dataConnection.Connection, bcOptions))
{
dynamic dbc = bc;
var notifyAfter = options.NotifyAfter == 0 && options.MaxBatchSize.HasValue ?
options.MaxBatchSize.Value : options.NotifyAfter;
if (notifyAfter != 0 && options.RowsCopiedCallback != null)
{
if (_bulkCopySubscriber == null)
{
_bulkCopySubscriber = CreateBulkCopySubscriber(bc, "DB2iSeriesRowsCopied");
}
dbc.NotifyAfter = notifyAfter;
_bulkCopySubscriber(bc, arg =>
{
dynamic darg = arg;
rc.RowsCopied = darg.RowsCopied;
options.RowsCopiedCallback(rc);
if (rc.Abort)
{
darg.Abort = true;
}
});
}
if (options.BulkCopyTimeout.HasValue)
{
dbc.BulkCopyTimeout = options.BulkCopyTimeout.Value;
}
dbc.DestinationTableName = tableName;
for (var i = 0; i < columns.Count; i++)
{
dbc.ColumnMappings.Add((dynamic)_columnMappingCreator(i, columns[i].ColumnName));
}
dbc.WriteToServer(rd);
}
rc.RowsCopied = rd.Count;
return(rc);
}
}
return(MultipleRowsCopy(dataConnection, options, source));
}
public void KeepIdentity_SkipOnInsertTrue(
[DataSources(false)] string context,
[Values(null, true, false)] bool?keepIdentity,
[Values] BulkCopyType copyType)
{
// don't use transactions as some providers will fallback to non-provider-specific implementation then
using (var db = new TestDataConnection(context))
using (db.BeginTransaction())
{
var lastId = db.InsertWithInt32Identity(new TestTable2());
try
{
var options = new BulkCopyOptions()
{
KeepIdentity = keepIdentity,
BulkCopyType = copyType
};
if (!Execute(context, perform, keepIdentity, copyType))
{
return;
}
var data = db.GetTable <TestTable2>().Where(_ => _.ID > lastId).OrderBy(_ => _.ID).ToArray();
Assert.AreEqual(2, data.Length);
// oracle supports identity insert only starting from version 12c, which is not used yet for tests
var useGenerated = keepIdentity != true ||
context == ProviderName.Oracle ||
context == ProviderName.OracleNative ||
context == ProviderName.OracleManaged;
Assert.AreEqual(lastId + (!useGenerated ? 10 : 1), data[0].ID);
Assert.AreEqual(200, data[0].Value);
Assert.AreEqual(lastId + (!useGenerated ? 20 : 2), data[1].ID);
Assert.AreEqual(300, data[1].Value);
void perform()
{
db.BulkCopy(
options,
new[]
{
new TestTable2()
{
ID = lastId + 10,
Value = 200
},
new TestTable2()
{
ID = lastId + 20,
Value = 300
}
});
}
}
finally
{
// cleanup
db.GetTable <TestTable2>().Delete(_ => _.ID >= lastId);
}
}
}
/// <summary>
/// 大数据复制
/// </summary>
/// <param name="conn">连接源</param>
/// <param name="tran">事务</param>
/// <param name="dt">数据源 dt.TableName一定要和数据库表名对应</param>
/// <param name="options">选项 默认Default</param>
/// <param name="timeout">超时时间7200 2小时</param>
/// <param name="batchSize">每一批次中的行数</param>
/// <param name="error">错误处理</param>
/// <returns></returns>
public bool DataBulkCopy(IDbConnection conn, IDbTransaction tran, DataTable dt, BulkCopyOptions options = BulkCopyOptions.Default, int timeout = 7200, int batchSize = 10000, Action<Exception> error = null) {
return false;
}
protected override BulkCopyRowsCopied ProviderSpecificCopy <T>(
[JetBrains.Annotations.NotNull] DataConnection dataConnection,
BulkCopyOptions options,
IEnumerable <T> source)
{
if (dataConnection == null)
{
throw new ArgumentNullException("dataConnection");
}
var sqlBuilder = dataConnection.DataProvider.CreateSqlBuilder();
var descriptor = dataConnection.MappingSchema.GetEntityDescriptor(typeof(T));
var tableName = GetTableName(sqlBuilder, descriptor);
if (dataConnection.Transaction == null)
{
if (_bulkCopyCreator == null)
{
var clientNamespace = OracleTools.AssemblyName + ".Client.";
var bulkCopyType = _connectionType.Assembly.GetType(clientNamespace + "OracleBulkCopy", false);
var bulkCopyOptionType = _connectionType.Assembly.GetType(clientNamespace + "OracleBulkCopyOptions", false);
var columnMappingType = _connectionType.Assembly.GetType(clientNamespace + "OracleBulkCopyColumnMapping", false);
if (bulkCopyType != null)
{
_bulkCopyCreator = CreateBulkCopyCreator(_connectionType, bulkCopyType, bulkCopyOptionType);
_columnMappingCreator = CreateColumnMappingCreator(columnMappingType);
}
}
if (_bulkCopyCreator != null)
{
var columns = descriptor.Columns.Where(c => !c.SkipOnInsert).ToList();
var rd = new BulkCopyReader(_dataProvider, columns, source);
var rc = new BulkCopyRowsCopied();
var bcOptions = 0; // Default
if (options.UseInternalTransaction == true)
{
bcOptions |= 1; // UseInternalTransaction = 1,
}
using (var bc = _bulkCopyCreator(dataConnection.Connection, bcOptions))
{
dynamic dbc = bc;
var notifyAfter = options.NotifyAfter == 0 && options.MaxBatchSize.HasValue ?
options.MaxBatchSize.Value : options.NotifyAfter;
if (notifyAfter != 0 && options.RowsCopiedCallback != null)
{
if (_bulkCopySubscriber == null)
{
_bulkCopySubscriber = CreateBulkCopySubscriber(bc, "OracleRowsCopied");
}
dbc.NotifyAfter = notifyAfter;
_bulkCopySubscriber(bc, arg =>
{
dynamic darg = arg;
rc.RowsCopied = darg.RowsCopied;
options.RowsCopiedCallback(rc);
if (rc.Abort)
{
darg.Abort = true;
}
});
}
if (options.MaxBatchSize.HasValue)
{
dbc.BatchSize = options.MaxBatchSize.Value;
}
if (options.BulkCopyTimeout.HasValue)
{
dbc.BulkCopyTimeout = options.BulkCopyTimeout.Value;
}
dbc.DestinationTableName = tableName;
for (var i = 0; i < columns.Count; i++)
{
dbc.ColumnMappings.Add((dynamic)_columnMappingCreator(i, columns[i].ColumnName));
}
dbc.WriteToServer(rd);
}
rc.RowsCopied = rd.Count;
return(rc);
}
}
return(MultipleRowsCopy(dataConnection, options, source));
}
protected override BulkCopyRowsCopied ProviderSpecificCopy <T>(
ITable <T> table,
BulkCopyOptions options,
IEnumerable <T> source)
{
if (table.DataContext is DataConnection dataConnection && _provider.Adapter.BulkCopy != null)
{
var connection = _provider.TryGetProviderConnection(dataConnection.Connection, dataConnection.MappingSchema);
// for run in transaction see
// https://stackoverflow.com/questions/57675379
// provider will call sp_oledb_columns which creates temp table
var transaction = dataConnection.Transaction;
if (connection != null && transaction != null)
{
transaction = _provider.TryGetProviderTransaction(transaction, dataConnection.MappingSchema);
}
if (connection != null && (dataConnection.Transaction == null || transaction != null))
{
var ed = dataConnection.MappingSchema.GetEntityDescriptor(typeof(T));
var columns = ed.Columns.Where(c => !c.SkipOnInsert || options.KeepIdentity == true && c.IsIdentity).ToList();
var sb = _provider.CreateSqlBuilder(dataConnection.MappingSchema);
var rd = new BulkCopyReader(dataConnection, columns, source);
var sqlopt = SybaseProviderAdapter.AseBulkCopyOptions.Default;
var rc = new BulkCopyRowsCopied();
if (options.CheckConstraints == true)
{
sqlopt |= SybaseProviderAdapter.AseBulkCopyOptions.CheckConstraints;
}
if (options.KeepIdentity == true)
{
sqlopt |= SybaseProviderAdapter.AseBulkCopyOptions.KeepIdentity;
}
if (options.TableLock == true)
{
sqlopt |= SybaseProviderAdapter.AseBulkCopyOptions.TableLock;
}
if (options.KeepNulls == true)
{
sqlopt |= SybaseProviderAdapter.AseBulkCopyOptions.KeepNulls;
}
if (options.FireTriggers == true)
{
sqlopt |= SybaseProviderAdapter.AseBulkCopyOptions.FireTriggers;
}
if (options.UseInternalTransaction == true)
{
sqlopt |= SybaseProviderAdapter.AseBulkCopyOptions.UseInternalTransaction;
}
using (var bc = _provider.Adapter.BulkCopy.Create(connection, sqlopt, transaction))
{
if (options.NotifyAfter != 0 && options.RowsCopiedCallback != null)
{
bc.NotifyAfter = options.NotifyAfter;
bc.AseRowsCopied += (sender, args) =>
{
rc.RowsCopied = args.RowCopied;
options.RowsCopiedCallback(rc);
if (rc.Abort)
{
args.Abort = true;
}
};
}
if (options.MaxBatchSize.HasValue)
{
bc.BatchSize = options.MaxBatchSize.Value;
}
if (options.BulkCopyTimeout.HasValue)
{
bc.BulkCopyTimeout = options.BulkCopyTimeout.Value;
}
var tableName = GetTableName(sb, options, table);
// do not convert table and column names to valid sql name, as sybase bulk copy implementation
// doesn't understand escaped names (facepalm)
// which means it will probably fail when escaping required anyways...
bc.DestinationTableName = GetDestinationTableName(sb, options, table);
for (var i = 0; i < columns.Count; i++)
{
bc.ColumnMappings.Add(_provider.Adapter.BulkCopy.CreateColumnMapping(columns[i].ColumnName, columns[i].ColumnName));
}
TraceAction(
dataConnection,
() => "INSERT BULK " + tableName + "(" + string.Join(", ", columns.Select(x => x.ColumnName)) + Environment.NewLine,
() => { bc.WriteToServer(rd); return(rd.Count); });
}
if (rc.RowsCopied != rd.Count)
{
rc.RowsCopied = rd.Count;
if (options.NotifyAfter != 0 && options.RowsCopiedCallback != null)
{
options.RowsCopiedCallback(rc);
}
}
return(rc);
}
}
return(MultipleRowsCopy(table, options, source));
}
private async Task <BulkCopyRowsCopied> ProviderSpecificCopyInternalAsync <T>(
ProviderConnections providerConnections,
ITable <T> table,
BulkCopyOptions options,
IAsyncEnumerable <T> source,
CancellationToken cancellationToken)
where T : notnull
{
var dataConnection = providerConnections.DataConnection;
var connection = providerConnections.ProviderConnection;
var transaction = providerConnections.ProviderTransaction;
var ed = table.DataContext.MappingSchema.GetEntityDescriptor(typeof(T));
var columns = ed.Columns.Where(c => !c.SkipOnInsert || options.KeepIdentity == true && c.IsIdentity).ToList();
var sb = _provider.CreateSqlBuilder(table.DataContext.MappingSchema);
var rc = new BulkCopyRowsCopied();
var bc = _provider.Adapter.BulkCopy !.Create(connection, transaction);
if (options.NotifyAfter != 0 && options.RowsCopiedCallback != null)
{
bc.NotifyAfter = options.NotifyAfter;
bc.MySqlRowsCopied += (sender, args) =>
{
rc.RowsCopied += args.RowsCopied;
options.RowsCopiedCallback(rc);
if (rc.Abort)
{
args.Abort = true;
}
};
}
if (options.BulkCopyTimeout.HasValue)
{
bc.BulkCopyTimeout = options.BulkCopyTimeout.Value;
}
var tableName = GetTableName(sb, options, table);
bc.DestinationTableName = GetTableName(sb, options, table);
for (var i = 0; i < columns.Count; i++)
{
bc.AddColumnMapping(_provider.Adapter.BulkCopy.CreateColumnMapping(i, columns[i].ColumnName));
}
// emulate missing BatchSize property
// this is needed, because MySql fails on big batches, so users should be able to limit batch size
var batches = EnumerableHelper.Batch(source, options.MaxBatchSize ?? int.MaxValue);
await foreach (var batch in batches.WithCancellation(cancellationToken).ConfigureAwait(Configuration.ContinueOnCapturedContext))
{
var rd = new BulkCopyReader <T>(dataConnection, columns, batch, cancellationToken);
await TraceActionAsync(
dataConnection,
() => (bc.HasWriteToServerAsync ? "INSERT ASYNC BULK " : "INSERT BULK ") + tableName + "(" + string.Join(", ", columns.Select(x => x.ColumnName)) + Environment.NewLine,
async() => {
if (bc.HasWriteToServerAsync)
{
await bc.WriteToServerAsync(rd, cancellationToken).ConfigureAwait(Configuration.ContinueOnCapturedContext);
}
else
{
bc.WriteToServer(rd);
}
return(rd.Count);
}).ConfigureAwait(Configuration.ContinueOnCapturedContext);
rc.RowsCopied += rd.Count;
}
if (options.NotifyAfter != 0 && options.RowsCopiedCallback != null)
{
options.RowsCopiedCallback(rc);
}
return(rc);
}
protected virtual BulkCopyRowsCopied ProviderSpecificCopy <T>(DataConnection dataConnection, BulkCopyOptions options, IEnumerable <T> source)
{
return(MultipleRowsCopy(dataConnection, options, source));
}
protected override Task <BulkCopyRowsCopied> MultipleRowsCopyAsync <T>(ITable <T> table, BulkCopyOptions options, IAsyncEnumerable <T> source, CancellationToken cancellationToken)
{
return(MultipleRowsCopy1Async(table, options, source, cancellationToken));
}
protected BulkCopyRowsCopied MultipleRowsCopy3 <T>(DataConnection dataConnection, BulkCopyOptions options, IEnumerable <T> source, string from)
{
var helper = new MultipleRowsHelper <T>(dataConnection, options, false);
helper.StringBuilder
.AppendFormat("INSERT INTO {0}", helper.TableName).AppendLine()
.Append("(");
foreach (var column in helper.Columns)
{
helper.StringBuilder
.AppendLine()
.Append("\t")
.Append(helper.SqlBuilder.Convert(column.ColumnName, ConvertType.NameToQueryField))
.Append(",");
}
helper.StringBuilder.Length--;
helper.StringBuilder
.AppendLine()
.AppendLine(")")
.AppendLine("SELECT * FROM")
.Append("(");
helper.SetHeader();
foreach (var item in source)
{
helper.StringBuilder
.AppendLine()
.Append("\tSELECT ");
helper.BuildColumns(item);
helper.StringBuilder.Append(from);
helper.StringBuilder.Append(" UNION ALL");
helper.RowsCopied.RowsCopied++;
helper.CurrentCount++;
if (helper.CurrentCount >= helper.BatchSize || helper.Parameters.Count > 10000 || helper.StringBuilder.Length > 100000)
{
helper.StringBuilder.Length -= " UNION ALL".Length;
helper.StringBuilder
.AppendLine()
.Append(")");
if (!helper.Execute())
{
return(helper.RowsCopied);
}
}
}
if (helper.CurrentCount > 0)
{
helper.StringBuilder.Length -= " UNION ALL".Length;
helper.StringBuilder
.AppendLine()
.Append(")");
helper.Execute();
}
return(helper.RowsCopied);
}
protected override Task <BulkCopyRowsCopied> ProviderSpecificCopyAsync <T>(
ITable <T> table, BulkCopyOptions options, IEnumerable <T> source, CancellationToken cancellationToken)
{
// call the synchronous provider-specific implementation
return(Task.FromResult(ProviderSpecificCopy(table, options, source)));
}
protected override BulkCopyRowsCopied ProviderSpecificCopy <T>(
[JetBrains.Annotations.NotNull] DataConnection dataConnection,
BulkCopyOptions options,
IEnumerable <T> source)
{
if (dataConnection == null)
{
throw new ArgumentNullException("dataConnection");
}
var connection = dataConnection.Connection;
if (connection == null)
{
return(MultipleRowsCopy(dataConnection, options, source));
}
if (!(connection.GetType() == _connectionType || connection.GetType().IsSubclassOfEx(_connectionType)))
{
return(MultipleRowsCopy(dataConnection, options, source));
}
var transaction = dataConnection.Transaction;
var ed = dataConnection.MappingSchema.GetEntityDescriptor(typeof(T));
var columns = ed.Columns.Where(c => !c.SkipOnInsert || options.KeepIdentity == true && c.IsIdentity).ToList();
var rc = new BulkCopyRowsCopied();
if (_bulkCopyCreator == null)
{
var clientNamespace = _dataProvider.ConnectionNamespace;
var bulkCopyType = _connectionType.AssemblyEx().GetType(clientNamespace + ".HanaBulkCopy", false);
var bulkCopyOptionType = _connectionType.AssemblyEx().GetType(clientNamespace + ".HanaBulkCopyOptions", false);
var columnMappingType = _connectionType.AssemblyEx().GetType(clientNamespace + ".HanaBulkCopyColumnMapping", false);
var transactionType = _connectionType.AssemblyEx().GetType(clientNamespace + ".HanaTransaction", false);
if (bulkCopyType != null)
{
_bulkCopyCreator = SapHanaCreateBulkCopyCreator(_connectionType, bulkCopyType, bulkCopyOptionType, transactionType);
_columnMappingCreator = CreateColumnMappingCreator(columnMappingType);
}
}
if (_bulkCopyCreator == null)
{
return(MultipleRowsCopy(dataConnection, options, source));
}
const int hanaOptions = 0; //default;
using (var bc = _bulkCopyCreator(connection, hanaOptions, transaction))
{
dynamic dbc = bc;
if (options.NotifyAfter != 0 && options.RowsCopiedCallback != null)
{
if (_bulkCopySubscriber == null)
{
_bulkCopySubscriber = CreateBulkCopySubscriber(bc, "HannaRowsCopied");
}
dbc.NotifyAfter = options.NotifyAfter;
_bulkCopySubscriber(bc, arg =>
{
dynamic darg = arg;
rc.RowsCopied = darg.RowsCopied;
options.RowsCopiedCallback(rc);
if (rc.Abort)
{
darg.Abort = true;
}
});
}
if (options.MaxBatchSize.HasValue)
{
dbc.BatchSize = options.MaxBatchSize.Value;
}
if (options.BulkCopyTimeout.HasValue)
{
dbc.BulkCopyTimeout = options.BulkCopyTimeout.Value;
}
var sqlBuilder = _dataProvider.CreateSqlBuilder();
var descriptor = dataConnection.MappingSchema.GetEntityDescriptor(typeof(T));
var tableName = GetTableName(sqlBuilder, options, descriptor);
dbc.DestinationTableName = tableName;
for (var i = 0; i < columns.Count; i++)
{
dbc.ColumnMappings.Add((dynamic)_columnMappingCreator(i, columns[i].ColumnName));
}
var rd = new BulkCopyReader(_dataProvider, columns, source);
TraceAction(
dataConnection,
"INSERT BULK " + tableName + Environment.NewLine,
() => { dbc.WriteToServer(rd); return(rd.Count); });
if (rc.RowsCopied != rd.Count)
{
rc.RowsCopied = rd.Count;
if (options.NotifyAfter != 0 && options.RowsCopiedCallback != null)
{
options.RowsCopiedCallback(rc);
}
}
return(rc);
}
}
protected override BulkCopyRowsCopied ProviderSpecificCopy <T>(
ITable <T> table,
BulkCopyOptions options,
IEnumerable <T> source)
{
// database name is not a part of table FQN in oracle
var serverName = options.ServerName ?? table.ServerName;
if (table.DataContext is DataConnection dataConnection && _provider.Adapter.BulkCopy != null &&
serverName == null)
{
var connection = _provider.TryGetProviderConnection(dataConnection.Connection, dataConnection.MappingSchema);
if (connection != null)
{
var ed = dataConnection.MappingSchema.GetEntityDescriptor(typeof(T));
var columns = ed.Columns.Where(c => !c.SkipOnInsert || options.KeepIdentity == true && c.IsIdentity).ToList();
var sb = _provider.CreateSqlBuilder(dataConnection.MappingSchema);
// ODP.NET doesn't bulk copy doesn't work if columns that require escaping:
// - if escaping applied, pre-flight validation fails as it performs uppercase comparison and quotes make it fail with
// InvalidOperationException: Column mapping is invalid
// - if escaping not applied - if fails as expected on server, because it treats passed name as uppercased name
// and gives "ORA-00904: "STRINGVALUE": invalid identifier" error
// That's quite common error in bulk copy implementation error by providers...
var supported = true;
foreach (var column in columns)
{
if (column.ColumnName != sb.ConvertInline(column.ColumnName, ConvertType.NameToQueryField))
{
// fallback to sql-based copy
// TODO: we should add support for by-ordinal column mapping to workaround it
supported = false;
break;
}
}
if (supported)
{
var rd = new BulkCopyReader <T>(dataConnection, columns, source);
var sqlopt = OracleProviderAdapter.OracleBulkCopyOptions.Default;
var rc = new BulkCopyRowsCopied();
var tableName = sb.ConvertInline(options.TableName ?? table.TableName, ConvertType.NameToQueryTable);
var schemaName = options.SchemaName ?? table.SchemaName;
if (schemaName != null)
{
schemaName = sb.ConvertInline(schemaName, ConvertType.NameToSchema);
}
if (options.UseInternalTransaction == true)
{
sqlopt |= OracleProviderAdapter.OracleBulkCopyOptions.UseInternalTransaction;
}
using (var bc = _provider.Adapter.BulkCopy.Create(connection, sqlopt))
{
var notifyAfter = options.NotifyAfter == 0 && options.MaxBatchSize.HasValue
? options.MaxBatchSize.Value
: options.NotifyAfter;
if (notifyAfter != 0 && options.RowsCopiedCallback != null)
{
bc.NotifyAfter = options.NotifyAfter;
bc.OracleRowsCopied += (sender, args) =>
{
rc.RowsCopied = args.RowsCopied;
options.RowsCopiedCallback(rc);
if (rc.Abort)
{
args.Abort = true;
}
};
}
if (options.MaxBatchSize.HasValue)
{
bc.BatchSize = options.MaxBatchSize.Value;
}
if (options.BulkCopyTimeout.HasValue)
{
bc.BulkCopyTimeout = options.BulkCopyTimeout.Value;
}
else if (Configuration.Data.BulkCopyUseConnectionCommandTimeout)
{
bc.BulkCopyTimeout = connection.ConnectionTimeout;
}
bc.DestinationTableName = tableName;
bc.DestinationSchemaName = schemaName;
for (var i = 0; i < columns.Count; i++)
{
bc.ColumnMappings.Add(_provider.Adapter.BulkCopy.CreateColumnMapping(i, columns[i].ColumnName));
}
TraceAction(
dataConnection,
() => "INSERT BULK " + (schemaName == null ? tableName : schemaName + "." + tableName) + "(" + string.Join(", ", columns.Select(x => x.ColumnName)) + ")" + Environment.NewLine,
() => { bc.WriteToServer(rd); return(rd.Count); });
}
if (rc.RowsCopied != rd.Count)
{
rc.RowsCopied = rd.Count;
if (options.NotifyAfter != 0 && options.RowsCopiedCallback != null)
{
options.RowsCopiedCallback(rc);
}
}
return(rc);
}
}
}
return(MultipleRowsCopy(table, options, source));
}
public virtual BulkCopyRowsCopied BulkCopy <T>(BulkCopyType bulkCopyType, ITable <T> table, BulkCopyOptions options, IEnumerable <T> source)
{
return(bulkCopyType switch
{
BulkCopyType.MultipleRows => MultipleRowsCopy(table, options, source),
BulkCopyType.RowByRow => RowByRowCopy(table, options, source),
_ => ProviderSpecificCopy(table, options, source),
});
public virtual BulkCopyRowsCopied BulkCopy <T>(ITable <T> table, BulkCopyOptions options, IEnumerable <T> source)
{
return(new BasicBulkCopy().BulkCopy(options.BulkCopyType, table, options, source));
}
protected override BulkCopyRowsCopied ProviderSpecificCopy <T>(ITable <T> table, BulkCopyOptions options, IEnumerable <T> source)
{
if (table.DataContext is DataConnection dataConnection)
{
var connection = _provider.TryGetProviderConnection(dataConnection.Connection, dataConnection.MappingSchema);
if (connection == null)
{
return(MultipleRowsCopy(table, options, source));
}
var sqlBuilder = (BasicSqlBuilder)_provider.CreateSqlBuilder(dataConnection.MappingSchema);
var ed = dataConnection.MappingSchema.GetEntityDescriptor(typeof(T));
var tableName = GetTableName(sqlBuilder, options, table);
var columns = ed.Columns.Where(c => !c.SkipOnInsert || options.KeepIdentity == true && c.IsIdentity).ToArray();
var fields = string.Join(", ", columns.Select(column => sqlBuilder.ConvertInline(column.ColumnName, ConvertType.NameToQueryField)));
var copyCommand = $"COPY {tableName} ({fields}) FROM STDIN (FORMAT BINARY)";
var rowsCopied = new BulkCopyRowsCopied();
// batch size numbers not based on any strong grounds as I didn't found any recommendations for it
var batchSize = Math.Max(10, options.MaxBatchSize ?? 10000);
var currentCount = 0;
var key = new { Type = typeof(T), options.KeepIdentity, ed };
var rowWriter = (Action <MappingSchema, NpgsqlProviderAdapter.NpgsqlBinaryImporter, ColumnDescriptor[], T>)_rowWriterCache.GetOrAdd(
key,
_ => _provider.Adapter.CreateBinaryImportRowWriter <T>(_provider, sqlBuilder, columns, dataConnection.MappingSchema));
var useComplete = _provider.Adapter.BinaryImporterHasComplete;
var writer = _provider.Adapter.BeginBinaryImport(connection, copyCommand);
try
{
foreach (var item in source)
{
rowWriter(dataConnection.MappingSchema, writer, columns, item);
currentCount++;
rowsCopied.RowsCopied++;
if (options.NotifyAfter != 0 && options.RowsCopiedCallback != null && rowsCopied.RowsCopied % options.NotifyAfter == 0)
{
options.RowsCopiedCallback(rowsCopied);
if (rowsCopied.Abort)
{
if (!useComplete)
{
writer.Cancel();
}
break;
}
}
if (currentCount >= batchSize)
{
if (useComplete)
{
writer.Complete();
}
writer.Dispose();
writer = _provider.Adapter.BeginBinaryImport(connection, copyCommand);
currentCount = 0;
}
}
if (!rowsCopied.Abort)
{
TraceAction(
dataConnection,
() => "INSERT BULK " + tableName + "(" + string.Join(", ", columns.Select(x => x.ColumnName)) + Environment.NewLine,
() => {
if (useComplete)
{
writer.Complete();
}
return((int)rowsCopied.RowsCopied);
});
}
if (options.NotifyAfter != 0 && options.RowsCopiedCallback != null)
{
options.RowsCopiedCallback(rowsCopied);
}
}
catch when(!useComplete)
{
writer.Cancel();
throw;
}
finally
{
writer.Dispose();
}
return(rowsCopied);
}
return(MultipleRowsCopy(table, options, source));
}
public virtual Task <BulkCopyRowsCopied> BulkCopyAsync <T>(
ITable <T> table, BulkCopyOptions options, IAsyncEnumerable <T> source, CancellationToken cancellationToken)
{
return(new BasicBulkCopy().BulkCopyAsync(options.BulkCopyType, table, options, source, cancellationToken));
}
protected override BulkCopyRowsCopied MultipleRowsCopy <T>(DataConnection dataConnection, BulkCopyOptions options, IEnumerable <T> source)
{
if ((options.MaxBatchSize ?? int.MaxValue) > MAX_ALLOWABLE_BATCH_SIZE)
{
options.MaxBatchSize = MAX_ALLOWABLE_BATCH_SIZE;
}
return(MultipleRowsCopy2(dataConnection, options, false, source, " FROM " + DB2iSeriesTools.iSeriesDummyTableName()));
}
public override BulkCopyRowsCopied BulkCopy <T>(DataConnection dataConnection, BulkCopyOptions options, IEnumerable <T> source)
{
if (_bulkCopy == null)
{
_bulkCopy = new DB2BulkCopy(GetConnectionType());
}
return(_bulkCopy.BulkCopy(
options.BulkCopyType == BulkCopyType.Default ? DB2Tools.DefaultBulkCopyType : options.BulkCopyType,
dataConnection,
options,
source));
}
/// <summary>
/// Oracle大数据复制
/// </summary>
/// <param name="dt">数据源 dt.TableName一定要和数据库表名对应</param>
/// <param name="dbkey">数据库</param>
/// <param name="options">选项 默认Default</param>
/// <param name="isTran">是否使用事务 默认false</param>
/// <param name="timeout">超时时间7200 2小时</param>
/// <param name="batchSize">每一批次中的行数</param>
/// <param name="error">错误处理</param>
/// <returns>true/false</returns>
public bool DataBulkCopy(DataTable dt, string dbkey = "", BulkCopyOptions options = BulkCopyOptions.Default, bool isTran = false, int timeout = 7200, int batchSize = 10000, Action<Exception> error = null) {
if (Data.Pool(dbkey).DBType != "OleDb") return false;
using (OleDbConnection connection = new OleDbConnection(Data.Pool(dbkey).ConnString)) {
connection.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter("select * from " + dt.TableName + " where 1=0", connection);
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
int rowcount = dt.Rows.Count;
for (int n = 0; n < rowcount; n++) {
dt.Rows[n].SetAdded();
}
adapter.UpdateBatchSize = batchSize;
try {
adapter.Update(dt);
} catch(Exception ex) {
if (error.IsNotNull()) error(ex);
return false;
}
}
return true;
}
static void RunScript(string configString, string divider, string name, Action <IDbConnection> action = null, string database = null)
{
Console.WriteLine("=== " + name + " === \n");
var scriptFolder = Path.Combine(Path.GetFullPath("."), "Database", "Create Scripts");
Console.WriteLine("Script folder exists: {1}; {0}", scriptFolder, Directory.Exists(scriptFolder));
var sqlFileName = Path.GetFullPath(Path.Combine(scriptFolder, Path.ChangeExtension(name, "sql")));
Console.WriteLine("Sql file exists: {1}; {0}", sqlFileName, File.Exists(sqlFileName));
var text = File.ReadAllText(sqlFileName);
while (true)
{
var idx = text.IndexOf("SKIP " + configString + " BEGIN");
if (idx >= 0)
{
text = text.Substring(0, idx) + text.Substring(text.IndexOf("SKIP " + configString + " END", idx));
}
else
{
break;
}
}
var cmds = text
.Replace("{DBNAME}", database)
.Replace("\r", "")
.Replace(divider, "\x1")
.Split('\x1')
.Select(c => c.Trim())
.Where(c => !string.IsNullOrEmpty(c))
.ToArray();
if (DataConnection.TraceSwitch.TraceInfo)
{
Console.WriteLine("Commands count: {0}", cmds.Length);
}
Exception exception = null;
using (var db = new TestDataConnection(configString))
{
//db.CommandTimeout = 20;
foreach (var command in cmds)
{
try
{
if (DataConnection.TraceSwitch.TraceInfo)
{
Console.WriteLine(command);
}
if (configString == ProviderName.OracleNative)
{
// we need this to avoid errors in trigger creation when native provider
// recognize ":NEW" as parameter
var cmd = db.CreateCommand();
cmd.CommandText = command;
((dynamic)cmd).BindByName = false;
cmd.ExecuteNonQuery();
}
else
{
db.Execute(command);
}
if (DataConnection.TraceSwitch.TraceInfo)
{
Console.WriteLine("\nOK\n");
}
}
catch (Exception ex)
{
if (DataConnection.TraceSwitch.TraceError)
{
if (!DataConnection.TraceSwitch.TraceInfo)
{
Console.WriteLine(command);
}
var isDrop =
command.TrimStart().StartsWith("DROP") ||
command.TrimStart().StartsWith("CALL DROP");
#if APPVEYOR
if (!isDrop)
#endif
Console.WriteLine(ex.Message);
if (isDrop)
{
#if !APPVEYOR
Console.WriteLine("\nnot too OK\n");
#endif
}
else
{
Console.WriteLine("\nFAILED\n");
if (exception == null)
{
exception = ex;
}
}
}
}
}
if (exception != null)
{
throw exception;
}
if (DataConnection.TraceSwitch.TraceInfo)
{
Console.WriteLine("\nBulkCopy LinqDataTypes\n");
}
var options = new BulkCopyOptions();
db.BulkCopy(
options,
new []
{
new LinqDataTypes2 {
ID = 1, MoneyValue = 1.11m, DateTimeValue = new DateTime(2001, 1, 11, 1, 11, 21, 100), BoolValue = true, GuidValue = new Guid("ef129165-6ffe-4df9-bb6b-bb16e413c883"), SmallIntValue = 1, StringValue = null, BigIntValue = 1
},
new LinqDataTypes2 {
ID = 2, MoneyValue = 2.49m, DateTimeValue = new DateTime(2005, 5, 15, 5, 15, 25, 500), BoolValue = false, GuidValue = new Guid("bc663a61-7b40-4681-ac38-f9aaf55b706b"), SmallIntValue = 2, StringValue = "", BigIntValue = 2
},
new LinqDataTypes2 {
ID = 3, MoneyValue = 3.99m, DateTimeValue = new DateTime(2009, 9, 19, 9, 19, 29, 90), BoolValue = true, GuidValue = new Guid("d2f970c0-35ac-4987-9cd5-5badb1757436"), SmallIntValue = 3, StringValue = "1"
},
new LinqDataTypes2 {
ID = 4, MoneyValue = 4.50m, DateTimeValue = new DateTime(2009, 9, 20, 9, 19, 29, 90), BoolValue = false, GuidValue = new Guid("40932fdb-1543-4e4a-ac2c-ca371604fb4b"), SmallIntValue = 4, StringValue = "2"
},
new LinqDataTypes2 {
ID = 5, MoneyValue = 5.50m, DateTimeValue = new DateTime(2009, 9, 20, 9, 19, 29, 90), BoolValue = true, GuidValue = new Guid("febe3eca-cb5f-40b2-ad39-2979d312afca"), SmallIntValue = 5, StringValue = "3"
},
new LinqDataTypes2 {
ID = 6, MoneyValue = 6.55m, DateTimeValue = new DateTime(2009, 9, 22, 9, 19, 29, 90), BoolValue = false, GuidValue = new Guid("8d3c5d1d-47db-4730-9fe7-968f6228a4c0"), SmallIntValue = 6, StringValue = "4"
},
new LinqDataTypes2 {
ID = 7, MoneyValue = 7.00m, DateTimeValue = new DateTime(2009, 9, 23, 9, 19, 29, 90), BoolValue = true, GuidValue = new Guid("48094115-83af-46dd-a906-bff26ee21ee2"), SmallIntValue = 7, StringValue = "5"
},
new LinqDataTypes2 {
ID = 8, MoneyValue = 8.99m, DateTimeValue = new DateTime(2009, 9, 24, 9, 19, 29, 90), BoolValue = false, GuidValue = new Guid("c1139f1f-1335-4cd4-937e-92602f732dd3"), SmallIntValue = 8, StringValue = "6"
},
new LinqDataTypes2 {
ID = 9, MoneyValue = 9.63m, DateTimeValue = new DateTime(2009, 9, 25, 9, 19, 29, 90), BoolValue = true, GuidValue = new Guid("46c5c512-3d4b-4cf7-b4e7-1de080789e5d"), SmallIntValue = 9, StringValue = "7"
},
new LinqDataTypes2 {
ID = 10, MoneyValue = 10.77m, DateTimeValue = new DateTime(2009, 9, 26, 9, 19, 29, 90), BoolValue = false, GuidValue = new Guid("61b2bc55-147f-4b40-93ed-a4aa83602fee"), SmallIntValue = 10, StringValue = "8"
},
new LinqDataTypes2 {
ID = 11, MoneyValue = 11.45m, DateTimeValue = new DateTime(2009, 9, 27, 0, 0, 0, 0), BoolValue = true, GuidValue = new Guid("d3021d18-97f0-4dc0-98d0-f0c7df4a1230"), SmallIntValue = 11, StringValue = "9"
},
new LinqDataTypes2 {
ID = 12, MoneyValue = 11.45m, DateTimeValue = new DateTime(2012, 11, 7, 19, 19, 29, 90), BoolValue = true, GuidValue = new Guid("03021d18-97f0-4dc0-98d0-f0c7df4a1230"), SmallIntValue = 12, StringValue = "0"
}
});
if (DataConnection.TraceSwitch.TraceInfo)
{
Console.WriteLine("\nBulkCopy Parent\n");
}
db.BulkCopy(
options,
new []
{
new Parent {
ParentID = 1, Value1 = 1
},
new Parent {
ParentID = 2, Value1 = null
},
new Parent {
ParentID = 3, Value1 = 3
},
new Parent {
ParentID = 4, Value1 = null
},
new Parent {
ParentID = 5, Value1 = 5
},
new Parent {
ParentID = 6, Value1 = 6
},
new Parent {
ParentID = 7, Value1 = 1
}
});
if (DataConnection.TraceSwitch.TraceInfo)
{
Console.WriteLine("\nBulkCopy Child\n");
}
db.BulkCopy(
options,
new []
{
new Child {
ParentID = 1, ChildID = 11
},
new Child {
ParentID = 2, ChildID = 21
},
new Child {
ParentID = 2, ChildID = 22
},
new Child {
ParentID = 3, ChildID = 31
},
new Child {
ParentID = 3, ChildID = 32
},
new Child {
ParentID = 3, ChildID = 33
},
new Child {
ParentID = 4, ChildID = 41
},
new Child {
ParentID = 4, ChildID = 42
},
new Child {
ParentID = 4, ChildID = 43
},
new Child {
ParentID = 4, ChildID = 44
},
new Child {
ParentID = 6, ChildID = 61
},
new Child {
ParentID = 6, ChildID = 62
},
new Child {
ParentID = 6, ChildID = 63
},
new Child {
ParentID = 6, ChildID = 64
},
new Child {
ParentID = 6, ChildID = 65
},
new Child {
ParentID = 6, ChildID = 66
},
new Child {
ParentID = 7, ChildID = 77
}
});
if (DataConnection.TraceSwitch.TraceInfo)
{
Console.WriteLine("\nBulkCopy GrandChild\n");
}
db.BulkCopy(
options,
new []
{
new GrandChild {
ParentID = 1, ChildID = 11, GrandChildID = 111
},
new GrandChild {
ParentID = 2, ChildID = 21, GrandChildID = 211
},
new GrandChild {
ParentID = 2, ChildID = 21, GrandChildID = 212
},
new GrandChild {
ParentID = 2, ChildID = 22, GrandChildID = 221
},
new GrandChild {
ParentID = 2, ChildID = 22, GrandChildID = 222
},
new GrandChild {
ParentID = 3, ChildID = 31, GrandChildID = 311
},
new GrandChild {
ParentID = 3, ChildID = 31, GrandChildID = 312
},
new GrandChild {
ParentID = 3, ChildID = 31, GrandChildID = 313
},
new GrandChild {
ParentID = 3, ChildID = 32, GrandChildID = 321
},
new GrandChild {
ParentID = 3, ChildID = 32, GrandChildID = 322
},
new GrandChild {
ParentID = 3, ChildID = 32, GrandChildID = 323
},
new GrandChild {
ParentID = 3, ChildID = 33, GrandChildID = 331
},
new GrandChild {
ParentID = 3, ChildID = 33, GrandChildID = 332
},
new GrandChild {
ParentID = 3, ChildID = 33, GrandChildID = 333
},
new GrandChild {
ParentID = 4, ChildID = 41, GrandChildID = 411
},
new GrandChild {
ParentID = 4, ChildID = 41, GrandChildID = 412
},
new GrandChild {
ParentID = 4, ChildID = 41, GrandChildID = 413
},
new GrandChild {
ParentID = 4, ChildID = 41, GrandChildID = 414
},
new GrandChild {
ParentID = 4, ChildID = 42, GrandChildID = 421
},
new GrandChild {
ParentID = 4, ChildID = 42, GrandChildID = 422
},
new GrandChild {
ParentID = 4, ChildID = 42, GrandChildID = 423
},
new GrandChild {
ParentID = 4, ChildID = 42, GrandChildID = 424
}
});
db.BulkCopy(
options,
new[]
{
new InheritanceParent2 {
InheritanceParentId = 1, TypeDiscriminator = null, Name = null
},
new InheritanceParent2 {
InheritanceParentId = 2, TypeDiscriminator = 1, Name = null
},
new InheritanceParent2 {
InheritanceParentId = 3, TypeDiscriminator = 2, Name = "InheritanceParent2"
}
});
db.BulkCopy(
options,
new[]
{
new InheritanceChild2()
{
InheritanceChildId = 1, TypeDiscriminator = null, InheritanceParentId = 1, Name = null
},
new InheritanceChild2()
{
InheritanceChildId = 2, TypeDiscriminator = 1, InheritanceParentId = 2, Name = null
},
new InheritanceChild2()
{
InheritanceChildId = 3, TypeDiscriminator = 2, InheritanceParentId = 3, Name = "InheritanceParent2"
}
});
action?.Invoke(db.Connection);
}
}
/// <summary>
/// SqlServer大数据复制
/// </summary>
/// <param name="dr">数据源</param>
/// <param name="tableName">对应的表名</param>
/// <param name="dbkey">数据库</param>
/// <param name="options">选项 默认Default</param>
/// <param name="isTran">是否使用事务 默认false</param>
/// <param name="timeout">超时时间7200 2小时</param>
/// <param name="batchSize">每一批次中的行数</param>
/// <param name="error">错误处理</param>
/// <returns>true/false</returns>
public bool DataBulkCopy(IDataReader dr, string tableName, string dbkey = "", BulkCopyOptions options = BulkCopyOptions.Default, bool isTran = false, int timeout = 7200, int batchSize = 10000, Action<Exception> error = null) {
return false;
}
private BulkCopyRowsCopied ProviderSpecificCopyImpl <T>(DataConnection dataConnection, ITable <T> table, BulkCopyOptions options, IEnumerable <T> source)
where T : notnull
{
var connection = _provider.TryGetProviderConnection(dataConnection.Connection, table.DataContext.MappingSchema);
if (connection == null)
{
return(MultipleRowsCopy(table, options, source));
}
var sqlBuilder = (BasicSqlBuilder)_provider.CreateSqlBuilder(table.DataContext.MappingSchema);
var ed = table.DataContext.MappingSchema.GetEntityDescriptor(typeof(T));
var tableName = GetTableName(sqlBuilder, options, table);
var columns = ed.Columns.Where(c => !c.SkipOnInsert || options.KeepIdentity == true && c.IsIdentity).ToArray();
var npgsqlTypes = new NpgsqlProviderAdapter.NpgsqlDbType[columns.Length];
var columnTypes = new DbDataType[columns.Length];
for (var i = 0; i < columns.Length; i++)
{
columnTypes[i] = columns[i].GetDbDataType(true);
var npgsqlType = _provider.GetNativeType(columns[i].DbType, true);
if (npgsqlType == null)
{
var sb = new System.Text.StringBuilder();
sqlBuilder.BuildTypeName(sb, new SqlQuery.SqlDataType(columnTypes[i]));
npgsqlType = _provider.GetNativeType(sb.ToString(), true);
}
if (npgsqlType == null)
{
throw new LinqToDBException($"Cannot guess PostgreSQL type for column {columns[i].ColumnName}. Specify type explicitly in column mapping.");
}
npgsqlTypes[i] = npgsqlType.Value;
}
var fields = string.Join(", ", columns.Select(column => sqlBuilder.ConvertInline(column.ColumnName, ConvertType.NameToQueryField)));
var copyCommand = $"COPY {tableName} ({fields}) FROM STDIN (FORMAT BINARY)";
// batch size numbers not based on any strong grounds as I didn't found any recommendations for it
var batchSize = Math.Max(10, options.MaxBatchSize ?? 10000);
var writer = _provider.Adapter.BeginBinaryImport(connection, copyCommand);
return(ProviderSpecificCopySyncImpl(dataConnection, options, source, connection, tableName, columns, columnTypes, npgsqlTypes, copyCommand, batchSize, writer));
}
