using Oefening_2_3.Models;
using Microsoft.Data.SqlClient;
using System.Configuration;

namespace Oefening7_5.Controllers
{
    public class DoctorController
    {
        
        String connectionString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
        /// <summary>
        /// Lees alle Doctors uit de database uit.
        /// </summary>
        /// <returns>De List met alle DoctorModel objecten</returns>
        public List<DoctorModel> ReadAll()
        {
            List<DoctorModel> doctorList = new List<DoctorModel>();

            try 
            {
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    String query = @"SELECT * FROM tblDoctor";
                    using (SqlCommand command = new SqlCommand(query, con))
                    {
                        con.Open();
                        SqlDataReader reader = command.ExecuteReader();

                        while (reader.Read())
                        {
                            DoctorModel doctor = new();

                            doctor.DoctorID = (Int32)reader["DoctorId"];
                            doctor.DoctorNumber = (Int32)reader["DoctorNumber"];
                            doctor.DoctorName = (String)reader["DoctorName"];
                            doctor.BirthDate = (DateTime)reader["BirthDate"];
                            doctor.FirstEpisodeDate = (DateTime)reader["FirstEpisodeDate"];

                            if (reader.IsDBNull(reader.GetOrdinal("LastEpisodeDate")))
                            {
                                doctor.LastEpisodeDate = null;
                            }
                            else
                            {
                                doctor.LastEpisodeDate = (DateTime)reader["LastEpisodeDate"];
                            }

                            doctorList.Add(doctor);
                        }
                    }
                }
            }
            catch 
            {
            
            }
            return doctorList;
        }
        /// <summary>
        /// Update een doctor in de database.
        /// </summary>
        /// <returns>nummer rows affected</returns>
        public Int32 Update(DoctorModel doctor)
        {
            Int32 result;
            try
            {
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    String query = @"UPDATE tblDoctor SET [DoctorNumber] = @doctorNumber,
                                                      [DoctorName] = @doctorName,
                                                      [BirthDate] = @birthDate,
                                                      [FirstEpisodeDate] = @firstEpisode,
                                                      [LastEpisodeDate] = @LastEpisode
                                 WHERE [DoctorId] = @doctorID";
                    using (SqlCommand command = new SqlCommand(query, con))
                    {
                        con.Open();
                        command.Parameters.AddWithValue("doctorNumber", doctor.DoctorNumber);
                        command.Parameters.AddWithValue("doctorName", doctor.DoctorName);
                        command.Parameters.AddWithValue("birthDate", doctor.BirthDate);
                        command.Parameters.AddWithValue("firstEpisode", doctor.FirstEpisodeDate);
                        command.Parameters.AddWithValue("LastEpisode", doctor.LastEpisodeDate);
                        command.Parameters.AddWithValue("doctorID", doctor.DoctorID);
                        result = command.ExecuteNonQuery();
                    }
                }
            }
            catch
            {
                result = 0;
            }

            return result;
        }
        /// <summary>
        /// maak een doctor in de database.
        /// </summary>
        /// <returns>nummer rows affected</returns>
        public Int32 Create(DoctorModel doctor)
        {
            Int32 result;
            try
            {
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    String query = @"INSERT INTO tblDoctor VALUES (@doctorNumber, @doctorName, @birthDate, @firstEpisode, @LastEpisode);";
                    using (SqlCommand command = new SqlCommand(query, con))
                    {
                        con.Open();
                        command.Parameters.AddWithValue("doctorNumber", doctor.DoctorNumber);
                        command.Parameters.AddWithValue("doctorName", doctor.DoctorName);
                        command.Parameters.AddWithValue("birthDate", doctor.BirthDate);
                        command.Parameters.AddWithValue("firstEpisode", doctor.FirstEpisodeDate);
                        command.Parameters.AddWithValue("LastEpisode", doctor.LastEpisodeDate);
                        result = command.ExecuteNonQuery();
                    }
                }
            }
            catch 
            { 
                result = 0;
            }
            return result;
        }
        /// <summary>
        /// Verwijder een doctor in de database.
        /// </summary>
        /// <returns>nummer rows affected</returns>
        public Int32 Delete(DoctorModel doctor)
        {
            Int32 result;

            try 
            {
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    String query = "DELETE FROM tblDoctor WHERE [DoctorId] = @doctorID";
                    using (SqlCommand command = new SqlCommand(query, con))
                    {
                        con.Open();
                        command.Parameters.AddWithValue("doctorID", doctor.DoctorID);
                        result = command.ExecuteNonQuery();
                    }
                }
            }
            catch 
            { 
                result = 0; 
            }

            return result;
        }
    }

}



