PIbd21.LyovushkinaA.A.SUBD.Exam_timetable/ExamTimetable/ExamTimetable_Database/Implementation.cs

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