private bool LookupFKForColumn(string name, out FKSpecification fkSpec, List <FKSpecification> fkList)
{
fkSpec = null;
bool hasFK = false;
foreach (FKSpecification f in fkList)
{
if (name.Trim().Equals(f.parentColumnName.Trim()))
{
hasFK = true;
fkSpec = f;
break;
}
}
return(hasFK);
}
private void QueryColumnData(List <ColumnMetaInfo> colList, List <FKSpecification> fkList, object xag)
{
foreach (PropertyInfo pi in xag.GetType().GetProperties())
{
MemberTypes mt = pi.MemberType;
string val = mt.ToString();
PropertyAttributes patt = pi.Attributes;
string nm = pi.PropertyType.Name;
String name = pi.Name;
Type rt = pi.GetMethod.ReturnType;
string tName = GuessMainType(rt.FullName);
FKSpecification fkSpec = null;
bool hasFK = LookupFKForColumn(name, out fkSpec, fkList);
ColumnMetaInfo cmi = new ColumnMetaInfo()
{
columnName = name, fkSpec = fkSpec, hasFK = hasFK, dbTypeName = tName
};
colList.Add(cmi);
}
}
public static List <FKSpecification> QueryForeignKeyRelationships(string connString, string tableToQuery)
{
List <FKSpecification> fkList = new List <FKSpecification>();
SqlConnection connection = null;
try
{
connection = new SqlConnection(connString);
connection.Open();
string statement1 = "SELECT f.name AS ForeignKey, OBJECT_NAME(f.parent_object_id) AS TableName," +
"COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,OBJECT_NAME " +
"(f.referenced_object_id) AS ReferenceTableName, COL_NAME(fc.referenced_object_id," +
"fc.referenced_column_id) AS ReferenceColumnName FROM sys.foreign_keys AS f " +
"INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id " +
"Where OBJECT_NAME(f.parent_object_id) = \'" + tableToQuery + "\';";
SqlCommand sqc = new SqlCommand(statement1, connection);
SqlDataReader reader = sqc.ExecuteReader();
while (reader.Read())
{
string fkName = reader[0].ToString();
string TableName = reader[1].ToString();
string ColName = reader[2].ToString();
string ReferencedTable = reader[3].ToString();
string ReferencedCol = reader[4].ToString();
FKSpecification fks = new FKSpecification()
{
parentColumnName = ColName, parentTableName = TableName, childColumnName = ReferencedCol, childTableName = ReferencedTable
};
fkList.Add(fks);
}
connection.Close();
}
catch (Exception ex)
{
}
return(fkList);
}
private List <ColumnMetaInfo> QueryColumns(string connString, object ob, string tableName)
{
List <FKSpecification> fkList = ForeignKeyUtils.QueryForeignKeyRelationships(connString, tableName);
List <ColumnMetaInfo> colList = new List <ColumnMetaInfo>();
foreach (PropertyInfo pi in ob.GetType().GetProperties())
{
MemberTypes mt = pi.MemberType;
string val = mt.ToString();
PropertyAttributes patt = pi.Attributes;
string nm = pi.PropertyType.Name;
String name = pi.Name;
FKSpecification fkSpec = null;
bool hasFK = LookupFKForColumn(name, out fkSpec, fkList);
Type rt = pi.GetMethod.ReturnType;
string tName = GuessMainType(rt.FullName);
ColumnMetaInfo cmi = new ColumnMetaInfo()
{
columnName = name, fkSpec = fkSpec, hasFK = hasFK, dbTypeName = tName
};
colList.Add(cmi);
}
return(colList);
}
