Übung 3: C# + Windows Forms + MySQL (without DataGridView and MySqlDataAdapter)

Auftrag.cs

namespace IHK2
{
    public class Auftrag
    {
        public Auftrag(int a_id, string nummer, double umsatz, int v_id)
        {
            this.A_id = a_id;
            this.Nummer = nummer;
            this.Umsatz = umsatz;
            this.V_id = v_id;
        }

        public int A_id { get; set; }
        public string Nummer { get; set; }
        public double Umsatz { get; set; }
        public int V_id { get; set; }
    }
}

DB.cs

using System;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
using System.Windows.Forms;

// TODO: avoid injections
// TODO: Better exception handling

namespace IHK2
{
    class DB
    {
        private MySqlConnection dbConnection = null;
        private MySqlCommand comm = null;
        private MySqlDataReader reader = null;

        private string[] connArgs = {
            "persist security info = false",
            "server = 127.0.0.1",
            "database = ihk2",
            "uid = root",
            "password = root",
            "encrypt = false"
        };

        private void dbOpen()
        {
            this.dbConnection = new MySqlConnection(string.Join(";", this.connArgs));
            this.dbConnection.Open();
        }

        private void dbClose()
        {
            this.dbConnection.Close();
        }

        private void dbComm()
        {
            comm = this.dbConnection.CreateCommand();
        }

        private void dbCommExecute()
        {
            comm.ExecuteNonQuery();
        }

        private void dbReaderExecute()
        {
            reader = comm.ExecuteReader();
        }

        public List<Vertreter> selectVertreter(int v_id = -2)
        {
            List<Vertreter> li = new List<Vertreter>();

            try
            {
                dbOpen();
                dbComm();

                if (v_id == -1)
                {
                    comm.CommandText = "SELECT * FROM vertreter WHERE v_id = (SELECT MAX(v_id) FROM vertreter);";
                }
                else if (v_id < -1)
                {
                    comm.CommandText = "SELECT * FROM vertreter;";
                }
                else
                {
                    comm.CommandText = $"SELECT * FROM vertreter WHERE v_id = {v_id};";
                }

                dbReaderExecute();

                while (reader.Read())
                {
                    li.Add(new Vertreter(
                                           reader.IsDBNull(0) ? -1 : reader.GetInt32(0),
                                           reader.IsDBNull(1) ? "NULL" : reader.GetString(1),
                                           reader.IsDBNull(2) ? "NULL" : reader.GetString(2)));
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (reader != null)
                    reader.Close();
                if (dbConnection != null)
                    dbClose();
            }

            return li;
        }

        public bool insertVertreter(string v_id, string nachname, string vorname)
        {
            bool ret = true;

            if (v_id == "")
                v_id = "NULL";
            if (nachname == "")
                nachname = "NULL";
            if (vorname == "")
                vorname = "NULL";

            try
            {
                dbOpen();
                dbComm();
                comm.CommandText = $"INSERT INTO vertreter VALUES({v_id}, '{nachname}', '{vorname}');";
                dbCommExecute();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                ret = false;
            }
            finally
            {
                if (dbConnection != null)
                    dbClose();
            }

            return ret;
        }

        public bool updateVertreter(string v_id, string nachname, string vorname)
        {
            bool ret = true;

            if (v_id == "" || v_id.ToLower() == "null")
                return false;
            if (nachname == "")
                nachname = "NULL";
            if (vorname == "")
                vorname = "NULL";

            try
            {
                dbOpen();
                dbComm();
                comm.CommandText = $"UPDATE vertreter SET nachname = '{nachname}', vorname = '{vorname}' WHERE v_id = {v_id};";
                dbCommExecute();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                ret = false;
            }
            finally
            {
                if (dbConnection != null)
                    dbClose();
            }

            return ret;
        }

        public bool deleteVertreter(string v_id)
        {
            bool ret = true;

            if (v_id == "" || v_id.ToLower() == "null")
                return false;

            try
            {
                dbOpen();
                dbComm();
                comm.CommandText = $"DELETE FROM vertreter WHERE v_id = {v_id};";
                dbCommExecute();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                ret = false;
            }
            finally
            {
                if (dbConnection != null)
                    dbClose();
            }

            return ret;
        }

        public List<Auftrag> selectAuftrag(int a_id = -2)
        {
            List<Auftrag> li = new List<Auftrag>();

            try
            {
                dbOpen();
                dbComm();

                if (a_id == -1)
                {
                    comm.CommandText = "SELECT * FROM auftrag WHERE a_id = (SELECT MAX(a_id) FROM auftrag);";
                }
                else if (a_id < -1)
                {
                    comm.CommandText = "SELECT * FROM auftrag;";
                }
                else
                {
                    comm.CommandText = $"SELECT * FROM auftrag WHERE a_id = {a_id};";
                }

                dbReaderExecute();

                while (reader.Read())
                {
                    li.Add(new Auftrag(
                                           reader.IsDBNull(0) ? -1 : reader.GetInt32(0),
                                           reader.IsDBNull(1) ? "NULL" : reader.GetString(1),
                                           reader.IsDBNull(2) ? -1.00 : reader.GetDouble(2),
                                           reader.IsDBNull(3) ? -1 : reader.GetInt32(3)));
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (reader != null)
                    reader.Close();
                if (dbConnection != null)
                    dbClose();
            }

            return li;
        }

        public bool insertAuftrag(string a_id, string nummer, string umsatz, string v_id)
        {
            bool ret = true;

            if (a_id == "")
                a_id = "NULL";
            if (nummer == "")
                nummer = "NULL";
            if (umsatz == "")
                umsatz = "NULL";
            if (v_id == "")
                v_id = "NULL";

            try
            {
                dbOpen();
                dbComm();
                comm.CommandText = $"INSERT INTO auftrag VALUES({a_id}, '{nummer}', {umsatz}, {v_id});";
                dbCommExecute();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                ret = false;
            }
            finally
            {
                if (dbConnection != null)
                    dbClose();
            }

            return ret;
        }

        public bool updateAuftrag(string a_id, string nummer, string umsatz, string v_id)
        {
            bool ret = true;

            if (a_id == "" || a_id.ToLower() == "null")
                return false;
            if (nummer == "")
                nummer = "NULL";
            if (umsatz == "")
                umsatz = "NULL";
            if (v_id == "")
                v_id = "NULL";

            try
            {
                dbOpen();
                dbComm();
                comm.CommandText = $"UPDATE auftrag SET nummer = '{nummer}', umsatz = {umsatz}, v_id = {v_id} WHERE a_id = {a_id};";
                dbCommExecute();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                ret = false;
            }
            finally
            {
                if (dbConnection != null)
                    dbClose();
            }

            return ret;
        }

        public bool deleteAuftrag(string a_id)
        {
            bool ret = true;

            if (a_id == "" || a_id.ToLower() == "null")
                return false;

            try
            {
                dbOpen();
                dbComm();
                comm.CommandText = $"DELETE FROM auftrag WHERE a_id = {a_id};";
                dbCommExecute();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                ret = false;
            }
            finally
            {
                if (dbConnection != null)
                    dbClose();
            }

            return ret;
        }
    }
}

Form0.cs

using System.Collections.Generic;
using System.Text.RegularExpressions;
using System.Windows.Forms;

namespace IHK2
{
    // abstract
    public partial class Form0 : Form
    {
        protected List<Form0> forms;
        protected bool[] inputValidated;

