public void FillCheck(Database database, string connectionString) { int parentId = 0; ISchemaBase table = null; using (SqlConnection conn = new SqlConnection(connectionString)) { using (SqlCommand command = new SqlCommand(ConstraintSQLCommand.GetCheck(database.Info.Version), conn)) { root.RaiseOnReading(new ProgressEventArgs("Reading constraint...", Constants.READING_CONSTRAINTS)); conn.Open(); command.CommandTimeout = 0; using (SqlDataReader reader = command.ExecuteReader()) { Constraint item = null; while (reader.Read()) { root.RaiseOnReadingOne(reader["Name"]); if (parentId != (int)reader["parent_object_id"]) { parentId = (int)reader["parent_object_id"]; if (reader["ObjectType"].ToString().Trim().Equals("U")) table = database.Tables.Find(parentId); else table = database.TablesTypes.Find(parentId); } item = new Constraint(table); item.Id = (int)reader["id"]; item.Name = reader["Name"].ToString(); item.Type = Constraint.ConstraintType.Check; item.Definition = reader["Definition"].ToString(); item.WithNoCheck = (bool)reader["WithCheck"]; item.IsDisabled = (bool)reader["is_disabled"]; item.Owner = reader["Owner"].ToString(); if (database.Options.Ignore.FilterNotForReplication) item.NotForReplication = (bool)reader["is_not_for_replication"]; if (reader["ObjectType"].ToString().Trim().Equals("U")) ((Table)table).Constraints.Add(item); else ((TableType)table).Constraints.Add(item); } } } } }
/// <summary> /// Clona el objeto Column en una nueva instancia. /// </summary> public override ISchemaBase Clone(ISchemaBase parent) { Constraint col = new Constraint(parent); col.Id = this.Id; col.Name = this.Name; col.NotForReplication = this.NotForReplication; col.RelationalTableFullName = this.RelationalTableFullName; col.Status = this.Status; col.Type = this.Type; col.WithNoCheck = this.WithNoCheck; col.OnDeleteCascade = this.OnDeleteCascade; col.OnUpdateCascade = this.OnUpdateCascade; col.Owner = this.Owner; col.Columns = this.Columns.Clone(); col.Index = (Index)this.Index.Clone(parent); col.IsDisabled = this.IsDisabled; col.Definition = this.Definition; col.Guid = this.Guid; return col; }
private static void FillForeignKey(Database database, string connectionString) { int lastid = 0; int parentId = 0; Table table = null; using (SqlConnection conn = new SqlConnection(connectionString)) { using (SqlCommand command = new SqlCommand(GetSQLForeignKey(), conn)) { conn.Open(); command.CommandTimeout = 0; using (SqlDataReader reader = command.ExecuteReader()) { Constraint con = null; while (reader.Read()) { if (parentId != (int)reader["parent_object_id"]) { parentId = (int)reader["parent_object_id"]; table = database.Tables.Find(parentId); } if (lastid != (int)reader["object_id"]) { con = new Constraint(table); con.Id = (int)reader["object_id"]; con.Name = reader["Name"].ToString(); con.Type = Constraint.ConstraintType.ForeignKey; con.WithNoCheck = (bool)reader["is_not_trusted"]; con.RelationalTableFullName = "[" + reader["ReferenceOwner"].ToString() + "].[" + reader["TableRelationalName"].ToString() + "]"; con.RelationalTableId = (int)reader["TableRelationalId"]; con.Owner = reader["Owner"].ToString(); con.IsDisabled = (bool)reader["is_disabled"]; con.OnDeleteCascade = (byte)reader["delete_referential_action"]; con.OnUpdateCascade = (byte)reader["update_referential_action"]; if (database.Options.Ignore.FilterNotForReplication) con.NotForReplication = (bool)reader["is_not_for_replication"]; lastid = (int)reader["object_id"]; table.Constraints.Add(con); } ConstraintColumn ccon = new ConstraintColumn(con); ccon.Name = reader["ColumnName"].ToString(); ccon.ColumnRelationalName = reader["ColumnRelationalName"].ToString(); ccon.ColumnRelationalId = (int)reader["ColumnRelationalId"]; ccon.Id = (int)reader["ColumnId"]; ccon.KeyOrder = con.Columns.Count; ccon.ColumnRelationalDataTypeId = (int)reader["user_type_id"]; //table.DependenciesCount++; con.Columns.Add(ccon); } } } } }
private static void FillUniqueKey(Database database, string connectionString) { int lastId = 0; int parentId = 0; bool change = false; ISchemaBase table = null; using (SqlConnection conn = new SqlConnection(connectionString)) { using (SqlCommand command = new SqlCommand(ConstraintSQLCommand.GetUniqueKey(database.Info.Version), conn)) { conn.Open(); command.CommandTimeout = 0; using (SqlDataReader reader = command.ExecuteReader()) { Constraint con = null; while (reader.Read()) { if (parentId != (int)reader["ID"]) { parentId = (int)reader["ID"]; if (reader["ObjectType"].ToString().Trim().Equals("U")) table = database.Tables.Find(parentId); else table = database.TablesTypes.Find(parentId); change = true; } else change = false; if ((lastId != (int)reader["Index_id"]) || (change)) { con = new Constraint(table); con.Name = reader["Name"].ToString(); con.Owner = (string)reader["Owner"]; con.Id = (int)reader["Index_id"]; con.Type = Constraint.ConstraintType.Unique; con.Index.Id = (int)reader["Index_id"]; con.Index.AllowPageLocks = (bool)reader["allow_page_locks"]; con.Index.AllowRowLocks = (bool)reader["allow_row_locks"]; con.Index.FillFactor = (byte)reader["fill_factor"]; con.Index.IgnoreDupKey = (bool)reader["ignore_dup_key"]; con.Index.IsAutoStatistics = (bool)reader["ignore_dup_key"]; con.Index.IsDisabled = (bool)reader["is_disabled"]; con.Index.IsPadded = (bool)reader["is_padded"]; con.Index.IsPrimaryKey = false; con.Index.IsUniqueKey = true; con.Index.Type = (Index.IndexTypeEnum)(byte)reader["type"]; con.Index.Name = con.Name; if (database.Options.Ignore.FilterTableFileGroup) con.Index.FileGroup = reader["FileGroup"].ToString(); lastId = (int)reader["Index_id"]; if (reader["ObjectType"].ToString().Trim().Equals("U")) ((Table)table).Constraints.Add(con); else ((TableType)table).Constraints.Add(con); } ConstraintColumn ccon = new ConstraintColumn(con); ccon.Name = reader["ColumnName"].ToString(); ccon.IsIncluded = (bool)reader["is_included_column"]; ccon.Order = (bool)reader["is_descending_key"]; ccon.Id = (int)reader["column_id"]; ccon.DataTypeId = (int)reader["user_type_id"]; con.Columns.Add(ccon); } } } } }
/// <summary> /// Compara dos campos y devuelve true si son iguales, caso contrario, devuelve false. /// </summary> public static Boolean Compare(Constraint origen, Constraint destino) { if (destino == null) throw new ArgumentNullException("destino"); if (origen == null) throw new ArgumentNullException("origen"); if (origen.NotForReplication != destino.NotForReplication) return false; if ((origen.RelationalTableFullName == null) && (destino.RelationalTableFullName != null)) return false; if (origen.RelationalTableFullName != null) if (!origen.RelationalTableFullName.Equals(destino.RelationalTableFullName, StringComparison.CurrentCultureIgnoreCase)) return false; if ((origen.Definition == null) && (destino.Definition != null)) return false; if (origen.Definition != null) if ((!origen.Definition.Equals(destino.Definition)) && (!origen.Definition.Equals("(" + destino.Definition + ")"))) return false; /*Solo si la constraint esta habilitada, se chequea el is_trusted*/ if (!destino.IsDisabled) if (origen.WithNoCheck != destino.WithNoCheck) return false; if (origen.OnUpdateCascade != destino.OnUpdateCascade) return false; if (origen.OnDeleteCascade != destino.OnDeleteCascade) return false; if (!ConstraintColumns.Compare(origen.Columns, destino.Columns)) return false; if ((origen.Index != null) && (destino.Index != null)) return Index.Compare(origen.Index, destino.Index); return true; }