I have the following code:
private void verificareData()
{
int regCasa_id;
Program.Connection.CommandText = "SELECT regCasa.regCasa_id, regCasa.regCasa_date, regCasa.regCasa_soldinitial, regCasa.regCasa_soldfinal FROM regCasa WHERE regCasa.regCasa_id=(select MAX(regCasa.regCasa_id) from regCasa)";
DataTable Table = new DataTable();
Program.Connection.FillDataTable(Table, true);
string DataDeVerificat = Table.Rows[0]["regCasa_date"].ToString();
if (DateTime.Today < Convert.ToDateTime(DataDeVerificat))
{
MessageBox.Show("A fost modificată data sistemului. Aplicația nu poate rula. \nUltima înregistrare în baza de date a fost în data de " + Convert.ToDateTime(DataDeVerificat).ToString("D") + ".\nCorectați data sistemului pentru a putea folosi această aplicație.", "Eraore data sistem. Aplicția se va închide.", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
else
{
if (Table.Rows.Count == 0)
{
try
{
Program.Connection.CommandText = "INSERT INTO regCasa (regCasa_date,regCasa_soldinitial,regCasa_incasari,regCasa_plati,regCasa_soldfinal,regCasa_gaj,regCasa_comision,regCasa_vanzari) values (@regCasa_date,@regCasa_soldinitial,@regCasa_incasari,@regCasa_plati,@regCasa_soldfinal,@regCasa_gaj,@regCasa_comision,@regCasa_vanzari)";
Program.Connection.AddParameter("@regCasa_date", DateTime.Today);
Program.Connection.AddParameter("@regCasa_soldinitial", 0);
Program.Connection.AddParameter("@regCasa_incasari", 0);
Program.Connection.AddParameter("@regCasa_plati", 0);
Program.Connection.AddParameter("@regCasa_soldfinal", 0);
Program.Connection.AddParameter("@regCasa_gaj", 0);
Program.Connection.AddParameter("@regCasa_comision", 0);
Program.Connection.AddParameter("@regCasa_vanzari", 0);
Program.Connection.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
double ValoareGaj = 0;
double ValoareComision = 0;
double TotalPlatit = 0;
double TotalVanzere = 0;
double TotalIncasat = 0;
double Sold_initial = Convert.ToDouble(Table.Rows[0]["regCasa_soldinitial"].ToString());
double Sold_final = 0;
regCasa_id = Convert.ToInt32(Table.Rows[0]["regCasa_id"]);
try
{
Program.Connection.CommandText = "select * from operatiiamanet where data_act=@data_act";
Program.Connection.AddParameter("@data_act", Convert.ToDateTime(DataDeVerificat));
DataTable TableIntegistrari = new DataTable();
Program.Connection.FillDataTable(TableIntegistrari, true);
for (int i = 0; i < TableIntegistrari.Rows.Count; ++i)
{
ValoareGaj += Convert.ToDouble(TableIntegistrari.Rows[i]["valoare_gaj"].ToString());
ValoareComision += Convert.ToDouble(TableIntegistrari.Rows[i]["valoare_comision"].ToString());
TotalPlatit += Convert.ToDouble(TableIntegistrari.Rows[i]["valoare_totala_contract"].ToString());
TotalVanzere += Convert.ToDouble(TableIntegistrari.Rows[i]["valoare_vanzare"].ToString());
TotalIncasat = ValoareGaj + ValoareComision + TotalVanzere;
Sold_final = Sold_initial + TotalIncasat - TotalPlatit;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
if (Convert.ToDateTime(Table.Rows[0]["regCasa_date"].ToString()) == DateTime.Today)
{
try
{
Program.Connection.CommandText = "UPDATE regCasa SET regCasa_soldinitial=@regCasa_soldinitial, regCasa_incasari=@regCasa_incasari, regCasa_plati=@regCasa_plati, regCasa_soldfinal=@regCasa_soldfinal, regCasa_gaj=@regCasa_gaj, regCasa_comision=@regCasa_comision, regCasa_vanzari=@regCasa_vanzari WHERE regCasa_id=@regCasa_id";
Program.Connection.AddParameter("@regCasa_soldinitial", Sold_initial);
Program.Connection.AddParameter("@regCasa_incasari", TotalIncasat);
Program.Connection.AddParameter("@regCasa_plati", TotalPlatit);
Program.Connection.AddParameter("@regCasa_soldfinal", Sold_final);
Program.Connection.AddParameter("@regCasa_gaj", ValoareGaj);
Program.Connection.AddParameter("@regCasa_comision", ValoareComision);
Program.Connection.AddParameter("@regCasa_vanzari", TotalVanzere);
Program.Connection.AddParameter("@regCasa_id", regCasa_id);
Program.Connection.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
if (Convert.ToDateTime(Table.Rows[0]["regCasa_date"].ToString()) < DateTime.Today && Convert.ToDateTime(Table.Rows[0]["regCasa_date"].ToString()) != DateTime.Today)
{
try
{
Program.Connection.CommandText = "UPDATE regCasa SET regCasa_soldinitial=@regCasa_soldinitial, regCasa_incasari=@regCasa_incasari, regCasa_plati=@regCasa_plati, regCasa_soldfinal=@regCasa_soldfinal, regCasa_gaj=@regCasa_gaj, regCasa_comision=@regCasa_comision, regCasa_vanzari=@regCasa_vanzari WHERE regCasa_id=@regCasa_id";
Program.Connection.AddParameter("@regCasa_soldinitial", Sold_initial);
Program.Connection.AddParameter("@regCasa_incasari", TotalIncasat);
Program.Connection.AddParameter("@regCasa_plati", TotalPlatit);
Program.Connection.AddParameter("@regCasa_soldfinal", Sold_final);
Program.Connection.AddParameter("@regCasa_gaj", ValoareGaj);
Program.Connection.AddParameter("@regCasa_comision", ValoareComision);
Program.Connection.AddParameter("@regCasa_vanzari", TotalVanzere);
Program.Connection.AddParameter("@regCasa_id", regCasa_id);
Program.Connection.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
double ValoareGaj_Azi = 0;
double ValoareComision_Azi = 0;
double TotalPlatit_Azi = 0;
double TotalVanzere_Azi = 0;
double TotalIncasat_Azi = 0;
double Sold_initial_Azi = Convert.ToDouble(Table.Rows[0]["regCasa_soldfinal"].ToString());
double Sold_final_Azi = 0;
try
{
Program.Connection.CommandText = "select * from operatiiamanet where data_act=@data_act";
Program.Connection.AddParameter("@data_act", DateTime.Today);
DataTable TableIntegistrari = new DataTable();
Program.Connection.FillDataTable(TableIntegistrari, true);
for (int i = 0; i < TableIntegistrari.Rows.Count; ++i)
{
ValoareGaj_Azi += Convert.ToDouble(TableIntegistrari.Rows[i]["valoare_gaj"].ToString());
ValoareComision_Azi += Convert.ToDouble(TableIntegistrari.Rows[i]["valoare_comision"].ToString());
TotalPlatit_Azi += Convert.ToDouble(TableIntegistrari.Rows[i]["valoare_totala_contract"].ToString());
TotalVanzere_Azi += Convert.ToDouble(TableIntegistrari.Rows[i]["valoare_vanzare"].ToString());
TotalIncasat_Azi = ValoareGaj_Azi + ValoareComision_Azi;
Sold_final_Azi = Sold_initial_Azi + TotalIncasat_Azi - TotalPlatit_Azi;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
Program.Connection.CommandText = "INSERT INTO regCasa (regCasa_date, regCasa_soldinitial, regCasa_incasari, regCasa_plati, regCasa_soldfinal, regCasa_gaj, regCasa_comision, regCasa_vanzari) values (@regCasa_date,@regCasa_soldinitial, @regCasa_incasari, @regCasa_plati, @regCasa_soldfinal, @regCasa_gaj, @regCasa_comision, @regCasa_vanzari)";
Program.Connection.AddParameter("@regCasa_date", DateTime.Today);
Program.Connection.AddParameter("@regCasa_soldinitial", Sold_initial_Azi);
Program.Connection.AddParameter("@regCasa_incasari", TotalIncasat_Azi);
Program.Connection.AddParameter("@regCasa_plati", TotalPlatit_Azi);
Program.Connection.AddParameter("@regCasa_soldfinal", Sold_final_Azi);
Program.Connection.AddParameter("@regCasa_gaj", ValoareGaj_Azi);
Program.Connection.AddParameter("@regCasa_comision", ValoareComision_Azi);
Program.Connection.AddParameter("@regCasa_vanzari", TotalVanzere_Azi);
Program.Connection.ExecuteNonQuery();
}
}
}
Is there a way to write another code wit the same result.
tanks.
I have two table in my database. One table contains entry for payments etc., and the second table contains entry form mathematics calculation from all payments from Table1 for every day.
Every time when the program is executed or closed the code above check the date of the system, if the date of the system is equal with the date of the last record from TABLE2, are selected all record from TABLE1 with that specific date and do the calculation, and update the result in table2 on the specific raw.
If the date of the system is greeter then the date of the last record from TABLE1, the code select all record from last day, and update that record, and insert a new record in table1.
Correct question would be, if I can do database queries more compact?