private void rows_add_addRow_button_Click(object sender, EventArgs e)
{
var table = dash_tables_listBox.SelectedItem?.ToString();
if (string.IsNullOrWhiteSpace(table))
{
HandleError("Must select table");
return;
}
if (dash_columns_listBox.Items.Count - 1 != rows_add_rowValues_listBox.Items.Count)
{
HandleError("Must provide a value for every column except ID");
return;
}
var columns = new List <ColumnDto>();
foreach (var col in dash_columns_listBox.Items)
{
var name = col.ToString().Split(' ')[0];
var type = col.ToString().Split(' ')[1].Trim(new char[] { '(', ')' });
columns.Add(new ColumnDto()
{
ColumnName = name,
Value = type
});
}
var rows = new List <string>();
foreach (var row in rows_add_rowValues_listBox.Items)
{
rows.Add(row.ToString());
}
var dto = new AddNewRowDto()
{
TableName = $"{dash_statusStrip_schema_value.Text}.{table}",
Columns = columns,
Rows = rows
};
try
{
var id = _sql.AddRowToTable(dto, connDto);
WriteToLog($"Successfully added values into row ID: {id}");
rows_add_rowValues_listBox.Items.Clear();
rows_add_rowValue_textBox.Text = string.Empty;
}
catch (Exception ex)
{
HandleError("Error adding values:", ex);
return;
}
}
public int AddRowToTable(AddNewRowDto tableDto, ConnectionDto connDto)
{
try
{
using var con = new NpgsqlConnection(ConnectionString(connDto));
con.Open();
using var cmd = new NpgsqlCommand();
cmd.Connection = con;
cmd.CommandText = $"INSERT INTO {tableDto.TableName} (";
for (int i = 1; i < tableDto.Columns.Count; i++)
{
cmd.CommandText += tableDto.Columns[i].ColumnName.ToString();
cmd.CommandText += ",";
}
cmd.CommandText = cmd.CommandText.TrimEnd(',');
cmd.CommandText += ") VALUES (";
for (int i = 0; i < tableDto.Rows.Count; i++)
{
if (RequiresQuotes(tableDto.Columns[i + 1].Value))
{
cmd.CommandText += $"'{tableDto.Rows[i]}'";
}
else
{
cmd.CommandText += tableDto.Rows[i];
}
cmd.CommandText += ",";
}
cmd.CommandText = cmd.CommandText.TrimEnd(',');
cmd.CommandText += ") RETURNING ID";
NpgsqlDataReader dr = cmd.ExecuteReader();
int id = 0;
while (dr.Read())
{
id = dr.GetInt32(0);
}
con.Close();
return(id);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
