/// <summary> /// Fill grid /// </summary> private void FillGrid() { DbCommunicator db = DbCommunicator.CreateInstance(_connectionString); try { log.Info("Get data to fill grid"); db.OpenConnection(); dataSet.Clear(); dataSet = db.ExecuteQuery(DBQuery.GET_TASK_DATA, "task"); dataGridView.DataSource = dataSet.Tables["task"]; } catch (Exception ex) { log.Error("Exception message is : " + ex.Message); throw ex; } finally { if (null != db) { db.CloseConnection(); } } }
public bool LoginAction(User u) { DbCommunicator dc = new DbCommunicator(); string query = $"SELECT * FROM Users WHERE UserName = '******' AND Password = '******'"; SqlDataReader data = dc.GetData(query); while (data.Read()) { u.Role = data.GetString(2); if (u.Role.Equals("admin")) { new AdminForm().Show(); } else if (u.Role.Equals("Salesman")) { SalesForm.user = u; new SalesForm().Show(); } dc.DbConnection.Close(); return(true); } u.ErrorMessage = "Login credentials do not match"; return(false); }
public bool AddSalesman(User u) { DbCommunicator dc = new DbCommunicator(); string query = $"SELECT* FROM Users WHERE UserName = '******'"; SqlDataReader data = dc.GetData(query); while (data.Read()) { u.ErrorMessage = "This user is already exist"; dc.DbConnection.Close(); return(false); } dc.DbConnection.Close(); query = $"INSERT INTO Users VALUES ('{u.UserName}','{u.Password}','{u.Role}')"; int row = dc.WriteData(query); dc.DbConnection.Close(); if (row > 0) { return(true); } else { return(false); } }
public bool SaveSales(List <SaleModel> sales) { int row = 0; foreach (SaleModel ss in sales) { DbCommunicator dc1 = new DbCommunicator(); string query = $"INSERT INTO Sales VALUES (" + $"{ss.PrductData.Id}, '{ss.Salesman.UserName}'," + $"{ss.SalesPrice}, {ss.Profit}," + $"'{ss.Date.Date}', {ss.Quantity})"; row += dc1.WriteData(query); dc1.DbConnection.Close(); DbCommunicator dc2 = new DbCommunicator(); query = "UPDATE Products SET CurrentStock = " + (ss.PrductData.CurrentStock - ss.Quantity) + " WHERE ID = '" + ss.PrductData.Id + "'"; row += dc2.WriteData(query); dc2.DbConnection.Close(); } if (row > 0) { return(true); } else { return(false); } }
/// <summary> /// Add new task. /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void AddTaskEventHanlder(object sender, EventArgs e) { DbCommunicator db = DbCommunicator.CreateInstance(_connectionString); try { log.Info("Adding new task"); // Fields validation in add task form. bool validationStatus = FieldValidation(); if (true == validationStatus) { log.Info("Successful validation"); db.OpenConnection(); int isReminder = (true == radioYes.Checked) ? 1 : 0; string query = String.Format(DBQuery.INSERT_TASK, txtTitle.Text, txtItemDesc.Text, DateTime.Parse(clderStartTime.Text).ToString("yyyy-MM-dd HH:mm:ss"), DateTime.Parse(cldrEndTime.Text).ToString("yyyy-MM-dd HH:mm:ss"), cmbStatus.SelectedItem, isReminder, txtNotificationTime.Text, _name ); log.Info("Query to add new task is : " + query); int numberOfRows = db.ExecuteNonQuery(query); if (0 < numberOfRows) { log.Info("New task added successfully with title " + txtTitle.Text); MessageBox.Show("Task added successfully. You will be notified " + txtNotificationTime.Text + " min(s) before the task", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information); UpdateGridDataEvent(); } else { log.Info("Failed to add new task."); MessageBox.Show("Failed to add todo task record in database.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error); return; } } else { return; } } catch (Exception ex) { log.Error("Exception message is : " + ex.Message); MessageBox.Show("Failed to add todo task record in database.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { if (null != db) { db.CloseConnection(); } } }
/// <summary> /// Update task event handler /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void UpdateTaskEventHanlder(object sender, EventArgs e) { DbCommunicator db = DbCommunicator.CreateInstance(_connectionString); try { log.Info("Updating task "); // Fields validation in add task form. bool validationStatus = FieldValidation(); if (true == validationStatus) { db.OpenConnection(); int isReminder = (true == radioYes.Checked) ? 1 : 0; string query = String.Format(DBQuery.UPDATE_TASK, txtTitle.Text, txtItemDesc.Text, clderStartTime.Text, cldrEndTime.Text, cmbStatus.SelectedItem, isReminder, txtNotificationTime.Text, _taskId); log.Info("Query to update task is : " + query); int numberOfRows = db.ExecuteNonQuery(query); if (0 < numberOfRows) { MessageBox.Show("Task updated successfully. You will be notified " + txtNotificationTime.Text + " min(s) before the task", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information); log.Info("Task updated successfully."); // Fire event to upgrade grid data. UpdateGridDataEvent(); } else { log.Info("Failed to update task."); MessageBox.Show("Failed to update todo task record in database.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error); return; } } else { return; } } catch (Exception ex) { MessageBox.Show("Failed to update todo task record in database.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error); log.Info("Exception message is : " + ex.Message); } finally { if (null != db) { db.CloseConnection(); } } }
public bool UpdateProduct(Product p) { DbCommunicator dc = new DbCommunicator(); string query = $"UPDATE Products SET CurrentStock = {p.CurrentStock}, BuyingCost = {p.BuyingCost}, Price = {p.Price}, RestockDate = '{p.RestockDate}'" + $" WHERE Id = {p.Id}"; int row = dc.WriteData(query); dc.DbConnection.Close(); if (row > 0) { return(true); } else { return(false); } }
public bool AddProduct(Product pp) { DbCommunicator dc = new DbCommunicator(); string query = $"INSERT INTO Products VALUES (" + $"'{pp.ProductName}', {pp.CurrentStock}," + $"'{pp.Description}', {pp.BuyingCost}," + $"{pp.Price}, '{pp.RestockDate}')"; int row = dc.WriteData(query); if (row > 0) { return(true); } else { return(false); } }
public ReportModel GetReport(DateTime dt) { ReportModel rp = new ReportModel(); string query = $"SELECT Profit, SalesPrice FROM Sales WHERE Date = '{dt.Date}'"; DbCommunicator dc = new DbCommunicator(); SqlDataReader data = dc.GetData(query); double profit = 0, total = 0; while (data.Read()) { profit += data.GetDouble(0); total += data.GetDouble(1); } dc.DbConnection.Close(); rp.Profit = profit.ToString(); rp.Total = total.ToString(); return(rp); }
/// <summary> /// click event handler for register button. /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnRegister_Click(object sender, EventArgs e) { DbCommunicator db = DbCommunicator.CreateInstance(_connectionString); try { log.Info("Clicked on Register button on New User form."); //Validate Field on Add user form. bool isValid = FieldsValidator(); if (true == isValid) { db.OpenConnection(); string query = String.Format(DBQuery.INSERT_USER, txtNewUsername.Text, txtNewPassword.Text, txtEmail.Text); int numberofRows = db.ExecuteNonQuery(query); if (0 < numberofRows) { log.Info("New user added successfully."); MessageBox.Show(txtNewUsername.Text + " user added successfully.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information); } else { log.Error("Failed to add new user"); MessageBox.Show("Failed to insert new user", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error); } } else { return; } } catch (Exception ex) { MessageBox.Show("Someting went wrong...", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error); log.Error("Exception message is : " + ex.Message); } finally { if (null != db) { db.CloseConnection(); } } }
public List <Product> GetAllProducts() { DbCommunicator dc = new DbCommunicator(); string query = "SELECT * FROM Products"; SqlDataReader data = dc.GetData(query); List <Product> ppList = new List <Product>(); while (data.Read()) { ppList.Add(new Product() { Id = data.GetInt32(0), ProductName = data.GetString(1), CurrentStock = data.GetInt32(2), Description = data.GetString(3), BuyingCost = data.GetDouble(4), Price = data.GetDouble(5) }); } dc.DbConnection.Close(); return(ppList); }
/// <summary> /// Check login status /// </summary> public void Login() { DbCommunicator db = DbCommunicator.CreateInstance(_connectionString); try { log.Info("Checking login for user."); db.OpenConnection(); string query = String.Format(DBQuery.GET_USER_COUNT, txtUserName.Text, txtPassword.Text); int numberOfRows = db.ExecuteScalar(query); if (0 < numberOfRows) { log.Info("Successful login for user" + txtUserName.Text); // On login success open form main. LoginSuccessEvent(); } else { log.Error("Login failed for " + txtUserName.Text); MessageBox.Show("Invalid credentails. \nPlease check username/password.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error); Application.Exit(); } } catch (Exception ex) { MessageBox.Show("Something went wrong", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error); log.Error("Exception message is : " + ex.Message); Application.Exit(); } finally { if (null != db) { db.CloseConnection(); } } }