/// <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);
}