使用LinqToCsv - . net MVC 4 -实体框架将CSV文件写入Sql数据库

本文关键字：文件 CSV Sql 数据库 框架 net LinqToCsv MVC 实体 使用 | 更新日期: 2023-09-27 18:04:13

我试图创建一个应用程序，其中用户可以将。csv文件上传到我创建的SQL数据库中。当涉及到实际从视图中获取文件路径并将其写入数据库时，我已经变得有点困惑了。

首先，这是我正在研究的模型:

    public class OutstandingCreditCsv
{
    [CsvColumn(FieldIndex = 1, CanBeNull = false)]
    public string PoNumber { get; set; }
    [CsvColumn(FieldIndex = 2, OutputFormat = "dd MMM HH:mm:ss")]
    public DateTime CreditInvoiceDate { get; set; }
    [CsvColumn(FieldIndex = 3)]
    public string CreditInvoiceNumber { get; set; }
    [CsvColumn(FieldIndex = 4, CanBeNull = false, OutputFormat = "C")]
    public decimal CreditInvoiceAmount { get; set; }
}

这里是目前为止的控制器代码:

        public ActionResult Index()
    {
        CsvFileDescription inputFileDescription = new CsvFileDescription
        {
            SeparatorChar = ',',
            FirstLineHasColumnNames = true
        };
        var context = new CsvContext();
        IEnumerable<OutstandingCreditCsv> csvList =
        context.Read<OutstandingCreditCsv>("C:/Users/BlahBlah/Desktop/CsvUploadTestFile.csv", inputFileDescription);
        foreach (OutstandingCreditCsv line in csvList)
        {
        }
        return View();
    }

有两个方面我需要一点指导。我不确定如何将文件从视图传递到控制器，假设我的视图是这样简单的:

<form action="" method="POST" enctype="multipart/form-data">
<table style="margin-top: 150px;">
    <tr>
        <td>
            <label for="file">Filename:</label>
        </td>
        <td>
            <input type="file" name="file" id="file"/>
        </td>
        <td><input type="submit" value="Upload"/></td>
    </tr>
</table>
</form>

我也不确定我将如何实际循环csv数据到我的数据库。你可以看到我的控制器中的foreach回路是空的。任何帮助都会很感激。谢谢!

使用LinqToCsv - . net MVC 4 -实体框架将CSV文件写入Sql数据库

我正在编辑我的帖子来回答我错过的部分问题。您在这里所做的是将csv文件上传到临时位置，将其读入对象，然后如果您愿意，您可以从临时位置删除csv文件(未显示)。

[HttpPost]
public JsonResult UploadValidationTable(HttpPostedFileBase csvFile)
{
    CsvFileDescription inputFileDescription = new CsvFileDescription
    {
         SeparatorChar = ',',
         FirstLineHasColumnNames = true
    };
    var cc = new CsvContext();
    string filePath = uploadFile(csvFile.InputStream);
    var model = cc.Read<OutstandingCreditCsv>(filePath, inputFileDescription);
    //if your csv has several rows convert it to a list of your model
    //var model = cc.Read<List<OutstandingCreditCsv>>(filePath, inputFileDescription);
   //then you can loop through and do the same as below
   /*foreach(var row in model)
   {
        var invoice = row.CreditInvoiceNumber;
   }*/
    try
    {
        //do what you need here, like save items to database
        var invoice = model.CreditInvoiceNumber;
        var invoiceTable = yourContext.yourTable
            .FirstOrDefault(x => x.yourTableID == passedInId);
       invoiceTable.CreditInvoiceNumber = model.CreditInvoiceNumber;
       yourContext.SaveChanges();
    }
    catch(LINQtoCSVException ex)
    {
    }
    return Json(model, "text/json");
}
private string uploadFile(Stream serverFileStream)
{
    string directory = "~/Content/CSVUploads";
    bool directoryExists = System.IO.Directory.Exists(Server.MapPath(directory));
    if (!directoryExists)
    {
        System.IO.Directory.CreateDirectory(Server.MapPath(directory));
    }
    string targetFolder = Server.MapPath(directory);
    string filename = Path.Combine(targetFolder, Guid.NewGuid().ToString() + ".csv");
    try
    {
        int length = 256; //todo: replace with actual length
        int bytesRead = 0;
        Byte[] buffer = new Byte[length];
        // write the required bytes
        using (FileStream fs = new FileStream(filename, FileMode.Create))
        {
            do
            {
                bytesRead = serverFileStream.Read(buffer, 0, length);
                fs.Write(buffer, 0, bytesRead);
            }
            while (bytesRead == length);
        }
        serverFileStream.Dispose();
        return filename;
    }
    catch (Exception ex)
    {
        return string.Empty;
    }
}
相关文章：