public string Delete(DSale sale)
{
string resp = "";
SqlConnection SqlCon = new SqlConnection();
try
{
SqlCon.ConnectionString = Conection.cn;
SqlCon.Open();
SqlCommand SqlCmd = new SqlCommand();
SqlCmd.Connection = SqlCon;
SqlCmd.CommandText = "spdelete_sale";
SqlCmd.CommandType = CommandType.StoredProcedure;
//Initialize variables
SqlParameter ParId = new SqlParameter();
ParId.ParameterName = "@idsale"; //same name as in the procedure
ParId.SqlDbType = SqlDbType.Int;
ParId.Value = sale.IDSale;
SqlCmd.Parameters.Add(ParId);
//Execute the command
resp = SqlCmd.ExecuteNonQuery() == 1 ? "Check if it was deleted " : "OK";
}
catch (Exception ex)
{
resp = "Something went wrong: " + ex.Message;
}
finally
{
if (SqlCon.State == ConnectionState.Open)
{
SqlCon.Close();
}
}
return(resp);
}
public string Insert(DSale sale, List <DDetail_Sale> detail)
{
string resp = "";
SqlConnection SqlCon = new SqlConnection();
try
{
SqlCon.ConnectionString = Conection.cn;
SqlCon.Open();
SqlTransaction SqlTra = SqlCon.BeginTransaction();
SqlCommand SqlCmd = new SqlCommand();
SqlCmd.Connection = SqlCon;
SqlCmd.Transaction = SqlTra;
SqlCmd.CommandText = "spinsert_sale";
SqlCmd.CommandType = CommandType.StoredProcedure;
//Initialize variables
SqlParameter ParIdSale = new SqlParameter();
ParIdSale.ParameterName = "@idsale";
ParIdSale.SqlDbType = SqlDbType.Int;
ParIdSale.Direction = ParameterDirection.Output;
SqlCmd.Parameters.Add(ParIdSale);
SqlParameter ParIdClient = new SqlParameter();
ParIdClient.ParameterName = "@idclient";
ParIdClient.SqlDbType = SqlDbType.Int;
ParIdClient.Value = sale.IDClient;
SqlCmd.Parameters.Add(ParIdClient);
SqlParameter ParIdStaff = new SqlParameter();
ParIdStaff.ParameterName = "@idstaff";
ParIdStaff.SqlDbType = SqlDbType.Int;
ParIdStaff.Value = sale.IDStaff;
SqlCmd.Parameters.Add(ParIdStaff);
SqlParameter ParDate = new SqlParameter();
ParDate.ParameterName = "@date";
ParDate.SqlDbType = SqlDbType.Date;
ParDate.Value = sale.Date;
SqlCmd.Parameters.Add(ParDate);
SqlParameter ParTypeReceipt = new SqlParameter();
ParTypeReceipt.ParameterName = "@type_receipt";
ParTypeReceipt.SqlDbType = SqlDbType.VarChar;
ParTypeReceipt.Size = 50;
ParTypeReceipt.Value = sale.Type_Receipt;
SqlCmd.Parameters.Add(ParTypeReceipt);
SqlParameter ParSerie = new SqlParameter();
ParSerie.ParameterName = "@serie";
ParSerie.SqlDbType = SqlDbType.VarChar;
ParSerie.Size = 4;
ParSerie.Value = sale.Serie;
SqlCmd.Parameters.Add(ParSerie);
SqlParameter ParCorelation = new SqlParameter();
ParCorelation.ParameterName = "@corelation";
ParCorelation.SqlDbType = SqlDbType.VarChar;
ParCorelation.Size = 7;
ParCorelation.Value = sale.Corelation;
SqlCmd.Parameters.Add(ParCorelation);
SqlParameter ParVat = new SqlParameter();
ParVat.ParameterName = "@vat";
ParVat.SqlDbType = SqlDbType.Decimal;
ParVat.Scale = 2;
ParVat.Precision = 4;
ParVat.Value = sale.Vat;
SqlCmd.Parameters.Add(ParVat);
//Execute the command
resp = SqlCmd.ExecuteNonQuery() == 1 ? "OK" : "Not Inserted";
if (resp.Equals("OK"))
{
//Obtain the sale code id that was generated
this.IDSale = Convert.ToInt32(SqlCmd.Parameters["@idsale"].Value);
foreach (DDetail_Sale det in detail)
{
det.IDSale = this.IDSale;
//call insert method from details input
resp = det.Insert(det, ref SqlCon, ref SqlTra);
if (!resp.Equals("OK"))
{
break;
}
else
{
resp = Reduce(det.IDDetail_Input, det.Quantity);
if (!resp.Equals("OK"))
{
break;
}
}
}
}
if (resp.Equals("OK"))
{
//Just when we use the commit is when all data are saved
SqlTra.Commit();
}
else
{
SqlTra.Rollback(); //cancel everything
}
}
catch (Exception ex)
{
resp = "Something went wrong: " + ex.Message;
}
finally
{
if (SqlCon.State == ConnectionState.Open)
{
SqlCon.Close();
}
}
return(resp);
}