使用C#(ASP.NET MVC)上传CSV文件 [英] Uploading csv file using C# (ASP.NET MVC)
问题描述
我有一个包含以下内容的CSV文件:
I have a CSV file which contain the following:
ProductName,EmployeeID,EmployeeName,ContactNo,Adddress
iPad,1233,Tom,89897898,34 Pitt st
iPad,1573,Jack,8978 9689,50 George st
iPad,1893,Peter,8878 8989,32 Martin st
以下代码将插入到一个表中.我要实现的目标是插入2个表中:
The following code will insert into one table. What I am trying to achieve is to insert into 2 tables:
Product table (parent table)
ProductId(Pk), ProductName
Employee Table (child table)
EmployeeId(Pk), ProductId(fk), EmployeeName, ContactNo, Address
因此，我基本上需要先将记录插入CSV文件中的Product表中，然后再插入Employee表中.
So I need to basically insert the record first into Product table and then into Employee table from the CSV file.
Controller.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
<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>
存储过程
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
推荐答案
首先，您应该将Controller与数据库代码分离，只需简单地创建一个新的Class项目并托管所有数据库访问权限，这样您就可以在其中控制器类似:
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)
{
...
}
这样，控制器就不会真正在意您对上传文件的处理方式，因为知道这一点不是Controller关注点，它的任务是知道它需要委托该工作并处理结果，就是这样.
This way, the controller does not really care what do you do with the uploaded file as it's not the Controller concern to know such thing, it has the task to know that it needs to delegate that job and process the result, that's it.
请注意，该操作方法称为UploadFile，而不是Index.最好避免发布相同的操作，以免在用户刷新页面时再次发布该页面.
Please see that the action method is called UploadFile and 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.
我还建议您使用 ADO.NET实体模型，ASP.NET网站中也有很多视频，它将大大帮助您简化数据库的使用.干净的方式.
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.
回到您的问题...在数据库类中，方法UploadProductFile应该类似于以下内容，并假设您要处理的记录不超过200条，最好使用
back to your question... Inside your Database class, the method UploadProductFile should 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 file rather than spend time to save and read again (for more, you should save the file and process it, like you already do):
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;
}
方法InsertProdutInfo将触发存储过程，该过程类似于:
the method InsertProdutInfo would fire a store procedure that would be something like:
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.
这篇关于使用C#(ASP.NET MVC)上传CSV文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
