使用事务和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.Check Constraints, sqlbulkTransaction);
copy.DestinationTableName = "T_SMS_SendInfo";
foreach (DataColumn dc in dataTable.Columns)
{
copy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
}
try
{
copy.WriteToServer(dataTable);
mit();
}
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}",da taTable.Columns[i].ColumnName.ToString(),dataTable.Rows[x][i].ToStrin g());
// }
// Console.WriteLine();
//}
//foreach (string str in result)
//{
// Console.WriteLine(str.ToString());
//}
}。