ADO.NET AnimalProject

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient; // for sql commands

namespace AnimalProject
{
    public partial class Form1 : Form
    {
        private SqlConnection con;
        private SqlDataAdapter[] dataAdapter = new SqlDataAdapter[5];
        private DataSet dataSet;

        private List<ClassNamesIDs> classNamesIDs = new List<ClassNamesIDs>();
        private List<AreaNamesIDs> areaNameIDs = new List<AreaNamesIDs>();

        private List<Vaccination> vaccinationNameIDs = new List<Vaccination>();
        private List<AnimalNameIDs> animalNameIDs = new List<AnimalNameIDs>();

        private List<Treatment> treatmentNameIDs = new List<Treatment>();

        public Form1()
        {
            con = new SqlConnection(@"Server=VAKHO-PC; Database=AnimalDatabase; Integrated Security=true;");
            //dataAdapter[0] = new SqlDataAdapter("SELECT * FROM dbo.Animals", con);
            dataAdapter[0] = new SqlDataAdapter("SELECT Animals.id, Animals.name, Classes.id as 'classId', Classes.name as 'class', Animals.birthDate, Animals.deathDate, Areas.id as 'areaId', Areas.name as 'area' FROM Animals, Classes, Areas WHERE Animals.areaId=Areas.id AND Animals.classId=Classes.id", con);
            //dataAdapter[1] = new SqlDataAdapter("SELECT * FROM dbo.AnimalVaccinations", con);
            dataAdapter[1] = new SqlDataAdapter("SELECT AnimalVaccinations.id, AnimalVaccinations.animalId, Animals.name as 'animalName', AnimalVaccinations.vaccinationId, Vaccinations.name as 'vaccinationName', AnimalVaccinations.vaccinationDate FROM AnimalVaccinations, Animals, Vaccinations WHERE Animals.id = AnimalVaccinations.animalId AND Vaccinations.id=AnimalVaccinations.vaccinationId", con);
            dataAdapter[2] = new SqlDataAdapter("SELECT AnimalTreatments.id, AnimalTreatments.description, AnimalTreatments.animalId, Animals.name as 'animalName', AnimalTreatments.treatmentId, Treatments.name as 'treatmentName', AnimalTreatments.treatmentDate FROM AnimalTreatments, Treatments, Animals WHERE AnimalTreatments.treatmentId=Treatments.id AND Animals.id=AnimalTreatments.animalId", con);

            dataAdapter[3] = new SqlDataAdapter("SELECT * FROM Vaccinations", con);
            dataAdapter[4] = new SqlDataAdapter("SELECT * FROM Treatments", con);

            // animal update
            dataAdapter[0].UpdateCommand = new SqlCommand("UPDATE Animals Set name=@name, birthDate=@birthDate, classId=@classId, areaId=@areaId, deathDate=@deathDate WHERE id=@id", con);
            dataAdapter[0].UpdateCommand.Parameters.Add(new SqlParameter("@name", SqlDbType.NVarChar));
            dataAdapter[0].UpdateCommand.Parameters.Add(new SqlParameter("@birthDate", SqlDbType.Date));
            dataAdapter[0].UpdateCommand.Parameters.Add(new SqlParameter("@classId", SqlDbType.Int));
            dataAdapter[0].UpdateCommand.Parameters.Add(new SqlParameter("@areaId", SqlDbType.Int));
            dataAdapter[0].UpdateCommand.Parameters.Add(new SqlParameter("@deathDate", SqlDbType.Date));
            dataAdapter[0].UpdateCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int));

            dataAdapter[0].UpdateCommand.Parameters["@name"].SourceColumn = "name";
            dataAdapter[0].UpdateCommand.Parameters["@birthDate"].SourceColumn = "birthDate";
            dataAdapter[0].UpdateCommand.Parameters["@classId"].SourceColumn = "classId";
            dataAdapter[0].UpdateCommand.Parameters["@areaId"].SourceColumn = "areaId";
            dataAdapter[0].UpdateCommand.Parameters["@deathDate"].SourceColumn = "deathDate";
            dataAdapter[0].UpdateCommand.Parameters["@id"].SourceColumn = "id";

