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