c# - 使用 LINQ to Excel 将数据从 Excel 导入到 SQL Server 数据库

Question

我是 Linq to SQL 的新手，我想将 Excel 文件内容导入到我的 SQL 服务器数据库。

这是我的代码:

private void btnImport_Click(object sender, RoutedEventArgs e)
    {
        dbEntities = new BASUEntities();
        string pathToExcelFile = importFileName;
        var excelFile = new ExcelQueryFactory(pathToExcelFile);

        excelFile.AddMapping<UserInfo>(x => x.FirstName, "FName");
        excelFile.AddMapping<UserInfo>(x => x.LastName, "LName");
        excelFile.AddMapping<UserInfo>(x => x.NationalCode, "NatCode");
        excelFile.AddMapping<UserInfo>(x => x.EmploymentID, "EmpID");
        excelFile.AddMapping<UserInfo>(x => x.WorkUnit, "WorkUnit");
        excelFile.AddMapping<UserInfo>(x => x.JobOrdination, "JobOrd");
        excelFile.AddMapping<UserInfo>(x => x.Profession, "Profession");
        excelFile.AddMapping<UserInfo>(x => x.PostTitle, "PTitle");
        excelFile.AddMapping<UserInfo>(x => x.EmploymentType, "EmpType");
        excelFile.AddMapping<UserInfo>(x => x.PhoneNumber, "PhoneNo");

        excelFile.TrimSpaces = TrimSpacesType.Both;
        excelFile.ReadOnly = true;

        IQueryable<UserInfo> UserInfz = (from a in excelFile.Worksheet<UserInfo>()
            select a);

        foreach (UserInfo userInfo in UserInfz)
        {
            dbEntities.UserInfoes.Add(userInfo);
            dbEntities.SaveChanges();
        }
        LoadAllUsers(); //Load Users in DataGrid

    }

它工作了 55 行，然后我得到了这个错误:

A first chance exception of type 'System.Data.Entity.Validation.DbEntityValidationException' occurred in EntityFramework.dll

Additional information: Validation failed for one or more entities. See 'EntityValidationErrors' property for more details.

我的 excel 文件包含 700 多行。我认为这是内存问题!

我该如何解决这个问题？

Answer 1

第 56 行和第 200 行生成验证错误，因为这些行中的数据与 UserInfo 的定义不匹配

下面的代码会准确地告诉你这些行的问题是什么

foreach (UserInfo userInfo in UserInfz)
{
    dbEntities.UserInfoes.Add(userInfo);
    dbEntities.SaveChanges();
}
catch (DbEntityValidationException ex)
{
    StringBuilder sb = new StringBuilder();
    foreach (var eve in ex.EntityValidationErrors)
    {
        sb.AppendLine(String.Format("Entity of type '{0}' in state '{1}' has the following validation errors:", eve.Entry.Entity.GetType().Name, eve.Entry.State));
        foreach (var ve in eve.ValidationErrors)
        {
            sb.AppendLine(String.Format("- Property: '{0}', Error: '{1}'", ve.PropertyName, ve.ErrorMessage));
        }
    }
    throw new Exception(sb.ToString(), ex);
}