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