public static void EditExpense(ExpenseInfo expenseToEdit) { MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "UPDATE excredit SET date=@date,type=@type,description=@description,amount=@amount WHERE id=@id"; msqlCommand.Parameters.AddWithValue("@date", expenseToEdit.date); msqlCommand.Parameters.AddWithValue("@type", expenseToEdit.type); msqlCommand.Parameters.AddWithValue("@description", expenseToEdit.description); msqlCommand.Parameters.AddWithValue("@amount", expenseToEdit.amount); msqlCommand.Parameters.AddWithValue("@id", expenseToEdit.id); msqlCommand.ExecuteNonQuery(); } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } }
private void submitBtn_Click(object sender, RoutedEventArgs e) { ExpenseManagerData.ExpenseInfo newExcredit = new ExpenseManagerData.ExpenseInfo(); newExcredit.id = GenerateId(); newExcredit.type = (ExpenseType)Enum.Parse(typeof(ExpenseType), typeCB.Text, false); ; newExcredit.date = dateDP.SelectedDate.Value; newExcredit.description = descritptionTB.Text; newExcredit.amount = Convert.ToDouble(amountTB.Text); //ExpenseManagerGUI.MainWindow objMainWindow = new ExpenseManagerGUI.MainWindow(); //objMainWindow.showIncomeExpenseDetails(); if (isEdit == false) { newExcredit.id = GenerateId(); ExpenseManagerDb.DbInteraction.DoRegisterNewExcredit(newExcredit); } else { newExcredit.id = expenseId; ExpenseManagerDb.DbInteraction.EditExpense(newExcredit); } this.Close(); }
private void getDatewiseReport() { ExpenseInfo expenseInfoObj = new ExpenseInfo(); expenseInfoObj.date = startingdateDP.SelectedDate.Value; expenseInfoObj.endDate = enddatepickerDP.SelectedDate.Value; List<ExpenseInfo> excredits = ExpenseManagerDb.DbInteraction.GetDatewiseAllExcreditList(expenseInfoObj); incomeExpenseCollection.Clear(); foreach (ExpenseInfo excredit in excredits) { incomeExpenseCollection.Add(excredit); } }
public static int DoRegisterNewExcredit(ExpenseInfo excreditDetails) { return DoRegisterNewExcreditInDb(excreditDetails); }
private static List<ExpenseInfo> QueryAllExcreditList() { List<ExpenseInfo> ExcreditList = new List<ExpenseInfo>(); MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "Select * From excredit;"; MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); while (msqlReader.Read()) { ExpenseInfo Excredit = new ExpenseInfo(); Excredit.id = msqlReader.GetString("id"); Excredit.date = msqlReader.GetDateTime("date"); Excredit.type = (ExpenseType)Enum.Parse(typeof(ExpenseType), msqlReader.GetString("type"), false); Excredit.description = msqlReader.GetString("description"); Excredit.amount = msqlReader.GetDouble("amount"); ExcreditList.Add(Excredit); } } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } return ExcreditList; }
private static int DoRegisterNewExcreditInDb(ExpenseInfo excreditDetails) { int returnVal = 0; MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); //define the connection used by the command object msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "INSERT INTO excredit(id,date,type,description,amount) " + "VALUES(@id,@date,@type,@description,@amount)"; msqlCommand.Parameters.AddWithValue("@id", excreditDetails.id); msqlCommand.Parameters.AddWithValue("@date", excreditDetails.date); msqlCommand.Parameters.AddWithValue("@type", excreditDetails.type); msqlCommand.Parameters.AddWithValue("@description", excreditDetails.description); msqlCommand.Parameters.AddWithValue("@amount", excreditDetails.amount); msqlCommand.ExecuteNonQuery(); returnVal = 1; } catch (Exception er) { returnVal = 0; } finally { //always close the connection msqlConnection.Close(); } return returnVal; }
public static List<ExpenseInfo> GetEachDayExcreditList(ExpenseInfo expenseInfoObj) { List<ExpenseInfo> ExcreditList = new List<ExpenseInfo>(); MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlConnection.Open(); //DateTime endDatePicker = expenseInfoObj.endDate; //DateTime startDatePicker = expenseInfoObj.date; ////TimeSpan diff = (TimeSpan)(endDatePicker - startDatePicker); //msqlCommand.CommandText = "Select * From excredit where date = @input;"; ////"SELECT purchaselist.datePurchase, purchaselist.invoiceNo,purchaselist.vendorId,purchasebilling.quantity,purchasebilling.rate, purchasebilling.amount, purchasebilling.vat,vendors.vendor_name,vendors.vat_no FROM purchaselist,purchasebilling,vendors WHERE purchaselist.invoiceNo=purchasebilling.invoiceNo AND date(purchaselist.datePurchase) >= DATE_SUB( @enddate, INTERVAL @diff DAY) AND purchaselist.vendorId = vendors.vendor_id ;"; //msqlCommand.Parameters.AddWithValue("@input", endDatePicker); MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); while (msqlReader.Read()) { ExpenseInfo Excredit = new ExpenseInfo(); Excredit.id = msqlReader.GetString("id"); Excredit.date = msqlReader.GetDateTime("date"); Excredit.type = (ExpenseType)Enum.Parse(typeof(ExpenseType), msqlReader.GetString("type"), false); Excredit.description = msqlReader.GetString("description"); Excredit.amount = msqlReader.GetDouble("amount"); ExcreditList.Add(Excredit); } } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } return ExcreditList; }