private void DeleteSettingForm_Load(object sender, EventArgs e) { try { using (profileContext db = new profileContext()) { var mainProfiles = db.MainProfile.Select(x => x).ToArray(); deleteLB.Items.AddRange(mainProfiles); } } catch (Exception ex) { MessageBox.Show(ex.Message); this.Close(); return; } switch (Type) { case "delete": { deleteLB.SelectionMode = SelectionMode.MultiExtended; break; } case "load": { deleteLB.SelectionMode = SelectionMode.One; break; } default: break; } }
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 }); } }
private void saveDBBtn_Click(object sender, EventArgs e)//+ { try { using (profileContext db = new profileContext()) { using (Microsoft.EntityFrameworkCore.Storage.IDbContextTransaction tr = db.Database.BeginTransaction()) { try { try { DataManipulationService.SaveProfileToDB(db, tr, Document); tr.Commit(); } catch (Exception ex) { tr.Rollback(); MessageBox.Show(ex.Message); return; } DataManipulationService.SaveResultToDB(db, tr, Document); } catch (Exception ex) { MainProfile mainProfile = db.MainProfile.SingleOrDefault(p => p.Name == Document.DocumentName); if (mainProfile != null) { db.MainProfile.Remove(mainProfile); db.SaveChanges(); } MessageBox.Show(ex.Message); return; } } } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public static void DeleteMainProfile(List <MainProfile> deletedMainProfiles) { using (profileContext db = new profileContext()) { using (var transaction = db.Database.BeginTransaction()) { try { foreach (var mainProfileItem in deletedMainProfiles) { db.MainProfile.Remove(mainProfileItem); } db.SaveChanges(); transaction.Commit(); } catch (Exception) { transaction.Rollback(); throw new Exception("При удалении произошла ошибка. Попытайтесь снова."); } } } }
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"); } }
public static void SaveProfileToDB(profileContext db, Microsoft.EntityFrameworkCore.Storage.IDbContextTransaction tr, Excel.ExcelDocument document) { Dictionary <string, QType> questionTypeMap = new Dictionary <string, QType>(); questionTypeMap = db.QType.Select(q => q).ToDictionary(q => q.Type, q => q); MainProfile mainProfile = db.MainProfile.SingleOrDefault(m => m.Name == document.DocumentName); if (mainProfile == null) { mainProfile = new MainProfile { Name = document.DocumentName }; } else { throw new Exception("Файл с таким именем уже был сохранен!"); } foreach (var profileItem in document.ProfilesListContent) { QType qType = null; if (questionTypeMap.ContainsKey(profileItem.Type)) { qType = questionTypeMap[profileItem.Type]; } else { qType = new QType { Type = profileItem.Type }; questionTypeMap.Add(qType.Type, qType); } Profile profile = mainProfile.Profile.SingleOrDefault(p => p.Name == profileItem.Name); if (profile == null) { profile = new Profile { SerialNumber = profileItem.Id, Name = profileItem.Name, Type = qType, Answer = profileItem.Answers, MainProfile = mainProfile }; mainProfile.Profile.Add(profile); } else { throw new Exception("В файле не может содержаться несколько анкет с одинаковым именем!"); } foreach (var questionItem in profileItem.Questions) { Question question = null; if (qType.Type == "range") { question = new Question { SerialNumber = questionItem.Id, Content = questionItem.Content, LeftLimit = questionItem.LeftLimit, RightLimit = questionItem.RightLimit }; } else { question = new Question { SerialNumber = questionItem.Id, Content = questionItem.Content, LeftLimit = null, RightLimit = null }; } profile.Question.Add(question); } } db.MainProfile.Add(mainProfile); db.SaveChanges(); Application.DoEvents(); }