How To Save Excel Records Into Database Using Stored Procedure In ASP.NET MVC

In this post , We will learn how to save excel records into database using stored procedure.

Now create two tables

Brand_Master

Brand_Master Table

2. Product_Master

Product_Master Table

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

Excel Records