SqlBulkCopy 批量导入数据 转换表字段类型

在使用SqlBulkCopy导入数据时，要有一个跟数据库里面同样的DataTable

要赋值表名

要求每个列跟数据库中列同名，并且列的类型要赋值跟数据库中列的类型对应的NET类型

要求数据库中为Null的数据，赋值DBNull.Value

代码：

        [AcceptVerbs(HttpVerbs.Post)]
        public JsonResult Upload(HttpPostedFileBase fileData)
        {
            if (fileData != null)
            {
                try
                {
                    Guid UserID = (Session["User"] as User).UserID;
                    List<string> columns = GetColumns();
                    string fileName = Path.GetFileName(fileData.FileName);// 原始文件名称
                    string fileExtension = Path.GetExtension(fileName); // 文件扩展名
                    DataTable dt = new DataTable();

                    IWorkbook workbook = null;
                    if (fileExtension == ".xlsx")
                    {
                        workbook = new XSSFWorkbook(fileData.InputStream); // .xlsx
                    }
                    else
                    {
                        workbook = new HSSFWorkbook(fileData.InputStream); // .xls
                    }

                    ISheet sheet = workbook.GetSheetAt(0);
                    System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                    int cellCount = columns.Count();
                    foreach (var col in columns)
                    {
                        dt.Columns.Add(col.Trim());
                    }
                    //用于跳出2层循环
                    var isT = false;
                    for (int i = (sheet.FirstRowNum + 3); i <= sheet.LastRowNum; i++)
                    {
                        if (isT) { break; }
                        IRow row = sheet.GetRow(i);
                        DataRow dataRow = dt.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (j == 0)
                            {
                                string num = row.GetCell(j).ToString();
                                if (string.IsNullOrEmpty(row.GetCell(j).ToString()))
                                {
                                    isT = true; break;
                                }
                                if (row.GetCell(j) != null)
                                    dataRow[j] = Guid.NewGuid();

                            }
                            else
                            {
                                if (row.GetCell(j) != null)
                                    dataRow[j] = row.GetCell(j).ToString();
                            }
                        }
                        if (!isT)
                        {
                            dt.Rows.Add(dataRow);
                        }
                    }
                    dt.Columns.Remove("合计");
                    dt.Columns.Add("InsuranceCompany");
                    dt.Columns.Add("IsDelete");
                    dt.Columns.Add("CreateTime");
                    dt.Columns.Add("CreateBy");
                    dt.Columns.Add("UpdateTime");
                    dt.Columns.Add("UpdateBy");
                    for (var i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow dr = dt.Rows[i];
                        dr["IsDelete"] = false;
                        dr["CreateTime"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                        dr["CreateBy"] = UserID;
                        dr["UpdateTime"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                        dr["UpdateBy"] = UserID;
                        string state = dr["State"].ToString().Trim();
                        if (state == "在修")
                        {
                            dr["State"] = 1;
                        }
                        else
                        {
                            //已结算
                            dr["State"] = 2;
                        }
                        string InsuranceCompany = "";
                        string str1 = dr["中保"].ToString().Trim();
                        string str2 = dr["太保"].ToString().Trim();
                        string str3 = dr["平安"].ToString().Trim();
                        string str4 = dr["其他"].ToString().Trim();
                        if (!string.IsNullOrEmpty(str1))
                        {
                            InsuranceCompany = "中保";
                        }
                        else if (!string.IsNullOrEmpty(str2))
                        {
                            InsuranceCompany = "太保";
                        }
                        else if (!string.IsNullOrEmpty(str3))
                        {
                            InsuranceCompany = "平安";
                        }
                        else if (!string.IsNullOrEmpty(str4))
                        {
                            InsuranceCompany = "其他";
                        }
                        dr["InsuranceCompany"] = InsuranceCompany;
                    }
                    dt.Columns.Remove("中保");
                    dt.Columns.Remove("太保");
                    dt.Columns.Remove("平安");
                    dt.Columns.Remove("其他");
                    dt.TableName = "T_DMSMaintenance";
                    DataTable dt2 = ConvertDataType(dt);
                    BizCenter biz = new BizCenter();
                    var isS = biz.SqlBulkCopyData(dt2, "ID", "CJGreenWay");
                    if (isS)
                    {
                        return Json(new { Success = true, Message = "导入数据成功！" }, JsonRequestBehavior.AllowGet);
                    }
                    else
                    {
                        return Json(new { Success = false, Message = "导入数据失败！" }, JsonRequestBehavior.AllowGet);
                    }
                }
                catch (Exception ex)
                {
                    return Json(new { Success = false, Message = ex.Message }, JsonRequestBehavior.AllowGet);
                }
            }
            else
            {
                return Json(new { Success = false, Message = "请选择要上传的文件！" }, JsonRequestBehavior.AllowGet);
            }
        }

        public DataTable ConvertDataType(DataTable dt)
        {
            var conn = new SqlConnection(ConnectionInstance.Instance.ConnectionNodes["CJGreenWay"].connectionString);
            conn.Open();
            if (dt.Rows.Count == 0) return null;
            DataTable result = new DataTable();
            //获取数据库表结构
            var res = new string[4];
            res[2] = dt.TableName;
            DataTable dtTemp = conn.GetSchema("Columns", res);
            foreach (DataRow row in dtTemp.Rows)
            {
                string colName = row["COLUMN_NAME"].ToString();
                string dataType = row["DATA_TYPE"].ToString();
                bool isNull = row["IS_NULLABLE"].ToString().Trim() == "YES" ? true : false;
                foreach (DataColumn dc in dt.Columns)
                {
                    if (dc.ColumnName == colName)
                    {
                        result.Columns.Add(colName, GetCSharpType(dataType));
                    }
                }
            }
            foreach (DataRow row in dt.Rows)
            {
                DataRow nRow = result.NewRow();
                foreach (DataColumn col in dt.Columns)
                {
                    if (string.IsNullOrEmpty(row[col.ColumnName].ToString()))
                    {
                        nRow[col.ColumnName] = DBNull.Value;
                    }
                    else
                    {
                       nRow[col.ColumnName] = row[col.ColumnName];
                    }
                }
                result.Rows.Add(nRow);
            }
            result.TableName = dt.TableName;
            conn.Close();
            return result;
        }
        public List<string> GetColumns()
        {

            string arr = @"   ID    ,State
            ,WorkOrderNO
      ,CustomerName
      ,CarNo
      ,ServiceAdvisor
      ,ElectricalLaborHour
      ,ElectricalParts
      ,SheetSprayLaborHour
      ,SheetSprayParts
      ,SheetSprayPayType
      ,SheetSprayTransLaborHour
      ,OilChangeLaborHour
      ,OilChangeParts
      ,WarrantyLaborHour
      ,WarrantyParts
      ,WarrantyTransLaborHour
      ,InternalElectricalLaborHour
      ,InternalParts
      ,InternalSheetSprayLaborHour
      ,InternalOil
      ,InternalPayDept
      ,ZeroWorkOrder
      ,合计 
      ,中保
 ,太保
 ,平安
 ,其他";
            return arr.Replace("
", "").Replace (" ","").Split(',').ToList();
        }

        public Type GetCSharpType(string type, bool isNull = false)
        {
            Type tp;
            switch (type.ToLower())
            {
                case "uniqueidentifier":
                    if (isNull) { tp = typeof(Guid?); } else { tp = typeof(Guid); } break;
                case "nvarchar":
                case "varchar":
                case "nchar":
                case "text":
                    tp = typeof(string); break;
                case "bit":
                    if (isNull) { tp = typeof(bool?); } else { tp = typeof(bool); } break;
                case "datetime":
                case "timestamp":
                    if (isNull) { tp = typeof(DateTime?); } else { tp = typeof(DateTime); } break;
                case "tinyint":
                case "int":
                case "bigint":
                case "float":
                case "decimal":
                case "numeric":
                    if (isNull) { tp = typeof(decimal?); } else { tp = typeof(decimal); } break;
                default:
                    tp = typeof(string); break;

            }
            return tp;
        }

先是导入Excel中数据，然后将Excel生成的DataTable转换成跟数据库中对应的，

使用新的表，因为有数据的表的列不能转换列的类型

conn.GetSchema("Columns", res); 获取表字段信息

查看具体的ConvertDataType方法，就是具体的给表的列赋值类型
表的类型不需要转换成C#中带null的类型，比如DateTime不需要让表的字段类型转成DateTime?，有null直接赋值DBNull.Value;