SqlBulkCopy批量导入错误数据异常处理

　　最近有个需求，将excel中的数据大批量导入到Sql Server中，方法很简单，使用SqlBulkCopy

即可，网上介绍的方法也比较多，这里只是介绍一下对于数据的异常处理。

　　由于项目是放在服务中运行，出现错误的数据希望只是希望将数据单独保存下来，不去影响其他数据的导入，网上找了一圈，没有找到合适的办法，只好自己仔细研究了一下，然后看到了SqlRowsCopied事件，MSDN中解释：在每次处理完 NotifyAfter 属性指定的行数时发生。如果将NotifyAfter设置为1的话，每次都记录一下当前执行到的行数，当出现异常的时候，可以定位到出错的行数。当知道了出错的行数，剩下的事情就很好办了，将出错的行数据单独拿出另存处理，计算一下已经正确导入到数据库中的数据行，移除出现，将剩下的数据继续导入。

　　

/// <summary>
    /// 导入数据，使用SqlBulkCopy
    /// </summary>
    public class SqlServerHelp : IDisposable
    {
        SqlConnection conn = null;
        SqlBulkCopy bulkCopy = null;

        private static string GetConnectionString()
        {
            return ConfigurationManager.AppSettings["SqlConnection"];
        }

        int batchSize = 1000;//批处理数据条数
        int bulkCopyTimeout = 3600;

        public SqlServerHelp()
        {
            string connectionString = GetConnectionString();
            conn = new SqlConnection(connectionString);
            Open();
            bulkCopy = new SqlBulkCopy(conn);
            bulkCopy.BatchSize = batchSize;
            bulkCopy.BulkCopyTimeout = bulkCopyTimeout;
            bulkCopy.NotifyAfter = 1;
            bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(SqlRowsCopied);
        }
        
        public void Dispose()
        {
            this.Close();
        }

        public void Close()
        {
            data = null;
            if (conn != null && conn.State != ConnectionState.Closed)
                conn.Close();
            if (bulkCopy != null)
                bulkCopy.Close();
        }

        public void Import(BaseImportData importdata)
        {
            if (importdata.CanImport)
            {
                if (importdata.DtSource == null || importdata.DtSource.Rows.Count == 0)
                {
                }
                else
                {
                    #region
                    bulkCopy.DestinationTableName = importdata.TableName;
                    bulkCopy.ColumnMappings.Clear();

                    foreach (ObjectColumn column in importdata.columns)
                    {
                        bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(
                            column.ExcelColumn, column.SqlServerColumn));
                    }

                    foreach (ObjectColumn column in importdata.defaultColumns)
                    {
                        bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(
                            column.ExcelColumn, column.SqlServerColumn));
                    }

                    try
                    {
                        data = importdata;
                        rowsCopiedId = 0;
                        indexRe = 0;
                        Open();
                        bulkCopy.WriteToServer(importdata.DtSource);
                    }
                    catch (SqlException ex)
                    {
                        DealSqlException(importdata, ex);
                    }
                    catch (InvalidOperationException ex)
                    {
                        DealExceptionNone(importdata, ex);
                    }
                    catch (Exception ex)
                    {
                        DealException(importdata, ex);
                    }
                    #endregion
                }
            }
        }

        /// <summary>
        /// 数据导入完成之后导入日志以及错误信息
        /// </summary>
        /// <param name="importdata"></param>
        public void ImportLog(BaseImportData importdata)
        {
            data = importdata;
            rowsCopiedId = 0;
            indexRe = 0;
            InsertImportLog(importdata);

            foreach (ErrorInfo error in importdata.errorList)
            {
                InsertErrorInfo(error);
            }
        }

        private void ImportError(BaseImportData importdata)
        {
            try
            {
                data = importdata;
                rowsCopiedId = 0;
                indexRe = 0;
                Open();
                bulkCopy.WriteToServer(importdata.DtSource);
            }
            catch (SqlException ex)
            {
                DealSqlException(importdata, ex);
            }
            catch (InvalidOperationException ex)
            {
                DealExceptionNone(importdata, ex);
            }
            catch (Exception ex)
            {
                DealException(importdata, ex);
            }
        }

        private void DealExceptionNone(BaseImportData importdata, Exception ex)
        {
            //错误行索引
            int errorIndex = Convert.ToInt32(rowsCopiedId);

            int succIndex = (errorIndex / batchSize) * batchSize;//成功导入到数据库中的数据最大索引
            //移除掉导入成功的数据
            for (int i = 0; i < succIndex; i++)
            {
                importdata.DtSource.Rows.RemoveAt(0);
            }
            rowsCopiedId = 0;

            Logger.Info("插入数据出现异常：" + ex.GetType() + ex.Message);
            
            this.ImportError(importdata);
        }

        /// <summary>
        /// 如果出现异常，进行处理
        /// </summary>
        private void DealSqlException(BaseImportData importdata, SqlException ex)
        {
            switch (ex.Number)
            {
                case -1:
                case -2:
                case 2:
                case 53:
                    //数据库连接不上，超时等，隔一段时间后重新连接
                    //错误行索引
                    int errorIndex = Convert.ToInt32(rowsCopiedId);

                    int succIndex = (errorIndex / batchSize) * batchSize;//成功导入到数据库中的数据最大索引
                    //移除掉导入成功的数据
                    for (int i = 0; i < succIndex; i++)
                    {
                        importdata.DtSource.Rows.RemoveAt(0);
                    }
                    rowsCopiedId = 0;

                    Logger.Info("导入数据异常：" + ex.Message);

                    System.Threading.Thread.Sleep(600000);

                    this.ImportError(importdata);
                    break;
                case 10053:
                case 64:
                    DealExceptionNone(importdata, ex);
                    break;
                default:
                    //其余的异常认为是数据行出错
                    DealException(importdata, ex);
                    break;
            }
        }

        /// <summary>
        /// 如果出现异常，进行处理
        /// </summary>
        private void DealException(BaseImportData importdata, Exception ex)
        {
            importdata.ImportResult = "存在错误数据";

            //错误行索引
            int errorIndex = Convert.ToInt32(rowsCopiedId);
            int succIndex = (errorIndex / batchSize) * batchSize;//成功导入到数据库中的数据最大索引
            //long errorNum = rowsNum + errorIndex + 1;
            
            ErrorInfo error = new ErrorInfo(importdata);
            if (ex is SqlException)
            {
                error.ErrorDesc = ex.GetType() + ((SqlException)ex).Number.ToString() + ex.Message;
            }
            else
                error.ErrorDesc = ex.GetType() + ex.Message;

            error.SetContent(importdata.DtSource.Rows[errorIndex]);

            //error.Remark = importdata.DtSource.TableName.Replace("'", "").Replace("$", "") + "中，第" + (
            //    errorNum + 1).ToString() + "行";
            error.Remark = importdata.DtSource.TableName.Replace("'", "").Replace("$", "") + "中，第" + (
               importdata.DtSource.Rows[errorIndex]["ExcelRowNum"]).ToString() + "行";
            importdata.errorList.Add(error);

            //rowsNum = rowsNum + succIndex + 1;

            Logger.Error("存在错误数据：" + error.Remark + error.ErrorDesc);//记录异常数据

            importdata.DtSource.Rows.RemoveAt(errorIndex);//删除异常数据

            for (int i = 0; i < succIndex; i++)
            {
                importdata.DtSource.Rows.RemoveAt(0);
            }

            rowsCopiedId = 0;
            this.ImportError(importdata);
        }

        //当前批量复制操作期间复制的行数
        long rowsCopiedId = 0;
        //成功导入到数据库中行数
        //long rowsNum = 0;
        long indexRe = 0;//已经批量执行到数据库中的次数
        BaseImportData data;
        private void SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
        {
            //错误行索引
            int errorIndex = Convert.ToInt32(rowsCopiedId);
            int succIndex = (errorIndex / batchSize) * batchSize;//成功导入到数据库中的数据最大索引
            
            rowsCopiedId = e.RowsCopied;
            if ((e.RowsCopied -1) / batchSize > indexRe)
            {
                Logger.Debug("已经成功导入" + succIndex + "行，excel中第" + data.DtSource.Rows[succIndex]["ExcelRowNum"].ToString() + "行");
                indexRe++;
            }
            else if (e.RowsCopied == data.DtSource.Rows.Count)
            {
                Logger.Debug("已经成功导入" + e.RowsCopied + "行，excel中第" + data.DtSource.Rows[succIndex]["ExcelRowNum"].ToString() + "行");
                indexRe++;
            }
        }

        /// <summary>
        /// 插入错误信息
        /// </summary>
        public void InsertErrorInfo(ErrorInfo info)
        {
                    }

        /// <summary>
        /// 插入导入日志
        /// </summary>
        public void InsertImportLog(BaseImportData importdata)
        {
            
        }

        /// <summary>
        /// 修改导入日志
        /// </summary>
        public void UpdateImportLog(BaseImportData importdata)
        {
            
        }

        public DataTable GetImportLog(string _dataType)
        {
            return null;
        }

        
        public void Open()
        {
            if (conn.State == ConnectionState.Closed)
            {
                try
                {
                    conn.Open();
                }
                catch (SqlException ex)
                {
                    Logger.Debug("打开数据库连接异常：" + ex.GetType() + ex.Number + ex.Message);
                    System.Threading.Thread.Sleep(600000);
                    this.Open();
                }
                catch (Exception ex)
                {
                    Logger.Debug("打开数据库连接异常：" + ex.GetType() + ex.Message);
                    System.Threading.Thread.Sleep(600000);
                    this.Open();
                }
            }
        }


        private DataTable QueryTable(string sql)
        {
            Open();
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            try
            {
                comm.CommandText = sql;
                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(ds);
                if (ds.Tables.Count > 0)
                    return ds.Tables[0];
            }
            catch (SqlException ex)
            {
                switch (ex.Number)
                {
                    case -1:
                    case -2:
                    case 2:
                    case 53:
                    case 10053:
                    case 64:
                        Logger.Debug("网络故障:执行sql查询出错：" + ex.GetType() + ex.Message);
                        return this.QueryTable(sql);
                    default:
                        throw;
                }
            }
            catch (InvalidOperationException ex)
            {
                Logger.Debug("网络故障:执行sql出错：" + ex.GetType() + ex.Message);
                return this.QueryTable(sql);
            }
            catch (Exception ex)
            {
                throw;
            }
            return null;
        }

        private void ExecuteNonQuery(string sql)
        {
            Open();
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            try
            {
                comm.CommandText = sql;
                comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                switch (ex.Number)
                {
                    case -1:
                    case -2:
                    case 2:
                    case 53:
                    case 10053:
                    case 64:
                        Logger.Debug("网络故障:执行sql出错：" + ex.GetType() + ex.Message);
                        this.ExecuteNonQuery(sql);
                        break;
                    default:
                        throw;
                }
            }
            catch (InvalidOperationException ex)
            {
                Logger.Debug("网络故障:执行sql出错：" + ex.GetType() + ex.Message);
                this.ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {
                throw;
            }
        }

    }    

　　第一次发文，写的比较散乱，希望可以给有相同需求的人一些启示。