            // vaccination update
            dataAdapter[1].UpdateCommand = new SqlCommand("UPDATE AnimalVaccinations Set animalId=@animalId, vaccinationId=@vaccinationId, vaccinationDate=@vaccinationDate WHERE id=@id", con);
            dataAdapter[1].UpdateCommand.Parameters.Add(new SqlParameter("@animalId", SqlDbType.Int));
            dataAdapter[1].UpdateCommand.Parameters.Add(new SqlParameter("@vaccinationId", SqlDbType.Int));
            dataAdapter[1].UpdateCommand.Parameters.Add(new SqlParameter("@vaccinationDate", SqlDbType.Date));
            dataAdapter[1].UpdateCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int));

            dataAdapter[1].UpdateCommand.Parameters["@animalId"].SourceColumn = "animalId";
            dataAdapter[1].UpdateCommand.Parameters["@vaccinationId"].SourceColumn = "vaccinationId";
            dataAdapter[1].UpdateCommand.Parameters["@vaccinationDate"].SourceColumn = "vaccinationDate";
            dataAdapter[1].UpdateCommand.Parameters["@id"].SourceColumn = "id";

            // treatment update
            dataAdapter[2].UpdateCommand = new SqlCommand("UPDATE AnimalTreatments Set description=@description, animalId=@animalId, treatmentId=@treatmentId, treatmentDate=@treatmentDate WHERE id=@id", con);
            dataAdapter[2].UpdateCommand.Parameters.Add(new SqlParameter("@description", SqlDbType.NVarChar));
            dataAdapter[2].UpdateCommand.Parameters.Add(new SqlParameter("@animalId", SqlDbType.Int));
            dataAdapter[2].UpdateCommand.Parameters.Add(new SqlParameter("@treatmentId", SqlDbType.Int));
            dataAdapter[2].UpdateCommand.Parameters.Add(new SqlParameter("@treatmentDate", SqlDbType.Date));
            dataAdapter[2].UpdateCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int));

            dataAdapter[2].UpdateCommand.Parameters["@description"].SourceColumn = "description";
            dataAdapter[2].UpdateCommand.Parameters["@animalId"].SourceColumn = "animalId";
            dataAdapter[2].UpdateCommand.Parameters["@treatmentId"].SourceColumn = "treatmentId";
            dataAdapter[2].UpdateCommand.Parameters["@treatmentDate"].SourceColumn = "treatmentDate";
            dataAdapter[2].UpdateCommand.Parameters["@id"].SourceColumn = "id";

            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            try
            {
                con.Open();
                toolStripStatusLabel1.Text = "Connected";

                this.refreshTable();
            }
            catch (SqlException ex)
            {
                toolStripStatusLabel1.Text = "Not Connected";
            }
        }

        // also refreshes comboBoxes
        private void refreshTable()
        {
            con.Close();
            dataSet = new DataSet();

            // Animals
            dataAdapter[0].Fill(dataSet, "Animals");
            dataGridView1.DataSource = dataSet.Tables["Animals"];

            // Animal Vaccinations
            dataAdapter[1].Fill(dataSet, "AnimalVaccinations");
            dataGridView2.DataSource = dataSet.Tables["AnimalVaccinations"];

            // Animal Treatments
            dataAdapter[2].Fill(dataSet, "AnimalTreatments");
            dataGridView3.DataSource = dataSet.Tables["AnimalTreatments"];

            // Vaccinations
            dataAdapter[3].Fill(dataSet, "Vaccinations");
            dataGridView4.DataSource = dataSet.Tables["Vaccinations"];

            // Treatments
            dataAdapter[4].Fill(dataSet, "Treatments");
            dataGridView5.DataSource = dataSet.Tables["Treatments"];

            dataGridView1.Columns[0].ReadOnly = true;
            dataGridView1.Columns[3].ReadOnly = true;
            dataGridView1.Columns[7].ReadOnly = true;

            dataGridView2.Columns[0].ReadOnly = true;
            dataGridView2.Columns[2].ReadOnly = true;
            dataGridView2.Columns[4].ReadOnly = true;

            dataGridView3.Columns[0].ReadOnly = true;
            dataGridView3.Columns[3].ReadOnly = true;
            dataGridView3.Columns[5].ReadOnly = true;

            con.Open();

            // classes
            SqlCommand classes = new SqlCommand("SELECT * FROM Classes", con);
            SqlDataReader classReader = classes.ExecuteReader();
            classNamesIDs.Clear();
            while (classReader.Read())
            {
                ClassNamesIDs cnis = new ClassNamesIDs(Convert.ToInt32(classReader["id"]), classReader["name"].ToString());
                classNamesIDs.Add(cnis);
            }

            comboBox1.Items.Clear();
            foreach (ClassNamesIDs cnis in classNamesIDs) {
                comboBox1.Items.Add(cnis.getId() + " - " + cnis.getName());
            }
            comboBox1.SelectedIndex = 0;
            classReader.Close();

            // areas
            SqlCommand areas = new SqlCommand("SELECT * FROM Areas", con);
            SqlDataReader areaReader = areas.ExecuteReader();
            areaNameIDs.Clear();
            while (areaReader.Read())
            {
                AreaNamesIDs aris = new AreaNamesIDs(Convert.ToInt32(areaReader["id"]), areaReader["name"].ToString());
                areaNameIDs.Add(aris);
            }

            comboBox2.Items.Clear();
            foreach (AreaNamesIDs aris in areaNameIDs)
            {
                comboBox2.Items.Add(aris.getId() + " - " + aris.getName());
            }
            comboBox2.SelectedIndex = 0;
            areaReader.Close();

            // animals
            SqlCommand animals = new SqlCommand("SELECT * FROM Animals", con);
            SqlDataReader animalReader = animals.ExecuteReader();
            animalNameIDs.Clear();
            while (animalReader.Read())
            {
                AnimalNameIDs anis = new AnimalNameIDs(Convert.ToInt32(animalReader["id"]), animalReader["name"].ToString());
                animalNameIDs.Add(anis);
            }

            comboBox3.Items.Clear();
            comboBox5.Items.Clear(); // for treatment tab
            foreach (AnimalNameIDs anis in animalNameIDs)
            {
                comboBox3.Items.Add(anis.getId() + " - " + anis.getName());
                comboBox5.Items.Add(anis.getId() + " - " + anis.getName()); // for treatment tab
            }
            comboBox3.SelectedIndex = 0;
            comboBox5.SelectedIndex = 0; // for treatment tab
            animalReader.Close();

            // vaccinations
            SqlCommand vaccinations = new SqlCommand("SELECT * FROM Vaccinations", con);
            SqlDataReader vaccinationReader = vaccinations.ExecuteReader();
            vaccinationNameIDs.Clear();
            while (vaccinationReader.Read())
            {
                Vaccination vnis = new Vaccination(Convert.ToInt32(vaccinationReader["id"]), vaccinationReader["name"].ToString());
                vaccinationNameIDs.Add(vnis);
            }

            comboBox4.Items.Clear();
            foreach (Vaccination vnis in vaccinationNameIDs)
            {
                comboBox4.Items.Add(vnis.getId() + " - " + vnis.getName());
            }
            comboBox4.SelectedIndex = 0;
            vaccinationReader.Close();

            // treatments
            SqlCommand treatments = new SqlCommand("SELECT * FROM Treatments", con);
            SqlDataReader treatmentReader = treatments.ExecuteReader();
            treatmentNameIDs.Clear();
            while (treatmentReader.Read())
            {
                Treatment tnis = new Treatment(Convert.ToInt32(treatmentReader["id"]), treatmentReader["name"].ToString());
                treatmentNameIDs.Add(tnis);
            }

            comboBox6.Items.Clear();
            foreach (Treatment tnis in treatmentNameIDs)
            {
                comboBox6.Items.Add(tnis.getId() + " - " + tnis.getName());
            }
            comboBox6.SelectedIndex = 0;
            treatmentReader.Close();
        }

        // Refresh Button
        private void button1_Click(object sender, EventArgs e)
        {
            this.refreshTable();
        }

        // Save Button
        private void button4_Click(object sender, EventArgs e)
        {
            dataAdapter[0].Update(dataSet, "Animals");
            dataAdapter[1].Update(dataSet, "AnimalVaccinations");
            dataAdapter[2].Update(dataSet, "AnimalTreatments");
            this.refreshTable();
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        // Add Button
        private void button2_Click(object sender, EventArgs e)
        {
            SqlCommand insertCommand = new SqlCommand();
            insertCommand.Connection = con;

            // add animal
            if (tabControl1.SelectedIndex == 0)
            {
                string name = textBox1.Text;
                if (name == string.Empty)
                {
                    MessageBox.Show("Name shouldn't be empty!", "Empty Field", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
                else
                {
                    string birthDate = dateTimePicker1.Value.ToShortDateString();
                    int classId = classNamesIDs[comboBox1.SelectedIndex].getId();
                    int areaId = areaNameIDs[comboBox2.SelectedIndex].getId();
                    string deathDate = dateTimePicker2.Value.ToShortDateString();
                    //MessageBox.Show(string.Format("name: {0}\nbirthDate: {1}\nclassId{2}\nareaId: {3}\ndeathDate: {4}", name, birthDate, classId, areaId, deathDate));
                    insertCommand.CommandText = string.Format("INSERT INTO Animals (name, birthDate, classId, areaId, deathDate) VALUES (N'{0}', '{1}', {2}, {3}, '{4}');", name, birthDate, classId, areaId, deathDate);
                }
            }

            // add animal vaccination
            else if (tabControl1.SelectedIndex == 1)
            {
                int animalId = animalNameIDs[comboBox3.SelectedIndex].getId();
                int vaccinationId = vaccinationNameIDs[comboBox4.SelectedIndex].getId();
                string vaccinationDate = dateTimePicker3.Value.ToShortDateString();
                insertCommand.CommandText = string.Format("INSERT INTO AnimalVaccinations (animalId, vaccinationId, vaccinationDate) VALUES ({0}, {1}, '{2}');", animalId, vaccinationId, vaccinationDate);
            }

            // add animal treatment
            else if (tabControl1.SelectedIndex == 2)
            {
                string description = richTextBox1.Text;
                if (description == string.Empty)
                {
                    MessageBox.Show("Description shouldn't be empty!", "Empty Field", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
                else
                {
                    int animalId = animalNameIDs[comboBox5.SelectedIndex].getId();
                    int treatmentId = treatmentNameIDs[comboBox6.SelectedIndex].getId();
                    string treatmentDate = dateTimePicker4.Value.ToShortDateString();
                    insertCommand.CommandText = string.Format("INSERT INTO AnimalTreatments (description, animalId, treatmentId, treatmentDate) VALUES (N'{0}', {1}, {2}, '{3}');", description, animalId, treatmentId, treatmentDate);
                }
            }

            // add vaccination
            else if (tabControl1.SelectedIndex == 3)
            {
                string name = textBox2.Text;
                if (name == string.Empty)
                {
                    MessageBox.Show("Name shouldn't be empty!", "Empty Field", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
                else
                {
                    insertCommand.CommandText = string.Format("INSERT INTO Vaccinations (name) VALUES (N'{0}');", name);
                }
            }

            // add treatment
            else if (tabControl1.SelectedIndex == 4)
            {
                string name = textBox3.Text;
                if (name == string.Empty)
                {
                    MessageBox.Show("Name shouldn't be empty!", "Empty Field", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
                else
                {
                    insertCommand.CommandText = string.Format("INSERT INTO Treatments (name) VALUES (N'{0}');", name);
                }
            }

            try
            {
                insertCommand.ExecuteNonQuery();
                this.refreshTable();
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, "SQL Exception Occured!", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        // Remove Button
        private void button3_Click(object sender, EventArgs e)
        {
            SqlCommand deleteCommand = new SqlCommand();
            deleteCommand.Connection = con;

            // remove animal
            if (tabControl1.SelectedIndex == 0)
            {
                string id = Convert.ToString(dataGridView1.SelectedRows[0].Cells[0].Value);
                deleteCommand.CommandText = "DELETE FROM Animals WHERE id=" + id;
            }

            // remove animal vaccination
            else if (tabControl1.SelectedIndex == 1)
            {
                string id = Convert.ToString(dataGridView2.SelectedRows[0].Cells[0].Value);
                deleteCommand.CommandText = "DELETE FROM AnimalVaccinations WHERE id=" + id;
            }

            // remove animal treatment
            else if (tabControl1.SelectedIndex == 2)
            {
                string id = Convert.ToString(dataGridView3.SelectedRows[0].Cells[0].Value);
                deleteCommand.CommandText = "DELETE FROM AnimalTreatments WHERE id=" + id;
            }

            // remove vaccination
            else if (tabControl1.SelectedIndex == 3)
            {
                string id = Convert.ToString(dataGridView4.SelectedRows[0].Cells[0].Value);
                deleteCommand.CommandText = "DELETE FROM Vaccinations WHERE id=" + id;
            }

            // remove treatment
            else if (tabControl1.SelectedIndex == 4)
            {
                string id = Convert.ToString(dataGridView5.SelectedRows[0].Cells[0].Value);
                deleteCommand.CommandText = "DELETE FROM Treatments WHERE id=" + id;
            }

            deleteCommand.ExecuteNonQuery();
            this.refreshTable();
        }

        private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (tabControl1.SelectedIndex >= 3)
            {
                button4.Enabled = false;
            }
            else
            {
                button4.Enabled = true;
            }
        }
    }

    class ClassNamesIDs
    {
        private int id;
        private string name;

        public ClassNamesIDs(int id, string name)
        {
            this.id = id;
            this.name = name;
        }

        public int getId()
        {
            return this.id;
        }

        public string getName()
        {
            return this.name;
        }

        public void setId(int id)
        {
            this.id = id;
        }

        public void setName(string name)
        {
            this.name = name;
        }
    }

    class AreaNamesIDs
    {
        private int id;
        private string name;

        public AreaNamesIDs(int id, string name)
        {
            this.id = id;
            this.name = name;
        }

        public int getId()
        {
            return this.id;
        }

        public string getName()
        {
            return this.name;
        }

        public void setId(int id)
        {
            this.id = id;
        }

        public void setName(string name)
        {
            this.name = name;
        }
    }

    class Vaccination
    {
        private int id;
        private string name;

        public Vaccination(int id, string name)
        {
            this.id = id;
            this.name = name;
        }

        public int getId()
        {
            return this.id;
        }

        public string getName()
        {
            return this.name;
        }

        public void setId(int id)
        {
            this.id = id;
        }

        public void setName(string name)
        {
            this.name = name;
        }
    }

    class AnimalNameIDs
    {
        private int id;
        private string name;

        public AnimalNameIDs(int id, string name)
        {
            this.id = id;
            this.name = name;
        }

        public int getId()
        {
            return this.id;
        }

        public string getName()
        {
            return this.name;
        }

        public void setId(int id)
        {
            this.id = id;
        }

        public void setName(string name)
        {
            this.name = name;
        }
    }

    class Treatment
    {
        private int id;
        private string name;

        public Treatment(int id, string name)
        {
            this.id = id;
            this.name = name;
        }

        public int getId()
        {
            return this.id;
        }

        public string getName()
        {
            return this.name;
        }

        public void setId(int id)
        {
            this.id = id;
        }

        public void setName(string name)
        {
            this.name = name;
        }
    }
}