private void GetReferenceData(ProgramItem programItem, ref List<string> refSPList, ref List<string> refTableList)
{
var stringComparer = StringComparer.Create(CultureInfo.InvariantCulture, true);
foreach (var item in programItem.SqlItems)
{
if (item is TableItem)
{
if (!refTableList.Contains(item.Name, stringComparer))
{
refTableList.Add(item.Name);
}
}
if (item is ProgramItem)
{
if (!refSPList.Contains(item.Name, stringComparer))
{
refSPList.Add(item.Name);
GetReferenceData(item as ProgramItem, ref refSPList, ref refTableList);
}
}
}
}
public override void UpdateReferences()
{
var connection = ConnectionManager.GetConnection();
if (connection != null)
{
// Update SP
try
{
var command = new SqlCommand
{
Connection = connection,
CommandText =
string.Format(
@"SELECT DISTINCT REFERENCED_ENTITY_NAME FROM SYS.DM_SQL_REFERENCED_ENTITIES ('DBO.{0}', 'OBJECT') AS RE INNER JOIN
(SELECT NAME FROM SYS.OBJECTS WHERE TYPE IN ('FN', 'IF', 'TF')
UNION
SELECT NAME FROM SYS.PROCEDURES
UNION
SELECT NAME FROM SYS.VIEWS) PR ON PR.NAME = RE.REFERENCED_ENTITY_NAME",
this.Name)
};
var dataTable = new DataTable();
var dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dataTable);
foreach (DataRow row in dataTable.Rows)
{
var programName = row[0].ToString();
var proramItem = SqlItemManager.GetRegisteredItem(programName);
if (proramItem != null)
{
_sqlItems.Add(proramItem);
}
else
{
proramItem = new ProgramItem(programName);
_sqlItems.Add(proramItem);
proramItem.UpdateReferences();
}
}
}
catch { };
// Update Tables
try
{
var command = new SqlCommand
{
Connection = connection,
CommandText =
string.Format(
@" SELECT DISTINCT REFERENCED_ENTITY_NAME FROM SYS.DM_SQL_REFERENCED_ENTITIES ('DBO.{0}', 'OBJECT') AS RE
INNER JOIN SYS.TABLES AS TB ON TB.NAME = RE.REFERENCED_ENTITY_NAME
WHERE (TB.NAME LIKE 'AH_MASTER%' OR TB.NAME = 'AH_MEMBER_WELLNESS_TIPS') AND TB.NAME NOT LIKE '%_LOCALIZED' ",
this.Name)
};
var dataTable = new DataTable();
var dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dataTable);
foreach (DataRow row in dataTable.Rows)
{
var tableName = row[0].ToString();
var tableItem = SqlItemManager.GetRegisteredItem(tableName) as TableItem;
if (tableItem != null)
{
if (!_sqlItems.Contains(tableItem))
{
tableItem.IsReferenced = true;
_sqlItems.Add(tableItem);
}
}
else
{
tableItem = new TableItem(tableName);
tableItem.IsReferenced = true;
_sqlItems.Add(tableItem);
tableItem.UpdateReferences();
}
}
}
catch { }
}
}