public EditWindow(Constants.Tablename_PKs tablename_PKs, List <Util.Header_Datatype> Headers_Datatypes, string[] ItemData) { InitializeComponent(); this.tablename_PKs = tablename_PKs; this.Headers_Datatypes = Headers_Datatypes; this.oldItemData = ItemData; this.textboxes = new List <TextBox>(); controller = new Controller(); modifiedItem = new Util.ModifiedItem { TableName = tablename_PKs.TableName, ChangesCount = 0, PrimaryKeysHeaders = tablename_PKs.PrimaryKeys, PrimaryKeysValues = new List <string>(new string[tablename_PKs.PrimaryKeys.Count]), DeletedHeaders = new List <string>(), ChangedHeaders = new List <string>(), ChangedValues = new List <string>() }; //get primary keys values modifiedItem = Util.GetPKValues(modifiedItem, Headers_Datatypes, oldItemData); Add_Labels_Textboxes(); }
public AddWindow(Constants.Tablename_PKs tablename_PKs, List <Util.Header_Datatype> Headers_Datatypes) { InitializeComponent(); this.tablename_PKs = tablename_PKs; this.Headers_Datatypes = Headers_Datatypes; this.textboxes = new List <TextBox>(); roleComboBox.ItemsSource = new string[] { "admin", "employee", "client" }; //user types controller = new Controller(); addedItem = new Util.ModifiedItem { TableName = tablename_PKs.TableName, ChangesCount = 0, PrimaryKeysHeaders = tablename_PKs.PrimaryKeys, PrimaryKeysValues = new List <string>(new string[tablename_PKs.PrimaryKeys.Count]), ChangedHeaders = new List <string>(), ChangedValues = new List <string>() }; //if the the table has an associated user if (Constants.tablesWithUsers.Contains(tablename_PKs.TableName)) { this.NextButton.Visibility = Visibility.Visible; } else { this.CommitNoUserButton.Visibility = Visibility.Visible; } Add_Labels_Textboxes(); }
//add an item public async Task <int> AddItem(Util.ModifiedItem addedItem) { int changesCount = 0; string query = "INSERT INTO " + addedItem.TableName + " ("; for (int i = 0; i < addedItem.ChangedHeaders.Count; i++) { query += addedItem.ChangedHeaders[i]; changesCount++; if (changesCount < addedItem.ChangesCount) { query += ", "; } } query += ") VALUES ("; changesCount = 0; for (int i = 0; i < addedItem.ChangedHeaders.Count; i++) { query += "'" + addedItem.ChangedValues[i] + "'"; changesCount++; if (changesCount < addedItem.ChangesCount) { query += ", "; } } query += ");"; Console.WriteLine(query); int result = await db_manager.ExecuteNonQueryAsync(query); this.errorMessage = db_manager.errorMessage; return(result); }
//update an item public async Task <int> UpdateItem(Util.ModifiedItem modifiedItem) { int changesCount = 0; string query = "UPDATE " + modifiedItem.TableName + " SET "; //SET deleted columns to null foreach (string header in modifiedItem.DeletedHeaders) { query += header + " = null "; changesCount++; if (changesCount < modifiedItem.ChangesCount) { query += ", "; } } //SET modified columns to their values for (int i = 0; i < modifiedItem.ChangedHeaders.Count; i++) { query += modifiedItem.ChangedHeaders[i] + " = '" + modifiedItem.ChangedValues[i] + "' "; changesCount++; if (changesCount < modifiedItem.ChangesCount) { query += ", "; } } //use the primary keys to identify the columns query += "WHERE "; for (int i = 0; i < modifiedItem.PrimaryKeysHeaders.Count; i++) { query += modifiedItem.PrimaryKeysHeaders[i] + " = '" + modifiedItem.PrimaryKeysValues[i] + "'"; if ((i + 1) < modifiedItem.PrimaryKeysHeaders.Count) { query += " AND "; } } query += ";"; Console.WriteLine(query); int result = await db_manager.ExecuteNonQueryAsync(query); this.errorMessage = db_manager.errorMessage; return(result); }
private async void DeletePaButton_Click(object sender, RoutedEventArgs e) { //if no row is selected if (PartsDataGrid.SelectedCells.Count == 0) { MessageBox.Show("No Part is Selected", "Select a Part"); return; } Util.ModifiedItem deletedItem = new Util.ModifiedItem(); deletedItem.TableName = "Part"; deletedItem.PrimaryKeysHeaders = Util.GetTablePk(deletedItem.TableName).PrimaryKeys; deletedItem = Util.GetPKValues(deletedItem, Parts_Datatypes, partsSelectedCells); //get values of primary keys (deletedItem.PrimaryKeysValues) if (MessageBox.Show("Are you sure you want to delete the Part?", "Confirm", MessageBoxButton.YesNo, MessageBoxImage.Question, MessageBoxResult.Yes) == MessageBoxResult.Yes) { await controller.DeleteItem(deletedItem); LoadParts(); } }
private async void DeleteEmpButton_Click(object sender, RoutedEventArgs e) { //if no row is selected if (EmployeesDataGrid.SelectedCells.Count == 0) { MessageBox.Show("No Employee is Selected", "Select an Employee"); return; } Util.ModifiedItem deletedItem = new Util.ModifiedItem(); deletedItem.TableName = "Employee"; deletedItem.PrimaryKeysHeaders = Util.GetTablePk(deletedItem.TableName).PrimaryKeys; deletedItem = Util.GetPKValues(deletedItem, Employees_Datatypes, employeesSelectedCells); //get values of primary keys (deletedItem.PrimaryKeysValues) if (MessageBox.Show("Are you sure you want to delete the Employee? (Deleting the Employee will also delete the user associated)", "Confirm", MessageBoxButton.YesNo, MessageBoxImage.Question, MessageBoxResult.Yes) == MessageBoxResult.Yes) { await controller.DeleteItem(deletedItem); LoadEmployees(); } }
//Delete an item (a row) public async Task <int> DeleteItem(Util.ModifiedItem deletedItem) { string query = "DELETE FROM " + deletedItem.TableName + " WHERE "; //use the primary keys to identify the columns for (int i = 0; i < deletedItem.PrimaryKeysHeaders.Count; i++) { query += deletedItem.PrimaryKeysHeaders[i] + " = '" + deletedItem.PrimaryKeysValues[i] + "'"; if ((i + 1) < deletedItem.PrimaryKeysHeaders.Count) { query += " AND "; } } query += ";"; Console.WriteLine(query); int result = await db_manager.ExecuteNonQueryAsync(query); this.errorMessage = db_manager.errorMessage; return(result); }
private async void CommitWithUserButton_Click(object sender, RoutedEventArgs e) { addedUser = new Util.ModifiedItem { TableName = "AppUser", ChangesCount = 0, ChangedHeaders = new List <string>(), ChangedValues = new List <string>() }; string username = usernameTextbox.Text; if (username.Equals("")) { MessageBox.Show("Username can't be empty", "Add Username"); return; } else if ((await controller.CheckUsername(username)) == 1) { MessageBox.Show("This username is already used", "Change Username"); return; } string password = passwordTextbox.Text; if (password.Length < 8) { MessageBox.Show("Password can't be less than 8 charachters", "Modify Password"); return; } var role_ = roleComboBox.SelectedItem; if (!(role_ != null)) { MessageBox.Show("Please choose a role for this user", "Choose a role"); return; } string role = role_.ToString(); if (role.Equals("client") && !(tablename_PKs.TableName.Equals("Client"))) { MessageBox.Show("client role is only for clients", "Choose a role"); return; } else if (role.Equals("admin") && !(tablename_PKs.TableName.Equals("Employee"))) { MessageBox.Show("admin role is only for employees", "Choose a role"); return; } else if (role.Equals("employee") && !(tablename_PKs.TableName.Equals("Employee"))) { MessageBox.Show("employee role is only for employees", "Choose a role"); return; } addedUser.ChangedHeaders.Add("username"); addedUser.ChangedHeaders.Add("password"); addedUser.ChangedHeaders.Add("role"); addedUser.ChangedValues.Add(username); addedUser.ChangedValues.Add(password); addedUser.ChangedValues.Add(role); addedUser.ChangesCount += 3; if (tablename_PKs.TableName.Equals("Employee")) { string ID_ = (await controller.GetNextIncrement("Employee")).ToString(); int ID = Convert.ToInt32(ID_) - 1; addedUser.ChangedHeaders.Add("EID"); addedUser.ChangedValues.Add(ID.ToString()); addedUser.ChangesCount += 1; } else if (tablename_PKs.TableName.Equals("Client")) { string ID_ = (await controller.GetNextIncrement("Client")).ToString(); int ID = Convert.ToInt32(ID_) - 1; addedUser.ChangedHeaders.Add("CID"); addedUser.ChangedValues.Add(ID.ToString()); addedUser.ChangesCount += 1; } int success = 0; success = await controller.AddItem(addedUser); if (success == 0) { //print sql error in a message box) MessageBox.Show(controller.errorMessage, "Database Error"); return; } //raise an event to update the employee in the main database OnRefreshTable(new RefreshDataEventArgs(tablename_PKs.TableName, true)); this.Close(); }