public bool AddComanda(ComandaPrinc mycomanda, List <ComandaItem> items, double sconto) { string sqlMaster = @"INSERT INTO comanda (data,n_coperti,totalecomanda,sconto) VALUES(?datam,?coperti,?ttcom,?pconto); SELECT LAST_INSERT_ID();"; string sqlItemOriginal = @"INSERT INTO comanda_alimento (id_comanda,id_alimento,qta,prezzo) VALUES(?idc,?ida,?qta,?przz); "; string sql_UpdateAlimentoqta = @"UPDATE alimento set quantit = ?qtaDisp where id =?idAl"; string sql = string.Empty; int _privcomanda; _connection.Open(); MySqlCommand myCommand = _connection.CreateCommand(); MySqlCommand myCommandItem = _connection.CreateCommand(); MySqlCommand myCommandUpdateComanda = _connection.CreateCommand(); MySqlTransaction myTrans; myTrans = _connection.BeginTransaction(); myCommandUpdateComanda.Connection = myCommandItem.Connection = myCommand.Connection = _connection; myCommandUpdateComanda.Transaction = myCommandItem.Transaction = myCommand.Transaction = myTrans; try { myCommand.CommandText = sqlMaster; myCommand.Parameters.AddWithValue("?datam", mycomanda.dataarr); myCommand.Parameters.AddWithValue("?coperti", mycomanda.ncoperti); myCommand.Parameters.AddWithValue("?ttcom", mycomanda.totPriceComanda); myCommand.Parameters.AddWithValue("?pconto", sconto); _privcomanda = Convert.ToInt32(myCommand.ExecuteScalar()); foreach (ComandaItem joi in items) { myCommandItem.CommandText = sqlItemOriginal; myCommandItem.Parameters.AddWithValue("?idc", _privcomanda); myCommandItem.Parameters.AddWithValue("?ida", joi.IdAlimento); myCommandItem.Parameters.AddWithValue("?qta", joi.Quant); myCommandItem.Parameters.AddWithValue("?przz", joi.totPrice); myCommandItem.ExecuteNonQuery(); myCommandItem.Parameters.Clear(); myCommandUpdateComanda.CommandText = sql_UpdateAlimentoqta; myCommandUpdateComanda.Parameters.AddWithValue("?qtaDisp", joi.quantRimasta); myCommandUpdateComanda.Parameters.AddWithValue("?idAl", joi.IdAlimento); myCommandUpdateComanda.ExecuteNonQuery(); myCommandUpdateComanda.Parameters.Clear(); } myTrans.Commit(); return(true); } catch (Exception e) { try { System.Windows.Forms.MessageBox.Show(e.ToString()); myTrans.Rollback(); } catch (MySqlException ex) { if (myTrans.Connection != null) { //Console.WriteLine("An exception of type " + ex.GetType() + // " was encountered while attempting to roll back the transaction."); } } return(false); } finally { _connection.Close(); } }
public bool AddComanda(ComandaPrinc mycomanda, List <ComandaItem> items) { return(AddComanda(mycomanda, items, 0)); }