using Microsoft.Data.SqlClient;
using Oefening_2_3.Models;
using System.Configuration;

namespace Oefening_2_3.Controllers
{
    public class EpisodeController
    {
        //String connectionString = @"Data Source=LUC-LAPTOP\DATABASE_SERVER;Initial Catalog=DoctorWho; Integrated Security=True; TrustServerCertificate=True;";
        String connectionString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
        public List<EpisodeModel> ReadAll()
        {
            List<EpisodeModel> result = new List<EpisodeModel>();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                String query = @"SELECT * FROM tblEpisode e JOIN tblDoctor d ON e.DoctorId = d.DoctorId JOIN tblAuthor a ON e.AuthorId = a.AuthorId;";
                using (SqlCommand command = new SqlCommand(query, con))
                {
                    con.Open();
                    SqlDataReader reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        EpisodeModel episode = new();

                        episode.EpisodeID = (Int32)reader["EpisodeId"];
                        episode.SeriesNumber = (Int32)reader["SeriesNumber"];
                            
                        if (reader.IsDBNull(reader.GetOrdinal("EpisodeNumber")))
                        {
                            episode.EpisodeNumber = -1;
                        }
                        else
                        {
                            episode.EpisodeNumber = (Int32)reader["EpisodeNumber"];
                        }
                        episode.EpisodeType = (String)reader["EpisodeType"];
                        episode.Title = (String)reader["Title"];
                        episode.EpisodeDate = (DateTime)reader["EpisodeDate"];
                            
                            
                        if (reader.IsDBNull(reader.GetOrdinal("Notes")))
                        {
                            episode.Notes = string.Empty;
                        }
                        else
                        {
                            episode.Notes = (String)reader["Notes"];
                        }
                        DoctorModel doctor = new DoctorModel();
                        doctor.DoctorID = (Int32)reader["DoctorId"];
                        doctor.DoctorNumber = (Int32)reader["DoctorNumber"];
                        doctor.DoctorName = (String)reader["DoctorName"];
                        doctor.BirthDate = (DateTime)reader["BirthDate"];
                        doctor.FirstEpisodeDate = (DateTime)reader["FirstEpisodeDate"];
                        if (reader.IsDBNull(reader.GetOrdinal("LastEpisodeDate")))
                        {
                            doctor.LastEpisodeDate = null;
                        }
                        else
                        {
                            doctor.LastEpisodeDate = (DateTime)reader["LastEpisodeDate"];
                        }
                        episode.Doctor = doctor;
                        AuthorModel author = new AuthorModel();
                        author.AuthorId = (Int32)reader["AuthorId"];
                        author.AuthorName = (String)reader["AuthorName"];

                        episode.Author = author;


                        result.Add(episode);
                    }
                }
            }
            return result;
        }
        public Int32 Delete(EpisodeModel episode)
        {
            Int32 result = 0;
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                String query = @"DELETE FROM tblEpisode WHERE EpisodeId = @episodeID;";
                using (SqlCommand command = new SqlCommand(query, con))
                {
                    con.Open();
                    command.Parameters.AddWithValue("episodeID", episode.EpisodeID);
                    result = command.ExecuteNonQuery();
                }
            }

            return result;
        }
        public Int32 Create(EpisodeModel episode)
        {
            Int32 result = 0;
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                String query = @"INSERT INTO tblEpisode VALUES (@seriesNumber, @episodeNumber, @episodeType, @title, @episodeDate, @author, @doctor, @Notes)";
                String query2 = @"INSERT INTO tblEpisode ";

                using (SqlCommand command = new SqlCommand(query, con))
                {
                    con.Open();
                    command.Parameters.AddWithValue("seriesNumber", episode.SeriesNumber);
                    command.Parameters.AddWithValue("episodeNumber", episode.EpisodeNumber);
                    command.Parameters.AddWithValue("episodeType", episode.EpisodeType);
                    command.Parameters.AddWithValue("title", episode.Title);
                    command.Parameters.AddWithValue("episodeDate", episode.EpisodeDate);
                    command.Parameters.AddWithValue("author", episode.Author.AuthorId);
                    command.Parameters.AddWithValue("doctor", episode.Doctor.DoctorID);
                    command.Parameters.AddWithValue("Notes", episode.Notes);

                    result = command.ExecuteNonQuery();
                }
            }

            return result;
        }
    }
}