using System;

using System.Collections.Generic;

using System.Data;

using System.Data.Common;

using System.Windows.Forms;

using MySql.Data.MySqlClient;

    public static class DBConn

    {

        // Mysql Connection string

        private static string connectionString = "server=localhost;uid=root;password=;database=app2000v";

        public static string ConnectionString { get => connectionString; }

​

        // Fetch datasets

        public static DataSet ExecuteProcedure(string procedureName, DataGridView dataGridName, List<DbParameter> parameters = null)

        {

            MySqlConnection conn = new MySqlConnection(connectionString);

            MySqlCommand cmd = new MySqlCommand(procedureName, conn);

            cmd.CommandType = System.Data.CommandType.StoredProcedure;

​

            if (parameters != null)

            {

                foreach (DbParameter parameter in parameters)

                {

                    // do type selection from .net types to selecte correct MySqlDbType

                    if (parameter.Value is string) { cmd.Parameters.Add(parameter.ParameterName, MySqlDbType.VarChar).Value = parameter.Value; }

                    else if (parameter.Value is byte) { cmd.Parameters.Add(parameter.ParameterName, MySqlDbType.Byte).Value = parameter.Value;}

                    else if (parameter.Value is short) { cmd.Parameters.Add(parameter.ParameterName, MySqlDbType.Int16).Value = parameter.Value; }

                    else if (parameter.Value is int) { cmd.Parameters.Add(parameter.ParameterName, MySqlDbType.Int32).Value = parameter.Value; }

                    else if (parameter.Value is long) { cmd.Parameters.Add(parameter.ParameterName, MySqlDbType.Int64).Value = parameter.Value; }

                    else if (parameter.Value is decimal) { cmd.Parameters.Add(parameter.ParameterName, MySqlDbType.Decimal).Value = parameter.Value;}

                    else if (parameter.Value is DateTime) { cmd.Parameters.Add(parameter.ParameterName, MySqlDbType.DateTime).Value = parameter.Value; }

                    else if (parameter.Value is bool) { cmd.Parameters.Add(parameter.ParameterName, MySqlDbType.Bit).Value = parameter.Value; }

                    else if (parameter.Value is TimeSpan) { cmd.Parameters.Add(parameter.ParameterName, MySqlDbType.Time).Value = parameter.Value; }

                    else if (parameter.Value == Convert.DBNull) { cmd.Parameters.Add(parameter.ParameterName, MySqlDbType.VarChar).Value = Convert.DBNull;}

                }

            }

​

            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

            DataSet dataset = new DataSet();

​

            try

            {

                // the only line you actualy need to try

                adapter.Fill(dataset, procedureName);

                dataGridName.DataSource = dataset;

                dataGridName.DataMember = procedureName;

            }

            catch (Exception ex)

            {

                //UserInterface uiForm = (UserInterface)Application.OpenForms["UserInterface"];

                MessageBox.Show(ex.Message);

                return null;

            }

​

            return dataset;

        }

    }

​

    public static class DBGetData

    {

​

        public static DataSet GetRoomBookings(int isactive)

        {

            Booking bookingForm = (Booking)Application.OpenForms["Booking"];

            DataGridView dataGridBookingRoom = bookingForm.dataGridViewRoom;

            List<DbParameter> parameters = new List<DbParameter>();

            parameters.Add(new MySqlParameter("isactive", isactive));

​

            return DBConn.ExecuteProcedure("room_reservation_active", dataGridBookingRoom, parameters);

        }

    }

​

​

    public void LoadDataRoom()

    {

    DataSet roomsData = DBGetData.GetRoomBookings(1);

    if (roomsData != null)

    {

    if (roomsData.Tables.Count == 0) // didnt get any tables

    {

        return;

    }

        else if (roomsData.Tables[0].Rows.Count == 0) // no rows? 

        {

            // show message that there is no data

            return;

        }

        // Hide ID and make readable table headers

        dataGridViewRoom.Columns[0].Name = "ReservationID";

        dataGridViewRoom.Columns[0].Visible = false;

        dataGridViewRoom.Columns[1].Name = "Firstname";

        dataGridViewRoom.Columns[1].HeaderText = "Firstname";

        dataGridViewRoom.Columns[2].Name = "Lastname";

        dataGridViewRoom.Columns[2].HeaderText = "Lastname";

        dataGridViewRoom.Columns[3].Name = "Room";

        dataGridViewRoom.Columns[3].HeaderText = "Room";

        dataGridViewRoom.Columns[3].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;

        dataGridViewRoom.Columns[4].Name = "Checkedin";

        dataGridViewRoom.Columns[4].HeaderText = "Checkedin";

        dataGridViewRoom.Columns[4].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;

        dataGridViewRoom.Columns[5].Name = "Roomtype";

        dataGridViewRoom.Columns[5].HeaderText = "Roomtype";

        dataGridViewRoom.Columns[6].Name = "Datefrom";

        dataGridViewRoom.Columns[6].HeaderText = "Arrival";

        dataGridViewRoom.Columns[6].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;

        dataGridViewRoom.Columns[7].Name = "Dateto";

        dataGridViewRoom.Columns[7].HeaderText = "Departure";

        dataGridViewRoom.Columns[7].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;

        dataGridViewRoom.Columns[8].Name = "Remark";

        dataGridViewRoom.Columns[8].HeaderText = "Remark";

    }

}