public List <InfoColumnObject> GetTableJoin(string nameTable, SqlConnection connection)
{
var dt = GetData($@"select replace(CONSTRAINT_NAME,'FK_'+TABLE_NAME+'_','') as NameTableJoin ,COLUMN_NAME as KeyJoin from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME ='{nameTable}'
and CONSTRAINT_NAME like 'FK_%'", connection);
var lstTable = new List <InfoColumnObject>();
if (dt != null && dt.Rows.Count > 0)
{
var allTable = new SqlDatabaseContext(connection).GetTable();
foreach (DataRow item in dt.Rows)
{
var tbl = new InfoColumnObject()
{
IsFK = true,
NameTableJoin = item["NameTableJoin"].ToString(),
Name = item["KeyJoin"].ToString()
};
int k = 0;
while (tbl.NameTableJoin.Length > 0 && !allTable.Any(q => q.Equals(tbl.NameTableJoin)))
{
if (int.TryParse(tbl.NameTableJoin[tbl.NameTableJoin.Length - 1].ToString(), out k))
{
tbl.NameTableJoin = tbl.NameTableJoin.Remove(tbl.NameTableJoin.Length - 1, 1);
}
}
lstTable.Add(tbl);
}
}
return(lstTable);
}
public Bussiness(string nameTable, SqlConnection connection, Setting setting)
{
Connection = connection;
sTable = nameTable;
Setting = setting;
LstInfoTable = new SqlDatabaseContext(connection).GetInfoTable(nameTable);
NameTable = setting.GetNameTable(nameTable);
Table = new TableObject(sTable, connection);
}
public TableObject(string nameTable, SqlConnection connection)
{
this.Name = nameTable;
this.Connection = connection;
lstColumns = new SqlDatabaseContext(Connection).GetInfoTable(Name);
lstFK = new SqlProvider().GetTableJoin(Name, connection);
if (lstFK.Count > 0)
{
lstFK.OrderBy(q => q.Name).ToList();
foreach (var item in lstFK)
{
var col = lstColumns.First(q => q.Name.Equals(item.Name));
col.IsFK = item.IsFK;
col.NameTableJoin = item.NameTableJoin;
}
}
}