public void AddColumnConstraint(AddColumnConstraintDto dto, ConnectionDto connDto)
{
try
{
using var con = new NpgsqlConnection(ConnectionString(connDto));
con.Open();
using var cmd = new NpgsqlCommand();
cmd.Connection = con;
cmd.CommandText = string.Empty;
string alterTable = $"ALTER TABLE {dto.TableName} ";
if (dto.Unique)
{
cmd.CommandText += alterTable + $"ADD UNIQUE ({dto.ColumnName});";
}
if (dto.NotNull)
{
cmd.CommandText += alterTable + $"ALTER COLUMN {dto.ColumnName} SET NOT NULL;";
}
if (dto.References)
{
cmd.CommandText += alterTable + $"ADD FOREIGN KEY ({dto.ColumnName}) REFERENCES {dto.ReferencesTableName}({dto.ReferencesColumnName}) ON UPDATE CASCADE ON DELETE CASCADE";
}
if (dto.Check)
{
cmd.CommandText += alterTable + $"ADD CONSTRAINT {dto.CheckName} CHECK ({dto.CheckColumn} {dto.CheckOperator} {dto.CheckValue});";
}
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
private void columns_addConstraint_add_button_Click(object sender, EventArgs e)
{
var tableName = dash_tables_listBox.SelectedItem?.ToString();
var columnName = dash_columns_listBox.SelectedItem?.ToString();
if (string.IsNullOrWhiteSpace(tableName) || string.IsNullOrWhiteSpace(columnName))
{
HandleError("Table and column must be selected.");
return;
}
if (!columns_addConstraint_unique_checkBox.Checked && !columns_addConstraint_notNull_checkBox.Checked && !columns_addConstraint_references_checkBox.Checked && !columns_addConstraint_check_checkBox.Checked)
{
HandleError("You must check one constraint");
return;
}
if (columns_addConstraint_references_checkBox.Checked && (string.IsNullOrWhiteSpace(columns_addConstraint_references_table_comboBox.SelectedItem?.ToString()) || string.IsNullOrWhiteSpace(columns_addConstraint_references_column_comboBox.SelectedItem?.ToString())))
{
HandleError("You must select a reference table and column.");
return;
}
if (columns_addConstraint_check_checkBox.Checked && (string.IsNullOrWhiteSpace(columns_addConstraint_check_checkName_textBox.Text) || string.IsNullOrWhiteSpace(columns_addConstraint_check_columns_comboBox.SelectedItem?.ToString()) || string.IsNullOrWhiteSpace(columns_addConstraint_check_operators_comboBox.SelectedItem?.ToString()) || string.IsNullOrWhiteSpace(columns_addConstraint_check_value_textBox.Text)))
{
HandleError("You must have a check name, column, operator, and value.");
return;
}
var dto = new AddColumnConstraintDto()
{
TableName = $"{dash_statusStrip_schema_value.Text}.{tableName}",
ColumnName = columnName.Split(' ')[0],
Unique = columns_addConstraint_unique_checkBox.Checked,
NotNull = columns_addConstraint_notNull_checkBox.Checked,
References = columns_addConstraint_references_checkBox.Checked,
Check = columns_addConstraint_check_checkBox.Checked
};
if (dto.References)
{
dto.ReferencesTableName = columns_addConstraint_references_table_comboBox.SelectedItem?.ToString();
dto.ReferencesColumnName = columns_addConstraint_references_column_comboBox.SelectedItem?.ToString();
}
if (dto.Check)
{
dto.CheckName = columns_addConstraint_check_checkName_textBox.Text;
dto.CheckColumn = columns_addConstraint_check_columns_comboBox.SelectedItem?.ToString();
dto.CheckOperator = columns_addConstraint_check_operators_comboBox.SelectedItem?.ToString();
dto.CheckValue = columns_addConstraint_check_value_textBox.Text;
}
try
{
_sql.AddColumnConstraint(dto, connDto);
WriteToLog("Successfully added constraint(s)");
dash_tables_listBox_SelectedIndexChanged(sender, e);
columns_addConstraint_check_checkBox.Checked = false;
columns_addConstraint_notNull_checkBox.Checked = false;
columns_addConstraint_references_checkBox.Checked = false;
columns_addConstraint_unique_checkBox.Checked = false;
columns_addConstraint_check_columns_comboBox.SelectedIndex = -1;
columns_addConstraint_check_operators_comboBox.SelectedIndex = -1;
columns_addConstraint_references_column_comboBox.SelectedIndex = -1;
columns_addConstraint_references_table_comboBox.SelectedIndex = -1;
columns_addConstraint_check_checkName_textBox.Text = string.Empty;
columns_addConstraint_check_value_textBox.Text = string.Empty;
}
catch (Exception ex)
{
HandleError("Error adding column constraint:", ex);
return;
}
}
