Beispiel #1
0
        public int UpdateRowFromTable(FormUpdateDto dto, ConnectionDto connDto)
        {
            try
            {
                using var con = new NpgsqlConnection(ConnectionString(connDto));
                con.Open();

                using var cmd  = new NpgsqlCommand();
                cmd.Connection = con;

                cmd.CommandText = $"UPDATE {dto.TableName} SET";

                for (int i = 0; i < dto.Columns.Count; i++)
                {
                    cmd.CommandText += $" {dto.Columns[i].ColumnName} = ";

                    if (RequiresQuotes(dto.Columns[i].Value))
                    {
                        cmd.CommandText += $"'{dto.Values[i]}'";
                    }
                    else
                    {
                        cmd.CommandText += $"{dto.Values[i]}";
                    }
                    cmd.CommandText += ",";
                }

                cmd.CommandText = cmd.CommandText.TrimEnd(',');

                cmd.CommandText += $" WHERE {dto.WhereColumn.ColumnName} {dto.WhereOperator} ";

                if (RequiresQuotes(dto.WhereColumn.Value))
                {
                    cmd.CommandText += $"'{dto.WhereValue}'";
                }
                else
                {
                    cmd.CommandText += $"{dto.WhereValue}";
                }

                NpgsqlDataReader dr = cmd.ExecuteReader();

                con.Close();

                return(dr.RecordsAffected);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
Beispiel #2
0
        private void rows_update_update_button_Click(object sender, EventArgs e)
        {
            var tableName = dash_tables_listBox.SelectedItem?.ToString();

            var selectedColumns = rows_update_selectedColumns_listBox.Items;
            var values          = rows_update_values_listBox.Items;

            var whereColumn   = rows_update_whereColumn_comboBox.SelectedItem?.ToString();
            var whereOperator = rows_update_whereOperator_comboBox.SelectedItem?.ToString();
            var whereValue    = rows_update_whereValue_textBox.Text;

            if (string.IsNullOrWhiteSpace(tableName))
            {
                HandleError("Must select a table.");
                return;
            }
            if (selectedColumns.Count != values.Count)
            {
                HandleError("Selected columns and values counts must match");
                return;
            }

            if (string.IsNullOrWhiteSpace(whereColumn) || string.IsNullOrWhiteSpace(whereOperator) || string.IsNullOrWhiteSpace(whereValue))
            {
                HandleError("Must have WHERE column, operator, and value");
                return;
            }

            try
            {
                var dto = new FormUpdateDto()
                {
                    TableName   = $"{dash_statusStrip_schema_value.Text}.{tableName}",
                    Columns     = new List <ColumnDto>(),
                    WhereColumn = new ColumnDto()
                    {
                        ColumnName = whereColumn.Split(' ')[0],
                        Value      = whereColumn.Split(' ')[1].Trim(new char[] { '(', ')' })
                    },
                    WhereOperator = whereOperator,
                    WhereValue    = whereValue,
                    Values        = new List <string>()
                };

                foreach (var val in values)
                {
                    dto.Values.Add(val.ToString());
                }
                foreach (var col in selectedColumns)
                {
                    dto.Columns.Add(new ColumnDto()
                    {
                        ColumnName = col.ToString().Split(' ')[0],
                        Value      = col.ToString().Split(' ')[1].Trim(new char[] { '(', ')' })
                    });
                }

                var count = _sql.UpdateRowFromTable(dto, connDto);

                WriteToLog($"Successfully updated {count} row(s).");

                rows_update_whereColumn_comboBox.SelectedIndex = -1;
                dash_tables_listBox_SelectedIndexChanged(sender, e);
                rows_update_selectedColumns_listBox.Items.Clear();
                rows_update_values_listBox.Items.Clear();
                rows_update_whereOperator_comboBox.SelectedIndex = -1;
                rows_update_whereValue_textBox.Text = string.Empty;
            }
            catch (Exception ex)
            {
                HandleError("Error updating rows:", ex);
                return;
            }
        }