c# - 使用 MVC C# 将 Excel 数据导入 SQL Server 数据库中的多个表
问题描述
我有两个表(qf_schoolQuestion
,qf_schoolOption
)qf_schoolOption
表,其中外键是表questionId
中的主键qf_schoolQuestion
。
TABLES:
qf_schoolQuestion | qf_schoolOption
----------------------------------
questionId | schoolOptionId
questionTitle | questionId
etc columns | firstChoice
| secondChoice
| thirdChoice
| fourthChoice
我想将 Excel 数据批量插入两个表,但问题是我的代码将在两个表中插入记录,但是当数据插入时qf_schoolQuestion
,它会插入所有数据,但是当它在另一个表中插入记录时,即qf_schoolOption
出现问题。 ..
问题是假设我们在excel表中有两条记录,然后在f_schoolQuestion
表中插入数据显示两条记录有两个生成的主键,但是当数据插入另一个表即qf_schoolOption
表时,它将向sql server输入4条记录,假设生成了id按qf_schoolQuestion
表是'2044','2045',然后qf_schoolOption
表将插入从excel表到'2044'ID的两行选择和'2045'ID的两个记录。
HttpPost:
public JsonResult SchoolQuesImport(HttpPostedFileBase fileUpload)
{
List<string> data = new List<string>();
if (fileUpload != null)
{
if (fileUpload.ContentType == "application/vnd.ms-excel" || fileUpload.ContentType == "application/octet-stream" || fileUpload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
string filename = fileUpload.FileName;
string targetpath = Server.MapPath("~/UploadContent/");
fileUpload.SaveAs(targetpath + filename);
string pathToExcelFile = targetpath + filename;
var connectionString = "";
if (filename.EndsWith(".xls"))
{
connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0; HDR=YES", pathToExcelFile);
}
else if (filename.EndsWith(".xlsx"))
{
connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", pathToExcelFile);
}
//"Xml;HDR=YES;IMEX=1\";
var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);
var ds = new DataSet();
adapter.Fill(ds, "ExcelTable");
DataTable dtable = ds.Tables["ExcelTable"];
string sheetName = "Sheet1";
var excelFile = new ExcelQueryFactory(pathToExcelFile);
var artistAlbums = from a in excelFile.Worksheet<qf_schoolQuestion>(sheetName) select a;
var parentartistalbum = from b in excelFile.Worksheet<qf_schoolOption>(sheetName) select b;
foreach (var a in artistAlbums)
{
try
{
if (a.questionTitle != "" && a.questionLevel != "" && a.questionLang != "")
{
qf_schoolQuestion sq = new qf_schoolQuestion();
#region School Question
//sq.questionId = a.questionId;
sq.examTypeId = a.examTypeId;
sq.examCategoryId = a.examCategoryId;
sq.examSubCategoryId = a.examSubCategoryId;
sq.examSubjectId = a.examSubjectId;
sq.questionLang = a.questionLang;
sq.questionLevel = a.questionLevel;
sq.questionRefBook = a.questionRefBook;
sq.questionTitle = a.questionTitle;
sq.questionYear = a.questionYear;
sq.createdDate = DateTime.Now;
sq.createdBy = User.Identity.Name;
#endregion
context.qf_schoolQuestion.Add(sq);
context.SaveChanges();
foreach (var b in parentartistalbum)
{
try
{
if (b.firstChoice != "")
{
qf_schoolOption so = new qf_schoolOption();
#region School Options
so.questionId = sq.questionId;
so.firstChoice = b.firstChoice;
so.secondChoice = b.secondChoice;
so.thirdChoice = b.thirdChoice;
so.fourthChoice = b.fourthChoice;
#endregion
context.qf_schoolOption.Add(so);
context.SaveChanges();
}
else
{
data.Add("<ul>");
if (b.firstChoice == "" || b.secondChoice == null) data.Add("<li>Choices are required.</li>");
data.Add("</ul>");
data.ToArray();
return Json(data, JsonRequestBehavior.AllowGet);
}
}
catch (DbEntityValidationException ex)
{
foreach (var entityValidationErrors in ex.EntityValidationErrors)
{
foreach (var validationError in entityValidationErrors.ValidationErrors)
{
Response.Write("Property: " + validationError.PropertyName + " Error: " + validationError.ErrorMessage);
}
}
}
}
}
else
{
data.Add("<ul>");
if (a.questionLang == "" || a.questionLang == null) data.Add("<li>Question Language is required.</li>");
data.Add("</ul>");
data.ToArray();
return Json(data, JsonRequestBehavior.AllowGet);
}
}
catch (DbEntityValidationException ex)
{
foreach (var entityValidationErrors in ex.EntityValidationErrors)
{
foreach (var validationError in entityValidationErrors.ValidationErrors)
{
Response.Write("Property: " + validationError.PropertyName + " Error: " + validationError.ErrorMessage);
}
}
}
}
//deleting excel file from folder
if ((System.IO.File.Exists(pathToExcelFile)))
{
System.IO.File.Delete(pathToExcelFile);
}
return Json("success", JsonRequestBehavior.AllowGet);
}
else
{
//alert message for invalid file format
data.Add("Only Excel file format is allowed");
data.ToArray();
return Json(data, JsonRequestBehavior.AllowGet);
}
}
else
{
if (fileUpload == null) data.Add("Please choose Excel file");
data.ToArray();
return Json(data, JsonRequestBehavior.AllowGet);
}
}
解决方案
老实说,问题的描述不是很清楚,但我会尝试提出一个解决方案:在我看来,您首先必须在表中插入所有记录qf_schoolQuestion
,然后再插入所有记录表qf_schoolOption
;这样,您应该避免记录重复。
推荐阅读
- html - 我应该如何以惯用的 React 方式访问自定义 HTML 组件的生成子项?
- django - 在 Docker 容器中努力解决 Django 和 Postgres 之间的连接问题
- java - JavaFX Animation Timer 类不以 60 fps 为上限
- javascript - 改变常量值的排序方法
- javascript - 根据 JavaScript 对象的键创建数组
- apache-spark - 初始作业未接受 AWS EMR 中的任何资源
- javascript - PHP 邮件程序没有从一个字段发送输入(一个变量不起作用)
- spring - 如何水平扩展 Spring Security OAuth2 客户端?
- angular-cli - ng build --prod 构建应用程序的过时版本
- javascript - 如何在 Dojo 定义中加载非 js 文件