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