/// <summary>
///大批量数据插入
/// </summary>
/// <param name="table">数据表</param>
/// <param name="connectionString">数据库连接字符串</param>
/// <returns></returns>
public bool OracleBulkInsert(DataTable table, string connectionString)
{
try
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
connection.Open();
OracleTransaction trans = connection.BeginTransaction();
using (OracleBulkCopy bulkCopy = new OracleBulkCopy(connection))
{
//设置源表名称
bulkCopy.DestinationTableName = table.TableName;
//设置超时限制
bulkCopy.BulkCopyTimeout = CommandTimeOut;
//要写入列
foreach (DataColumn dtColumn in table.Columns)
{
bulkCopy.ColumnMappings.Add(dtColumn.ColumnName.ToUpper(), dtColumn.ColumnName.ToUpper());
}
try
{
// 写入
bulkCopy.WriteToServer(table);
// 提交事务
trans.Commit();
return(true);
}
catch
{
trans.Rollback();
bulkCopy.Close();
return(false);
}
finally
{
connection.Close();
connection.Dispose();
bulkCopy.Close();
bulkCopy.Dispose();
}
}
}
}
catch (Exception e)
{
DbLog.WriteException(e);
return(false);
}
}
/// <summary>
///大批量数据插入
/// </summary>
/// <param name="table">数据表</param>
/// <param name="connectionString">数据库连接字符串</param>
/// <returns></returns>
public bool SqlServerBulkInsert(DataTable table, string connectionString)
{
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
SqlBulkCopy sqlbulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, trans);
// 设置源表名称
sqlbulkCopy.DestinationTableName = table.TableName;
//分几次拷贝
//sqlbulkCopy.BatchSize = 10;
// 设置超时限制
sqlbulkCopy.BulkCopyTimeout = CommandTimeOut;
foreach (DataColumn dtColumn in table.Columns)
{
sqlbulkCopy.ColumnMappings.Add(dtColumn.ColumnName, dtColumn.ColumnName);
}
try
{
// 写入
sqlbulkCopy.WriteToServer(table);
// 提交事务
trans.Commit();
return(true);
}
catch
{
trans.Rollback();
sqlbulkCopy.Close();
return(false);
}
finally
{
conn.Close();
conn.Dispose();
sqlbulkCopy.Close();
}
}
}
catch (Exception e)
{
DbLog.WriteException(e);
return(false);
}
}
/// <summary>
///大批量数据插入
/// </summary>
/// <param name="table">数据表</param>
/// <param name="connectionString">数据库连接字符串</param>
/// <returns></returns>
public bool MySqlBulkInsert(DataTable table, string connectionString)
{
try
{
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
MySqlTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
{
FieldTerminator = ",",
FieldQuotationCharacter = '"',
EscapeCharacter = '"',
LineTerminator = "\r\n",
NumberOfLinesToSkip = 0,
TableName = table.TableName,
};
bulk.Timeout = CommandTimeOut;
bulk.Columns.AddRange(table.Columns.Cast <DataColumn>().Select(colum => colum.ColumnName).ToList());
tran.Commit();
return(true);
}
catch
{
tran.Rollback();
return(false);
}
finally
{
conn.Close();
conn.Dispose();
}
}
}
catch (Exception e)
{
DbLog.WriteException(e);
return(false);
}
}