public void AddColumnToTable(AddNewTableDto tableDto, ConnectionDto connDto)
{
try
{
using var con = new NpgsqlConnection(ConnectionString(connDto));
con.Open();
using var cmd = new NpgsqlCommand();
cmd.Connection = con;
cmd.CommandText = $"ALTER TABLE IF EXISTS {tableDto.TableName}";
foreach (var column in tableDto.Columns)
{
cmd.CommandText += $" ADD COLUMN {column.ColumnName} {column.Value},";
}
cmd.CommandText = cmd.CommandText.TrimEnd(',');
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public void CreateDatabaseTable(AddNewTableDto tableDto, ConnectionDto connDto)
{
try
{
using var con = new NpgsqlConnection(ConnectionString(connDto));
con.Open();
using var cmd = new NpgsqlCommand();
cmd.Connection = con;
cmd.CommandText = $"DROP TABLE IF EXISTS {tableDto.TableName}";
cmd.ExecuteNonQuery();
cmd.CommandText = $@"CREATE TABLE {tableDto.TableName}(id SERIAL PRIMARY KEY";
foreach (var column in tableDto.Columns)
{
cmd.CommandText += $", {column.ColumnName} {column.Value}";
}
cmd.CommandText += ")";
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
private void tables_add_addTable_button_Click(object sender, EventArgs e)
{
var tableName = tables_add_tableName_textBox.Text;
var columns = tables_add_columnNames_listBox.Items;
var valueTypes = tables_add_valueTypes_listBox.Items;
if (string.IsNullOrWhiteSpace(tableName) || columns.Count == 0 || valueTypes.Count == 0 || columns.Count != valueTypes.Count)
{
HandleError("Table name, columns, and value types must not be empty. Column and values count must match.");
return;
}
var newTableDto = new AddNewTableDto()
{
TableName = $"{dash_statusStrip_schema_value.Text}.{tableName}",
Columns = new List <ColumnDto>()
};
for (var i = 0; i < columns.Count; i++)
{
newTableDto.Columns.Add(new ColumnDto
{
ColumnName = columns[i].ToString(),
Value = valueTypes[i].ToString()
});
}
try
{
_sql.CreateDatabaseTable(newTableDto, connDto);
WriteToLog("Successfully created new table:");
WriteToLog(tableName);
resetTableList();
tables_add_columnNames_listBox.Items.Clear();
tables_add_valueTypes_listBox.Items.Clear();
tables_add_tableName_textBox.Text = string.Empty;
}
catch (Exception ex)
{
HandleError("Error creating new table:", ex);
return;
}
}
