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