使用事务和SqlBulkCopy导入大批量数据
个人测试所用。调用请修改。
///
<summary>
/// 使用事务和SqlBulkCopy批量导入数据 示例
/// By Conan304 2009年8月29日21:51:02
/// </summary>
/// <param name="phone"></param>
/// <param name="smsInfo"></param>
private static void SqlBulkCopyData( string [] phone, string smsInfo)
{
if (phone == null ) return ;
// 生成DataTable
DataTable dataTable = new DataTable();
DataColumn column = new DataColumn();
// qy_id
column.DataType = System.Type.GetType( " System.Int32 " );
column.ColumnName = " QY_ID " ;
dataTable.Columns.Add(column);
// Info_ID
column = new DataColumn();
column.DataType = System.Type.GetType( " System.String " );
column.MaxLength = 32 ;
column.ColumnName = " Info_ID " ;
dataTable.Columns.Add(column);
// SP_Port
column = new DataColumn();
column.DataType = System.Type.GetType( " System.String " );
column.MaxLength = 24 ;
column.ColumnName = " SP_Port " ;
dataTable.Columns.Add(column);
// Phone
column = new DataColumn();
column.DataType = System.Type.GetType( " System.String " );
column.MaxLength = 11 ;
column.ColumnName = " Phone " ;
dataTable.Columns.Add(column);
// Content
column = new DataColumn();
column.DataType = System.Type.GetType( " System.String " );
column.MaxLength = 300 ;
column.ColumnName = " Content " ;
dataTable.Columns.Add(column);
// SendTime
column = new DataColumn();
column.DataType = System.Type.GetType( " System.DateTime " );
column.ColumnName = " SendTime " ;
column.DefaultValue = DateTime.Now;
dataTable.Columns.Add(column);
// SendLevel
column = new DataColumn();
column.DataType = System.Type.GetType( " System.Int32 " );
column.ColumnName = " SendLevel " ;
column.DefaultValue = 4 ;
dataTable.Columns.Add(column);
// IsLong
column = new DataColumn();
column.DataType = System.Type.GetType( " System.Int32 " );
column.ColumnName = " IsLong " ;
dataTable.Columns.Add(column);
// AdminID
column = new DataColumn();
column.DataType = System.Type.GetType( " System.Int32 " );
column.ColumnName = " AdminID " ;
column.DefaultValue = 0 ;
dataTable.Columns.Add(column);
string [] result = new string [phone.Length];
for ( int i = 0 ; i < phone.Length; i ++ )
{
// 生成GUID
string Guid = System.Guid.NewGuid().ToString( " N " ).ToUpper();
string phoneNew = string .Empty;
int phoneType = GetMobileType(phone[i], out phoneNew);
if (phoneType == 0 )
{
result[i] = phoneNew + " -0-0 " ;
}
else
{
DataRow dataRow = dataTable.NewRow();
dataRow[ " qy_id " ] = 16 ;
dataRow[ " Info_ID " ] = Guid;
dataRow[ " SP_Port " ] = " 10657027014211 " ;
dataRow[ " Phone " ] = phoneNew;
dataRow[ " Content " ] = smsInfo;
dataRow[ " IsLong " ] = 0 ;
dataTable.Rows.Add(dataRow);
result[i] = phoneNew + " -1- " + Guid;
}
}
// BCP copy
SqlConnection conn = new SqlConnection();
conn.ConnectionString = " server=.;uid=dmkj_hpc;pwd=#$wlh*&1110h%c;database=DMKJ_SMS " ;
conn.Open();
SqlTransaction sqlbulkTransaction = conn.BeginTransaction();
// 请在插入数据的同时检查约束,如果发生错误调用sqlbulkTransaction事务
SqlBulkCopy copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, sqlbulkTransaction);
copy.DestinationTableName = " T_SMS_SendInfo " ;
foreach (DataColumn dc in dataTable.Columns)
{
copy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
}
try
{
copy.WriteToServer(dataTable);
sqlbulkTransaction.Commit();
}
catch (Exception ex)
{
sqlbulkTransaction.Rollback();
Console.WriteLine(ex.ToString());
}
finally
{
copy.Close();
conn.Close();
}
// for (int x = 0; x < dataTable.Rows.Count; x++)
// {
// for (int i = 0; i < dataTable.Columns.Count; i++)
// {
// Console.WriteLine("Column Name:{0},and value is:{1}",dataTable.Columns[i].ColumnName.ToString(),dataTable.Rows[x][i].ToString());
// }
// Console.WriteLine();
// }
// foreach (string str in result)
// {
// Console.WriteLine(str.ToString());
// }
}
/// 使用事务和SqlBulkCopy批量导入数据 示例
/// By Conan304 2009年8月29日21:51:02
/// </summary>
/// <param name="phone"></param>
/// <param name="smsInfo"></param>
private static void SqlBulkCopyData( string [] phone, string smsInfo)
{
if (phone == null ) return ;
// 生成DataTable
DataTable dataTable = new DataTable();
DataColumn column = new DataColumn();
// qy_id
column.DataType = System.Type.GetType( " System.Int32 " );
column.ColumnName = " QY_ID " ;
dataTable.Columns.Add(column);
// Info_ID
column = new DataColumn();
column.DataType = System.Type.GetType( " System.String " );
column.MaxLength = 32 ;
column.ColumnName = " Info_ID " ;
dataTable.Columns.Add(column);
// SP_Port
column = new DataColumn();
column.DataType = System.Type.GetType( " System.String " );
column.MaxLength = 24 ;
column.ColumnName = " SP_Port " ;
dataTable.Columns.Add(column);
// Phone
column = new DataColumn();
column.DataType = System.Type.GetType( " System.String " );
column.MaxLength = 11 ;
column.ColumnName = " Phone " ;
dataTable.Columns.Add(column);
// Content
column = new DataColumn();
column.DataType = System.Type.GetType( " System.String " );
column.MaxLength = 300 ;
column.ColumnName = " Content " ;
dataTable.Columns.Add(column);
// SendTime
column = new DataColumn();
column.DataType = System.Type.GetType( " System.DateTime " );
column.ColumnName = " SendTime " ;
column.DefaultValue = DateTime.Now;
dataTable.Columns.Add(column);
// SendLevel
column = new DataColumn();
column.DataType = System.Type.GetType( " System.Int32 " );
column.ColumnName = " SendLevel " ;
column.DefaultValue = 4 ;
dataTable.Columns.Add(column);
// IsLong
column = new DataColumn();
column.DataType = System.Type.GetType( " System.Int32 " );
column.ColumnName = " IsLong " ;
dataTable.Columns.Add(column);
// AdminID
column = new DataColumn();
column.DataType = System.Type.GetType( " System.Int32 " );
column.ColumnName = " AdminID " ;
column.DefaultValue = 0 ;
dataTable.Columns.Add(column);
string [] result = new string [phone.Length];
for ( int i = 0 ; i < phone.Length; i ++ )
{
// 生成GUID
string Guid = System.Guid.NewGuid().ToString( " N " ).ToUpper();
string phoneNew = string .Empty;
int phoneType = GetMobileType(phone[i], out phoneNew);
if (phoneType == 0 )
{
result[i] = phoneNew + " -0-0 " ;
}
else
{
DataRow dataRow = dataTable.NewRow();
dataRow[ " qy_id " ] = 16 ;
dataRow[ " Info_ID " ] = Guid;
dataRow[ " SP_Port " ] = " 10657027014211 " ;
dataRow[ " Phone " ] = phoneNew;
dataRow[ " Content " ] = smsInfo;
dataRow[ " IsLong " ] = 0 ;
dataTable.Rows.Add(dataRow);
result[i] = phoneNew + " -1- " + Guid;
}
}
// BCP copy
SqlConnection conn = new SqlConnection();
conn.ConnectionString = " server=.;uid=dmkj_hpc;pwd=#$wlh*&1110h%c;database=DMKJ_SMS " ;
conn.Open();
SqlTransaction sqlbulkTransaction = conn.BeginTransaction();
// 请在插入数据的同时检查约束,如果发生错误调用sqlbulkTransaction事务
SqlBulkCopy copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, sqlbulkTransaction);
copy.DestinationTableName = " T_SMS_SendInfo " ;
foreach (DataColumn dc in dataTable.Columns)
{
copy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
}
try
{
copy.WriteToServer(dataTable);
sqlbulkTransaction.Commit();
}
catch (Exception ex)
{
sqlbulkTransaction.Rollback();
Console.WriteLine(ex.ToString());
}
finally
{
copy.Close();
conn.Close();
}
// for (int x = 0; x < dataTable.Rows.Count; x++)
// {
// for (int i = 0; i < dataTable.Columns.Count; i++)
// {
// Console.WriteLine("Column Name:{0},and value is:{1}",dataTable.Columns[i].ColumnName.ToString(),dataTable.Rows[x][i].ToString());
// }
// Console.WriteLine();
// }
// foreach (string str in result)
// {
// Console.WriteLine(str.ToString());
// }
}
使用SqlBulkCopyOptions.UseInternalTransaction:
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
connectionString, SqlBulkCopyOptions.KeepIdentity |
SqlBulkCopyOptions.UseInternalTransaction))
{
bulkCopy.BatchSize = 10;
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoMatchingColumns";
// Write from the source to the destination.
// This should fail with a duplicate key error
// after some of the batches have been copied.
try
{
bulkCopy.WriteToServer(XXX);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
//some code;
}
}
使用SqlTransaction :
using (SqlConnection destinationConnection =
new SqlConnection(connectionString))
{
destinationConnection.Open();
using (SqlTransaction transaction =
destinationConnection.BeginTransaction())
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
destinationConnection, SqlBulkCopyOptions.KeepIdentity,
transaction))
{
bulkCopy.BatchSize = 10;
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoMatchingColumns";
// Write from the source to the destination.
// This should fail with a duplicate key error.
try
{
bulkCopy.WriteToServer(XXX);
transaction.Commit();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
transaction.Rollback();
}
finally
{
//code;
}
}
}
}
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
connectionString, SqlBulkCopyOptions.KeepIdentity |
SqlBulkCopyOptions.UseInternalTransaction))
{
bulkCopy.BatchSize = 10;
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoMatchingColumns";
// Write from the source to the destination.
// This should fail with a duplicate key error
// after some of the batches have been copied.
try
{
bulkCopy.WriteToServer(XXX);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
//some code;
}
}
使用SqlTransaction :
using (SqlConnection destinationConnection =
new SqlConnection(connectionString))
{
destinationConnection.Open();
using (SqlTransaction transaction =
destinationConnection.BeginTransaction())
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
destinationConnection, SqlBulkCopyOptions.KeepIdentity,
transaction))
{
bulkCopy.BatchSize = 10;
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoMatchingColumns";
// Write from the source to the destination.
// This should fail with a duplicate key error.
try
{
bulkCopy.WriteToServer(XXX);
transaction.Commit();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
transaction.Rollback();
}
finally
{
//code;
}
}
}
}
SqlBulkCopyOptions
成员名称 | 说明 | |
---|---|---|
Default | 对所有选项使用默认值。 | |
KeepIdentity | 保留源标识值。如果未指定,则由目标分配标识值。 | |
CheckConstraints | 请在插入数据的同时检查约束。默认情况下,不检查约束。 | |
TableLock | 在批量复制操作期间获取批量更新锁。如果未指定,则使用行锁。 | |
KeepNulls | 保留目标表中的空值,而不管默认值的设置如何。如果未指定,则空值将由默认值替换(如果适用)。 | |
FireTriggers | 指定后,会导致服务器为插入到数据库中的行激发插入触发器。 | |
UseInternalTransaction | 如果已指定,则每一批批量复制操作将在事务中发生。如果指示了此选项,并且为构造函数提供了 SqlTransaction 对象,则发生 ArgumentException 。 |