/// <summary>
/// Method for get all of groups from the database
/// </summary>
/// <returns></returns>
public override List <BaseModel> GetAll()
{
List <BaseModel> groups = new List <BaseModel>();
using (SqlConnection connection = new SqlConnection(base.ConnectionString))
{
using (var query = new SqlCommand())
{
query.Connection = connection;
query.CommandText = SqlQueriesHelper.FormSelectQuery(typeof(Group));
query.Connection.Open();
using (SqlDataReader reader = query.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
groups.Add(new Group()
{
Id = (int)reader["Id"],
Name = (string)reader["Name"]
});
}
}
}
query.Connection.Close();
}
}
return(groups);
}
/// <summary>
/// Method for get group by id value from the database
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public override BaseModel GetById(int id)
{
Group group = null;
using (SqlConnection connection = new SqlConnection(base.ConnectionString))
{
using (var query = new SqlCommand())
{
query.Connection = connection;
query.CommandText = SqlQueriesHelper.FormSelectByIdQuery(typeof(Group), id);
query.Connection.Open();
using (SqlDataReader reader = query.ExecuteReader())
{
if (reader.HasRows && reader.Read())
{
group = new Group()
{
Id = (int)reader["Id"],
Name = (string)reader["Name"]
};
}
}
query.Connection.Close();
}
}
return(group);
}
/// <summary>
/// Method for get session by id value from the database
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public override BaseModel GetById(int id)
{
Session session = null;
using (SqlConnection connection = new SqlConnection(base.ConnectionString))
{
using (var query = new SqlCommand())
{
query.Connection = connection;
query.CommandText = SqlQueriesHelper.FormSelectByIdQuery(typeof(Session), id);
query.Connection.Open();
using (SqlDataReader reader = query.ExecuteReader())
{
if (reader.HasRows && reader.Read())
{
session = new Session()
{
Id = (int)reader["Id"],
EducationPeriod = (string)reader["EducationPeriod"],
Semestr = (int)reader["Semestr"]
};
}
}
query.Connection.Close();
}
}
return(session);
}
/// <summary>
/// Method for realize of deploy database and fill it
/// </summary>
/// <returns></returns>
public bool DeployDatabase()
{
using (SqlConnection connection = new SqlConnection("Server=localhost;Integrated security=SSPI;database=master"))
{
using (var query = new SqlCommand())
{
string deploySql = SqlQueriesHelper.GetDeployScript();
string fillSql = SqlQueriesHelper.GetFillDataScript();
try
{
query.Connection = connection;
query.CommandText = deploySql;
query.Connection.Open();
query.ExecuteNonQuery();
query.Connection.Close();
query.CommandText = fillSql;
query.Connection.Open();
query.ExecuteNonQuery();
query.Connection.Close();
return(true);
}
catch (Exception ex)
{
return(false);
}
}
}
}
/// <summary>
/// Method for get all of sessions from the database
/// </summary>
/// <returns></returns>
public override List <BaseModel> GetAll()
{
List <BaseModel> sessions = new List <BaseModel>();
using (SqlConnection connection = new SqlConnection(base.ConnectionString))
{
using (var query = new SqlCommand())
{
query.Connection = connection;
query.CommandText = SqlQueriesHelper.FormSelectQuery(typeof(Session));
query.Connection.Open();
using (SqlDataReader reader = query.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
sessions.Add(new Session()
{
Id = (int)reader["Id"],
EducationPeriod = (string)reader["EducationPeriod"],
Semestr = (int)reader["Semestr"]
});
}
}
}
query.Connection.Close();
}
}
return(sessions);
}
/// <summary>
/// Method for get all of results of exam from the database
/// </summary>
/// <returns></returns>
public override List <BaseModel> GetAll()
{
List <BaseModel> resultsOfSession = new List <BaseModel>();
using (SqlConnection connection = new SqlConnection(base.ConnectionString))
{
using (var query = new SqlCommand())
{
query.Connection = connection;
query.CommandText = SqlQueriesHelper.FormSelectQuery(typeof(ResultsOfExam));
query.Connection.Open();
using (SqlDataReader reader = query.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
resultsOfSession.Add(new ResultsOfExam()
{
Id = (int)reader["Id"],
Student = new Student {
Id = (int)reader["StudentId"]
},
Exam = new Exam {
Id = (int)reader["ExamId"]
},
Result = (int)reader["Result"]
});
}
}
}
query.Connection.Close();
}
}
if (resultsOfSession.Any())
{
foreach (ResultsOfExam model in resultsOfSession)
{
Student student = (Student)base.DbContext.StudentsRepository.GetById(model.Student.Id);
Exam exam = (Exam)base.DbContext.ExamsRepository.GetById(model.Exam.Id);
model.Student = student;
model.Exam = exam;
}
}
return(resultsOfSession);
}
/// <summary>
/// Method for get all of exams from the database
/// </summary>
/// <returns></returns>
public override List <BaseModel> GetAll()
{
List <BaseModel> exams = new List <BaseModel>();
using (SqlConnection connection = new SqlConnection(base.ConnectionString))
{
using (var query = new SqlCommand())
{
query.Connection = connection;
query.CommandText = SqlQueriesHelper.FormSelectQuery(typeof(Exam));
query.Connection.Open();
using (SqlDataReader reader = query.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
exams.Add(new Exam()
{
Id = (int)reader["Id"],
Session = new Session {
Id = (int)reader["SessionId"]
},
ExamDate = ((DateTime)reader["ExamDate"]).Date,
SubjectsOfGroup = new SubjectsOfGroup {
Id = (int)reader["SubjectsOfGroupId"]
}
});
}
}
}
query.Connection.Close();
}
}
if (exams.Any())
{
foreach (Exam model in exams)
{
Session session = (Session)base.DbContext.SessionsRepository.GetById(model.Session.Id);
SubjectsOfGroup subjectsOfGroup = (SubjectsOfGroup)base.DbContext.SubjectsOfGroupsRepository.GetById(model.SubjectsOfGroup.Id);
model.Session = session;
model.SubjectsOfGroup = subjectsOfGroup;
}
}
return(exams);
}
/// <summary>
/// Method for get all of subjects of groups from the database
/// </summary>
/// <returns></returns>
public override List <BaseModel> GetAll()
{
List <BaseModel> subjectsOfGroups = new List <BaseModel>();
using (SqlConnection connection = new SqlConnection(base.ConnectionString))
{
using (var query = new SqlCommand())
{
query.Connection = connection;
query.CommandText = SqlQueriesHelper.FormSelectQuery(typeof(SubjectsOfGroup));
query.Connection.Open();
using (SqlDataReader reader = query.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
subjectsOfGroups.Add(new SubjectsOfGroup()
{
Id = (int)reader["Id"],
Group = new Group {
Id = (int)reader["GroupId"]
},
Subject = new Subject {
Id = (int)reader["SubjectId"]
}
});
}
}
}
query.Connection.Close();
}
}
if (subjectsOfGroups.Any())
{
foreach (SubjectsOfGroup model in subjectsOfGroups)
{
Group group = (Group)base.DbContext.GroupsRepository.GetById(model.Group.Id);
Subject subject = (Subject)base.DbContext.SubjectsRepository.GetById(model.Subject.Id);
model.Group = group;
model.Subject = subject;
}
}
return(subjectsOfGroups);
}
/// <summary>
/// Method for get all of students from the database
/// </summary>
/// <returns></returns>
public override List <BaseModel> GetAll()
{
List <BaseModel> students = new List <BaseModel>();
using (SqlConnection connection = new SqlConnection(base.ConnectionString))
{
using (var query = new SqlCommand())
{
query.Connection = connection;
query.CommandText = SqlQueriesHelper.FormSelectQuery(typeof(Student));
query.Connection.Open();
using (SqlDataReader reader = query.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
students.Add(new Student()
{
Id = (int)reader["Id"],
FullName = (string)reader["FullName"],
Gender = (string)reader["Gender"],
DateOfBirth = ((DateTime)reader["DateOfBirth"]).Date,
Group = new Group {
Id = (int)reader["GroupId"]
}
});
}
}
}
query.Connection.Close();
}
}
if (students.Any())
{
foreach (Student model in students)
{
Group group = (Group)base.DbContext.GroupsRepository.GetById(model.Group.Id);
model.Group = group;
}
}
return(students);
}
/// <summary>
/// Method for get results of exam by id value from the database
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public override BaseModel GetById(int id)
{
ResultsOfExam results = null;
using (SqlConnection connection = new SqlConnection(base.ConnectionString))
{
using (var query = new SqlCommand())
{
query.Connection = connection;
query.CommandText = SqlQueriesHelper.FormSelectByIdQuery(typeof(ResultsOfExam), id);
query.Connection.Open();
using (SqlDataReader reader = query.ExecuteReader())
{
if (reader.HasRows && reader.Read())
{
results = new ResultsOfExam()
{
Id = (int)reader["Id"],
Student = new Student {
Id = (int)reader["StudentId"]
},
Exam = new Exam {
Id = (int)reader["ExamId"]
},
Result = (int)reader["Result"]
};
}
}
query.Connection.Close();
}
}
if (results != null)
{
Student student = (Student)base.DbContext.StudentsRepository.GetById(results.Student.Id);
Exam exam = (Exam)base.DbContext.ExamsRepository.GetById(results.Exam.Id);
results.Student = student;
results.Exam = exam;
}
return(results);
}
/// <summary>
/// Method for get exam by id value from the database
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public override BaseModel GetById(int id)
{
Exam exam = null;
using (SqlConnection connection = new SqlConnection(base.ConnectionString))
{
using (var query = new SqlCommand())
{
query.Connection = connection;
query.CommandText = SqlQueriesHelper.FormSelectByIdQuery(typeof(Exam), id);
query.Connection.Open();
using (SqlDataReader reader = query.ExecuteReader())
{
if (reader.HasRows && reader.Read())
{
exam = new Exam()
{
Id = (int)reader["Id"],
Session = new Session {
Id = (int)reader["SessionId"]
},
ExamDate = ((DateTime)reader["ExamDate"]).Date,
SubjectsOfGroup = new SubjectsOfGroup {
Id = (int)reader["SubjectsOfGroupId"]
}
};
}
}
query.Connection.Close();
}
}
if (exam != null)
{
Session session = (Session)base.DbContext.SessionsRepository.GetById(exam.Session.Id);
SubjectsOfGroup subjectsOfGroup = (SubjectsOfGroup)base.DbContext.SubjectsOfGroupsRepository.GetById(exam.SubjectsOfGroup.Id);
exam.Session = session;
exam.SubjectsOfGroup = subjectsOfGroup;
}
return(exam);
}
/// <summary>
/// Method for get subjects of group by id value from the database
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public override BaseModel GetById(int id)
{
SubjectsOfGroup subjectsOfGroup = null;
using (SqlConnection connection = new SqlConnection(base.ConnectionString))
{
using (var query = new SqlCommand())
{
query.Connection = connection;
query.CommandText = SqlQueriesHelper.FormSelectByIdQuery(typeof(SubjectsOfGroup), id);
query.Connection.Open();
using (SqlDataReader reader = query.ExecuteReader())
{
if (reader.HasRows && reader.Read())
{
subjectsOfGroup = new SubjectsOfGroup()
{
Id = (int)reader["Id"],
Group = new Group {
Id = (int)reader["GroupId"]
},
Subject = new Subject {
Id = (int)reader["SubjectId"]
}
};
}
}
query.Connection.Close();
}
}
if (subjectsOfGroup != null)
{
Group group = (Group)base.DbContext.GroupsRepository.GetById(subjectsOfGroup.Group.Id);
Subject subject = (Subject)base.DbContext.SubjectsRepository.GetById(subjectsOfGroup.Subject.Id);
subjectsOfGroup.Group = group;
subjectsOfGroup.Subject = subject;
}
return(subjectsOfGroup);
}
/// <summary>
/// Method for delete results of exam by id value from the database
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public override bool Delete(int id)
{
using (SqlConnection connection = new SqlConnection(base.ConnectionString))
{
using (var query = new SqlCommand())
{
try
{
query.Connection = connection;
query.CommandText = SqlQueriesHelper.FormDeleteQuery(typeof(ResultsOfExam), id);
query.Connection.Open();
query.ExecuteNonQuery();
query.Connection.Close();
return(true);
}
catch (Exception ex)
{
return(false);
}
}
}
}
/// <summary>
/// Method for get student by id value from the database
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public override BaseModel GetById(int id)
{
Student student = null;
using (SqlConnection connection = new SqlConnection(base.ConnectionString))
{
using (var query = new SqlCommand())
{
query.Connection = connection;
query.CommandText = SqlQueriesHelper.FormSelectByIdQuery(typeof(Student), id);
query.Connection.Open();
using (SqlDataReader reader = query.ExecuteReader())
{
if (reader.HasRows && reader.Read())
{
student = new Student()
{
Id = (int)reader["Id"],
FullName = (string)reader["FullName"],
Gender = (string)reader["Gender"],
DateOfBirth = (DateTime)reader["DateOfBirth"],
Group = new Group {
Id = (int)reader["GroupId"]
}
};
}
}
query.Connection.Close();
}
}
if (student != null)
{
student.Group = (Group)base.DbContext.GroupsRepository.GetById(student.Group.Id);
}
return(student);
}
