【转载】使用事务和SqlBulkCopy导入大批量数据

使用事务和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());
    //}
}