protected IEnumerable <ParentChildEntity> GetEntities(IEnumerable <TableModel> tables)
{
foreach (var tableModel in tables)
{
var tblEntity = new TableColumnEntity
{
Key = String.Format("{0}/Table", tableModel.ID),
ID = tableModel.ID,
Name = tableModel.Name,
Status = tableModel.Status,
};
yield return(tblEntity);
if (tableModel.Columns != null)
{
foreach (var columnModel in tableModel.Columns)
{
var colEntity = new TableColumnEntity
{
Key = String.Format("{0}/Column", columnModel.ID),
ID = columnModel.ID,
Name = columnModel.Name,
ParentID = tblEntity.ID,
Type = columnModel.Type,
};
yield return(colEntity);
}
}
}
}
public List <TableColumnEntity> GetTableColumns(string connStr, string databaseName, string schemaName, string tableName)
{
var helper = SqlHelperFactory.OpenDatabase(connStr, _provider.GetProviderFactory(), SqlType.MySql);
const string sql = @"SELECT TABLE_NAME as TName,COLUMN_NAME as CName,COLUMN_TYPE as CType,COLUMN_COMMENT as CComment,COLUMN_DEFAULT as CDefault,IS_NULLABLE as CIsNull
from information_schema.`COLUMNS` WHERE TABLE_SCHEMA=@0 and TABLE_NAME=@1";
var schemaInfos2 = helper.Select <SchemaInfo>(sql, databaseName, tableName);
helper.Dispose();
List <TableColumnEntity> columnInfos = new List <TableColumnEntity>();
foreach (var column in schemaInfos2)
{
TableColumnEntity columnInfo = new TableColumnEntity()
{
ColumnName = column.CName,
Type = column.CType,
Comment = column.CComment,
DefaultValue = column.CDefault,
IsNullAble = column.CIsNull.ToUpper() == "YES"
};
columnInfos.Add(columnInfo);
}
return(columnInfos);
}
public List <TableColumnEntity> GetTableColumns(string connStr, string databaseName, string schemaName, string tableName)
{
var sql = @"SELECT col.table_schema AS SchemaName, col.table_name TableName,col.ordinal_position AS Index, col.column_name ColumnName, col.character_maximum_length Length,
case when col.is_nullable='YES' then 'true' else 'false' end IsNullAble,
col.numeric_precision AS Precision, col.numeric_scale Scale,
col_description(c.oid, col.ordinal_position) AS Comment,
col.column_default AS DefaultValue,
case when col.is_identity='YES' then 'true' else 'false' end IsIdentity,
col.data_type AS col_type,
bt.typname AS elem_name,
(case when(select count(*) from pg_constraint where conrelid = b.attrelid and conkey[1] = b.attnum and contype = 'p') > 0 then 'true' else 'false' end) IsPrimaryKey
FROM information_schema.columns AS col
LEFT JOIN pg_namespace ns ON ns.nspname = col.table_schema
LEFT JOIN pg_class c ON col.table_name = c.relname AND c.relnamespace = ns.oid
LEFT JOIN pg_attrdef a ON c.oid = a.adrelid AND col.ordinal_position = a.adnum
LEFT JOIN pg_attribute b ON b.attrelid = c.oid AND b.attname = col.column_name
LEFT JOIN pg_type et ON et.oid = b.atttypid
LEFT JOIN pg_collation coll ON coll.oid = b.attcollation
LEFT JOIN pg_namespace colnsp ON coll.collnamespace = colnsp.oid
LEFT JOIN pg_type bt ON et.typelem = bt.oid LEFT JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid
WHERE col.table_schema = @0 AND col.table_name = @1
ORDER BY col.table_schema, col.table_name, col.ordinal_position";
var helper = SqlHelperFactory.OpenDatabase(connStr, _provider.GetProviderFactory(), SqlType.PostgreSQL);
helper.ChangeDatabase(databaseName);
var entities = helper.Select <TableColumnTemp>(sql, schemaName, tableName);
helper.Dispose();
List <TableColumnEntity> result = new List <TableColumnEntity>();
foreach (var temp in entities)
{
TableColumnEntity entity = new TableColumnEntity()
{
DatabaseName = databaseName,
SchemaName = temp.SchemaName,
TableName = temp.TableName,
Index = temp.Index,
ColumnName = temp.ColumnName,
Comment = temp.Comment,
DefaultValue = temp.DefaultValue,
Length = temp.Length,
Precision = temp.Precision,
Scale = temp.Scale,
IsPrimaryKey = temp.IsPrimaryKey,
IsIdentity = temp.IsIdentity,
IsNullAble = temp.IsNullAble,
Type = temp.col_type,
};
if (entity.Type == "ARRAY")
{
entity.Type = temp.elem_name + "[]";
}
result.Add(entity);
}
return(result);
}
private void AddToTables(DataTable dataTable, string tableName, List <TableColumnEntity> tables)
{
foreach (DataRow row in dataTable.Rows)
{
var entity = new TableColumnEntity()
{
TableName = tableName,
ColumnName = row["name"].ToString(),
Type = row["type"].ToString(),
IsNullAble = row["notnull"].ToString() == "0",
DefaultValue = row["dflt_value"]?.ToString(),
IsPrimaryKey = row["pk"].ToString() == "1"
};
tables.Add(entity);
}
}
public StructureItemModel(TableColumnEntity columnEntity)
{
ColumnName = columnEntity.ColumnName;
Nullable = columnEntity.IsNullAble ? "?" : null;
if (string.IsNullOrEmpty(columnEntity.DefaultValue) == false)
{
DefaultValue = columnEntity.DefaultValue;
}
if (string.IsNullOrEmpty(columnEntity.Comment) == false)
{
Comment = columnEntity.Comment;
}
if (columnEntity.IsPrimaryKey && columnEntity.IsIdentity)
{
KeyType = "fka";
}
else if (columnEntity.IsPrimaryKey)
{
KeyType = "fk";
}
else if (columnEntity.IsIdentity)
{
KeyType = "fa";
}
else
{
KeyType = "f";
}
ColumnType = columnEntity.Type.ToLower();
if (ColumnType == "varchar" || ColumnType == "nvarchar" || ColumnType == "char" || ColumnType == "nchar")
{
ColumnType = ColumnType + "(" + columnEntity.Length + ")";
}
else if (ColumnType == "decimal" || ColumnType == "numeric")
{
ColumnType = ColumnType + "(" + columnEntity.Precision + "," + columnEntity.Scale + ")";
}
}
