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