Importing data from Excel to SQL Server DB using LINQ to Excel
I am new to Linq to SQL and I want to import the contents of an Excel file into a SQL Server database.
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 lines and then I got this error:
First accidental exception of type "System.Data.Entity.Validation.DbEntityValidationException" in EntityFramework.dll
Additional information: Failed to check for one or more objects. For more information, see EntityValidationErrors Property.
My excel file contains over 700 lines. I think this is a memory problem!
How can I solve this?
+3
source to share
1 answer
Lines 56 and 200 generate a validation error because the data in these lines does not match the definition UserInfo
The following code will tell you what the problem is with these lines
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);
}
0
source to share