当作为IEnumerable执行批量插入/更新时，如何为DataTable中的DBNull值分配默认值

本文关键字：DataTable 中的 默认值 分配 DBNull 更新 执行 IEnumerable 插入 | 更新日期: 2023-09-27 18:11:42

背景

我开发了一个简单的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时，导入过程成功完成。

需要考虑的问题：

  1. 当InitPrice或FinalPrice列在源DataTable上包含DBNull值时，如何在相应的IEnumerable成员上将默认值(零或null(分配为Nullable<decimal>？

  2. 在不使用Select方法声明每个目标列字段的情况下，如何将存储在DataTable中的现有字段用作批量追加的IEnumerable？如果不能，可以采取哪些变通办法？

我在如何使用SQL批量插入C#在SQL Server 2012中执行更新和插入并忽略重复的值(如果已经存在于数据库中(中寻找建议；大容量插入Sql Server数百万条记录，但这些问题使用普通的SqlBulkCopy而不是SqlBulkTools，或者使用存储过程进行大容量追加。

当作为IEnumerable执行批量插入/更新时，如何为DataTable中的DBNull值分配默认值

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。

相关文章：