Import Excel file using .NET Core ASP.NET Web Application to SQL Server database

Question

I'm trying to import an Excel file into my code then transport it to a database using NET Core MVC web Application. I want to know any great libraries on Visual Studios and links that could be great help. Right now I'm using EPPlus version that grabs the data from the Excel file and converts it into a ToString.

Here some code I'm working on most of the work is in method import When I try to set the database and model to used anything else then string types I get errors like:

Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF

I tried int.Parse() but keep getting error. I tried doing to SQL Server running SET IDENTITY_INSERT [StudentsList-2].[dbo].[Student] ON; it tells me command ran successfully but I keep getting the error when I run the program. Any help or links would be great!

using ImportExcel.Models;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using Microsoft.Extensions.DependencyInjection;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;

namespace ImportExcel.Controllers
{
    public class HomeController : Controller
    {
        private readonly ApplicationDbContext _dbContext;

        public HomeController(ApplicationDbContext dbContext)
        {
           _dbContext = dbContext;
        }
        public IActionResult Index()
        {
            return View();
        }
        ***public async Task<List<Countries>> Import(IFormFile file)
        {
            var list = new List<Countries>();
            using (var stream = new MemoryStream())
            {
                await file.CopyToAsync(stream);
                using (var package = new ExcelPackage(stream))
                {

                    ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
                    var rowcount = worksheet.Dimension.Rows;
                    var colcount = worksheet.Dimension.Columns;

                    for (int row = 2; row < rowcount; row++)
                    {
                        list.Add(new Countries {
                            CountryId=worksheet.Cells[row,1].Value.ToString().Trim(),
                            CountryName = worksheet.Cells[row, 2].Value.ToString().Trim(),
                            TwoCharCountryCode = worksheet.Cells[row, 3].Value.ToString().Trim(),
                            ThreeCharCountryCode = worksheet.Cells[row, 4].Value.ToString().Trim()
                        });
                    }
                }
            }
            //SaveDataToDb(list);
            _dbContext.Countries.AddRange(list);
            await _dbContext.SaveChangesAsync();
            
            return list;
            

        }***

       

        public IActionResult About()
        {
            ViewData["Message"] = "Your application description page.";

            return View();
        }

        public IActionResult Contact()
        {
            ViewData["Message"] = "Your contact page.";

            return View();
        }

        public IActionResult Privacy()
        {
            return View();
        }

        [ResponseCache(Duration = 0, Location = ResponseCacheLocation.None, NoStore = true)]
        public IActionResult Error()
        {
            return View(new ErrorViewModel { RequestId = Activity.Current?.Id ?? HttpContext.TraceIdentifier });
        }
    }
}

Answer 1

Specifically about the problem you're having with the error, you have to run the command in-code to ensure it's on the same connection; it's not globally applied to all connections. You can do that using this:

await _dbContext.Database.ExecuteSqlCommandAsync(@"SET IDENTITY_INSERT [StudentsList-2].[dbo].[Student] ON");
await _dbContext.SaveChangesAsync();
await _dbContext.Database.ExecuteSqlCommandAsync(@"SET IDENTITY_INSERT [StudentsList-2].[dbo].[Student] OFF");