private void PrepareOrder() { //array with controls to check Control[] c = new Control[] { BZ_straat, BZ_HuisNr, BZ_Postcode, BZ_plaats, BZ_land, BZ_ontvanger, BZ_Datum }; //Checkstate and proceed if (Check._Ctrl(c, Methode.Color) && palletsTemp.Count != 0) { Bezorging b = new Bezorging(BZ_straat.Text, Convert.string_int(BZ_HuisNr.Text), BZ_Postcode.Text, BZ_plaats.Text, BZ_land.Text, BZ_ontvanger.Text, BZ_Datum.Text); CreateOrder(palletsTemp, b); } //if not all fields are filled in else if (!Check._Ctrl(c, Methode.Color)) { MessageBox.Show("Not all Bezorging fields are filled in!"); } //if pallet_list is empty else if (palletsTemp.Count == 0) { MessageBox.Show("There are no pallets please add some!"); } //if both above failed else { MessageBox.Show("Something is wrong! [Both wrong?]"); } }
private void CreateOrder(List <Pallets> p, Bezorging b) { #region Variable int Order_ID = 0; #endregion #region Order to Database using (MySqlConnection connection = new MySqlConnection(Db.ConString)) { using (MySqlCommand command = new MySqlCommand()) { command.Connection = connection; command.CommandType = CommandType.Text; command.CommandText = "" + "INSERT INTO `orders`(`order_ID`, `klant_ID`, `rit_ID`, `postcode`, `straatnaam`, `huis_nr`, `plaats`, `land`, `ontvanger`, `datum`) " + "VALUES (@order_ID,@klant_ID,@rit_ID,@postcode,@straatnaam,@huis_nr,@plaats,@land,@ontvanger,@datum)"; //Set parameters command.Parameters.AddWithValue("@order_ID", ""); command.Parameters.AddWithValue("@klant_ID", KlantID); command.Parameters.AddWithValue("@rit_ID", ""); command.Parameters.AddWithValue("@postcode", b.Postcode); command.Parameters.AddWithValue("@straatnaam", b.Straat); command.Parameters.AddWithValue("@huis_nr", b.HuisNr); command.Parameters.AddWithValue("@plaats", b.Plaats); command.Parameters.AddWithValue("@land", b.Land); command.Parameters.AddWithValue("@ontvanger", b.Ontvanger); command.Parameters.AddWithValue("@datum", b.Datum); try { connection.Open(); int recordsAffected = command.ExecuteNonQuery(); MessageBox.Show("Order has added"); } catch (SqlException) { throw; } finally { connection.Close(); } } } #endregion #region Search Created Order //create connection and open it MySqlConnection SelectCommand = new MySqlConnection(Db.ConString); SelectCommand.Open(); //try to connect to database try { //Get Order_id and pass it MySqlCommand id_cmd = SelectCommand.CreateCommand(); id_cmd.CommandText = "SELECT `order_ID` FROM `orders` " + "WHERE `klant_ID`= @klant_ID " + "AND `postcode`= @postcode " + "AND `straatnaam`= @straatnaam " + "AND `huis_nr`= @huis_nr " + "AND `plaats`= @plaats " + "AND `land`= @land " + "AND `ontvanger`= @ontvanger " + "AND `datum`= @datum"; //Set params id_cmd.Parameters.AddWithValue("@klant_ID", KlantID); id_cmd.Parameters.AddWithValue("@postcode", b.Postcode); id_cmd.Parameters.AddWithValue("@straatnaam", b.Straat); id_cmd.Parameters.AddWithValue("@huis_nr", b.HuisNr); id_cmd.Parameters.AddWithValue("@plaats", b.Plaats); id_cmd.Parameters.AddWithValue("@land", b.Land); id_cmd.Parameters.AddWithValue("@ontvanger", b.Ontvanger); id_cmd.Parameters.AddWithValue("@datum", b.Datum); MySqlDataReader reader = id_cmd.ExecuteReader(); //Read results and set Order ID if (reader.Read()) { Order_ID = int.Parse(reader["order_ID"].ToString()); } else { MessageBox.Show("no match id found!"); } } //finally finally { //check state and clone if (SelectCommand.State == ConnectionState.Open) { SelectCommand.Clone(); } } #endregion #region Create pallets for (int i = 0; i < p.Count; i++) { using (MySqlConnection connection = new MySqlConnection(Db.ConString)) { using (MySqlCommand command = new MySqlCommand()) { command.Connection = connection; command.CommandType = CommandType.Text; command.CommandText = "" + "INSERT INTO `pallets`(`pallet_ID`, `order_ID`, `gewicht`, `inhoud`, `notitie`) " + "VALUES (@pallet_ID,@order_ID,@gewicht,@inhoud,@notitie)"; //Set parameters command.Parameters.AddWithValue("@pallet_ID", ""); command.Parameters.AddWithValue("@order_ID", Order_ID); command.Parameters.AddWithValue("@gewicht", p[i].Gewicht); command.Parameters.AddWithValue("@inhoud", p[i].Inhoud); command.Parameters.AddWithValue("@notitie", p[i].Notitie); try { connection.Open(); int recordsAffected = command.ExecuteNonQuery(); MessageBox.Show("Pallet has been added"); } catch (SqlException) { throw; } finally { connection.Close(); } } } } #endregion }