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("\r\n", "").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;
