c# - 上传 Excel 工作表并将数据导入 SQL Server Express 数据库

Question

我正在使用我发现的帖子中的示例。这似乎很适合我正在做的事情。这是保存文件的代码:

[HttpPost]
public ActionResult FileUpload(HttpPostedFileBase excelFile)
{
   //Save the uploaded file to the disc.
   string savedFileName = "~/App_Data/uploads/";// +excelFile.FileName;
   string filePath = Path.Combine(savedFileName, excelFile.FileName); 
   excelFile.SaveAs(Server.MapPath(filePath));

   return View();
}

下面是将数据放入数据库表的代码:

private void SaveFileToDatabase(string savedFileName)
{
    String strConnection = "Data Source=.\\SQLEXPRESS;AttachDbFilename='catalog=QQAEntities'Integrated Security=SSPI;User Instance=True";
    String connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;", Server.MapPath(savedFileName));
    {
    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
       using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM [dbo_ts_quality_audit_tbl$]", connection))
       {
          connection.Open();

          using (OleDbDataReader dReader = cmd.ExecuteReader())
          {
             using (SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection))
             {
                 //Give your Destination table name 
                 sqlBulk.DestinationTableName = "AuditSchedules";
                 sqlBulk.WriteToServer(dReader);
             }
          }
       }
       }
   }
}

private string GetLocalFilePath(string saveDirectory, FileUpload fileUploadControl)
{
   //System.Web.UI.WebControls.WebControl
   string filePath = Path.Combine(saveDirectory, fileUploadControl.FileName);

   fileUploadControl.SaveAs(filePath);

   return filePath;
}

这是 FileUpload 的 View :

<h2>FileUpload</h2>

@using (Html.BeginForm("FileUpload", "Admin", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
<input type="file" id="excelFile" name="excelFile" />
<input type="submit" value="Upload" />
}

上传有效。我的问题是；如何在 View 中调用数据传输 (SaveFileToDatabase)？这一切都将在管理部分完成。

此外，我已经在配置中连接到数据库 - 我该如何清理它？

谢谢

Answer 1

View 应该获取文件信息，然后将所有执行传递给 Controller ​​。在 Controller 内部，您应该调用 SaveFileToDatabase 函数并将您保存的临时文件传递给它，如下所示:

[HttpPost]
public ActionResult FileUpload(HttpPostedFileBase excelFile)
{
    //Save the uploaded file to the disc.
    string savedFileName = "~/App_Data/uploads/";// +excelFile.FileName;
    string filePath = Path.Combine(savedFileName, excelFile.FileName); 
    excelFile.SaveAs(Server.MapPath(filePath));

    // Call function to place temporary file into database
    SaveFileToDatabase(filePath);

    // Optional: Delete temporary Excel file from server

    return View();
}

(不要忘记考虑是否要在服务器上保留临时 Excel 文件。如果不是，则需要在知道处理完成后注意删除文件)