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