使用事务和SqlBulkCopy导入大批量数据

 

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

     
     }