Background
I have developing a simple MVC 5 application which able to import Excel file to a SQL Server 2012 database with Entity Framework 6 and SqlBulkTools (Github). The code structure has shown below.
Model (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)
}
Controller (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");
}
}
View (Import.cshtml)
@{
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>
Problem Statement
The application imports data inside Excel worksheet into a DataTable which targeting Product table with bulk upsert procedure (update if existing data found, insert if no matching data present).
The Excel worksheet table structure is exactly same as database table and model class has, however the values are submitted by someone else thus I can't alter worksheet contents, and possibly InitPrice and FinalPrice column has empty value, which likely translated as DBNull. All other numeric values are treated as double.
When any data entry person uploaded his/her Excel worksheet through Import page with null value(s) exist on either InitPrice or FinalPrice column (of course it doesn't fill entire column with nulls), it returned same page with message:
An error occurred when importing data. Message: Unable to cast object of type 'System.DBNull' to type 'System.Double'.
which exception pointed to InitPrice or FinalPrice assignment inside Select method.
However when zero values assigned substituting nulls, the import process completed successfully.
Questions to consider with:
How to assign default value (zero or null) as
Nullable<decimal>on correspondingIEnumerablemember(s) when eitherInitPriceorFinalPricecolumn containsDBNullvalue on sourceDataTable?How can I use existing fields stored in
DataTableasIEnumerablefor bulk upsert without declaring every target column fields usingSelectmethod? If can't, which workarounds can be done?
I had looking for suggestions in How to perform Update and Insert in SQL Server 2012 using SQL Bulk insert C# and ignore the duplicate values if already present in database & Bulk Insert Sql Server millions of record, but those problem uses plain SqlBulkCopy instead of SqlBulkTools or bulk upsert with stored procedure.
