protected override BulkCopyRowsCopied MultipleRowsCopy <T>(
ITable <T> table, BulkCopyOptions options, IEnumerable <T> source)
{
BulkCopyRowsCopied ret;
var helper = new MultipleRowsHelper <T>(table, options);
if (options.KeepIdentity == true)
{
helper.DataConnection.Execute("SET IDENTITY_INSERT " + helper.TableName + " ON");
}
switch (((SqlServerDataProvider)helper.DataConnection.DataProvider).Version)
{
case SqlServerVersion.v2000:
case SqlServerVersion.v2005: ret = MultipleRowsCopy2(helper, source, ""); break;
default: ret = MultipleRowsCopy1(helper, source); break;
}
if (options.KeepIdentity == true)
{
helper.DataConnection.Execute("SET IDENTITY_INSERT " + helper.TableName + " OFF");
}
return(ret);
}
static BulkCopyRowsCopied OracleMultipleRowsCopy3(MultipleRowsHelper helper, IEnumerable source)
{
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(")")
;
helper.SetHeader();
foreach (var item in source)
{
helper.StringBuilder
.AppendLine()
.Append("\tSELECT ");
helper.BuildColumns(item, _ => _.DataType == DataType.Text || _.DataType == DataType.NText);
helper.StringBuilder.Append(" FROM DUAL ");
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()
;
if (!helper.Execute())
{
return(helper.RowsCopied);
}
}
}
if (helper.CurrentCount > 0)
{
helper.StringBuilder.Length -= " UNION ALL".Length;
helper.StringBuilder
.AppendLine()
;
helper.Execute();
}
return(helper.RowsCopied);
}
BulkCopyRowsCopied MultipleRowsCopy2 <T>(
DataConnection dataConnection, BulkCopyOptions options, IEnumerable <T> source)
{
var helper = new MultipleRowsHelper <T>(dataConnection, options, options.KeepIdentity ?? false);
helper.StringBuilder.AppendFormat("INSERT INTO {0} (", helper.TableName);
foreach (var column in helper.Columns)
{
helper.StringBuilder
.Append(helper.SqlBuilder.Convert(column.ColumnName, ConvertType.NameToQueryField))
.Append(", ");
}
helper.StringBuilder.Length -= 2;
helper.StringBuilder.Append(") VALUES (");
for (var i = 0; i < helper.Columns.Length; i++)
{
helper.StringBuilder.Append(":p" + (i + 1)).Append(", ");
}
helper.StringBuilder.Length -= 2;
helper.StringBuilder.AppendLine(")");
helper.SetHeader();
var list = new List <T>(31);
foreach (var item in source)
{
list.Add(item);
helper.RowsCopied.RowsCopied++;
helper.CurrentCount++;
if (helper.CurrentCount >= helper.BatchSize)
{
if (!Execute(dataConnection, helper, list))
{
return(helper.RowsCopied);
}
list.Clear();
}
}
if (helper.CurrentCount > 0)
{
Execute(dataConnection, helper, list);
}
return(helper.RowsCopied);
}
static BulkCopyRowsCopied OracleMultipleRowsCopy2(MultipleRowsHelper helper, IEnumerable source)
{
helper.StringBuilder.AppendFormat("INSERT INTO {0} (", helper.TableName);
foreach (var column in helper.Columns)
{
helper.StringBuilder
.Append(helper.SqlBuilder.Convert(column.ColumnName, ConvertType.NameToQueryField))
.Append(", ");
}
helper.StringBuilder.Length -= 2;
helper.StringBuilder.Append(") VALUES (");
for (var i = 0; i < helper.Columns.Length; i++)
{
helper.StringBuilder.Append(":p" + (i + 1)).Append(", ");
}
helper.StringBuilder.Length -= 2;
helper.StringBuilder.AppendLine(")");
helper.SetHeader();
var list = new List <object>(31);
foreach (var item in source)
{
list.Add(item);
helper.RowsCopied.RowsCopied++;
helper.CurrentCount++;
if (helper.CurrentCount >= helper.BatchSize)
{
if (!Execute(helper, list))
{
return(helper.RowsCopied);
}
list.Clear();
}
}
if (helper.CurrentCount > 0)
{
Execute(helper, list);
}
return(helper.RowsCopied);
}
protected override BulkCopyRowsCopied MultipleRowsCopy <T>(
DataConnection dataConnection, BulkCopyOptions options, IEnumerable <T> source)
{
var helper = new MultipleRowsHelper <T>(dataConnection, options, false);
helper.StringBuilder.AppendLine("INSERT ALL");
helper.SetHeader();
foreach (var item in source)
{
helper.StringBuilder.AppendFormat("\tINTO {0} (", helper.TableName);
foreach (var column in helper.Columns)
{
helper.StringBuilder
.Append(helper.SqlBuilder.Convert(column.ColumnName, ConvertType.NameToQueryField))
.Append(", ");
}
helper.StringBuilder.Length -= 2;
helper.StringBuilder.Append(") VALUES (");
helper.BuildColumns(item);
helper.StringBuilder.AppendLine(")");
helper.RowsCopied.RowsCopied++;
helper.CurrentCount++;
if (helper.CurrentCount >= helper.BatchSize || helper.Parameters.Count > 10000 ||
helper.StringBuilder.Length > 100000)
{
helper.StringBuilder.AppendLine("SELECT * FROM dual");
if (!helper.Execute())
{
return(helper.RowsCopied);
}
}
}
if (helper.CurrentCount > 0)
{
helper.StringBuilder.AppendLine("SELECT * FROM dual");
helper.Execute();
}
return(helper.RowsCopied);
}
static bool Execute(MultipleRowsHelper helper, List <object> list)
{
for (var i = 0; i < helper.Columns.Length; i++)
{
var column = helper.Columns[i];
var dataType = column.DataType == DataType.Undefined
? helper.DataConnection.MappingSchema.GetDataType(column.MemberType).Type.DataType
: column.DataType;
helper.Parameters.Add(new DataParameter(":p" + (i + 1), list.Select(o => column.GetValue(helper.DataConnection.MappingSchema, o)).ToArray(), dataType, column.DbType)
{
Direction = ParameterDirection.Input,
IsArray = true,
});
}
return(helper.Execute());
}
bool Execute <T>(DataConnection dataConnection, MultipleRowsHelper <T> helper, List <T> list)
{
for (var i = 0; i < helper.Columns.Length; i++)
{
var column = helper.Columns[i];
var dataType = column.DataType == DataType.Undefined
? dataConnection.MappingSchema.GetDataType(column.MemberType).DataType
: column.DataType;
//var type = dataConnection.DataProvider.ConvertParameterType(column.MemberType, dataType);
helper.Parameters.Add(new DataParameter(":p" + (i + 1), list.Select(o => column.GetValue(o)).ToArray(), dataType)
{
Direction = ParameterDirection.Input,
IsArray = true,
});
}
return(helper.Execute());
}
protected override BulkCopyRowsCopied MultipleRowsCopy <T>(
ITable <T> table, BulkCopyOptions options, IEnumerable <T> source)
{
var helper = new MultipleRowsHelper <T>(table, options);
if (options.KeepIdentity == true)
{
helper.DataConnection.Execute("SET IDENTITY_INSERT " + helper.TableName + " ON");
}
var ret = MultipleRowsCopy2(helper, source, "");
if (options.KeepIdentity == true)
{
helper.DataConnection.Execute("SET IDENTITY_INSERT " + helper.TableName + " OFF");
}
return(ret);
}
protected override async Task <BulkCopyRowsCopied> MultipleRowsCopyAsync <T>(
ITable <T> table, BulkCopyOptions options, IAsyncEnumerable <T> source, CancellationToken cancellationToken)
{
var helper = new MultipleRowsHelper <T>(table, options);
if (options.KeepIdentity == true)
{
await helper.DataConnection.ExecuteAsync("SET IDENTITY_INSERT " + helper.TableName + " ON", cancellationToken)
.ConfigureAwait(Common.Configuration.ContinueOnCapturedContext);
}
var ret = await MultipleRowsCopy2Async(helper, source, "", cancellationToken)
.ConfigureAwait(Common.Configuration.ContinueOnCapturedContext);
if (options.KeepIdentity == true)
{
await helper.DataConnection.ExecuteAsync("SET IDENTITY_INSERT " + helper.TableName + " OFF", cancellationToken)
.ConfigureAwait(Common.Configuration.ContinueOnCapturedContext);
}
return(ret);
}
protected override async Task <BulkCopyRowsCopied> MultipleRowsCopyAsync <T>(
ITable <T> table, BulkCopyOptions options, IAsyncEnumerable <T> source, CancellationToken cancellationToken)
{
BulkCopyRowsCopied ret;
var helper = new MultipleRowsHelper <T>(table, options);
if (options.KeepIdentity == true)
{
await helper.DataConnection.ExecuteAsync("SET IDENTITY_INSERT " + helper.TableName + " ON", cancellationToken)
.ConfigureAwait(Common.Configuration.ContinueOnCapturedContext);
}
switch (((SqlServerDataProvider)helper.DataConnection.DataProvider).Version)
{
case SqlServerVersion.v2000:
case SqlServerVersion.v2005:
ret = await MultipleRowsCopy2Async(helper, source, "", cancellationToken)
.ConfigureAwait(Common.Configuration.ContinueOnCapturedContext);
break;
default:
ret = await MultipleRowsCopy1Async(helper, source, cancellationToken)
.ConfigureAwait(Common.Configuration.ContinueOnCapturedContext);
break;
}
if (options.KeepIdentity == true)
{
await helper.DataConnection.ExecuteAsync("SET IDENTITY_INSERT " + helper.TableName + " OFF", cancellationToken)
.ConfigureAwait(Common.Configuration.ContinueOnCapturedContext);
}
return(ret);
}
