背景
我开发了一个简单的MVC 5应用程序，该应用程序能够使用Entity Framework 6和SqlBulkTools(Github(将Excel文件导入SQL Server 2012数据库。代码结构如下所示。
型号(Project.Models(
public class DataContext : DbContext
{
public DataContext : base("DefaultConnection")
{
}
public DbSet<Product> Products { get; set; }
}
[Table("Product")]
public class Product
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int ProductId { get; set; }
public String SessionNo { get; set; }
public String ProductName { get; set; }
public DateTime Date { get; set; }
public String LotNumber { get; set; }
public String RegNumber { get; set; }
public decimal? InitPrice { get; set; }
public decimal? FinalPrice { get; set; }
public String TaxNote { get; set; }
}
public class FileModel
{
public String FileName { get; set; } // Excel file name
public String SheetName { get; set; } // source worksheet name
public String TableName { get; set; } // target table name
public HttpPostedFileBase FileToUpload { get; set; } // uploaded Excel file (version 2007 or above)
}
控制器(Project.Controllers.FileController(
using SqlBulkTools;
[Route("File")]
public class FileController : Controller
{
// class-level single datatable
DataTable dt = new DataTable();
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
// GET
public ViewResult Import()
{
return View();
}
// POST
[HttpPost]
public ActionResult Import(FileModel model)
{
// when file name is empty, execute these lines below
if (String.IsNullOrEmpty(model.FileName)
{
foreach (String file in Request.Files)
{
model.FileToUpload = this.Request.Files[file];
}
if (model.FileToUpload != null && model.FileToUpload.ContentLength > 0)
{
model.FileName = Path.GetFileName(FileToUpload.FileName);
}
}
var path = Path.Combine(Server.MapPath("~/Files/Imported"), model.FileName);
if (System.IO.File.Exists(path))
{
System.IO.File.Delete(path);
}
model.FileToUpload.SaveAs(path);
String oleconstring = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + path + "; Extended Properties="Excel 12.0; HDR=Yes; IMEX=2"; Persist Security Info=False";
String olecmdstring = "SELECT * FROM [" + model.SheetName + "$]";
using (var oleda = new OleDbDataAdapter())
{
using (var olecon = new OleDbConnection(oleconstring))
{
try
{
oleda.SelectCommand = new OleDbCommand(olecmdstring, olecon);
oleda.Fill(dt);
// remove all "null" values from Excel worksheet if any
dt = dt.Rows.Cast<DataRow>().Where(r => !r.ItemArray.All(f => f is DBNull || f as String == null || String.Compare((f as String).Trim(), String.Empty) == 0)).CopyToDataTable();
// trim all whitespaces after column names
foreach (DataColumn cols in dt.Columns)
{
cols.ColumnName = cols.ColumnName.Trim();
}
if (dt != null && dt.Rows.Count > 0)
{
switch (model.TableName)
{
case "Product":
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["TaxNote"].ToString().Equals("None", StringComparison.OrdinalIgnoreCase))
{
dt.Rows[i]["TaxNote"] = DBNull.Value;
}
else
{
if (dt.Rows[i]["TaxNote"] is DateTime)
{
dt.Rows[i]["TaxNote"] = String.Format("{0:yyyy-mm-dd}", dt.Rows[i]["TaxNote"]);
}
else
{
dt.Rows[i]["TaxNote"] = DBNull.Value;
}
}
}
var bulkOperation = new BulkOperations();
// convert DataTable into IEnumerable for bulk upsert
var productList = dt.AsEnumerable().Select(x => new Product()
{
SessionNo = x.Field<double>("SessionNo").ToString(),
ProductName = x.Field<String>("ProductName"),
Date = x.Field<DateTime>("Date"),
LotNumber = x.Field<String>("LotNumber"),
RegNumber = x.Field<String>("RegNumber"),
// this won't work if source column in Excel contains null
InitPrice = (decimal)(x.Field<Nullable<double>>("InitPrice") != null ? x.Field<Nullable<double>>("InitPrice") : 0),
// this won't work if source column in Excel contains null
FinalPrice = (decimal)(x.Field<Nullable<double>>("FinalPrice") != null ? x.Field<Nullable<double>>("FinalPrice") : 0),
TaxNote = x.Field<String>("TaxNote")
});
bulkOperation.Setup<Product>()
.ForCollection(productList) // requires IEnumerable to work with destination table
.WithTable("Product")
.AddAllColumns()
.BulkInsertOrUpdate()
.SetIdentityColumn(x => x.ProductId)
.MatchTargetOn(x => x.SessionNo)
.MatchTargetOn(x => x.LotNumber)
.MatchTargetOn(x => x.RegNumber);
bulkOperation.CommitTransaction(conn);
break;
// other unrelated case stuffs
}
}
else
{
// Error: DataTable is null or empty
ViewBag.Error = "No data present."
return View(model);
}
}
catch (Exception e)
{
ViewBag.Error = "An error occurred when importing data. Message: " + e.Message;
return View(model);
}
}
}
return RedirectToAction("Success", "Notify");
}
}
查看(Import.cs.html(
@{
ViewBag.Title = "Data Import Example";
Layout = "~/Views/Shared/_Layout.cshtml";
}
@using Project.Models
@model FileModel
<div>
@using (Html.BeginForm("Import", "File", FormMethod.Post))
{
<p>File name:</p>
@Html.TextBoxFor(m => m.FileName)
<br />
<p>Worksheet name:</p>
@Html.TextBoxFor(m => m.SheetName)
<br />
<p>SQL table name:</p>
@Html.TextBoxFor(m => m.TableName)
<br />
<p>File to upload:</p>
@Html.TextBoxFor(m => m.FileToUpload, new { type = "file" })
<br /><br />
<input type="submit" value="Import to Database" />
}
</div>
<div>@ViewBag.Error</div>
问题陈述
应用程序将Excel工作表中的数据导入到DataTable中，该CCD_1通过批量追加过程(如果发现现有数据则更新，如果不存在匹配数据则插入(将产品表作为目标。
Excel工作表的表结构与数据库表和模型类完全相同，但值是由其他人提交的，因此我无法更改工作表内容，并且可能InitPrice和FinalPrice列有空值，可能翻译为DBNull。所有其他数值都被视为double。
当任何数据输入人员通过Import页面上传他/她的Excel工作表时，InitPrice或FinalPrice列中都存在空值(当然，它不会用空值填充整列(，它会返回相同的页面，并返回消息：
导入数据时出错。消息：无法强制转换对象类型"System.DBNull"到类型"System.Double"的。
该异常指向CCD_ 11方法内的CCD_。
但是，当赋值为零值以替换null时，导入过程成功完成。
需要考虑的问题：
当
InitPrice或FinalPrice列在源DataTable上包含DBNull值时，如何在相应的IEnumerable成员上将默认值(零或null(分配为Nullable<decimal>？在不使用
Select方法声明每个目标列字段的情况下，如何将存储在DataTable中的现有字段用作批量追加的IEnumerable？如果不能，可以采取哪些变通办法？
我在如何使用SQL批量插入C#在SQL Server 2012中执行更新和插入并忽略重复的值(如果已经存在于数据库中(中寻找建议；大容量插入Sql Server数百万条记录，但这些问题使用普通的SqlBulkCopy而不是SqlBulkTools，或者使用存储过程进行大容量追加。
1。检查DBNull
由于使用了Field<T>(String(，这将导致对列的访问是强类型的。这行代码
InitPrice = (decimal)(x.Field<Nullable<double>>("InitPrice") != null ? x.Field<Nullable<double>>("InitPrice") : 0),
甚至在进行空比较之前就失败了，因为Field<T>(String(正试图将DBNull强制转换为可为Null的<加倍>，要安全地检查DBNull，请改用Item[String]，它返回一个Object，因此DBNull没有问题。
示例：
// convert DataTable into IEnumerable for bulk upsert
var productList = dt.AsEnumerable().Select(x => new Product()
{
SessionNo = x.Field<double>("SessionNo").ToString(),
ProductName = x.Field<String>("ProductName"),
Date = x.Field<DateTime>("Date"),
LotNumber = x.Field<String>("LotNumber"),
RegNumber = x.Field<String>("RegNumber"),
InitPrice = (decimal)(!DBNull.Value.Equals(x["InitPrice"]) ? x.Field<Nullable<double>>("InitPrice") : 0),
FinalPrice = (decimal)(!DBNull.Value.Equals(x["FinalPrice"]) ? x.Field<Nullable<double>>("FinalPrice") : 0),
TaxNote = x.Field<String>("TaxNote")
});
2.将DataTable转换为可枚举的SqlBulkTools可以直接读取
SqlBulkTools检查您提供的对象上的所有公共属性，并尝试将它们的名称转换为SQL查询中的列名。将DataTable更改为Enumerable将为您提供IEnumerable<DataRow>，在DataRow上，列不是属性，而是通过字符串索引或列索引访问的，因此SqlBulkTools无法找到它们。您可以使用类似AutoMapper的东西来代替Select语句，但无论如何，只要初始数据存储在DataTable中，您就必须以某种方式转换它，SqlBulkTools才能读取它。
完全避免DBNull
如果您根本不想处理DBNull，则必须放弃使用OleDbDataAdapter和DataTable。如果这是您的一个选项，我建议您使用EPPlus(请注意，版本5及更高版本需要许可证(。与列名相反，您必须使用行号和列号或单元格范围语法来访问数据，但当单元格中没有任何内容时，您将获得null而不是DBNull。