public static bool IsColumnInPrimaryKeys(this TableInfo table, TableColumn column)
{
IEnumerable<string> query = from pks in table.PKColumns
where "".MatchByString(column.Name, pks.Name)
select pks.Name;
return query.HaveMembers();
}
// Public Methods (2)
public List<TableColumn> GetbyTable(string tableName)
{
List<TableColumn> _columns = new List<TableColumn>();
string query = @"Select *,IDENT_SEED(table_name) as id_seed from information_schema.columns col
LEFT OUTER JOIN ::fn_listextendedproperty(NULL, 'schema','dbo','table', '[[TableName]]' ,'column', null) des ON col.column_name = des.objname COLLATE latin1_general_ci_ai
where table_name = '[[TableName]]'".Replace("[[TableName]]", tableName);
SqlConnection conn;
using (IDataReader reader = SqlCommandX.Instance.GetReader(
this.ConnectionString, query, CommandType.Text, out conn))
{
while (reader.Read())
{
TableColumn cs = new TableColumn(
reader["column_name"].ToString(),
reader["table_name"].ToString(),
(reader["Is_Nullable"].ToString() == "YES") ? true : false,
reader["data_type"].ToString(),
(reader["id_seed"].Text() == "1"));
if (DBNull.Value != reader["character_maximum_length"])
{
cs.MaxLength = Convert.ToInt32(reader["character_maximum_length"]);
}
if (DBNull.Value != reader["character_octet_length"])
{
cs.OctetLength = Convert.ToInt32(reader["character_octet_length"]);
}
if (DBNull.Value != reader["numeric_precision"])
{
cs.NumericPrecision = Convert.ToInt32(reader["numeric_precision"]);
}
if (DBNull.Value != reader["numeric_precision_radix"])
{
cs.NumericPrecisionRadix = Convert.ToInt32(reader["numeric_precision_radix"]);
}
if (DBNull.Value != reader["numeric_scale"])
{
cs.NumericScale = Convert.ToInt32(reader["numeric_scale"]);
}
if (DBNull.Value != reader["datetime_precision"])
{
cs.DateTimePrecision = Convert.ToInt32(reader["datetime_precision"]);
}
if (DBNull.Value != reader["value"])
{
cs.Description = reader["value"].Text();
}
_columns.Add(cs);
}
}
conn.Destroy();
return _columns;
}
public static List<TableColumn> GetTableColumns(string connectionString, params string[] filterTables)
{
List<TableColumn> _columns = new List<TableColumn>();
string query = "Select *,IDENT_SEED(table_name) as id_seed from information_schema.columns";
if (filterTables.Length > 0)
{
query = query + " WHERE table_name in " + filterTables.ToWhereClauseInCommaList();
}
SqlConnection conn;
using (IDataReader reader = SqlCommandX.Instance.GetReader(
connectionString, query, CommandType.Text, out conn))
{
while (reader.Read())
{
TableColumn cs = new TableColumn(
reader["column_name"].ToString(),
reader["table_name"].ToString(),
(reader["Is_Nullable"].ToString() == "YES") ? true : false,
reader["data_type"].ToString(),
(reader["id_seed"].Text() == "1"));
if (DBNull.Value != reader["character_maximum_length"])
{
cs.MaxLength = Convert.ToInt32(reader["character_maximum_length"]);
}
if (DBNull.Value != reader["character_octet_length"])
{
cs.OctetLength = Convert.ToInt32(reader["character_octet_length"]);
}
if (DBNull.Value != reader["numeric_precision"])
{
cs.NumericPrecision = Convert.ToInt32(reader["numeric_precision"]);
}
if (DBNull.Value != reader["numeric_precision_radix"])
{
cs.NumericPrecisionRadix = Convert.ToInt32(reader["numeric_precision_radix"]);
}
if (DBNull.Value != reader["numeric_scale"])
{
cs.NumericScale = Convert.ToInt32(reader["numeric_scale"]);
}
if (DBNull.Value != reader["datetime_precision"])
{
cs.DateTimePrecision = Convert.ToInt32(reader["datetime_precision"]);
}
_columns.Add(cs);
}
}
//load description property from tables
conn.Destroy();
return _columns;
}
