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