How to upload excel to database in MVC – Part 2

Welcome to the part two of Uploading excel data to database. In case you have missed what was done, you can check out the part one of this post here

Remember that I promised we need to persist the uploaded file data into your database.

Okay let’s go.

Assuming your uploaded data looks like this one

SampleData

Paste the code below in your SQL Server Management Studio:

Create Table Animal (
ID Int Identity(1, 1) Not Null,
Name NVarchar(20) Not Null,
Age Int Not Null,
Constraint PK_Animal Primary Key (ID Asc),
Constraint UQ_Animal_Name Unique (Name)
)
Go

Now right-click on the Models folder and choose Add -> New Item -> ADO.NET Entity Data Model as shown below

AddEDMX

Now, click OK button

Select Generate from database and click Next

Select or create a new connection string to your database and click Next

Select the (Animal) table you just created and click Finish

In your HomeController, add the following code below the last using statement:


using UploadExcelToDatabase.Models;

 

Now replace the initial UploadSample method in the HomeController with the following:


[HttpPost]
public ActionResult UploadSample(HttpPostedFileBase uploadedFile)
{
string operationMessage = string.Empty;
try
{
DataTable table = GetUploadedFileData(uploadedFile);
if (table != null)
{
if (table.Rows.Count > 0)
{
using (DemoEntities dataContext = new DemoEntities())
{
foreach (DataRow row in table.Rows)
{
if (!row.IsNull("Name") && !row.IsNull("Age"))
{
// any other forms of validation can still be done here based on your requirements
Animal animal = new Animal()
{
Name = HttpUtility.HtmlEncode(row["Name"].ToString().Trim()),
Age = int.Parse(HttpUtility.HtmlEncode(row["Age"].ToString()))
};
dataContext.Animals.Add(animal);
}
}
dataContext.SaveChanges();
operationMessage = "Operation completed successfully";
}
}
else
{
operationMessage = "Nothing was uploaded";
}
}
else
{
operationMessage = "No data found for upload";
}
}
catch (Exception ex)
{
operationMessage = ex.Message;
}
ViewBag.OperationMessage = operationMessage;
return View();
}

Below should be your output if all goes well:

App Result
DB Result

Happy programming!

Save

Save

Save

Leave a Reply

Your email address will not be published. Required fields are marked *