/// <summary> /// Refreshes employees <see cref="DataGridView"/> /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void EmployeesRefreshButton_Click(object sender, EventArgs e) { string cmdText = null; if (pilotCheckBox.Checked && !employeeCheckBox.Checked) { cmdText = "SELECT * FROM person RIGHT JOIN pilot ON person.pesel = pilot.pesel"; } else if (!pilotCheckBox.Checked && employeeCheckBox.Checked) { cmdText = "SELECT * FROM person RIGHT JOIN employee ON person.pesel = employee.pesel"; } else { cmdText = "SELECT pesel, name, address_city, phone, address_street, address_homenumber FROM person"; } var sqlCommand = new MySqlCommand(cmdText); if (!string.IsNullOrEmpty(employeeBox1.Text)) { sqlCommand.AddWhereParameterClause("pesel", "@Pesel", employeeBox1.Text); } if (!string.IsNullOrEmpty(employeeBox2.Text)) { sqlCommand.AddWhereParameterClause("name", "@Name", employeeBox2.Text); } UpdateDataGridOrGetDataTable(sqlCommand, "Employee", dataGridView3); }
/// <summary> /// Creates an update statement to given values with given conditions /// </summary> /// <param name="valueList">List of values to change to</param> /// <param name="conditionList">List of conditions where to change values</param> /// <returns></returns> public static IEnumerable <MySqlCommand> CreateUpdateStatement(List <InfoForQuery> valueList, List <InfoForQuery> conditionList) { //Get list of tables var tableNames = valueList.Select(x => x.TableName) .ToList() .Distinct() .ToList(); var returnedList = new List <MySqlCommand>(); var peselHappened = false; var cmdText = new StringBuilder("UPDATE " + string.Join(",", tableNames.ToArray()) + " SET "); //Create a list of assignments var valueChanges = new List <string>(); foreach (var infoForQuery in valueList) { valueChanges.Add(infoForQuery.TableName + "." + infoForQuery.ColumnName + " = \"" + infoForQuery.Value + "\""); } //Add assignments with , as separator cmdText.Append(string.Join(",", valueChanges.ToArray())); var mySqlCommand = new MySqlCommand(cmdText.ToString()); //Add where clauses foreach (var infoForQuery in conditionList) { mySqlCommand.AddWhereParameterClause(infoForQuery.TableName + "." + infoForQuery.ColumnName, "@" + infoForQuery.ColumnName.ToUpper(), infoForQuery.Value); } returnedList.Add(mySqlCommand); return(returnedList); }
/// <summary> /// Creates a delete statement separately for every table with given conditions /// </summary> /// <param name="conditionList">List of conditions, which records to delete</param> /// <returns></returns> public static IEnumerable <MySqlCommand> CreateDeleteStatement(List <InfoForQuery> conditionList) { //Get list of tables var tableNames = conditionList.Select(x => x.TableName) .ToList() .Distinct() .ToList(); var mySqlCommands = new List <MySqlCommand>(); foreach (var tableName in tableNames) { var cmdText = new StringBuilder("DELETE "); //Add it to cmd cmdText.Append("FROM "); //Add table name to cmd cmdText.Append(tableName + " "); var mySqlCommand = new MySqlCommand(cmdText.ToString()); foreach (var infoForQuery in conditionList.Where(x => x.TableName == tableName)) { mySqlCommand.AddWhereParameterClause(infoForQuery.ColumnName, "@" + infoForQuery.ColumnName.ToUpper(), infoForQuery.Value); } mySqlCommands.Add(mySqlCommand); } return(mySqlCommands); }
/// <summary> /// Refreshes hangar <see cref="DataGridView"/> /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void HangarsRefreshButton_Click(object sender, EventArgs e) { // Does basically the same as airplane refresh button var sqlCommand = new MySqlCommand( "SELECT hangarID, location, capacity FROM hangar"); if (!string.IsNullOrEmpty(hangarBox1.Text) && int.TryParse(hangarBox1.Text, out var hangarID)) { sqlCommand.AddWhereParameterClause("hangarID", "@HangarID", hangarID); } if (!string.IsNullOrEmpty(hangarBox2.Text)) { sqlCommand.AddWhereParameterClause("location", "@Location", hangarBox2.Text); } if (!string.IsNullOrEmpty(hangarBox3.Text) && int.TryParse(hangarBox3.Text, out var capacity)) { sqlCommand.AddWhereParameterClause("capacity", "@Capacity", capacity); } UpdateDataGridOrGetDataTable(sqlCommand, "Hangar", dataGridView2); }
/// <summary> /// Refreshes airplane <see cref="DataGridView"/> /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void AirplanesRefreshButton_Click(object sender, EventArgs e) { //Create a new command object var sqlCommand = new MySqlCommand( "SELECT airplaneID, stored_in, model, is_refuelled FROM airplane"); //Set int var for TryParse method //Start checking if boxes have content in them, and if they do, add appropriate param to sql command if (!string.IsNullOrEmpty(airplaneBox1.Text)) { sqlCommand.AddWhereParameterClause("airplaneID", "@AirplaneID", airplaneBox1.Text); } if (!string.IsNullOrEmpty(airplaneBox2.Text) && int.TryParse(airplaneBox2.Text, out var hangar)) { sqlCommand.AddWhereParameterClause("stored_in", "@StoredIn", hangar); } if (!string.IsNullOrEmpty(airplaneBox3.Text)) { sqlCommand.AddWhereParameterClause("model", "@Model", airplaneBox3.Text); } if (!string.IsNullOrEmpty(airplaneBox4.Text) || !string.Equals(airplaneBox4.Text, "Don't care")) { if (airplaneBox4.Text.Equals("Yes")) { sqlCommand.AddWhereParameterClause("is_refuelled", "@Refuelled", 1); } else if (airplaneBox4.Text.Equals("No")) { sqlCommand.AddWhereParameterClause("is_refuelled", "@Refuelled", 0); } } //Force an update with new command. This will download the data again. UpdateDataGridOrGetDataTable(sqlCommand, "Airplane", dataGridView1); }