在使用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();
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 + ); 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 == )
{
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 = ; 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"] = ;
}
else
{
//已结算
dr["State"] = ;
}
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 == ) return null;
DataTable result = new DataTable();
//获取数据库表结构
var res = new string[];
res[] = 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;