private string GetQueryForMultipleTables(Student.Includes includes) { string selectStatement = $"SELECT {Tables.Students.allColumnsAlias} "; string fromStatement = $" FROM {Tables.Students.TableName} "; if (includes.HasFlag(Student.Includes.Bank)) { selectStatement += $", {Tables.Banks.allColumnsAlias}"; fromStatement += $" LEFT JOIN {Tables.Banks.TableName} ON " + $"{Tables.Banks.TableName}.{Tables.Banks.Id.Name} = {Tables.Students.TableName}.{Tables.Students.BankId.Name} "; } if (includes.HasFlag(Student.Includes.Address)) { selectStatement += $",{Tables.Address.allColumnsAlias},{Tables.Countries.allColumnsAlias},{Tables.Cities.allColumnsAlias},{Tables.Streets.allColumnsAlias} "; fromStatement += $" LEFT JOIN {Tables.Address.TableName} ON " + $" {Tables.Address.TableName}.{Tables.Address.Id.Name} = {Tables.Students.TableName}.{Tables.Students.AddressId.Name}" + $" LEFT JOIN {Tables.Countries.TableName} ON {Tables.Address.TableName}.{Tables.Address.CountryId.Name}= {Tables.Countries.TableName}.{Tables.Countries.Id.Name} " + $" LEFT JOIN {Tables.Cities.TableName} ON {Tables.Address.TableName}.{Tables.Address.CityId.Name}={Tables.Cities.TableName}.{Tables.Cities.Id.Name} " + $" LEFT JOIN {Tables.Streets.TableName} ON {Tables.Address.TableName}.{Tables.Address.StreetId.Name}={Tables.Streets.TableName}.{Tables.Streets.Id.Name} "; } if (includes.HasFlag(Student.Includes.Children)) { selectStatement += $", {Tables.StudentsChildren.allColumnsAlias}"; fromStatement += $" LEFT JOIN {Tables.StudentsChildren.TableName} ON " + $"{Tables.StudentsChildren.TableName}.{Tables.StudentsChildren.StudentId.Name} = {Tables.Students.TableName}.{Tables.Students.Id.Name} "; } if (includes.HasFlag(Student.Includes.BranchStudent)) { selectStatement += $", {Tables.BranchStudents.allColumnsAlias}"; fromStatement += $" LEFT JOIN {Tables.BranchStudents.TableName} ON " + $"{Tables.BranchStudents.TableName}.{Tables.BranchStudents.StudentId.Name} = {Tables.Students.TableName}.{Tables.Students.Id.Name} "; } if (includes.HasFlag(Student.Includes.Branch)) { selectStatement += $", {Tables.Branches.allColumnsAlias}"; fromStatement += $" LEFT JOIN {Tables.Branches.TableName} ON " + $"{Tables.Branches.TableName}.{Tables.Branches.Id.Name} = {Tables.BranchStudents.TableName}.{Tables.BranchStudents.BranchId.Name} "; } string sql = selectStatement + fromStatement; return(sql); }
public static Student RowToStudent(IEnumerable <DataRow> rows, Student.Includes include) { DataRow row = rows.FirstOrDefault(); BranchesStudents branchesStudent = new BranchesStudents(); if (include.HasFlag(Student.Includes.BranchStudent)) { var studentInstoitutionList = rows.GroupBy(studentRow => DataRowHelper.GetValue <int>(studentRow, Tables.BranchStudents.Id.FullName), (id, group) => BranchStudentConverter.RowToBranchStudent(group.CopyToDataTable().AsEnumerable(), Student.Includes.Branch)).ToList(); branchesStudent.AddRange(studentInstoitutionList); } return(new Student() { FirstName = DataRowHelper.GetValue <string>(row, Tables.Students.FirstName.FullName), LastName = DataRowHelper.GetValue <string>(row, Tables.Students.LastName.FullName), AccountNumber = DataRowHelper.GetValue <string>(row, Tables.Students.AccountNumber.FullName), Bank = include.HasFlag(Student.Includes.Bank) ? BankConverter.RowToBank(row) : null, Address = include.HasFlag(Student.Includes.Address) ? AddressConverter.RowToAddress(row) : null, BornDate = DataRowHelper.GetValue <DateTime>(row, Tables.Students.BornDate.FullName), PhoneNumber = DataRowHelper.GetValue <string>(row, Tables.Students.PhoneNumber.FullName), CellphoneNumber = DataRowHelper.GetValue <string>(row, Tables.Students.CellphoneNumber.FullName), ChildrenNumber = DataRowHelper.GetValue <int>(row, Tables.Students.ChildrenNumber.FullName), FaxNumber = DataRowHelper.GetValue <string>(row, Tables.Students.FaxNumber.FullName), IdCard = DataRowHelper.GetValue <string>(row, Tables.Students.IdCard.FullName), IdentityNumber = DataRowHelper.GetValue <string>(row, Tables.Students.IdentityNumber.FullName), Image = DataRowHelper.GetValue <string>(row, Tables.Students.Image.FullName), JobWife = DataRowHelper.GetValue <string>(row, Tables.Students.JobWife.FullName), MarriedChildrenNumber = DataRowHelper.GetValue <int>(row, Tables.Students.MarriedChildrenNumber.FullName), MonthlyIncome = DataRowHelper.GetValue <decimal>(row, Tables.Students.MonthlyIncome.FullName), Id = DataRowHelper.GetValue <int>(row, Tables.Students.Id.FullName), TravelExpenses = DataRowHelper.GetValue <decimal>(row, Tables.Students.TravelExpenses.FullName), WifeName = DataRowHelper.GetValue <string>(row, Tables.Students.WifeName.FullName), StudentChildren = include.HasFlag(Student.Includes.Children) ? rows.GroupBy(studentRow => DataRowHelper.GetValue <int>(studentRow, Tables.StudentsChildren.Id.FullName), (id, child) => StudentChildrenConverter.RowToStudentChildren(child.CopyToDataTable().AsEnumerable())).ToList() : null, StudentBranches = include.HasFlag(Student.Includes.BranchStudent) ? branchesStudent : null, IsActive = DataRowHelper.GetValue <bool>(row, Tables.Students.IsActive.FullName) }); }
private string GetQueryForMultipleTables(Student.Includes includes) { string selectStatement = $"SELECT {Tables.BranchStudents.allColumnsAlias} "; string fromStatement = $" FROM {Tables.BranchStudents.TableName} "; if (includes.HasFlag(Student.Includes.Branch)) { selectStatement += $", {Tables.Branches.allColumnsAlias}"; fromStatement += $" LEFT JOIN {Tables.Branches.TableName} ON " + $"{Tables.Branches.TableName}.{Tables.Branches.Id.Name} = {Tables.BranchStudents.TableName}.{Tables.BranchStudents.BranchId.Name} "; } string sql = selectStatement + fromStatement; return(sql); }
public static BranchStudent RowToBranchStudent(IEnumerable <DataRow> rows, Student.Includes includes = Student.Includes.None) { DataRow row = rows.FirstOrDefault(); return(new BranchStudent() { Id = DataRowHelper.GetValue <int>(row, Tables.BranchStudents.Id.FullName), EntryGregorianDate = DataRowHelper.GetValue <DateTime>(row, Tables.BranchStudents.EntryGregorianDate.FullName), EntryHebrewDate = DataRowHelper.GetValue <DateTime>(row, Tables.BranchStudents.EntryHebrewDate.FullName), Branch = includes.HasFlag(Student.Includes.Branch) ? BranchConverter.RowToBranch(rows, Branch.Includes.Address):null, ReleaseGregorianDate = DataRowHelper.GetValue <DateTime>(row, Tables.BranchStudents.ReleaseGregorianDate.FullName), ReleaseHebrewDate = DataRowHelper.GetValue <DateTime>(row, Tables.BranchStudents.ReleaseHebrewDate.FullName), IsActive = DataRowHelper.GetValue <bool>(row, Tables.BranchStudents.IsActive.FullName), Student = new Student() { Id = DataRowHelper.GetValue <int>(row, Tables.BranchStudents.StudentId.FullName) }, //Status=(StatusInBranch)Enum.Parse(typeof(StatusInBranch), DataRowHelper.GetValue<string>(row, Tables.BranchStudents.Status.FullName), true), }); }