private void setForeignKey(string columnName, string tableName)
{
if (columnName.ToUpper().EndsWith("ID"))
{
string primaryKeyTableName = columnName.ToUpper().Remove(columnName.Length - 2, 2);
if (this.Schema.Tables.Any(x => x.Name.ToUpper() == primaryKeyTableName))
{
Table primaryKeyTable = this.Schema.Tables.First(x => x.Name.ToUpper() == primaryKeyTableName.ToUpper());
DatabaseContracts.Constraint primaryKey = primaryKeyTable.Constraints.FirstOrDefault(x => x.IsPrimaryKey);
if (primaryKey != null)
{
var foreignKey = new ForeignKey()
{
ForeignKeyName = ($"FK_{tableName}_{columnName}_{primaryKeyTableName}_{primaryKey.Columns.First().Name}").ToLower(),
Columns = new List <ForeignKeyColumn>()
{
new ForeignKeyColumn()
{
ForeignKeyColumnName = columnName,
ForeignKeySchemaName = this.Schema.Name,
ForeignKeyTableName = this.Table.Name,
Order = 0,
PrimaryKeyColumnName = primaryKey.Columns.First().Name,
PrimaryKeySchemaName = this.Schema.Name,
PrimaryKeyTableName = primaryKeyTable.Name
}
}
};
DatabaseContracts.Constraint contraint = new DatabaseContracts.Constraint()
{
ConstraintType = "NONCLUSTERED",
Name = $"IX_{tableName}_{columnName}",
SchemaName = this.Schema.Name,
TableName = tableName,
IsPrimaryKey = false,
IsUnique = false,
Columns = new List <ConstraintColumn>()
{
new ConstraintColumn()
{
Descending = false,
IsIncludedColumn = false,
Order = 0,
Name = columnName,
IsIdentity = false,
}
}
};
this.ForeignKeySavedEvent(this, new ForeignKeySavedEventArgs(foreignKey, contraint));
}
}
}
}
private void loadConstraints(DatabaseContracts.Constraint selection = null)
{
this.comboBoxConstraints.DataSource = null;
this.comboBoxConstraints.DataSource = this.Table.Constraints;
this.comboBoxConstraints.DisplayMember = "Name";
if (selection != null)
{
this.comboBoxConstraints.SelectedItem = selection;
}
}
private void buttonSave_Click(object sender, EventArgs e)
{
if (this.Table.Constraints.Any(c => c.Columns.Count == 0))
{
DatabaseContracts.Constraint constraint = this.Table.Constraints.First(c => c.Columns.Count == 0);
MessageBox.Show($"Constraint {constraint.Name} does not have any columns and cannot be saved. You must add columns or delete it.");
return;
}
this.Saved = true;
this.Close();
}
private void setPrimaryKey(string columnName)
{
if (columnName.ToUpper() == "ID")
{
var primaryKey = new DatabaseContracts.Constraint();
primaryKey.Columns.Add(new ConstraintColumn()
{
Name = columnName,
Order = 0,
IsIdentity = true
});
primaryKey.Name = "PK_" + this.Table.Name;
primaryKey.SchemaName = this.Schema.Name;
primaryKey.TableName = this.Table.Name;
primaryKey.IsPrimaryKey = true;
primaryKey.IsUnique = true;
this.ConstraintSavedEvent(this, new ConstraintSavedEventArgs(primaryKey));
}
}
private void createPrimaryKeyToolStripMenuItem_Click(object sender, EventArgs e)
{
if (listBoxColumns.SelectedIndex > -1)
{
var column = (UIColumn)listBoxColumns.SelectedItem;
var table = (Table)listBoxTables.SelectedItem;
var schema = (Schema)listBoxSchemas.SelectedItem;
var primaryKey = new DatabaseContracts.Constraint()
{
Name = $"PK_{table.Name}",
SchemaName = schema.Name,
TableName = table.Name,
IsPrimaryKey = true,
IsUnique = true,
ConstraintType = "CLUSTERED",
Columns = new List <ConstraintColumn>()
};
bool isMultiTenant = table.Columns.Any(x => x.Name.ToUpper() == "TENANTID");
if (isMultiTenant)
{
primaryKey.Columns.Add(new ConstraintColumn()
{
Name = "tenantId",
Order = 0,
Descending = false,
IsIncludedColumn = false
});
}
primaryKey.Columns.Add(new ConstraintColumn()
{
Name = column.Name,
Order = isMultiTenant ? 1 : 0,
Descending = false,
IsIncludedColumn = false
});
table.AddConstraint(primaryKey);
this.loadColumns(table.Columns);
}
}
public string GenerateCreateTableStatement(string schema, Table table, List <ForeignKey> foreignKeys)
{
StringBuilder sb = new StringBuilder();
List <Column> columnList = table.Columns;
Constraint primaryKey = table.Constraints.FirstOrDefault(x => x.IsPrimaryKey && x.TableName == table.Name);
if (primaryKey != null)
{
Column primaryKeyColumn = columnList.First(x => x.Name == primaryKey.Columns.First().Name);
columnList.Remove(primaryKeyColumn);
columnList.Insert(0, primaryKeyColumn);
}
sb.AppendLine($@"CREATE TABLE ""{schema}"".""{table.Name}""(");
string statement = string.Empty;
columnList.ForEach(column =>
{
bool isRowGuidColumn = table.DefaultConstraints.Any(x => x.IsRowGuid && x.ColumnName.ToUpper() == column.Name.ToUpper());
string maxLengthString = String.Empty;
maxLengthString = column.MaxLength > 0 ? $"({ column.MaxLength.ToString()})" : "";
DatabaseContracts.Constraint identityConstraint = table.Constraints.FirstOrDefault(x =>
x.SchemaName == schema &&
x.TableName == table.Name &&
x.Columns.FirstOrDefault(c => c.Name.ToUpper() != "TENANTID" && c.IsIdentity)?.Name == column.Name);
string identityString = String.Empty;
if (identityConstraint != null && column.Name.ToUpper() != "TENANTID")
{
if (column.DataType.ToUpper() == "SMALLINT" || column.DataType.ToUpper() == "INT2" || column.DataType.ToUpper() == "SMALLSERIAL" || column.DataType.ToUpper() == "SERIAL2")
{
identityString = "SMALLSERIAL";
}
else if (column.DataType.ToUpper() == "INTEGER" || column.DataType.ToUpper() == "INT4" || column.DataType.ToUpper() == "SERIAL" || column.DataType.ToUpper() == "SERIAL4" || column.DataType.ToUpper() == "INT")
{
identityString = "SERIAL";
}
else if (column.DataType.ToUpper() == "BIGINT" || column.DataType.ToUpper() == "BIGSERIAL" || column.DataType.ToUpper() == "SERIAL8" || column.DataType.ToUpper() == "INT8")
{
identityString = "BIGSERIAL";
}
}
if (string.IsNullOrWhiteSpace(identityString))
{
string nullString = column.IsNullable ? "NULL" : "NOT NULL";
statement += $@"""{column.Name}"" {column.DataType} {maxLengthString} {identityString} {nullString},";
statement += Environment.NewLine;
}
else
{
statement += $@"""{column.Name}"" {identityString} ,";
statement += Environment.NewLine;
}
});
sb.Append(statement.Remove(statement.LastIndexOf("," + Environment.NewLine)));
sb.AppendLine($");");
return(sb.ToString());
}
private void buttonSave_Click(object sender, EventArgs e)
{
if (comboBoxTable.SelectedIndex > -1 && comboBoxColumn.SelectedIndex > -1)
{
var column = (Column)comboBoxColumn.SelectedItem;
var table = (Table)comboBoxTable.SelectedItem;
ForeignKeyColumn fkColumn = new ForeignKeyColumn()
{
ForeignKeyColumnName = this._foreignColumn,
ForeignKeyTableName = this._foreignTable,
PrimaryKeyColumnName = column.Name,
PrimaryKeyTableName = table.Name,
ForeignKeySchemaName = this._schema.Name,
PrimaryKeySchemaName = this._schema.Name
};
ForeignKeyColumn tenantColumn = new ForeignKeyColumn()
{
ForeignKeyColumnName = "tenantId",
ForeignKeyTableName = this._foreignTable,
PrimaryKeyColumnName = "tenantId",
PrimaryKeyTableName = table.Name,
ForeignKeySchemaName = this._schema.Name,
PrimaryKeySchemaName = this._schema.Name
};
DatabaseContracts.Constraint contraint = new DatabaseContracts.Constraint()
{
ConstraintType = "NONCLUSTERED",
Name = $"IX_{this._foreignTable}_{this._foreignColumn}",
SchemaName = this._schema.Name,
TableName = this._foreignTable,
IsPrimaryKey = false,
IsUnique = false,
Columns = new List <ConstraintColumn>()
};
bool isMultiTenant = table.Columns.Any(x => x.Name.ToUpper() == "TENANTID");
if (isMultiTenant)
{
contraint.Columns.Add(new ConstraintColumn()
{
Descending = false,
IsIncludedColumn = false,
Order = 0,
Name = "tenantId",
IsIdentity = false,
});
}
contraint.Columns.Add(new ConstraintColumn()
{
Descending = false,
IsIncludedColumn = false,
Order = isMultiTenant ? 1 :0,
Name = this._foreignColumn,
IsIdentity = false,
});
this.Constraint = contraint;
this.ForeignKey.ForeignKeyName = textBoxKeyName.Text;
if (isMultiTenant)
{
this.ForeignKey.Columns.Add(tenantColumn);
}
this.ForeignKey.Columns.Add(fkColumn);
this.Saved = true;
}
;
this.Close();
}
public string GenerateCreateTableStatement(string schema, Table table, List <ForeignKey> foreignKeys)
{
StringBuilder sb = new StringBuilder();
List <Column> columnList = table.Columns;
Constraint primaryKey = table.Constraints.FirstOrDefault(x => x.IsPrimaryKey && x.TableName == table.Name);
if (primaryKey != null)
{
Column primaryKeyColumn = columnList.First(x => x.Name == primaryKey.Columns.First().Name);
columnList.Remove(primaryKeyColumn);
columnList.Insert(0, primaryKeyColumn);
}
sb.AppendLine($"CREATE TABLE [{schema}].[{table.Name}](");
columnList.ForEach(column =>
{
bool isRowGuidColumn = table.DefaultConstraints.Any(x => x.IsRowGuid && x.ColumnName.ToUpper() == column.Name.ToUpper());
string maxLengthString = String.Empty;
if (column.DataType.ToUpper() == "HIERARCHYID")
{
maxLengthString = "";
}
else if (column.MaxLength > 8000)
{
maxLengthString = "";
}
else
{
maxLengthString = column.MaxLength > 0 ? $"({ column.MaxLength.ToString()})" : "";
}
string rowGuid = String.Empty;
if (isRowGuidColumn)
{
rowGuid = "ROWGUIDCOL";
}
DatabaseContracts.Constraint identityConstraint = table.Constraints.FirstOrDefault(x =>
x.SchemaName == schema &&
x.TableName == table.Name &&
x.Columns.FirstOrDefault(c => c.Name.ToUpper() != "TENANTID" && c.IsIdentity)?.Name == column.Name);
string identityString = String.Empty;
if (identityConstraint != null && column.Name.ToUpper() != "TENANTID")
{
if (column.DataType.ToUpper() == "INT" || column.DataType.ToUpper() == "BIGINT")
{
identityString = "IDENTITY(1,1)";
}
}
string nullString = column.IsNullable ? "NULL" : "NOT NULL";
sb.AppendLine($"[{column.Name}] [{column.DataType}] {maxLengthString} {identityString} {rowGuid} {nullString},");
});
sb.AppendLine($") ON[PRIMARY]");
sb.AppendLine($"GO");
return(sb.ToString());
}
