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