protected void LoadTriggerRelationships()
{
DataTable trigs = new DataTable();
using (SqlConnection conn = new SqlConnection(ConnectionStringBuilder.ConnectionString))
{
conn.Open();
using (SqlDataAdapter sda = new SqlDataAdapter(TRIGGER_QUERY, conn))
{
sda.Fill(trigs);
}
conn.Close();
}
foreach (DataRow trow in trigs.Rows)
{
string tblname = trow["table_name"].ToString();
string trigname = trow["trigger_name"].ToString();
SqlServerTriggerNode trignode = trigList.Where(t => t.ObjectName == trigname).SingleOrDefault();
SqlServerTableNode tblnode = tableList.Where(t => t.ObjectName == tblname).SingleOrDefault();
if (trignode != null && tblnode != null)
{
DataConnection trigrel = new DataConnection
{
ConnectionType = DbProcesses.Trigger,
StartNode = trignode,
EndNode = tblnode,
Name = String.Format("Trigger {0} => {1}.{2}", trignode.ObjectName, tblnode.Schema, tblnode.ObjectName),
Description = "Table Trigger"
};
Relationships.Add(trigrel);
}
}
}
protected List <SqlServerTableNode> LoadTables()
{
List <SqlServerTableNode> tlist = new List <SqlServerTableNode>();
using (SqlConnection conn = new SqlConnection(ConnectionStringBuilder.ConnectionString))
{
conn.Open();
DataTable tables = conn.GetSchema(SqlClientMetaDataCollectionNames.Tables, new string[] { null, null, null, "BASE TABLE" });
foreach (DataRow trow in tables.Rows)
{
SqlServerTableNode tnode = new SqlServerTableNode(trow["TABLE_NAME"].ToString(), ConnectionStringBuilder);
tnode.Schema = trow["TABLE_SCHEMA"].ToString();
tnode.ObjectName = trow["TABLE_NAME"].ToString();
List <string> ancestry = new List <string>();
ancestry.Add(Name);
if (!String.IsNullOrEmpty(tnode.Schema))
{
ancestry.Add(tnode.Schema);
}
tnode.Lineage = CreateNameSpace(ancestry);
tlist.Add(tnode);
}
conn.Close();
}
return(tlist);
}
protected void LoadTableRelationships()
{
DataTable fkdatatable = new DataTable();
using (SqlConnection conn = new SqlConnection(ConnectionStringBuilder.ConnectionString))
{
conn.Open();
using (SqlDataAdapter sda = new SqlDataAdapter(FK_QUERY, conn))
{
sda.Fill(fkdatatable);
}
conn.Close();
}
//start parsing through the data rows
string pktable;
string pkcolumn;
string fktable;
string fkcolumn;
foreach (DataRow fkrow in fkdatatable.Rows)
{
pktable = fkrow["PK_table"].ToString();
pkcolumn = fkrow["PK_column"].ToString();
fktable = fkrow["FK_table"].ToString();
fkcolumn = fkrow["FK_column"].ToString();
SqlServerTableNode pknode = tableList.Where(t => t.ObjectName == pktable).SingleOrDefault();
SqlServerTableNode fknode = tableList.Where(t => t.ObjectName == fktable).SingleOrDefault();
if (pknode != null && fknode != null)
{
DataConnection fkrel = new DataConnection
{
ConnectionType = RecordKeys.ForeignKey,
StartNode = pknode,
EndNode = fknode,
Name = String.Format("{0}.{1}.{2} => {3}.{4}.{5}", pknode.Schema, pknode.ObjectName, pkcolumn, fknode.Schema, fknode.ObjectName, fkcolumn),
Description = String.Format("{0}: On Update = {1}, On Delete = {2}", fkrow["FK_name"].ToString(), fkrow["Update_Action"], fkrow["Delete_Action"].ToString())
};
Relationships.Add(fkrel);
}
}
}
