asp.net-mvc 使用 C# (ASP.NET MVC) 上传 csv 文件

Uploading csv file using C# (ASP.NET MVC)

提问by Spidey

I have a CSV file which contain the following:

我有一个包含以下内容的 CSV 文件：

ProductName,EmployeeID,EmployeeName,ContactNo,Adddress
iPad,1233,Tom,89897898,34 Pitt st
iPad,1573,Hyman,8978 9689,50 George st
iPad,1893,Peter,8878 8989,32 Martin st 

The following code will insert into one table. What I am trying to achieve is to insert into 2 tables:

以下代码将插入到一张表中。我想要实现的是插入 2 个表：

Product table (parent table)
ProductId(Pk), ProductName

Employee Table (child table)
EmployeeId(Pk), ProductId(fk), EmployeeName, ContactNo, Address

So I need to basically insert the record first into Product table and then into Employee table from the CSV file.

所以我需要首先将记录插入到 Product 表中，然后再从 CSV 文件中插入到 Employee 表中。

Controller.cs

控制器.cs

[HttpPost]
public ActionResult Index(HttpPostedFileBase FileUpload)
{
    // Set up DataTable place holder 

    Guid ProductId= Guid.NewGuid();
    using (SqlConnection conn = new SqlConnection(connString))
    {
        conn.Open();

        using (SqlCommand cmd = new SqlCommand(
               "INSERT INTO Product VALUES(" + "@ReferralListID,  @ProductName)", conn))
        {
            //Note product name need to read from csv file
            cmd.Parameters.AddWithValue("@ProductId", ProductId);
            cmd.Parameters.AddWithValue("@ProductName", ProductName); 

            int rows = cmd.ExecuteNonQuery();

            //rows number of record got inserted
        }
    }

    DataTable dt = new DataTable();

    //check we have a file 
    if (FileUpload.ContentLength > 0)
    {
        //Workout our file path
        string fileName = Path.GetFileName(FileUpload.FileName);
        string path = Path.Combine(Server.MapPath("~/App_Data/uploads"), fileName);

        //Try and upload
        try
        {
            FileUpload.SaveAs(path);
            //Process the CSV file and capture the results to our DataTable place holder
            dt = ProcessCSV(path);

            //Process the DataTable and capture the results to our SQL Bulk copy
            ViewData["Feedback"] = ProcessBulkCopy(dt);
        }
        catch (Exception ex)
        {
            //Catch errors
            ViewData["Feedback"] = ex.Message;
        }
    }
    else
    {
        //Catch errors
        ViewData["Feedback"] = "Please select a file";
    }

    //Tidy up
    dt.Dispose();

    return View("Index", ViewData["Feedback"]);
}

/// <summary>
/// Process the file supplied and process the CSV to a dynamic datatable
/// </summary>
/// <param name="fileName">String</param>
/// <returns>DataTable</returns>
private static DataTable ProcessCSV(string fileName)
{
    //Set up our variables 
    string Feedback = string.Empty;
    string line = string.Empty;
    string[] strArray;  
    DataTable dt = new DataTable();
    DataRow row;

    // work out where we should split on comma, but not in a sentance
    Regex r = new Regex(",(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))");

    //Set the filename in to our stream
    StreamReader sr = new StreamReader(fileName);

    //Read the first line and split the string at , with our regular express in to an array
    line = sr.ReadLine();
    strArray = r.Split(line);

    //For each item in the new split array, dynamically builds our Data columns. Save us having to worry about it.
    Array.ForEach(strArray, s => dt.Columns.Add(new DataColumn()));


    //Read each line in the CVS file until it's empty
    while ((line = sr.ReadLine()) != null)
    {
        row = dt.NewRow();

        //add our current value to our data row
        row.ItemArray = r.Split(line);
        dt.Rows.Add(row);
    }

    //Tidy Streameader up
    sr.Dispose();

    //return a the new DataTable
    return dt;


}

/// <summary>
/// Take the DataTable and using WriteToServer(DataTable) send it all to the database table "BulkImportDetails" in one go
/// </summary>
/// <param name="dt">DataTable</param>
/// <returns>String</returns>
private static String ProcessBulkCopy(DataTable dt)
{
    string Feedback = string.Empty;
    string connString = ConfigurationManager.ConnectionStrings["DataBaseConnectionString"].ConnectionString;

    //make our connection and dispose at the end    
    using(  SqlConnection conn = new SqlConnection(connString))
    {
        //make our command and dispose at the end
        using (var copy = new SqlBulkCopy(conn))
        {
            //Open our connection
            conn.Open();

            //Set target table and tell the number of rows
            copy.DestinationTableName = "Employee";
            copy.BatchSize = dt.Rows.Count;
            try
            {
                //Send it to the server
                copy.WriteToServer(dt);
                Feedback = "Upload complete";
            }
            catch (Exception ex)
            {
                Feedback = ex.Message;
            }
        }
    }

    return Feedback;
}

View.aspx

视图.aspx

<asp:Content ID="Content1" ContentPlaceHolderID="TitleContent" runat="server">
    Home Page
