In this post , We will learn how to save excel records into database using stored procedure.
Now create two tables
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:
In this article, we have to show Create and Used PIPE in angular
In this article, we have to show Create and Used PIPE in angular
In this article, we have to show Create and Used PIPE in angular