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));
}