public static ExcelDocument LoadMainProfileDB(MainProfile mainProfile) { List <ExcelResult> answerListContent = new List <ExcelResult>(); List <ExcelProfile> profilesListContent = new List <ExcelProfile>(); using (profileContext db = new profileContext()) { mainProfile = db.MainProfile.SingleOrDefault(p => p == mainProfile); db.Entry(mainProfile).Collection(t => t.Profile).Load(); db.Entry(mainProfile).Collection(t => t.Questioned).Load(); foreach (var profileItem in mainProfile.Profile) { db.Entry(profileItem).Collection(t => t.Question).Load(); db.Entry(profileItem).Collection(t => t.Result).Load(); db.Entry(profileItem).Reference(t => t.Type).Load(); List <ExcelQuestion> questions = new List <ExcelQuestion>(); foreach (var questionItem in profileItem.Question) { if (questionItem.LeftLimit == null && questionItem.RightLimit == null) { questions.Add(new ExcelQuestion { Id = questionItem.SerialNumber, Content = questionItem.Content, LeftLimit = "", RightLimit = "" }); } else { questions.Add(new ExcelQuestion { Id = questionItem.SerialNumber, Content = questionItem.Content, LeftLimit = questionItem.LeftLimit, RightLimit = questionItem.RightLimit }); } } ExcelProfile excelProfile = new ExcelProfile { Id = profileItem.SerialNumber, Answers = profileItem.Answer, Name = profileItem.Name, Type = profileItem.Type.Type, Questions = questions }; profilesListContent.Add(excelProfile); foreach (var resultItem in profileItem.Result) { string questionedId = mainProfile.Questioned.SingleOrDefault(q => q.Id == resultItem.QuestionedId).Number; ExcelResult excelResult = new ExcelResult { Id = questionedId, ProfileNum = resultItem.Profile.SerialNumber, QuestionNum = resultItem.Question.SerialNumber, Answer = resultItem.Answer }; answerListContent.Add(excelResult); } } return(new ExcelDocument { DocumentName = mainProfile.Name, AnswerListContent = answerListContent, ProfilesListContent = profilesListContent }); } }
public static void SaveResultToDB(profileContext db, Microsoft.EntityFrameworkCore.Storage.IDbContextTransaction tr, Excel.ExcelDocument document) { MainProfile mainProfile = db.MainProfile.SingleOrDefault(m => m.Name == document.DocumentName); if (mainProfile == null) { throw new Exception("Ошибка при сохранении!"); } List <Profile> profiles = db.Profile.Where(p => p.MainProfile == mainProfile).ToList(); foreach (var profileItem in profiles) { db.Entry(profileItem).Collection(t => t.Question).Load(); } Dictionary <string, Questioned> questionedMap = new Dictionary <string, Questioned>(); DataTable questionedDT = new DataTable(); questionedDT.Columns.Add(new DataColumn("id")); questionedDT.Columns.Add(new DataColumn("number")); questionedDT.Columns.Add(new DataColumn("main_profile_id")); foreach (var resultItem in document.AnswerListContent) { if (!questionedMap.ContainsKey(resultItem.Id)) { questionedMap.Add(resultItem.Id, new Questioned { Number = resultItem.Id, MainProfile = mainProfile }); DataRow row = questionedDT.NewRow(); row["id"] = null; row["number"] = resultItem.Id; row["main_profile_id"] = mainProfile.Id; questionedDT.Rows.Add(row); } } BulkWriteToDB(questionedDT, "questioned"); Application.DoEvents(); questionedMap = db.Questioned.Where(q => q.MainProfile == mainProfile).ToDictionary(q => q.Number, q => q); DataTable resultDT = new DataTable(); resultDT.Columns.Add(new DataColumn("id")); resultDT.Columns.Add(new DataColumn("profile_id")); resultDT.Columns.Add(new DataColumn("question_id")); resultDT.Columns.Add(new DataColumn("answer")); resultDT.Columns.Add(new DataColumn("questioned_id")); foreach (var resultItem in document.AnswerListContent) { Profile profile = profiles.SingleOrDefault(p => p.SerialNumber == resultItem.ProfileNum); Question question = profile.Question.SingleOrDefault(q => q.SerialNumber == resultItem.QuestionNum); Questioned questioned = questionedMap[resultItem.Id]; DataRow row = resultDT.NewRow(); row["id"] = null; row["profile_id"] = profile.Id; row["question_id"] = question.Id; row["answer"] = resultItem.Answer; row["questioned_id"] = questioned.Id; resultDT.Rows.Add(row); if (resultDT.Rows.Count > 30000) { BulkWriteToDB(resultDT, "result"); resultDT.Rows.Clear(); Application.DoEvents(); } } if (resultDT.Rows.Count != 0) { BulkWriteToDB(resultDT, "result"); } }