340 lines
14 KiB
C#
340 lines
14 KiB
C#
using Npgsql;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Runtime.ConstrainedExecution;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
|
|
namespace ExamTimetable_Database
|
|
{
|
|
public class Implementation : Abstractions
|
|
{
|
|
private NpgsqlConnection GetConnection()
|
|
{
|
|
return new NpgsqlConnection("Host=127.0.0.1;Port=5555;Username=anna;Database=subd_anna;");
|
|
}
|
|
//university
|
|
public override void AddUniversity(University university)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"INSERT INTO university (univer_name, univer_adress, phone_number, email_addr) VALUES (@univer_name ,@univer_adress, @phone_number, @email_addr)", conn);
|
|
cmd.Parameters.AddWithValue("@univer_name", university.univer_name);
|
|
cmd.Parameters.AddWithValue("@univer_adress", university.univer_adress);
|
|
cmd.Parameters.AddWithValue("@phone_number", university.phone_number);
|
|
cmd.Parameters.AddWithValue("@email_addr", university.email_addr);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
public override List<University> GetUniversities()
|
|
{
|
|
var universities = new List<University>();
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("SELECT * FROM university", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
while (reader.Read())
|
|
{
|
|
universities.Add(new University
|
|
{
|
|
univer_id = reader.GetInt32(0),
|
|
univer_name = reader.GetString(1),
|
|
univer_adress = reader.GetString(2),
|
|
phone_number = reader.GetString(3),
|
|
email_addr = reader.GetString(4)
|
|
});
|
|
}
|
|
return universities;
|
|
}
|
|
public override University GetUniversityById(int id)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"SELECT * FROM university WHERE univer_id = {id}", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
if (reader.Read())
|
|
{
|
|
return new University
|
|
{
|
|
univer_id = reader.GetInt32(0),
|
|
univer_name = reader.GetString(1),
|
|
univer_adress = reader.GetString(2),
|
|
phone_number = reader.GetString(3),
|
|
email_addr = reader.GetString(4)
|
|
};
|
|
}
|
|
return null;
|
|
}
|
|
public override void UpdateUniversity(University university)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"UPDATE university SET univer_name = {university.univer_name}, univer_adress = {university.univer_adress}, phone_number = {university.phone_number}, email_addr = {university.email_addr} WHERE univer_id = {university.univer_id}", conn);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
public override void DeleteUniversity(int id)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"DELETE FROM university WHERE univer_id = {id}", conn);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
//Faculty
|
|
public override void AddFaculty(Faculty faculty)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"INSERT INTO faculty (faculty_name, uni_id) VALUES (@faculty_name, @uni_id)", conn);
|
|
cmd.Parameters.AddWithValue("@faculty_name", faculty.faculty_name);
|
|
cmd.Parameters.AddWithValue("@uni_id", faculty.uni_id);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
public override List<Faculty> GetFaculties()
|
|
{
|
|
var faculties = new List<Faculty>();
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("SELECT * FROM faculty", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
while (reader.Read())
|
|
{
|
|
faculties.Add(new Faculty
|
|
{
|
|
faculty_id = reader.GetInt32(0),
|
|
faculty_name = reader.GetString(1),
|
|
uni_id = reader.GetInt32(2)
|
|
});
|
|
}
|
|
return faculties;
|
|
}
|
|
public override Faculty GetFacultyById(int id)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"SELECT * FROM faculty WHERE faculty_id = {id}", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
if (reader.Read())
|
|
{
|
|
return new Faculty
|
|
{
|
|
faculty_id = reader.GetInt32(0),
|
|
faculty_name = reader.GetString(1),
|
|
uni_id = reader.GetInt32(2)
|
|
};
|
|
}
|
|
return null;
|
|
}
|
|
public override void UpdateFaculty(Faculty faculty)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"UPDATE faculty SET faculty_name = @faculty_name, uni_id = @uni_id WHERE faculty_id = @faculty_id", conn);
|
|
cmd.Parameters.AddWithValue("@faculty_id", faculty.faculty_id);
|
|
cmd.Parameters.AddWithValue("@faculty_name", faculty.faculty_name);
|
|
cmd.Parameters.AddWithValue("@uni_id", faculty.uni_id);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
public override void DeleteFaculty(int id)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"DELETE FROM faculty WHERE faculty_id = {id}", conn);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
//Specialization
|
|
public override void AddSpecialization(Specialization specialization)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"INSERT INTO specialization (spec_name, fuc_id) VALUES (@spec_name, @fuc_id)", conn);
|
|
cmd.Parameters.AddWithValue("@spec_name", specialization.spec_name);
|
|
cmd.Parameters.AddWithValue("@fuc_id", specialization.fuc_id);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
public override List<Specialization> GetSpecializations()
|
|
{
|
|
var specializations = new List<Specialization>();
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("SELECT * FROM specialization", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
while (reader.Read())
|
|
{
|
|
specializations.Add(new Specialization
|
|
{
|
|
spec_id = reader.GetInt32(0),
|
|
spec_name = reader.GetString(1),
|
|
fuc_id = reader.GetInt32(2)
|
|
});
|
|
}
|
|
return specializations;
|
|
}
|
|
public override Specialization GetSpecializationById(int id)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"SELECT * FROM specialization WHERE spec_id = {id}", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
if (reader.Read())
|
|
{
|
|
return new Specialization
|
|
{
|
|
spec_id = reader.GetInt32(0),
|
|
spec_name = reader.GetString(1),
|
|
fuc_id = reader.GetInt32(2)
|
|
};
|
|
}
|
|
return null;
|
|
}
|
|
public override void UpdateSpecialization(Specialization specialization)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"UPDATE specialization SET spec_name = {specialization.spec_name}, fuc_id = {specialization.fuc_id} WHERE spec_id = {specialization.spec_id}", conn);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
public override void DeleteSpecialization(int id)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"DELETE FROM specialization WHERE spec_id = {id}", conn);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
//Comission
|
|
public override void AddComission(Comission comission)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"INSERT INTO comission (teaching_stuff, fu_id) VALUES (@teaching_stuff, @fu_id)", conn);
|
|
cmd.Parameters.AddWithValue("@teaching_stuff", comission.teaching_stuff);
|
|
cmd.Parameters.AddWithValue("@fu_id", comission.fu_id);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
public override List<Comission> GetComissions()
|
|
{
|
|
var comissions = new List<Comission>();
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("SELECT * FROM comission", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
while (reader.Read())
|
|
{
|
|
comissions.Add(new Comission
|
|
{
|
|
comm_id = reader.GetInt32(0),
|
|
teaching_stuff = reader.GetString(1),
|
|
fu_id = reader.GetInt32(2)
|
|
});
|
|
}
|
|
return comissions;
|
|
}
|
|
public override Comission GetComissionById(int id)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"SELECT * FROM comission WHERE comm_id = {id}", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
if (reader.Read())
|
|
{
|
|
return new Comission
|
|
{
|
|
comm_id = reader.GetInt32(0),
|
|
teaching_stuff = reader.GetString(1),
|
|
fu_id = reader.GetInt32(2)
|
|
};
|
|
}
|
|
return null;
|
|
}
|
|
public override void UpdateComission(Comission comission)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"UPDATE comission SET teaching_stuff = {comission.teaching_stuff}, fu_id = {comission.fu_id} WHERE comm_id = {comission.comm_id}", conn);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
public override void DeleteComission(int id)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"DELETE FROM comission WHERE comm_id = {id}", conn);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
//Exam
|
|
public override void AddExam(Exam exam)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"INSERT INTO exam (exam_date, exam_place, exam_subject, com_id, exam_time) VALUES (@exam_date, @exam_place, @exam_subject, @com_id, @exam_time)", conn);
|
|
cmd.Parameters.AddWithValue("@exam_date", exam.exam_date);
|
|
cmd.Parameters.AddWithValue("@exam_place", exam.exam_place);
|
|
cmd.Parameters.AddWithValue("@exam_subject", exam.exam_subject);
|
|
cmd.Parameters.AddWithValue("@com_id", exam.com_id);
|
|
cmd.Parameters.AddWithValue("@exam_time", exam.exam_time);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
public override List<Exam> GetExams()
|
|
{
|
|
var exams = new List<Exam>();
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("SELECT * FROM exam", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
var aboba = reader.GetDataTypeName(1);
|
|
while (reader.Read())
|
|
{
|
|
exams.Add(new Exam
|
|
{
|
|
exam_id = reader.GetInt32(0),
|
|
exam_date = reader.GetDateTime(1),
|
|
exam_place = reader.GetString(2),
|
|
exam_subject = reader.GetString(3),
|
|
com_id = reader.GetInt32(4),
|
|
exam_time = reader.GetTimeSpan(5)
|
|
});
|
|
}
|
|
return exams;
|
|
}
|
|
public override Exam GetExamById(int id)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"SELECT * FROM exam WHERE exam_id = {id}", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
if (reader.Read())
|
|
{
|
|
return new Exam
|
|
{
|
|
exam_id = reader.GetInt32(0),
|
|
exam_date = reader.GetDateTime(1),
|
|
exam_place = reader.GetString(2),
|
|
exam_subject = reader.GetString(3),
|
|
com_id = reader.GetInt32(4),
|
|
exam_time = reader.GetTimeSpan(5)
|
|
};
|
|
}
|
|
return null;
|
|
}
|
|
public override void UpdateExam(Exam exam)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
//using var cmd = new NpgsqlCommand($"UPDATE exam SET exam_date = {exam.exam_date}, exam_place = {exam.exam_place}, exam_subject = {exam.exam_subject}, com_id = {exam.com_id}, exam_time= {exam.exam_time} WHERE exam_id = {exam.exam_id}", conn);
|
|
using var cmd = new NpgsqlCommand($"UPDATE exam SET exam_date = @exam_date, exam_place = @exam_place, exam_subject = @exam_subject, com_id = @com_id, exam_time= @exam_time WHERE exam_id = @exam_id", conn);
|
|
cmd.Parameters.AddWithValue("@exam_id", exam.exam_id);
|
|
cmd.Parameters.AddWithValue("@exam_date", exam.exam_date);
|
|
cmd.Parameters.AddWithValue("@exam_place", exam.exam_place);
|
|
cmd.Parameters.AddWithValue("@exam_subject", exam.exam_subject);
|
|
cmd.Parameters.AddWithValue("@com_id", exam.com_id);
|
|
cmd.Parameters.AddWithValue("@exam_time", exam.exam_time);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
public override void DeleteExam(int id)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"DELETE FROM exam WHERE exam_id = {id}", conn);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
}
|
|
}
|