Exemplo n.º 1
0
        private void updateButton_Click(object sender, EventArgs e)
        {
            Database             db = new Database();
            DynamicInputParamBox inputFields;

            db.OpenConnection();
            try
            {
                OleDbCommand command    = new OleDbCommand();
                DataRow      defaultRow = ((DataRowView)dataGridView1.SelectedRows[0].DataBoundItem).Row;
                inputFields = new DynamicInputParamBox("UPDATE", currentTableName.Text, (DataTable)dataGridView1.DataSource,
                                                       primaryKeys[currentTableName.Text], defaultRow);
                if (inputFields.ShowDialog() != DialogResult.OK)
                {
                    return;
                }

                String        setPart = "", wherePart = "";
                String        updateString = $"UPDATE [{currentTableName.Text}]";
                List <String> inputs       = inputFields.getFieldsData();

                int  i = 0;                                      // для присваивания параметров внутри команды.
                int  j = 0;                                      // для хождения по коллекции возвращённых результатов.
                bool setBeginFlag = true, whereBeginFlag = true; // флаги, обозначающие начала конструкций частей SET и WHERE.
                foreach (DataColumn column in ((DataTable)(dataGridView1.DataSource)).Columns)
                {
                    if (primaryKeys[currentTableName.Text].Contains(column.ColumnName))
                    {
                        continue;
                    }

                    String argument = inputs[j++];
                    if (!setBeginFlag)
                    {
                        setPart += ", ";
                    }
                    else
                    {
                        setBeginFlag = false;
                    }

                    setPart += $"[{column.ColumnName}]=@{i}";

                    if (column.DataType == typeof(int))
                    {
                        int result = int.Parse(argument);
                        command.Parameters.AddWithValue($"@{i}", result);
                    }
                    else if (column.DataType == typeof(double))
                    {
                        double result = double.Parse(argument);
                        command.Parameters.AddWithValue($"@{i}", result);
                    }
                    else if (column.DataType == typeof(DateTime))
                    {
                        DateTime result = DateTime.Parse(argument);
                        command.Parameters.AddWithValue($"@{i}", result);
                    }
                    else if (column.DataType == typeof(short))
                    {
                        short result = short.Parse(argument);
                        command.Parameters.AddWithValue($"@{i}", result);
                    }
                    else
                    {
                        command.Parameters.AddWithValue($"@{i}", argument);
                    }
                    i++;
                }

                j = 0;
                foreach (DataColumn column in ((DataTable)(dataGridView1.DataSource)).Columns)
                {
                    if (!primaryKeys[currentTableName.Text].Contains(column.ColumnName))
                    {
                        continue;
                    }

                    String argument = inputs[j++];
                    if (!whereBeginFlag)
                    {
                        wherePart += "AND ";
                    }
                    else
                    {
                        whereBeginFlag = false;
                    }

                    wherePart += $"[{column.ColumnName}]=@{i} ";

                    if (column.DataType == typeof(int))
                    {
                        int result = int.Parse(argument);
                        command.Parameters.AddWithValue($"@{i}", result);
                    }
                    else if (column.DataType == typeof(double))
                    {
                        double result = double.Parse(argument);
                        command.Parameters.AddWithValue($"@{i}", result);
                    }
                    else if (column.DataType == typeof(DateTime))
                    {
                        DateTime result = DateTime.Parse(argument);
                        command.Parameters.AddWithValue($"@{i}", result);
                    }
                    else if (column.DataType == typeof(short))
                    {
                        short result = short.Parse(argument);
                        command.Parameters.AddWithValue($"@{i}", result);
                    }
                    else
                    {
                        command.Parameters.AddWithValue($"@{i}", argument);
                    }
                    i++;
                }

                if (setPart.Length == 0 || wherePart.Length == 0)
                {
                    MessageBox.Show("You can't update this element. Delete this and create a new element.",
                                    "Error",
                                    MessageBoxButtons.OK,
                                    MessageBoxIcon.Warning,
                                    MessageBoxDefaultButton.Button1,
                                    MessageBoxOptions.DefaultDesktopOnly);
                    return;
                }

                updateString       += " SET " + setPart + " WHERE " + wherePart;
                command.Connection  = db.GetConnection();
                command.CommandText = updateString;

                if (command.ExecuteNonQuery() == 1)
                {
                    UpdateTable(db, currentTableName.Text);
                }
            }
            catch (OleDbException ex)
            {
                MessageBox.Show(ex.Message, "Warning",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Warning,
                                MessageBoxDefaultButton.Button1,
                                MessageBoxOptions.DefaultDesktopOnly);
            }
            finally
            {
                db.CloseConnection();
            }
        }
Exemplo n.º 2
0
        private void insertButton_Click(object sender, EventArgs e)
        {
            Database             db = new Database();
            DynamicInputParamBox inputFields;
            int i;

            db.OpenConnection();
            try
            {
                inputFields = new DynamicInputParamBox("ADD", currentTableName.Text, (DataTable)dataGridView1.DataSource);
                if (inputFields.ShowDialog() != DialogResult.OK)
                {
                    return;
                }

                String insertString = $"INSERT INTO [{currentTableName.Text}] VALUES(";
                bool   isFirstParam = true;
                i = 0;
                foreach (DataColumn column in ((DataTable)dataGridView1.DataSource).Columns)
                {
                    if (!isFirstParam)
                    {
                        insertString += ", ";
                    }
                    else
                    {
                        isFirstParam = false;
                    }
                    insertString += $"@{i}";
                    i++;
                }
                insertString += ")";

                OleDbCommand  command = new OleDbCommand(insertString, db.GetConnection());
                List <String> inputs  = inputFields.getFieldsData();
                i = 0;
                foreach (DataColumn column in ((DataTable)dataGridView1.DataSource).Columns)
                {
                    if (column.DataType == typeof(int))
                    {
                        int result = int.Parse(inputs[i]);
                        command.Parameters.AddWithValue($"@{i}", result);
                    }
                    else if (column.DataType == typeof(double))
                    {
                        double result = double.Parse(inputs[i]);
                        command.Parameters.AddWithValue($"@{i}", result);
                    }
                    else if (column.DataType == typeof(DateTime))
                    {
                        DateTime result = DateTime.Parse(inputs[i]);
                        command.Parameters.AddWithValue($"@{i}", result);
                    }
                    else if (column.DataType == typeof(short))
                    {
                        short result = short.Parse(inputs[i]);
                        command.Parameters.AddWithValue($"@{i}", result);
                    }
                    else
                    {
                        command.Parameters.AddWithValue($"@{i}", inputs[i]);
                    }
                    i++;
                }

                if (command.ExecuteNonQuery() == 1)
                {
                    UpdateTable(db, currentTableName.Text);
                }
            }
            catch (OleDbException ex)
            {
                MessageBox.Show(ex.Message, "Warning",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Warning,
                                MessageBoxDefaultButton.Button1,
                                MessageBoxOptions.DefaultDesktopOnly);
            }
            finally
            {
                db.CloseConnection();
            }
        }