public void Update(DataTable table, BatchOperationOption option)
        {
            if (option == null)
            {
                throw new Exception("参数option不能为空");
            }
            if (string.IsNullOrEmpty(option.ConnectionString))
            {
                throw new Exception("参数option的属性ConnectionString必须是一个数据库连接字符串");
            }
            if (string.IsNullOrEmpty(option.SPName))
            {
                throw new Exception("参数option的属性SPName必须是一个数据库存储过程用来更新表");
            }
            var tempTable = table.ToTable(option.AllowedAllColumns);

            if (option.ChiefId > 0)
            {
                table.Columns.Add("ChiefId", typeof(string), option.ChiefId.ToString());
            }
            using (SqlConnection con = new SqlConnection(option.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(option.SPName))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection  = con;
                    cmd.Parameters.AddWithValue("@table", tempTable);
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
        }
        public List <T> Select <T>(BatchOperationOption option) where T : class
        {
            if (option == null)
            {
                throw new Exception("参数option不能为空");
            }
            if (string.IsNullOrEmpty(option.ConnectionString))
            {
                throw new Exception("参数option的属性ConnectionString必须是一个数据库连接字符串");
            }
            if (string.IsNullOrEmpty(option.TableName))
            {
                throw new Exception("参数option的属性TableName必须是一个数据库表名称");
            }
            if (string.IsNullOrEmpty(option.Where))
            {
                throw new Exception("参数option的属性Where必须是一个去掉where的sql查询条件");
            }
            var sql = "";

            if (!string.IsNullOrEmpty(option.SelectColumns))
            {
                sql = string.Format("select {0} from {1} where {2}", option.SelectColumns, option.TableName, option.Where);
            }
            else
            {
                sql = string.Format("select * from {0} where {1}", option.TableName, option.Where);
            }
            using (var con = new SqlConnection(option.ConnectionString))
            {
                var list = con.Query <T>(sql).ToList();
                return(list);
            }
        }
        public void Insert(DataTable table, BatchOperationOption option)
        {
            if (option == null)
            {
                throw new Exception("参数option不能为空");
            }
            if (string.IsNullOrEmpty(option.ConnectionString))
            {
                throw new Exception("参数option的属性ConnectionString必须是一个数据库连接字符串");
            }
            if (string.IsNullOrEmpty(option.TableName))
            {
                throw new Exception("参数option的属性TableName必须是一个数据库表名称");
            }
            if (option.ColumnMapping == null && option.ColumnMapping.Count == 0)
            {
                throw new Exception("参数option的属性ColumnMapping必须是一个从excel到数据库字段名的字典");
            }
            using (var sqlCopy = new SqlBulkCopy(option.ConnectionString))
            {
                sqlCopy.DestinationTableName = option.TableName;
                if (option.BatchSize > 0)
                {
                    sqlCopy.BatchSize = option.BatchSize;
                }
                if (option.BulkCopyTimeout > 0)
                {
                    sqlCopy.BulkCopyTimeout = option.BulkCopyTimeout;
                }
                sqlCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
                sqlCopy.NotifyAfter    = option.NotifyAfter;

                // Set up the column mappings by name.
                sqlCopy.AddValueToValueMappings(option.ColumnMapping);
                if (option.ChiefId > 0)
                {
                    table.Columns.Add("ChiefId", typeof(string), option.ChiefId.ToString());
                    sqlCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("ChiefId", "ChiefId"));
                }
                try
                {
                    // Write from the source to the destination.
                    sqlCopy.WriteToServer(table);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    throw ex;
                }
                finally
                {
                    table.Clear();
                }
            }
        }
        /// <summary>
        /// 对导入数据进行拆分,分为插入和更新
        /// </summary>
        /// <param name="table"></param>
        /// <param name="option"></param>
        public void InsertOrUpdate(DataTable table, BatchOperationOption option)
        {
            if (table == null || table.Rows.Count == 0)
            {
                throw new Exception("参数table为空");
            }
            var firstColumnName = table.Columns[0].ColumnName;
            var insertTable     = table.Where(firstColumnName, a => string.IsNullOrEmpty(a));
            var updateTable     = table.Where(firstColumnName, a => !string.IsNullOrEmpty(a));

            if (insertTable != null && insertTable.Rows != null && insertTable.Rows.Count > 0)
            {
                Insert(insertTable, option);
            }
            if (updateTable != null && updateTable.Rows != null && updateTable.Rows.Count > 0)
            {
                Update(updateTable, option);
            }
        }