SqlBulkCopy(大数据量拷贝)

///   <summary>
///  批量执行SqlBulkCopy数据迁移操作
///   </summary>
///   <param name="dtblSource"> 数据源 </param>
///   <param name="strPreSqlSentence"> 执行前需要执行的脚本，如先清空表等，可为空 </param>
///   <param name="strDestinationTablesName"> 目标数据库表名称 </param>
///   <returns></returns>
public   static   bool  ExeSqlBulkCopy(DataTable dtblSource,  string  strPreSqlSentence, string  strDestinationTablesName)
{
     using  (SqlConnection connection  =   new  SqlConnection(connectionString))
    {
         bool  blReturnValue  =   false ; connection.Open();

         // 请在插入数据的同时检查约束，如果发生错误调用 sqlbulkTransaction事务
        SqlTransaction sqlbulkTransaction  =  connection.BeginTransaction();
        
         if  (strPreSqlSentence  !=   "" )
        {
            SqlCommand sqlCmd  =   new  SqlCommand();
            sqlCmd.Connection  =  connection; sqlCmd.Transaction  =  sqlbulkTransaction;

             try
            {
                sqlCmd.CommandText  =  strPreSqlSentence; sqlCmd.ExecuteNonQuery(); blReturnValue  =   true ;
            }
             catch  (Exception exc)
            {
                Log.WriteLog( " 批量插入前语句失败 " , exc); sqlbulkTransaction.Rollback(); connection.Close();  return   false ;
            }
        }

        SqlBulkCopy BulkCopy  =   new  SqlBulkCopy(connection, SqlBulkCopyOptions.CheckConstraints, sqlbulkTransaction);

        BulkCopy.DestinationTableName  =  strDestinationTablesName; BulkCopy.BulkCopyTimeout  =   1200 ;

         try
        {
            BulkCopy.WriteToServer(dtblSource); sqlbulkTransaction.Commit(); blReturnValue  =   true ;
        }
         catch  (Exception exp)
        {
            sqlbulkTransaction.Rollback(); Log.WriteLog( " 批量插入语句失败 " , exp); blReturnValue  =   false ;
        }
         finally
        {
            BulkCopy.Close(); connection.Close();
        }

         return  blReturnValue;
    }
}

 

转载于:https://www.cnblogs.com/zhangpengshou/archive/2010/07/01/1769214.html