コード例 #1
0
ファイル: Form1.cs プロジェクト: alphaaBeta/BDAirportManager
        /// <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);
        }
コード例 #2
0
        /// <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);
        }
コード例 #3
0
        /// <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);
        }
コード例 #4
0
ファイル: Form1.cs プロジェクト: alphaaBeta/BDAirportManager
        /// <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);
        }
コード例 #5
0
ファイル: Form1.cs プロジェクト: alphaaBeta/BDAirportManager
        /// <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);
        }