public EntitySelector(Entity e) { InitializeComponent(); entity = e; var list = SchemaCreator.ALL_RELATIONSHIP.Where(x => x.Parent_Table == entity.Table_Name).ToList(); if (list.Count > 1) { //open dialog to select opstions HasMany = true; foreach (var item in list) { Button b = new Button { Content = item.Child_Table, Margin = new Thickness(5, 2, 5, 2) }; b.Click += (s, ev) => { Root = item.Clone() as TableRelationShips; Root.reset(); SchemaCreator.CurrentChoise = Root; CreateInitialSchema(); }; ManyEntity.Children.Add(b); } } else { Root = list.FirstOrDefault().Clone() as TableRelationShips; Root.reset(); SchemaCreator.CurrentChoise = Root; } }
private void ProcessTables(SQLJoinRules buildNewSQL) { TablesAndViewsUI.PrepareSelectedTables(); if (buildNewSQL.BuildJoin()) { editBox.Text = buildNewSQL.SQLString; } else { editBox.Text = "Finding needed tables to build query"; //editBox.Text = "One or more Tables found with no relevent relationships.\r\n\r\n"; bool processAgain = false; List <TableName> stillNone = new List <TableName>(); foreach (TableName aTable in buildNewSQL.NoRelationFound) { //editBox.Text += aTable.Name + "\r\n"; List <Joiner> joins = new List <Joiner>(); if (TableRelationShips.FindNeededTable(aTable, TablesAndViewsUI.AllTables, TablesAndViewsUI.SelectedTables, joins)) { var excludeIteratedTable = TablesAndViewsUI.SelectedTables.Tables.Where(x => !x.Name.Equals(aTable.Name)).ToList(); //joiners can be further resolved to find more distant relationships, will need a class for that kind of processing. var joiner = TableRelationShips.CheckForeignKeyTables(joins, excludeIteratedTable); if (joiner != null && !TablesAndViewsUI.SelectedTables.Tables.Any(x => x.Name.Equals(joiner.Table1.Name))) { TablesAndViewsUI.ExternallyCheckTable(joiner.Table1.Name); //TablesAndViewsUI.SelectedTables.Tables.Add(joiner.Table1); //TablesAndViewsUI.PrepareTable(TablesAndViewsUI.SelectedTables.Tables[TablesAndViewsUI.SelectedTables.Tables.Count - 1]); processAgain = true; } } else { stillNone.Add(aTable); } } if (processAgain) { ProcessTables(buildNewSQL); } else { editBox.Text = "No relationships found to complete query build"; } } }
public static List <TableRelationShips> getTableRelationShips() { string sql = @"SELECT fk.name as RelationShip, tp.name as Child_Table, cp.name as ChildKey , tr.name as Parent_Table, cr.name as ParentKey FROM sys.foreign_keys fk INNER JOIN sys.tables tp ON fk.parent_object_id = tp.object_id INNER JOIN sys.tables tr ON fk.referenced_object_id = tr.object_id INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id INNER JOIN sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id INNER JOIN sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id ORDER BY tp.name, cp.column_id "; var list = new List <TableRelationShips>(); SqlConnection cnn; SqlCommand cmd; SqlDataReader reader; var connetionString = getConnectionString(); cnn = new SqlConnection(connetionString); try { cnn.Open(); cmd = new SqlCommand(sql, cnn); reader = cmd.ExecuteReader(); while (reader.Read()) { var t = new TableRelationShips { RelationShip = reader.GetValue(0).ToString(), Child_Table = reader.GetValue(1).ToString(), ParentKey = reader.GetValue(4).ToString(), Parent_Table = reader.GetValue(3).ToString(), ChildKey = reader.GetValue(2).ToString() }; list.Add(t); } reader.Close(); cmd.Dispose(); cnn.Close(); return(list); } catch (Exception ex) { return(new List <TableRelationShips>()); } }