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 List <InfoColumnObject> GetInfoTable(string nameTable)
{
var dt = new SqlProvider().GetData($@"select COLUMN_NAME , DATA_TYPE , CHARACTER_MAXIMUM_LENGTH , COLUMNPROPERTY(object_id(TABLE_SCHEMA+'.'+TABLE_NAME), COLUMN_NAME, 'IsIdentity') as IsIdentity
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = N'{nameTable}'", Connection);
if (dt == null || dt.Rows.Count <= 0)
{
return(null);
}
List <InfoColumnObject> lst = new List <InfoColumnObject>();
var ListKey = GetKeyOfTable(nameTable);
foreach (DataRow item in dt.Rows)
{
int d = 0;
var ob = new InfoColumnObject();
ob.Name = item["COLUMN_NAME"].ToString();
ob.Type = item["DATA_TYPE"].ToString();
ob.Length = int.TryParse(item["CHARACTER_MAXIMUM_LENGTH"].ToString(), out d) ? d == -1 ? "MAX" : d.ToString() : null;
ob.IsIdentity = item["IsIdentity"].ToString().Equals("1");
ob.IsPK = ListKey != null && ListKey.Any(q => q.Equals(ob.Name));
lst.Add(ob);
}
return(lst.OrderBy(q => q.Name).ToList());
}