async public static Task ExecutePgCopyAsync <T>(this IInsert <T> that, CancellationToken cancellationToken = default) where T : class
{
var insert = that as FreeSql.PostgreSQL.Curd.PostgreSQLInsert <T>;
if (insert == null)
{
throw new Exception("ExecutePgCopyAsync 是 FreeSql.Provider.PostgreSQL 特有的功能");
}
var dt = that.ToDataTable();
if (dt.Rows.Count == 0)
{
return;
}
Func <NpgsqlConnection, Task> binaryImportAsync = async conn =>
{
var copyFromCommand = new StringBuilder().Append("COPY ").Append(insert.InternalCommonUtils.QuoteSqlName(dt.TableName)).Append("(");
var colIndex = 0;
foreach (DataColumn col in dt.Columns)
{
if (colIndex++ > 0)
{
copyFromCommand.Append(", ");
}
copyFromCommand.Append(insert.InternalCommonUtils.QuoteSqlName(col.ColumnName));
}
copyFromCommand.Append(") FROM STDIN BINARY");
using (var writer = conn.BeginBinaryImport(copyFromCommand.ToString()))
{
foreach (DataRow item in dt.Rows)
{
await writer.WriteRowAsync(cancellationToken, item.ItemArray);
}
writer.Complete();
}
copyFromCommand.Clear();
};
try
{
if (insert.InternalConnection == null && insert.InternalTransaction == null)
{
using (var conn = await insert.InternalOrm.Ado.MasterPool.GetAsync())
{
await binaryImportAsync(conn.Value as NpgsqlConnection);
}
}
else if (insert.InternalTransaction != null)
{
await binaryImportAsync(insert.InternalTransaction.Connection as NpgsqlConnection);
}
else if (insert.InternalConnection != null)
{
var conn = insert.InternalConnection as NpgsqlConnection;
var isNotOpen = false;
if (conn.State != System.Data.ConnectionState.Open)
{
isNotOpen = true;
await conn.OpenAsync(cancellationToken);
}
try
{
await binaryImportAsync(conn);
}
finally
{
if (isNotOpen)
{
await conn.CloseAsync();
}
}
}
else
{
throw new NotImplementedException("ExecutePgCopyAsync 未实现错误，请反馈给作者");
}
}
finally
{
dt.Clear();
}
}
/// <summary>
/// PostgreSQL COPY 批量导入功能，封装了 NpgsqlConnection.BeginBinaryImport 方法<para></para>
/// 使用 IgnoreColumns/InsertColumns 设置忽略/指定导入的列<para></para>
/// 使用 WithConnection/WithTransaction 传入连接/事务对象<para></para>
/// 提示：若本方法不能满足，请使用 IInsert<T>.ToDataTable 方法得到 DataTable 对象后，自行处理。<para></para>
/// COPY 与 insert into t values(..),(..),(..) 性能测试参考：<para></para>
/// 插入180000行，52列：10,090ms 与 46,756ms，10列：4,081ms 与 9,786ms<para></para>
/// 插入10000行，52列：583ms 与 3,294ms，10列：167ms 与 568ms<para></para>
/// 插入5000行，52列：337ms 与 2,269ms，10列：93ms 与 366ms<para></para>
/// 插入2000行，52列：136ms 与 1,019ms，10列：39ms 与 157ms<para></para>
/// 插入1000行，52列：88ms 与 374ms，10列：21ms 与 102ms<para></para>
/// 插入500行，52列：61ms 与 209ms，10列：12ms 与 34ms<para></para>
/// 插入100行，52列：30ms 与 51ms，10列：4ms 与 9ms<para></para>
/// 插入50行，52列：25ms 与 37ms，10列：2ms 与 6ms<para></para>
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="that"></param>
public static void ExecutePgCopy <T>(this IInsert <T> that) where T : class
{
var insert = that as FreeSql.PostgreSQL.Curd.PostgreSQLInsert <T>;
if (insert == null)
{
throw new Exception(CoreStrings.S_Features_Unique("ExecutePgCopy", "PostgreSQL"));
}
var dt = that.ToDataTable();
if (dt.Rows.Count == 0)
{
return;
}
Action <NpgsqlConnection> binaryImport = conn =>
{
var copyFromCommand = new StringBuilder().Append("COPY ").Append(insert.InternalCommonUtils.QuoteSqlName(dt.TableName)).Append("(");
var colIndex = 0;
foreach (DataColumn col in dt.Columns)
{
if (colIndex++ > 0)
{
copyFromCommand.Append(", ");
}
copyFromCommand.Append(insert.InternalCommonUtils.QuoteSqlName(col.ColumnName));
}
copyFromCommand.Append(") FROM STDIN BINARY");
using (var writer = conn.BeginBinaryImport(copyFromCommand.ToString()))
{
foreach (DataRow item in dt.Rows)
{
writer.WriteRow(item.ItemArray);
}
writer.Complete();
}
copyFromCommand.Clear();
};
try
{
if (insert.InternalConnection == null && insert.InternalTransaction == null)
{
using (var conn = insert.InternalOrm.Ado.MasterPool.Get())
{
binaryImport(conn.Value as NpgsqlConnection);
}
}
else if (insert.InternalTransaction != null)
{
binaryImport(insert.InternalTransaction.Connection as NpgsqlConnection);
}
else if (insert.InternalConnection != null)
{
var conn = insert.InternalConnection as NpgsqlConnection;
var isNotOpen = false;
if (conn.State != System.Data.ConnectionState.Open)
{
isNotOpen = true;
conn.Open();
}
try
{
binaryImport(conn);
}
finally
{
if (isNotOpen)
{
conn.Close();
}
}
}
else
{
throw new NotImplementedException($"ExecutePgCopy {CoreStrings.S_Not_Implemented_FeedBack}");
}
}
finally
{
dt.Clear();
}
}
/// <summary>
/// Oracle CopyBulk 批量插入功能<para></para>
/// 使用 IgnoreColumns/InsertColumns 设置忽略/指定导入的列<para></para>
/// 使用 WithConnection/WithTransaction 传入连接/事务对象<para></para>
/// 提示：若本方法不能满足，请使用 IInsert<T>.ToDataTable 方法得到 DataTable 对象后，自行处理。
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="that"></param>
/// <param name="copyOptions"></param>
/// <param name="batchSize"></param>
/// <param name="bulkCopyTimeout"></param>
public static void ExecuteOracleBulkCopy <T>(this IInsert <T> that, OracleBulkCopyOptions copyOptions = OracleBulkCopyOptions.Default, int?batchSize = null, int?bulkCopyTimeout = null) where T : class
{
var insert = that as FreeSql.Oracle.Curd.OracleInsert <T>;
if (insert == null)
{
throw new Exception("ExecuteOracleBulkCopy 是 FreeSql.Provider.Oracle 特有的功能");
}
var dt = that.ToDataTable();
if (dt.Rows.Count == 0)
{
return;
}
Action <OracleBulkCopy> writeToServer = bulkCopy =>
{
if (batchSize.HasValue)
{
bulkCopy.BatchSize = batchSize.Value;
}
if (bulkCopyTimeout.HasValue)
{
bulkCopy.BulkCopyTimeout = bulkCopyTimeout.Value;
}
bulkCopy.DestinationTableName = dt.TableName;
for (int i = 0; i < dt.Columns.Count; i++)
{
bulkCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
}
bulkCopy.WriteToServer(dt);
};
try
{
if (insert.InternalConnection == null && insert.InternalTransaction == null)
{
if (insert._orm.Ado?.TransactionCurrentThread != null)
{
using (var bulkCopy = new OracleBulkCopy(insert._orm.Ado.TransactionCurrentThread.Connection as OracleConnection, copyOptions))
writeToServer(bulkCopy);
}
else
{
using (var conn = insert.InternalOrm.Ado.MasterPool.Get())
{
using (var bulkCopy = copyOptions == OracleBulkCopyOptions.Default ?
new OracleBulkCopy(conn.Value as OracleConnection) :
new OracleBulkCopy(conn.Value as OracleConnection, copyOptions))
{
writeToServer(bulkCopy);
}
}
}
}
else if (insert.InternalTransaction != null)
{
using (var bulkCopy = copyOptions == OracleBulkCopyOptions.Default ?
new OracleBulkCopy(insert.InternalTransaction.Connection as OracleConnection) :
new OracleBulkCopy(insert.InternalTransaction.Connection as OracleConnection, copyOptions))
{
writeToServer(bulkCopy);
}
}
else if (insert.InternalConnection != null)
{
var conn = insert.InternalConnection as OracleConnection;
var isNotOpen = false;
if (conn.State != System.Data.ConnectionState.Open)
{
isNotOpen = true;
conn.Open();
}
try
{
using (var bulkCopy = copyOptions == OracleBulkCopyOptions.Default ?
new OracleBulkCopy(conn) :
new OracleBulkCopy(conn, copyOptions))
{
writeToServer(bulkCopy);
}
}
finally
{
if (isNotOpen)
{
conn.Close();
}
}
}
else
{
throw new NotImplementedException("ExecuteOracleBulkCopy 未实现错误，请反馈给作者");
}
}
finally
{
dt.Clear();
}
}
/// <summary>
/// MySql MySqlCopyBulk 批量插入功能<para></para>
/// 使用 IgnoreColumns/InsertColumns 设置忽略/指定导入的列<para></para>
/// 使用 WithConnection/WithTransaction 传入连接/事务对象<para></para>
/// 提示：若本方法不能满足，请使用 IInsert<T>.ToDataTable 方法得到 DataTable 对象后，自行处理。<para></para>
/// MySqlCopyBulk 与 insert into t values(..),(..),(..) 性能测试参考：<para></para>
/// 插入180000行，52列：28,405ms 与 38,481ms，10列：6,504ms 与 11,171ms<para></para>
/// 插入10000行，52列：1,142ms 与 2,234ms，10列：339ms 与 866ms<para></para>
/// 插入5000行，52列：657ms 与 1,136ms，10列：257ms 与 366ms<para></para>
/// 插入2000行，52列：451ms 与 284ms，10列：116ms 与 80ms<para></para>
/// 插入1000行，52列：435ms 与 239ms，10列：87ms 与 83ms<para></para>
/// 插入500行，52列：592ms 与 167ms，10列：100ms 与 50ms<para></para>
/// 插入100行，52列：47ms 与 66ms，10列：16ms 与 24ms<para></para>
/// 插入50行，52列：22ms 与 30ms，10列：16ms 与 34ms<para></para>
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="that"></param>
/// <param name="bulkCopyTimeout"></param>
public static void ExecuteMySqlBulkCopy <T>(this IInsert <T> that, int?bulkCopyTimeout = null) where T : class
{
var insert = that as FreeSql.MySql.Curd.MySqlInsert <T>;
if (insert == null)
{
throw new Exception("ExecuteMySqlBulkCopy 是 FreeSql.Provider.MySqlConnector 特有的功能");
}
var dt = that.ToDataTable();
if (dt.Rows.Count == 0)
{
return;
}
Action <MySqlBulkCopy> writeToServer = bulkCopy =>
{
if (bulkCopyTimeout.HasValue)
{
bulkCopy.BulkCopyTimeout = bulkCopyTimeout.Value;
}
bulkCopy.DestinationTableName = dt.TableName;
bulkCopy.WriteToServer(dt);
};
try
{
if (insert.InternalConnection == null && insert.InternalTransaction == null)
{
using (var conn = insert.InternalOrm.Ado.MasterPool.Get())
{
writeToServer(new MySqlBulkCopy(conn.Value as MySqlConnection));
}
}
else if (insert.InternalTransaction != null)
{
writeToServer(new MySqlBulkCopy(insert.InternalTransaction.Connection as MySqlConnection));
}
else if (insert.InternalConnection != null)
{
var conn = insert.InternalConnection as MySqlConnection;
var isNotOpen = false;
if (conn.State != System.Data.ConnectionState.Open)
{
isNotOpen = true;
conn.Open();
}
try
{
writeToServer(new MySqlBulkCopy(conn));
}
finally
{
if (isNotOpen)
{
conn.Close();
}
}
}
else
{
throw new NotImplementedException("ExecuteMySqlBulkCopy 未实现错误，请反馈给作者");
}
}
finally
{
dt.Clear();
}
}
async public static Task ExecuteMySqlBulkCopyAsync <T>(this IInsert <T> that, int?bulkCopyTimeout = null, CancellationToken cancellationToken = default) where T : class
{
var insert = that as FreeSql.MySql.Curd.MySqlInsert <T>;
if (insert == null)
{
throw new Exception("ExecuteMySqlBulkCopyAsync 是 FreeSql.Provider.MySqlConnector 特有的功能");
}
var dt = that.ToDataTable();
if (dt.Rows.Count == 0)
{
return;
}
Func <MySqlBulkCopy, Task> writeToServer = bulkCopy =>
{
if (bulkCopyTimeout.HasValue)
{
bulkCopy.BulkCopyTimeout = bulkCopyTimeout.Value;
}
bulkCopy.DestinationTableName = dt.TableName;
return(bulkCopy.WriteToServerAsync(dt, cancellationToken));
};
try
{
if (insert.InternalConnection == null && insert.InternalTransaction == null)
{
using (var conn = insert.InternalOrm.Ado.MasterPool.Get())
{
await writeToServer(new MySqlBulkCopy(conn.Value as MySqlConnection));
}
}
else if (insert.InternalTransaction != null)
{
await writeToServer(new MySqlBulkCopy(insert.InternalTransaction.Connection as MySqlConnection, insert.InternalTransaction as MySqlTransaction));
}
else if (insert.InternalConnection != null)
{
var conn = insert.InternalConnection as MySqlConnection;
var isNotOpen = false;
if (conn.State != System.Data.ConnectionState.Open)
{
isNotOpen = true;
await conn.OpenAsync(cancellationToken);
}
try
{
await writeToServer(new MySqlBulkCopy(conn));
}
finally
{
if (isNotOpen)
{
await conn.CloseAsync();
}
}
}
else
{
throw new NotImplementedException("ExecuteMySqlBulkCopyAsync 未实现错误，请反馈给作者");
}
}
finally
{
dt.Clear();
}
}
/// <summary>
/// 达梦 CopyBulk 批量插入功能<para></para>
/// 使用 IgnoreColumns/InsertColumns 设置忽略/指定导入的列<para></para>
/// 使用 WithConnection/WithTransaction 传入连接/事务对象<para></para>
/// 提示：若本方法不能满足，请使用 IInsert<T>.ToDataTable 方法得到 DataTable 对象后，自行处理。
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="that"></param>
/// <param name="copyOptions"></param>
/// <param name="batchSize"></param>
/// <param name="bulkCopyTimeout"></param>
public static void ExecuteDmBulkCopy <T>(this IInsert <T> that, DmBulkCopyOptions copyOptions = DmBulkCopyOptions.Default, int?batchSize = null, int?bulkCopyTimeout = null) where T : class
{
var insert = that as FreeSql.Dameng.Curd.DamengInsert <T>;
if (insert == null)
{
throw new Exception(CoreStrings.S_Features_Unique("ExecuteDmBulkCopy", "Dameng"));
}
var dt = that.ToDataTable();
if (dt.Rows.Count == 0)
{
return;
}
Action <DmBulkCopy> writeToServer = bulkCopy =>
{
if (batchSize.HasValue)
{
bulkCopy.BatchSize = batchSize.Value;
}
if (bulkCopyTimeout.HasValue)
{
bulkCopy.BulkCopyTimeout = bulkCopyTimeout.Value;
}
bulkCopy.DestinationTableName = dt.TableName;
for (int i = 0; i < dt.Columns.Count; i++)
{
bulkCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
}
bulkCopy.WriteToServer(dt);
};
try
{
if (insert.InternalConnection == null && insert.InternalTransaction == null)
{
if (insert._orm.Ado?.TransactionCurrentThread != null)
{
using (var bulkCopy = new DmBulkCopy(insert._orm.Ado.TransactionCurrentThread.Connection as DmConnection, copyOptions, insert._orm.Ado.TransactionCurrentThread as DmTransaction))
writeToServer(bulkCopy);
}
else
{
using (var conn = insert.InternalOrm.Ado.MasterPool.Get())
{
using (var bulkCopy = copyOptions == DmBulkCopyOptions.Default ?
new DmBulkCopy(conn.Value as DmConnection) :
new DmBulkCopy(conn.Value as DmConnection, copyOptions, insert.InternalTransaction as DmTransaction))
{
writeToServer(bulkCopy);
}
}
}
}
else if (insert.InternalTransaction != null)
{
using (var bulkCopy = new DmBulkCopy(insert.InternalTransaction.Connection as DmConnection, copyOptions, insert.InternalTransaction as DmTransaction))
{
writeToServer(bulkCopy);
}
}
else if (insert.InternalConnection != null)
{
var conn = insert.InternalConnection as DmConnection;
var isNotOpen = false;
if (conn.State != System.Data.ConnectionState.Open)
{
isNotOpen = true;
conn.Open();
}
try
{
using (var bulkCopy = copyOptions == DmBulkCopyOptions.Default ?
new DmBulkCopy(conn) :
new DmBulkCopy(conn, copyOptions, null))
{
writeToServer(bulkCopy);
}
}
finally
{
if (isNotOpen)
{
conn.Close();
}
}
}
else
{
throw new NotImplementedException($"ExecuteDmBulkCopy {CoreStrings.S_Not_Implemented_FeedBack}");
}
}
finally
{
dt.Clear();
}
}
async public static Task ExecuteMySqlBulkCopyAsync <T>(this IInsert <T> that, int?bulkCopyTimeout = null, CancellationToken cancellationToken = default) where T : class
{
var insert = that as FreeSql.MySql.Curd.MySqlInsert <T>;
if (insert == null)
{
throw new Exception(CoreStrings.S_Features_Unique("ExecuteMySqlBulkCopyAsync", "MySqlConnector"));
}
var dt = that.ToDataTable();
if (dt.Rows.Count == 0)
{
return;
}
Func <MySqlBulkCopy, Task> writeToServer = async bulkCopy =>
{
if (bulkCopyTimeout.HasValue)
{
bulkCopy.BulkCopyTimeout = bulkCopyTimeout.Value;
}
bulkCopy.DestinationTableName = dt.TableName;
for (int i = 0; i < dt.Columns.Count; i++)
{
bulkCopy.ColumnMappings.Add(new MySqlBulkCopyColumnMapping(i, dt.Columns[i].ColumnName));
}
await bulkCopy.WriteToServerAsync(dt, cancellationToken);
};
try
{
if (insert.InternalConnection == null && insert.InternalTransaction == null)
{
if (insert._orm.Ado?.TransactionCurrentThread != null)
{
await writeToServer(new MySqlBulkCopy(insert._orm.Ado.TransactionCurrentThread.Connection as MySqlConnection, insert._orm.Ado?.TransactionCurrentThread as MySqlTransaction));
}
else
{
using (var conn = insert.InternalOrm.Ado.MasterPool.Get())
await writeToServer(new MySqlBulkCopy(conn.Value as MySqlConnection));
}
}
else if (insert.InternalTransaction != null)
{
await writeToServer(new MySqlBulkCopy(insert.InternalTransaction.Connection as MySqlConnection, insert.InternalTransaction as MySqlTransaction));
}
else if (insert.InternalConnection != null)
{
var conn = insert.InternalConnection as MySqlConnection;
var isNotOpen = false;
if (conn.State != System.Data.ConnectionState.Open)
{
isNotOpen = true;
await conn.OpenAsync(cancellationToken);
}
try
{
await writeToServer(new MySqlBulkCopy(conn));
}
finally
{
if (isNotOpen)
{
await conn.CloseAsync();
}
}
}
else
{
throw new NotImplementedException($"ExecuteMySqlBulkCopyAsync {CoreStrings.S_Not_Implemented_FeedBack}");
}
}
finally
{
dt.Clear();
}
}
