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