        public Form0()
        {
            //InitializeComponent();
        }

        public virtual string OperationDesc { get; set; }
        //public virtual bool InputValidated { get; set; }

        public virtual void setEmpty()
        {
        }

        public virtual bool validateTextBox(TextBox tb, string type)
        {
            string pat;

            switch (type)
            {
                case "date":
                    pat = @"^\d{4}-\d{2}-\d{2}$|^$|null";
                    break;
                case "digit":
                    pat = @"^\d+$|^$|null";
                    break;
                case "double":
                    pat = @"^\d+\.\d+|^$|null$";
                    break;
                case "time":
                    pat = @"^\d{2}:\d{2}:\d{2}$|^$|null";
                    break;
                default:
                    return false;
            }

            Regex rgx = new Regex(pat, RegexOptions.IgnoreCase);

            return rgx.IsMatch(tb.Text);
        }
    }
}

Form1.cs

using System;
using System.Collections.Generic;
using System.Windows.Forms;

namespace IHK2
{
    public partial class Form1 : Form0
    {
        public Form1()
        {
            InitializeComponent();
            forms = new List<Form0>();
            fillForms();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            fillListBox1();
        }

        private void fillListBox1()
        {
            listBox1.Items.Clear();
            foreach (Form0 f in forms)
            {
                if (f.OperationDesc != null)
                    this.listBox1.Items.Add(f.OperationDesc);
            }
        }

        private void fillForms()
        {
            forms.Clear();
            forms.Add(new Form2());
            forms.Add(new Form3());
        }

        private void addNewForm(Form0 f, int index)
        {
            forms.RemoveAt(index);
            forms.Insert(index, f);
        }

        private void button1_Click_1(object sender, EventArgs e)
        {
            if (listBox1.Items.Count == 0 || listBox1.SelectedIndex == -1)
            {
                MessageBox.Show("No operation selected");
            }
            else
            {
                Form0 copy = forms[listBox1.SelectedIndex];
                try
                {
                    forms[listBox1.SelectedIndex].ShowDialog();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    copy.setEmpty();
                    addNewForm(copy, listBox1.SelectedIndex);
                }
            }
        }
    }
}

Form2.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;

namespace IHK2
{
    public partial class Form2 : Form0
    {
        DB db = new DB();
        List<Vertreter> liVertreter = new List<Vertreter>();

        public Form2()
        {
            InitializeComponent();
            this.OperationDesc = "Vertreter bearbeiten";
            this.inputValidated = Enumerable.Repeat(true, 3).ToArray();

            liVertreter = db.selectVertreter();
        }

        //public override string OperationDesc { get; set;  }

        private void Form2_Load(object sender, EventArgs e)
        {
            fillListBox1();
        }

