3

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 });
        }
    }
}
2
  • 1
    Suggest "I want to know any great libraries on Visual Studios and links that could be great help" should be it's own question on softwarerecs.stackexchange.com Commented Mar 10, 2021 at 1:19
  • @Richard Brescher, can you provide the database design? I think it may be the key for your problem. Commented Mar 11, 2021 at 5:42

1 Answer 1

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");
Sign up to request clarification or add additional context in comments.

7 Comments

binderbound Thanks! Is there any links or websites where I can learn more!
I'm not sure what you want to know more about?
This talks about scope for identity insert: stackoverflow.com/questions/5791941/…
binderbound Thanks again! also does await _dbContext.Database.ExecuteSqlCommandAsync(@"SET IDENTITY_INSERT [StudentsList-2].[dbo].[Student] ON"); go before of after _dbContext.Student.AddRange(list); ?
I don't think it would matter, but, before would be safer I guess
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.