public void DropColumn(Table table, Column col) { var dropColumn = conn.CreateCommand(); dropColumn.CommandText = string.Format("ALTER TABLE [{0}] DROP COLUMN [{1}]", table.Name, col.Name); dropColumn.ExecuteNonQuery(); }
private void AddColumn(Table table, Column column) { var addColumn = conn.CreateCommand(); if (column.IsComputed) addColumn.CommandText = string.Format("ALTER TABLE [{0}] ADD [{1}] AS ({2})", table.Name, column.Name, column.ComputedDefinition); else if (column.DataType.Equals(SqlDbType.NVarChar.ToString(), StringComparison.InvariantCultureIgnoreCase)) addColumn.CommandText = string.Format("ALTER TABLE [{0}] ADD [{1}] {2}({3}) {4}", table.Name, column.Name, column.DataType, column.Length, column.IsNullable ? "NULL" : "NOT NULL"); else addColumn.CommandText = string.Format("ALTER TABLE [{0}] ADD [{1}] {2} {3}", table.Name, column.Name, column.DataType, column.IsNullable ? "NULL" : "NOT NULL"); addColumn.ExecuteNonQuery(); }
public void Ensure(IEnumerable<EntityDefinition> definitions, IEnumerable<EntityRelation> relations) { foreach (var def in definitions) { EntityDefinition existing = Registry.GetDefintionByName(def.Name); if (existing != null) { //Upgrade foreach (var prop in def.Properties) { var existingProp = existing.Properties.Find(p => p.Name == prop.Name); if (existingProp == null) //added existing.Properties.Add(prop); else //change { existing.Properties.Remove(existingProp); existing.Properties.Add(prop); } } } else { //Create //TODO: Think about adding the id prop to definitions //var idProp = def.Properties.Find(prop => prop.Name.Equals("Id", StringComparison.InvariantCultureIgnoreCase)); //if (idProp == null) //{ // idProp = new PropertyDefinition("Id", PropertyTypes.Integer, nullable: false); //} Registry.AddDefinition(def); } } if (relations != null) { foreach (var relation in relations) { var existing = Registry.GetRelation(relation.LeftEntity, relation.Role); if (existing != null && relation.Type != existing.Type) throw new NotImplementedException("Upgrade of relations is not implemented yet!"); else if (existing == null) //new relation Registry.AddRelation(relation); } } List<StoredProcedure> procs = new List<StoredProcedure>(); var tables = new List<Table>(); foreach (var def in Registry.GetAllDefinitions()) { var table = new Table(def.Name); foreach (var prop in def.Properties) { var length = 0; string computedDefinition = null; if (prop is ComputedProperty) computedDefinition = ((ComputedProperty)prop).Format; if (prop is StringProperty) length = ((StringProperty)prop).Length; table.Columns.Add(new Column(prop.Name, EntityValueMapper.GetDbType(prop.Type), length, prop.Nullable, computed: computedDefinition)); if (prop.Unique) table.Constraints.Add(new Sql.Constraint(string.Format("UK_{0}_{1}", def.Name, prop.Name), Sql.Constraint.UNIQUE, prop.Name)); //if(prop.HasDefault) //{ // string defValue = null; // if (prop.Type == PropertyTypes.Boolean) // { // defValue = bool.Parse(prop.DefaultValue) ? "1" : "0"; // } // else // throw new NotImplementedException(); //TODO: Default constraint! // //table.Constraints.Add(new Sql.DefaultConstraint(string.Format("DFLT_{0}_{1}", def.Name, prop.Name), prop.Name, defValue)); //} } var idCol = new Column("Id", EntityValueMapper.GetDbType(PropertyTypes.Integer), nullable: false, identity: true); table.Columns.Add(idCol); table.Constraints.Add(new Sql.Constraint(string.Format("PK_{0}", def.Name), Sql.Constraint.PRIMARY_KEY, idCol.Name)); tables.Add(table); procs.AddRange(BuildProcs(def)); } foreach (var relation in Registry.GetAllRelations()) { var table = new Table(string.Format("{0}_{1}_{2}", relation.LeftEntity, relation.RightEntity, relation.Role)); table.Columns.Add(new Column("Id", SqlDbType.Int, nullable: false, identity: true)); table.Columns.Add(new Column("LID", SqlDbType.Int, nullable: false)); table.Columns.Add(new Column("RID", SqlDbType.Int, nullable: false)); switch (relation.Type) { case RelationTypes.OneToOne: table.Constraints.Add(new Sql.Constraint(string.Format("PK_{0}", table.Name), Sql.Constraint.PRIMARY_KEY, "LID", "RID")); break; case RelationTypes.OneToMany: table.Constraints.Add(new Sql.Constraint(string.Format("PK_{0}", table.Name), Sql.Constraint.PRIMARY_KEY, "RID")); break; case RelationTypes.ManyToOne: table.Constraints.Add(new Sql.Constraint(string.Format("PK_{0}", table.Name), Sql.Constraint.PRIMARY_KEY, "LID")); break; case RelationTypes.ManyToMany: table.Constraints.Add(new Sql.Constraint(string.Format("PK_{0}", table.Name), Sql.Constraint.PRIMARY_KEY, "Id")); break; default: throw new NotImplementedException("Unknown RelationType."); } table.Constraints.Add(new Sql.ForeignKeyConstraint(string.Format("FK_{0}_{1}", table.Name, relation.LeftEntity), new string[] { "LID" }, relation.LeftEntity, new string[] { "Id" })); table.Constraints.Add(new Sql.ForeignKeyConstraint(string.Format("FK_{0}_{1}", table.Name, relation.RightEntity), new string[] { "RID" }, relation.RightEntity, new string[] { "Id" })); tables.Add(table); } using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EntityDB"].ConnectionString)) { conn.Open(); //var tran = conn.BeginTransaction(); DatabaseManager du = new DatabaseManager(conn); du.Merge(tables, procs); //tran.Commit(); } SaveRegistry(); }