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