使用LINQ to Excel将数据从Excel导入SQL Server数据库

I'm newbie in Linq to SQL and I want to import an Excel file contents to my SQL server Database.

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

This is My Code :

这是我的代码:

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

    }

it worked for 55 rows and then i got this error :

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

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

EntityFramework.dll中出现'System.Data.Entity.Validation.DbEntityValidationException'类型的第一次机会异常

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

附加信息:一个或多个实体的验证失败。有关详细信息,请参阅“EntityValidationErrors”属性。

My excel file contains more than 700 rows. I think it's a Memory problem!

我的excel文件包含700多行。我认为这是一个记忆问题!

How can i solve this?

我怎么解决这个问题?

1 个解决方案

#1

Rows 56 and 200 generate a validation error because the data in those rows doesn't match the definition of UserInfo

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

The following code will tell you exactly what the issue with those rows is

以下代码将准确告诉您这些行的问题是什么

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);
}

#1