</asp:Content>

<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">

    <h2>CSV Bulk Upload</h2> 

    <% using (Html.BeginForm("","",FormMethod.Post, new {enctype="multipart/form-data"})){ %>

        <input type="file" name="FileUpload" />
        <input type="submit" name="Submit" id="Submit" value="Upload" />
    <% } %>

    <p><%= Html.Encode(ViewData["Feedback"]) %></p> 
</asp:Content>

Stored Procedure

存储过程

USE [BULkDatabase]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO



CREATE PROCEDURE [dbo].[InsertProdutInfo] 
(
   @ProductName varchar (50),  
   @EmployeeName varchar (50),
   @EmployeeAddress varchar (50)
)

AS


BEGIN TRAN

   update [dbo.Product] 
   set    [ProductName] = @ProductName
   where  [ProductName] = @ProductName;

   -- get product id
   select ProductId = [ProductId] 
   from   [dbo.Product]
   where  [ProductName] = @ProductName;  

   if @@rowcount = 0
   BEGIN TRAN

      DECLARE @ProductId uniqueidentifier
      -- there's no such product, let's create it
      insert into [dbo.Product]
      values (NEWID(),@ProductName);

      select @ProductId = SCOPE_IDENTITY()
   end

   -- now that we know we have added the product and have the id, let's add the rest
   insert into [dbo.Employees]
   values (NEWID(), @EmployeeName, @EmployeeAddress, @ProductId);

COMMIT TRAN

回答by balexandre

first of all you should decouple the Controller from your database code, just simply create a new Class project and host all database access there, so you can have in your Controller something like:

首先，您应该将控制器与数据库代码分离，只需简单地创建一个新的类项目并在那里托管所有数据库访问，这样您就可以在控制器中拥有如下内容：

[HttpPost]
public ActionResult UploadFile(HttpPostedFileBase FileUpload)
{
    if (FileUpload.ContentLength > 0) {
        // there's a file that needs our attention
        var success = db.UploadProductFile(FileUpload);

        // was everything ok?
        if (success)
            return View("UploadSuccess");
        else
            return View("UploadFail");
    }

    return RedirectToAction("Index", new { error = "Please upload a file..." });
}

public ActionResult Index(string error)
{
    ...
}

This way, the controller does not really care what do you do with the uploaded file as it's not the Controllerconcernto know such thing, it has the task to know that it needs to delegate that job and process the result, that's it.

这样，控制器并不真正关心你对上传的文件做了什么，因为它不Controller关心知道这样的事情，它的任务是知道它需要委派那个工作并处理结果，就是这样。

Please see that the action method is called UploadFileand not Index. It's not a good practice to post to the same action to avoid, when the user refreshes the page, post it again.

请注意 action 方法被调用UploadFile而不是Index。发布到相同的操作并不是一个好习惯，以避免在用户刷新页面时再次发布。

I also suggest you to use ADO.NET Entity Model, there are plenty of Videos out there, in ASP.NET website as well, and it will greatly help you use the database in a simpler and clean way.

我还建议您使用ADO.NET Entity Model，那里有很多视频，在 ASP.NET 网站中也是如此，它将极大地帮助您以更简单和干净的方式使用数据库。

back to your question... Inside your Database class, the method UploadProductFileshould be something like, and assuming that you don't have more than 200 records to process it's better to use the memory to deal with the filerather than spend time to save and read again (for more, you should save the file and process it, like you already do):

回到你的问题......在你的数据库类中，该方法UploadProductFile应该是这样的，假设你没有超过200条记录来处理最好使用内存来处理文件而不是花时间来保存并再次阅读（更多，你应该保存文件并处理它，就像你已经做的那样）：

private bool UploadProductFile(HttpPostedFileBase FileUpload)
{
    // get the file stream in a readable way
    StreamReader reader = new StreamReader(FileUpload.InputStream);

    // get a DataTable representing the passed string
    System.Data.DataTable dt = ProcessCSV(reader.ReadToEnd()); 

    // for each row, compose the statement
    bool success = true;
    foreach (System.Data.DataRow row in dt.Rows)
        success = db.InsertProdutInfo(row);

    return success;
}

the method InsertProdutInfowould fire a store procedurethat would be something like:

该方法InsertProdutInfo将触发一个类似于以下内容的存储过程：

declare @product_key int

begin tran

   update [tbl_products] 
   set    [name] = @product_name, [last_update] = getdate()
   where  [name] = @product_name;

   -- get product id
   select @product_key = [id] 
   from   [tbl_products]
   where  [name] = @product_name;  

   if @@rowcount = 0
   begin
      -- there's no such product, let's create it
      insert into [tbl_products] (name, last_update)
      values (@product_name, getdate());

      select @product_key = SCOPE_IDENTITY()
   end

   -- now that we know we have added the product and have the id, let's add the rest
   insert into [tbl_Employees] (id, product_id, name, contact, address)
   values (@employee_id, @product_key, @employee_name,
           @employee_contact, @employee_address);

commit tran

this way you will have everything you need.

这样，您将拥有所需的一切。