private void ReportMajor_Load(object sender, EventArgs e)
{
using (var context = new Entities())
{
var res = from candidate in context.ReportViews
where candidate.AdmittingMajorSubjectCombinationID == null
select candidate;
gridControl.DataSource = res.ToList();
}
}
private void loadDataToGrid()
{
using (var context = new Entities())
{
var res = from data in context.ReportViews
where data.AdmittingMajorSubjectCombinationID != null
select data;
pivotGridControl.DataSource = res.ToList();
}
}
private int BeneficiaryIDFromName(string beneficiaryName)
{
using (var entities = new Entities())
{
var beneficiaryIDQuery = from beneficiary in entities.Beneficiaries
where beneficiary.Name == beneficiaryName
select beneficiary.ID;
var beneficiaryID = ValidateAndGetFirstItem(beneficiaryIDQuery);
return beneficiaryID;
}
}
private void AdmissionList_Load(object sender, EventArgs e)
{
using (var context = new Entities())
{
var res = from candidate in context.Candidates
join majorsubjectconbination in context.MajorSujectCombinationsViews
on candidate.AdmittingMajorSubjectCombinationID equals majorsubjectconbination.ID
where candidate.AdmittingMajorSubjectCombinationID.HasValue
select new { CandidateID = candidate.CandidateID,candidate.AdmissionMark, majorName = majorsubjectconbination.Name };
gridControl.DataSource = res.ToList();
}
}
public void ImportCSV(string candidatesFilePath, string optionsFilePath)
{
using (var entities = new Entities())
{
entities.Database.ExecuteSqlCommand("TruncateEverything");
}
var candidatesData = DataTableFromCSV(candidatesFilePath);
ImportCandidatesData(candidatesData);
var optionsData = DataTableFromCSV(optionsFilePath);
ImportOptionsData(optionsData);
}
private void ValidateSubjectNames(string[] validSubjectNames)
{
using (var entities = new Entities())
{
var subjectNames = from subject in entities.Subjects
orderby subject.ID
select subject.SubjectName;
bool dataIsvalid = NamesAreValid(subjectNames, validSubjectNames);
if (!dataIsvalid)
throw new FormatException("Subject name mismatched.");
}
}
private int RegionIDFromName(string regionName)
{
using (var entities = new Entities())
{
var regionIDQuery = from region in entities.Regions
where region.Name == regionName
select region.ID;
var regionID = ValidateAndGetFirstItem(regionIDQuery);
return regionID;
}
}
private void ImportOptionsData(DataTable optionsData)
{
var validColumnNames = new[] { "SBD", "NV1.N", "NV1.TH", "NV2.N", "NV2.TH",
"NV3.N", "NV3.TH", "NV4.N", "NV4.TH"};
var columnNames = from column in optionsData.Columns.Cast<DataColumn>()
select column.ColumnName;
ValidateColumnNames(columnNames, validColumnNames);
var entities = new Entities();
entities.Configuration.AutoDetectChangesEnabled = false;
int id = 0;
foreach (DataRow dataRow in optionsData.Rows)
{
var option = OptionFromDataRow(dataRow);
entities.Options.Add(option);
id++;
if (id % 777 == 776)
{
entities.SaveChanges();
entities.Dispose();
entities = new Entities();
entities.Configuration.AutoDetectChangesEnabled = false;
}
}
entities.SaveChanges();
entities.Dispose();
}
private void ImportCandidatesData(DataTable candidatesData)
{
var validColumnNames = new[] { "SBD", "HoTen", "NgaySinh", "KV", "DT", "UT",
"Toan", "Van", "Ly", "Hoa", "Sinh", "Su", "Dia", "Anh", "Nga", "Phap", "Trung", "Duc", "Nhat" };
var columnNames = from column in candidatesData.Columns.Cast<DataColumn>()
select column.ColumnName;
ValidateColumnNames(columnNames, validColumnNames);
var validSubjectNames = new[] { "Toán", "Văn", "Lý", "Hóa", "Sinh", "Sử", "Địa",
"Anh", "Nga", "Pháp", "Trung", "Đức", "Nhật" };
ValidateSubjectNames(validSubjectNames);
var entities = new Entities();
entities.Configuration.AutoDetectChangesEnabled = false;
int id = 0;
foreach (DataRow dataRow in candidatesData.Rows)
{
var candidate = CandidateFromData(dataRow);
entities.Candidates.Add(candidate);
id++;
for (int i = 0; i < 13; i++)
{
int subjectID = i + 1;
var markString = (string)dataRow[6 + i];
if (markString == "NA")
continue;
var mark = MarkFromData(id, subjectID, markString);
entities.Marks.Add(mark);
}
if (id % 777 == 776)
{
entities.SaveChanges();
entities.Dispose();
entities = new Entities();
entities.Configuration.AutoDetectChangesEnabled = false;
}
}
entities.SaveChanges();
entities.Dispose();
}
private int? GetMajorSubjectCombinationID(string majorName, string combinationName)
{
if (majorName == "NA")
return null;
using (var entities = new Entities())
{
var query = from majorSubjectCombination in entities.MajorSubjectCombinations
where majorSubjectCombination.Major.Name == majorName
&& majorSubjectCombination.SubjectCombination.SubjectCombinationName == combinationName
select majorSubjectCombination.ID;
return ValidateAndGetFirstItem(query);
}
}
private int GetCandidateID(string theOtherKindOfID)
{
using (var entities = new Entities())
{
var query = from candidate in entities.Candidates
where candidate.CandidateID == theOtherKindOfID
select candidate.ID;
return ValidateAndGetFirstItem(query);
}
}