public string DisplayType(DbColumn column) { var dbDataType = column.DataType; switch (dbDataType) { case "nvarchar": case "nchar": if (column.MaxLength == -1) dbDataType += "(MAX)"; else dbDataType += "(" + column.MaxLength / 2 + ")"; break; case "varchar": case "char": case "varbinary": if (column.MaxLength == -1) dbDataType += "(MAX)"; else dbDataType += "(" + column.MaxLength + ")"; break; } if (column.IsIdentity) dbDataType += "(identity)"; return dbDataType; }
public Dictionary<string, List<DbColumn>> GetTableColumns(DbConnectionInfo connectionInfo) { var columns = new Dictionary<string, List<DbColumn>>(); var sql = @"SELECT TABLE_NAME as fullTableName , c.COLUMN_NAME as columnName, c.ORDINAL_POSITION as ordinal , c.IS_NULLABLE as is_nullable, DATA_TYPE as datatype,c.CHARACTER_MAXIMUM_LENGTH as max_length , c.NUMERIC_PRECISION FROM INFORMATION_SCHEMA.COLUMNS c where TABLE_SCHEMA != 'information_schema' order by TABLE_NAME , ORDINAL_POSITION "; using (var conn = new MySqlConnection(connectionInfo.ConnectionString)) { conn.Open(); using (var cmd = conn.CreateCommand()) { cmd.CommandType = CommandType.Text; cmd.CommandText = sql; var reader = cmd.ExecuteReader(); var fullTableNamePos = reader.GetOrdinal("fullTableName"); var columnNamePos = reader.GetOrdinal("columnName"); var is_nullablePos = reader.GetOrdinal("is_nullable"); //var is_identityPos = reader.GetOrdinal("is_identity"); var datatypePos = reader.GetOrdinal("datatype"); var max_lengthPos = reader.GetOrdinal("max_length"); while (reader.Read()) { var fullTableName = reader.GetString(fullTableNamePos); if (!columns.ContainsKey(fullTableName)) columns.Add(fullTableName, new List<DbColumn>()); var col = new DbColumn { Name = reader.GetString(columnNamePos), IsNullable = (reader.GetString(is_nullablePos) == "YES"), //IsIdentity = reader.GetBoolean(is_identityPos), DataType = reader.GetString(datatypePos), MaxLength = GetMaxLength(reader.GetValue(max_lengthPos)) }; col.DisplayDataType = DisplayType(col); columns[fullTableName].Add(col); } } } return columns; }
public Dictionary<string, List<DbColumn>> GetViewColumns(DbConnectionInfo connectionInfo) { var columns = new Dictionary<string, List<DbColumn>>(); var sql = @"select SCHEMA_NAME(so.schema_id) + '.'+ so.name as fullTableName ,c.name as columnName,c.column_id as ordinal, c.is_nullable ,TYPE_NAME(c.system_type_id) as datatype,c.max_length, c.precision from sys.columns c inner join sys.objects so on so.object_id = c.object_id where so.type in ('V') order by so.object_id, c.column_id"; using (var conn = new SqlConnection(connectionInfo.ConnectionString)) { conn.Open(); using (var cmd = conn.CreateCommand()) { cmd.CommandType = CommandType.Text; cmd.CommandText = sql; var reader = cmd.ExecuteReader(); var fullTableNamePos = reader.GetOrdinal("fullTableName"); var columnNamePos = reader.GetOrdinal("columnName"); var is_nullablePos = reader.GetOrdinal("is_nullable"); var datatypePos = reader.GetOrdinal("datatype"); var max_lengthPos = reader.GetOrdinal("max_length"); var ordinalPos = reader.GetOrdinal("ordinal"); while (reader.Read()) { var fullTableName = reader.GetString(fullTableNamePos); if (!columns.ContainsKey(fullTableName)) columns.Add(fullTableName, new List<DbColumn>()); var col = new DbColumn { Name = reader.GetString(columnNamePos), IsNullable = reader.GetBoolean(is_nullablePos), DataType = reader.GetString(datatypePos), MaxLength = (Int16) reader.GetValue(max_lengthPos), Ordinal = reader.GetInt32(ordinalPos) }; col.DisplayDataType = DisplayType(col); columns[fullTableName].Add(col); } } } return columns; }