        private void fillListBox1()
        {
            listBox1.Items.Clear();
            foreach (var v in liVertreter)
            {
                listBox1.Items.Add($"{v.Nachname}, {v.Vorname}");
            }
        }

        private void addLast()
        {
            List<Vertreter> liLast = db.selectVertreter(-1);

            if (liLast.Count == 0)
            {
                MessageBox.Show("Error: Could not adapt changes.");
                return;
            }

            liVertreter.Add(liLast[0]);
            listBox1.Items.Add($"{liLast[0].Nachname}, {liLast[0].Vorname}");
        }

        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (listBox1.SelectedIndex == -1)
            {
                setEmpty();
                return;
            }

            int index = listBox1.SelectedIndex;

            textBox1.Text = liVertreter[index].V_id.ToString();
            textBox2.Text = liVertreter[index].Nachname;
            textBox3.Text = liVertreter[index].Vorname;
        }

        public override void setEmpty()
        {
            textBox1.Text = string.Empty;
            textBox2.Text = string.Empty;
            textBox3.Text = string.Empty;

            listBox1.SelectedIndex = -1;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            setEmpty();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (listBox1.SelectedIndex != -1)
            {
                MessageBox.Show("Error: Do not select an entry in the List box.");
                return;
            }

            if (!this.inputValidated.All(x => x))
            {
                MessageBox.Show("Error: Input is invalid.");
                return;
            }

            if (!db.insertVertreter(textBox1.Text, textBox2.Text, textBox3.Text))
                return;

            addLast();
            listBox1.SelectedIndex = listBox1.Items.Count - 1;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (listBox1.SelectedIndex == -1)
                return;

            if (!this.inputValidated.All(x => x))
            {
                MessageBox.Show("Error: Input is invalid.");
                return;
            }

            if (!db.updateVertreter(textBox1.Text, textBox2.Text, textBox3.Text))
                return;

            int index_old = listBox1.SelectedIndex;

            updateAt(listBox1.SelectedIndex, int.Parse(textBox1.Text));

            listBox1.SelectedIndex = index_old;
        }

        private void updateAt(int index, int v_id)
        {
            List<Vertreter> liUpdated = db.selectVertreter(v_id);

            if (liUpdated.Count == 0)
            {
                MessageBox.Show("Error: Could not adapt changes.");
                return;
            }

            liVertreter.RemoveAt(index);
            liVertreter.Insert(index, liUpdated[0]);
            listBox1.Items.RemoveAt(index);
            listBox1.Items.Insert(index, $"{liUpdated[0].Nachname}, {liUpdated[0].Vorname}");
        }

        private void button4_Click(object sender, EventArgs e)
        {
            if (listBox1.SelectedIndex == -1)
                return;

            if (!this.inputValidated.All(x => x))
            {
                MessageBox.Show("Error: Input is invalid.");
                return;
            }

            if (!db.deleteVertreter(textBox1.Text))
                return;

            int index_old = listBox1.SelectedIndex;

            deleteAt(listBox1.SelectedIndex);

            listBox1.SelectedIndex = index_old - 1 == -1 ? listBox1.Items.Count - 1 : index_old - 1;
        }

        private void deleteAt(int index)
        {
            liVertreter.RemoveAt(index);
            listBox1.Items.RemoveAt(index);
        }
    }
}

Form3.cs

//

ini.sql

start transaction;

set foreign_key_checks=0;

drop database if exists ihk2;
create database ihk2;
use ihk2;

create table vertreter (
	v_id integer primary key auto_increment,
	nachname varchar(100),
	vorname varchar(100)
);

create table auftrag (
	a_id integer primary key auto_increment,
	nummer varchar(100),
	umsatz double,
	v_id integer,
	foreign key(v_id) references vertreter(v_id) on delete cascade
);

insert into vertreter values
(420, 'hermann', 'rolf'),
(851, 'merten', 'josef'),
(824, 'schulze', 'maria');

insert into auftrag(nummer, umsatz, v_id) values
('A-201103-00205', 25370.24, 420),
('A-201103-00845', 50212.51, 420),
('A-201103-01024', 11270.10, 420),
('A-201103-01126', 30698.85, 420),

('A-201103-00267', 33989.95, 824),
('A-201103-00370', 51012.56, 824),
('A-201103-00910', 75850.24, 824),
('A-201103-01205', 35340.24, 824),
('A-201103-01267', 43289.35, 824),
('A-201103-01370', 71312.17, 824),
('A-201103-01910', 15050.14, 824),
('A-201103-02035', 45621.29, 824),

('A-201103-01268', 33781.36, 851),
('A-201103-01374', 11417.77, 851),
('A-201103-02291', 13118.39, 851);

commit;

-- vim: set ft=sql :

Vertreter.cs

namespace IHK2
{
    public class Vertreter
    {
        public Vertreter(int v_id, string nachname, string vorname)
        {
            this.V_id = v_id;
            this.Nachname = nachname;
            this.Vorname = vorname;
        }

        public int V_id { get; set; }
        public string Nachname { get; set; }
        public string Vorname { get; set; }
    }
}