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

Abteilung.cs

namespace Datenbank
{
    class Abteilung
    {
        public int abtNr;
        public string abtName;

        public Abteilung(int abtNr, string abtName)
        {
            this.abtNr = abtNr;
            this.abtName = abtName;
        }
    }
}

Datenbank.cs

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

namespace Datenbank
{
    class Datenbank
    {
        private MySqlConnection dbConnection;

        public void dbOeffnen()
        {
            try
            {
                dbConnection = new MySqlConnection("persist security info = false; server = 127.0.0.1; database = test; uid = root; password = root; encrypt = false");

                //MessageBox.Show("Datenbank geoffnet");
                dbConnection.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public void dbSchliessen()
        {
            try
            {
                dbConnection.Close();
                //MessageBox.Show("Datenbank geschlossen");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public List<Abteilung> gibAlleAbteilungen()
        {
            List<Abteilung> liAbt = new List<Abteilung>();

            dbOeffnen();

            MySqlCommand comm = dbConnection.CreateCommand();
            comm.CommandText = "SELECT * FROM abteilung;";

            MySqlDataReader reader = comm.ExecuteReader();

            while (reader.Read())
            {
                liAbt.Add(new Abteilung(
                    reader.GetInt32(0),
                    reader.GetString(1)
                    ));
            }

            reader.Close();

            dbSchliessen();

            return liAbt;
        }

        public List<Personal> gibAllePersonen()
        {
            List<Personal> liPers = new List<Personal>();

            dbOeffnen();

            MySqlCommand comm = dbConnection.CreateCommand();
            comm.CommandText = "SELECT * FROM personal;";

            MySqlDataReader reader = comm.ExecuteReader();

            while (reader.Read())
            {
                liPers.Add(new Personal(
                    reader.GetInt32(0),
                    reader.GetString(1),
                    reader.GetString(2),
                    reader.IsDBNull(3) ? 0: reader.GetInt32(3)
                    ));
            }

            reader.Close();
            
            dbSchliessen();

            return liPers;
        }

        public bool savePersonal(string nname, string vname, string abtNr)
        {
            if (nname == "" && vname == "" && abtNr == "NULL")
                return false;

            dbOeffnen();

            MySqlCommand comm = dbConnection.CreateCommand();
            comm.CommandText = "INSERT INTO personal VALUES(NULL,'" + nname + "', '" + vname + "', '" + abtNr + "');";
            comm.ExecuteNonQuery();
            
            dbSchliessen();

            return true;
        }

        public void updatePersonal(string perNr, string nname, string vname, string abtNr)
        {
            dbOeffnen();

            MySqlCommand comm = dbConnection.CreateCommand();
            comm.CommandText = "UPDATE personal SET nachname = '" + nname + "', vorname = '" + vname + "', abtNr = " + abtNr + " WHERE id = " + perNr + ";";
            comm.ExecuteNonQuery();

            dbSchliessen();
        }

        public void deletePersonal(string perNr)
        {
            dbOeffnen();

            MySqlCommand comm = dbConnection.CreateCommand();
            comm.CommandText = "DELETE FROM personal WHERE id = " + perNr + ";";
            comm.ExecuteNonQuery();

            dbSchliessen();
        }
    }
}

db.sql

START TRANSACTION;

set foreign_key_checks=1;

drop database if exists test;

create database test;
use test;

create table abteilung(
	id integer primary key auto_increment,
	abtName varchar(100)
);

create table personal (
	id integer primary key auto_increment,
	nachname varchar(100),
	vorname varchar(100),
	abtnr integer,
	foreign key(abtnr) references abteilung(id) on delete cascade
);

create table projekte (
	id integer primary key auto_increment,
	proName varchar(100)
);

create table personal_projekte(
	projekte_id integer not null,
	personal_id integer not null,
	foreign key (projekte_id) references projekte(id) on delete cascade,
	foreign key (personal_id) references personal(id) on delete cascade
);

-- :INSERTS:

insert into abteilung values
(null, 'marketing'),
(null, 'produktion'),
(null, 'vertrieb');

insert into personal(nachname, vorname) values
('mustermann', 'max', 1),
('normann', 'kari', 1),
('normann', 'ola', 3),
('doe', 'john', 3);

insert into projekte values
(null, 'p1'),
(null, 'p2'),
(null, 'p3');

insert into personal_projekte values
(1,1),
(2,2);

COMMIT;

-- vim: set ft=sql :

Form1.cs

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

namespace Datenbank
{
    public partial class Form1 : Form
    {
        List<Personal> liPers = new List<Personal>();
        List<Abteilung> liAbt = new List<Abteilung>();
        Datenbank db = new Datenbank();

        public Form1()
        {
            InitializeComponent(); 
            liPers = db.gibAllePersonen();
            liAbt = db.gibAlleAbteilungen();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            foreach (Personal p in liPers)
            {
                listBox1.Items.Add(p.nname + ", " + p.vname);
            }

            foreach (Abteilung a in liAbt)
            {
                comboBox1.Items.Add(a.abtName);
            }
        }

        private void label1_Click(object sender, EventArgs e)
        {
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (listBox1.SelectedIndex < 0)
            {
                if (db.savePersonal(textBox1.Text, textBox2.Text, comboBox1.SelectedIndex < 0 ? "NULL" : liAbt[comboBox1.SelectedIndex].abtNr.ToString()))
                {
                    button2_Click(sender, e);
                }
                else
                {
                    MessageBox.Show("Empty data set!");
                }
            }
            else
            {
                db.updatePersonal(liPers[listBox1.SelectedIndex].persNr.ToString(), textBox1.Text, textBox2.Text, comboBox1.SelectedIndex < 0 ? "NULL" : liAbt[comboBox1.SelectedIndex].abtNr.ToString());
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            liPers.Clear();
            liPers = db.gibAllePersonen();

            listBox1.Items.Clear();
            foreach (Personal p in liPers)
            {
                listBox1.Items.Add(p.nname + ", " + p.vname);
            }

            if (listBox1.Items.Count == 0)
            {
                textBox1.Text = "";
                textBox2.Text = "";
                listBox1.SelectedIndex = -1;
                comboBox1.SelectedIndex = -1;
            }
            else
            {
                textBox1.Text = liPers[liPers.Count - 1].nname;
                textBox2.Text = liPers[liPers.Count - 1].vname;
                listBox1.SelectedIndex = liPers.Count - 1;
            }
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            return;
        }

        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (listBox1.SelectedIndex < 0)
                return;

            textBox1.Text = liPers[listBox1.SelectedIndex].nname;
            textBox2.Text = liPers[listBox1.SelectedIndex].vname;
            comboBox1.SelectedIndex = getAbtIndex(liPers[listBox1.SelectedIndex].abtnr);
        }

        private void label3_Click(object sender, EventArgs e)
        {
        }

        private int getAbtIndex(int abtNr)
        {
            int ret = -1;

            for (int i = 0; i < liAbt.Count; i++)
            {
                if (liAbt[i].abtNr == abtNr)
                {
                    ret = i;
                    break;
                }
            }

            return ret;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            textBox1.Text = string.Empty;
            textBox2.Text = string.Empty;
            listBox1.SelectedIndex = -1;
            comboBox1.SelectedIndex = -1;
        }

        private void button4_Click(object sender, EventArgs e)
        {
            if (listBox1.SelectedIndex < 0)
                return;

            db.deletePersonal(liPers[listBox1.SelectedIndex].persNr.ToString());
            button2_Click(sender, e);
        }
    }   
}

Form1Designer.cs

namespace Datenbank
{
    partial class Form1
    {
        /// <summary>
        /// Erforderliche Designervariable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary>
        /// Verwendete Ressourcen bereinigen.
        /// </summary>
        /// <param name="disposing">True, wenn verwaltete Ressourcen gelöscht werden sollen; andernfalls False.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Vom Windows Form-Designer generierter Code

        /// <summary>
        /// Erforderliche Methode für die Designerunterstützung.
        /// Der Inhalt der Methode darf nicht mit dem Code-Editor geändert werden.
        /// </summary>
        private void InitializeComponent()
        {
            this.listBox1 = new System.Windows.Forms.ListBox();
            this.label1 = new System.Windows.Forms.Label();
            this.label2 = new System.Windows.Forms.Label();
            this.label3 = new System.Windows.Forms.Label();
            this.textBox1 = new System.Windows.Forms.TextBox();
            this.textBox2 = new System.Windows.Forms.TextBox();
            this.button1 = new System.Windows.Forms.Button();
            this.button2 = new System.Windows.Forms.Button();
            this.comboBox1 = new System.Windows.Forms.ComboBox();
            this.button3 = new System.Windows.Forms.Button();
            this.button4 = new System.Windows.Forms.Button();
            this.SuspendLayout();
            // 
            // listBox1
            // 
            this.listBox1.FormattingEnabled = true;
            this.listBox1.Location = new System.Drawing.Point(131, 12);
            this.listBox1.Name = "listBox1";
            this.listBox1.Size = new System.Drawing.Size(179, 290);
            this.listBox1.TabIndex = 8;
            this.listBox1.SelectedIndexChanged += new System.EventHandler(this.listBox1_SelectedIndexChanged);
            // 
            // label1
            // 
            this.label1.AutoSize = true;
            this.label1.Location = new System.Drawing.Point(336, 28);
            this.label1.Name = "label1";
            this.label1.Size = new System.Drawing.Size(62, 13);
            this.label1.TabIndex = 1;
            this.label1.Text = "Nachname:";
            this.label1.Click += new System.EventHandler(this.label1_Click);
            // 
            // label2
            // 
            this.label2.AutoSize = true;
            this.label2.Location = new System.Drawing.Point(337, 79);
            this.label2.Name = "label2";
            this.label2.Size = new System.Drawing.Size(52, 13);
            this.label2.TabIndex = 2;
            this.label2.Text = "Vorname:";
            // 
            // label3
            // 
            this.label3.AutoSize = true;
            this.label3.Location = new System.Drawing.Point(337, 128);
            this.label3.Name = "label3";
            this.label3.Size = new System.Drawing.Size(51, 13);
            this.label3.TabIndex = 3;
            this.label3.Text = "Abteilung";
            this.label3.Click += new System.EventHandler(this.label3_Click);
            // 
            // textBox1
            // 
            this.textBox1.Location = new System.Drawing.Point(339, 45);
            this.textBox1.Name = "textBox1";
            this.textBox1.Size = new System.Drawing.Size(100, 20);
            this.textBox1.TabIndex = 1;
            // 
            // textBox2
            // 
            this.textBox2.Location = new System.Drawing.Point(339, 95);
            this.textBox2.Name = "textBox2";
            this.textBox2.Size = new System.Drawing.Size(100, 20);
            this.textBox2.TabIndex = 2;
            // 
            // button1
            // 
            this.button1.Location = new System.Drawing.Point(13, 79);
            this.button1.Name = "button1";
            this.button1.Size = new System.Drawing.Size(100, 23);
            this.button1.TabIndex = 6;
            this.button1.Text = "save";
            this.button1.UseVisualStyleBackColor = true;
            this.button1.Click += new System.EventHandler(this.button1_Click);
            // 
            // button2
            // 
            this.button2.Location = new System.Drawing.Point(12, 12);
            this.button2.Name = "button2";
            this.button2.Size = new System.Drawing.Size(101, 23);
            this.button2.TabIndex = 4;
            this.button2.Text = "reload";
            this.button2.UseVisualStyleBackColor = true;
            this.button2.Click += new System.EventHandler(this.button2_Click);
            // 
            // comboBox1
            // 
            this.comboBox1.FormattingEnabled = true;
            this.comboBox1.Location = new System.Drawing.Point(339, 144);
            this.comboBox1.Name = "comboBox1";
            this.comboBox1.Size = new System.Drawing.Size(101, 21);
            this.comboBox1.TabIndex = 3;
            this.comboBox1.SelectedIndexChanged += new System.EventHandler(this.comboBox1_SelectedIndexChanged);
            // 
            // button3
            // 
            this.button3.Location = new System.Drawing.Point(12, 45);
            this.button3.Name = "button3";
            this.button3.Size = new System.Drawing.Size(101, 23);
            this.button3.TabIndex = 5;
            this.button3.Text = "new";
            this.button3.UseVisualStyleBackColor = true;
            this.button3.Click += new System.EventHandler(this.button3_Click);
            // 
            // button4
            // 
            this.button4.Location = new System.Drawing.Point(13, 108);
            this.button4.Name = "button4";
            this.button4.Size = new System.Drawing.Size(100, 23);
            this.button4.TabIndex = 7;
            this.button4.Text = "delete";
            this.button4.UseVisualStyleBackColor = true;
            this.button4.Click += new System.EventHandler(this.button4_Click);
            // 
            // Form1
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.ClientSize = new System.Drawing.Size(523, 342);
            this.Controls.Add(this.button4);
            this.Controls.Add(this.button3);
            this.Controls.Add(this.comboBox1);
            this.Controls.Add(this.button2);
            this.Controls.Add(this.button1);
            this.Controls.Add(this.textBox2);
            this.Controls.Add(this.textBox1);
            this.Controls.Add(this.label3);
            this.Controls.Add(this.label2);
            this.Controls.Add(this.label1);
            this.Controls.Add(this.listBox1);
            this.Name = "Form1";
            this.Text = "Form1";
            this.Load += new System.EventHandler(this.Form1_Load);
            this.ResumeLayout(false);
            this.PerformLayout();

        }

        #endregion

        private System.Windows.Forms.ListBox listBox1;
        private System.Windows.Forms.Label label1;
        private System.Windows.Forms.Label label2;
        private System.Windows.Forms.Label label3;
        private System.Windows.Forms.TextBox textBox1;
        private System.Windows.Forms.TextBox textBox2;
        private System.Windows.Forms.Button button1;
        private System.Windows.Forms.Button button2;
        private System.Windows.Forms.ComboBox comboBox1;
        private System.Windows.Forms.Button button3;
        private System.Windows.Forms.Button button4;
    }
}

Personal.cs

namespace Datenbank
{
    class Personal
    {
        public int persNr;
        public string nname;
        public string vname;
        public int abtnr;

        public Personal(int persNr, string nname, string vname, int abtnr)
        {
            this.persNr = persNr;
            this.nname = nname;
            this.vname = vname;
            this.abtnr = abtnr;
        }
    }
}

Program.cs

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

namespace Datenbank
{
    static class Program
    {
        /// <summary>
        /// Der Haupteinstiegspunkt für die Anwendung.
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form1());
        }
    }
}