In this post , We will learn how to save excel records into database using stored procedure.
Now create two tables
- Brand_Master
2. Product_Master
Now create User-Defined-Table-Types as below
CREATE TYPE [dbo].[UDT_Brand] AS TABLE( [ProductId] [int] NOT NULL, [BrandID] [int] NOT NULL, [BrandName] [nvarchar](50) NULL, [ProductName] [nvarchar](50) NULL, [OpeningQty] [int] NULL, [PurchaseQty] [int] NULL, [SaleQty] [int] NULL, [BalanceQty] [int] NULL, [PurchaseRate] [float] NULL, [SaleRate] [float] NULL, [PurchaseValuation] [float] NULL, [Salevaluation] [float] NULL )
Now create ASP.NET MVC Entity Framework Project
Add tables in project
Now Add Controller
Now create stored procedure as below
Create or ALTER proc [dbo].[usp_InsertData](@BrandData UDT_Brand readonly)
AS
BEGIN
insert into [Brand-Master] select B.[BrandName] from @BrandData B LEFT JOIN [Brand-Master] BM on BM.BrandName = B.BrandName where BM.BrandName Is null;
insert into Product_Master
select
BM.BrandID,
B.[ProductName],
B.[OpeningQty],
B.[PurchaseQty],
B.[SaleQty],
B.[BalanceQty],
B.[PurchaseRate],
B.[SaleRate],
B.[PurchaseValuation],
B.[Salevaluation]
from
@BrandData B LEFT JOIN Product_Master P on P.ProductName = B.ProductName RIGHT JOIN [Brand-Master] BM on BM.BrandName = B.BrandName where P.ProductName Is null and B.BrandID = 0;
Update Product_Master
set
BrandID = P.BrandID,
OpeningQty = (B.BalanceQty + P.SaleQty) - P.PurchaseQty,
PurchaseQty = P.PurchaseQty,
SaleQty = P.SaleQty,
BalanceQty = B.BalanceQty,
PurchaseRate = B.PurchaseRate,
SaleRate = B.SaleRate,
PurchaseValuation = B.BalanceQty * B.PurchaseRate,
Salevaluation = B.BalanceQty * B.SaleRate
from
@BrandData B Join Product_Master P on P.ProductName = B.ProductName Where P.ProductName = B.ProductName;
end;
Add below code in controller
using LinqToExcel;
using System.Data;
using System.Data.Entity;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using UploadExcelSaveRecords.Models;
namespace UploadExcelSaveRecords.Controllers
{
public class Brand_MasterController : Controller
{
private ExcelEntities db = new ExcelEntities();
public ActionResult Index()
{
return View(db.Product_Master.Include(x => x.Brand_Master).ToList());
}
public ActionResult UploadFile()
{
return View();
}
[HttpPost]
public ActionResult UploadFile(HttpPostedFileBase FileUpload)
{
string data = "";
if (FileUpload != null)
{
if (FileUpload.ContentType == "application/vnd.ms-excel" || FileUpload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
string filename = FileUpload.FileName;
if (filename.EndsWith(".xlsx"))
{
string targetpath = Server.MapPath("~/Upload/DetailFormatInExcel/");
if (!Directory.Exists(targetpath))
{
Directory.CreateDirectory(targetpath);
}
FileUpload.SaveAs(targetpath + filename);
string pathToExcelFile = targetpath + filename;
string sheetName = "Sheet1";
var excelFile = new ExcelQueryFactory(pathToExcelFile);
var BrandProduct = from b in excelFile.Worksheet<DTOViewModel>(sheetName) select b;
var Data = BrandProduct.ToList();
db.usp_InsertData(Data);
return RedirectToAction("Index");
}
else
{
data = "This file is not valid format";
ViewBag.Message = data;
}
return RedirectToAction("Index");
}
else
{
data = "Only Excel file format is allowed";
ViewBag.Message = data;
return View();
}
}
else
{
if (FileUpload == null)
{
data = "Please choose Excel file";
}
ViewBag.Message = data;
return View();
}
}
}
}
Now add below code in DbContext
public virtual int usp_InsertData(List<DTOViewModel> Data)
{
DataTable dataTable = new DataTable();
var columns = new List<string>() { "ProductId","BrandID","BrandName", "ProductName", "OpeningQty", "PurchaseQty", "SaleQty", "BalanceQty", "PurchaseRate", "SaleRate", "PurchaseValuation", "Salevaluation" };
columns.ForEach(x => dataTable.Columns.Add(x));
foreach (var item in Data)
{
var dtRow = dataTable.NewRow();
dtRow["ProductId"] = item.ProductId;
dtRow["BrandID"] = item.BrandID;
dtRow["BrandName"] = item.BrandName;
dtRow["ProductName"] = item.ProductName;
dtRow["OpeningQty"] = item.OpeningQty = 0;
dtRow["PurchaseQty"] = item.PurchaseQty = 0;
dtRow["SaleQty"] = item.SaleQty = 0;
dtRow["BalanceQty"] = item.BalanceQty;
dtRow["PurchaseRate"] = item.PurchaseRate ?? 0;
dtRow["SaleRate"] = item.SaleRate ?? 0;
dtRow["PurchaseValuation"] = item.PurchaseValuation = 0;
dtRow["Salevaluation"] = item.Salevaluation = 0;
dataTable.Rows.Add(dtRow);
}
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ToString());
SqlCommand cmd = new SqlCommand("usp_InsertData", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@BrandData", dataTable);
con.Open();
int i = cmd.ExecuteNonQuery();
con.Close();
if (i >= 1)
{
return 0;
}
else
{
return 1;
}
}
Upload File View
@model IEnumerable<UploadExcelSaveRecords.Models.Brand_Master>
@{
ViewBag.Title = "UploadFile";
}
<h2>Upload File</h2>
<div align="center">
@if (ViewBag.Message != null)
{
<span class="alert alert-warning"> @ViewBag.Message</span>
}
<div align="right" class="btn btn-default">
@using (Html.BeginForm("UploadFile", "Brand_Master", FormMethod.Post, new { @enctype = "multipart/form-data" }))
{
<input type="file" id="fileUpload" class="btn btn-primary" name="FileUpload" /><br />
<input type="submit" class="btn btn-primary" name="UploadNewEmployee" id="fileUploadExcel" value="Upload" />
<a href="/Brand_Master/Index">
<input type="button" class="btn btn-default" value="Cancel" />
</a>
}
</div>
</div>
<script src="https://cdn.jsdelivr.net/npm/sweetalert2@10"></script>
<script src="http://code.jquery.com/jquery-1.11.0.min.js"></script>
<script>
$('#fileUploadExcel').click(function (e) {
if ($('#fileUpload').val() === "") {
Swal.fire({
icon: 'error',
title: 'Oops...',
text: 'Please select The File',
})
return false;
}
});
</script>
Now create Excel same as below
Now Upload File
Output:
