【.NET】使用SqlBulkCopy将DataTable中的数据批量插入数据库中

#region 使用SqlBulkCopy将DataTable中的数据批量插入数据库中  
      /// <summary>  
      /// 使用SqlBulkCopy将DataTable中的数据批量插入数据库中  
      /// </summary>  
      /// <param name="strTableName">数据库中对应的表名</param>  
      /// <param name="dtData">数据集</param>  
      public void SqlBulkCopyInsert(string strTableName, DataTable dtData)  
      {  
          string ConStr = connectionString;// 数据库连接字符串  
  
          try  
          {  
              using (SqlBulkCopy sqlRevdBulkCopy = new SqlBulkCopy(ConStr))//引用SqlBulkCopy  
              {  
                  sqlRevdBulkCopy.DestinationTableName = strTableName;//数据库中对应的表名  
  
                  sqlRevdBulkCopy.NotifyAfter = dtData.Rows.Count;//有几行数据  
  
                  sqlRevdBulkCopy.WriteToServer(dtData);//数据导入数据库  
  
                  sqlRevdBulkCopy.Close();//关闭连接  
              }  
          }  
          catch (Exception ex)  
          {  
              WriteErrorLog(ex.Message + "数据库处理出错654行。SqlBulkCopyInsert");  
              throw (ex);  
          }  
      }  
      #endregion  

使用事务：

 

//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();  
    }