public List <TableEntity> GetTableList()
{
string sql = "SHOW TABLE STATUS";
IEnumerable <dynamic> data;
using (var conn = DbHelper.GetConn())
{
data = conn.Query(sql);
}
List <TableEntity> tableList = new List <TableEntity>();
foreach (var item in data)
{
TableEntity model = new TableEntity();
model.Name = item.Name;
if (ConfigHelper.TableComment)
{
model.Comment = item.Comment;
}
tableList.Add(model);
}
return(tableList);
}
public List <ColumnEntity> GetColumnList(TableEntity tableEntity)
{
string sql = @"SELECT
ColumnName=a.name,
IsKey=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) then 1 else 0 end,
IsIdentity=case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then 1 else 0 end,
ColumnType=b.name,
ColumnLength=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
DecimalDigit =isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
ColumnCommnent=isnull(g.[value],''),
AllowNull=case when a.isnullable=1 then 1 else 0 end,
DefaultValue=isnull(e.text,'')
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id
left join sys.extended_properties f on d.id=f.major_id and f.minor_id =0
where d.name=@name
order by a.id,a.colorder";
IEnumerable <dynamic> data;
using (var conn = DbHelper.GetConn())
{
data = conn.Query(sql, new { name = tableEntity.Name });
}
List <ColumnEntity> columnList = new List <ColumnEntity>();
foreach (var item in data)
{
ColumnEntity model = new ColumnEntity();
model.Name = item.ColumnName; //列表
model.NameUpper = MyUtils.ToUpper(model.Name); //首字母大写
model.NameLower = MyUtils.ToLower(model.Name); //首字母小写
if (item.IsKey == 1)
{
tableEntity.KeyName = model.Name;
if (item.IsIdentity == 1)
{
tableEntity.IsIdentity = "true";
}
}
string columnType = item.ColumnType;//数据类型
if (string.IsNullOrEmpty(columnType))
{
columnType = "";
}
string t = columnType.ToLower();
var cs = ConfigHelper.DbTypeDictionary[ConfigHelper.SqlServerCSharp].FirstOrDefault(f => f.Name == t);
if (cs != null)
{
model.CsType = cs.To;
}
else
{
model.CsType = ConfigHelper.UnKnowDbType;
}
var java = ConfigHelper.DbTypeDictionary[ConfigHelper.SqlServerJava].FirstOrDefault(f => f.Name == t);
if (java != null)
{
model.JavaType = java.To;
}
else
{
model.JavaType = ConfigHelper.UnKnowDbType;
}
model.DbType = item.ColumnType + "," + item.ColumnLength + "," + item.DecimalDigit;
if (ConfigHelper.ColumnComment)
{
model.Comment = item.ColumnCommnent; //说明
}
model.AllowNull = item.AllowNull.ToString(); //是否允许空
model.DefaultValue = item.DefaultValue; //默认值
columnList.Add(model);
}
return(columnList);
}
public List <ColumnEntity> GetColumnList(TableEntity tableEntity)
{
string sql1 = "set session \"myapp.name\"='" + tableEntity.Name + "';"; //this only select name and comment
sql1 += "SELECT ";
sql1 += "a.attname as name,";
sql1 += "col_description(a.attrelid,a.attnum) as comment, ";
sql1 += "concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\\(.*\\)')) as type ";
sql1 += "FROM pg_class as c,pg_attribute as a, pg_type t ";
sql1 += "where c.relname=current_setting('myapp.name') and a.attrelid =c.oid and a.attnum>0 and a.atttypid=t.oid ";
string sql2 = "set session \"myapp.name\" = '" + tableEntity.Name + "';";
sql2 += @"select
column_name as name,
is_nullable as cannull,
column_default as defaultval,
case when position('nextval' in column_default)>0 then '1' else '0' end as isidentity,
case when b.pk_name is null then '0' else '1' end as ispk,
c.DeText as detext
from information_schema.columns
left join (
select pg_attr.attname as colname,pg_constraint.conname as pk_name from pg_constraint
inner join pg_class on pg_constraint.conrelid = pg_class.oid
inner join pg_attribute pg_attr on pg_attr.attrelid = pg_class.oid and pg_attr.attnum = pg_constraint.conkey[1]
inner join pg_type on pg_type.oid = pg_attr.atttypid
where pg_class.relname = current_setting('myapp.name') and pg_constraint.contype='p'
) b on b.colname = information_schema.columns.column_name
left join (
select attname,description as DeText from pg_class
left join pg_attribute pg_attr on pg_attr.attrelid= pg_class.oid
left join pg_description pg_desc on pg_desc.objoid = pg_attr.attrelid and pg_desc.objsubid=pg_attr.attnum
where pg_attr.attnum>0 and pg_attr.attrelid=pg_class.oid and pg_class.relname=current_setting('myapp.name')
)c on c.attname = information_schema.columns.column_name
where table_schema='public' and table_name=current_setting('myapp.name') order by ordinal_position asc";
IEnumerable <dynamic> data;
IEnumerable <dynamic> data2;
using (var conn = DbHelper.GetConn())
{
data = conn.Query(sql1);
data2 = conn.Query(sql2);
}
List <ColumnEntity> columnList = new List <ColumnEntity>();
foreach (var item in data)
{
dynamic ddd = data2.FirstOrDefault(s => s.name == item.name);
ColumnEntity model = new ColumnEntity();
model.Name = item.name;
model.NameUpper = MyUtils.ToUpper(model.Name); //首字母大写
model.NameLower = MyUtils.ToLower(model.Name); //首字母小写
if (ddd.ispk == "1")
{
tableEntity.KeyName = model.Name;
if (ddd.isidentity == "1")
{
tableEntity.IsIdentity = "true";
}
}
string columnType = item.type;//数据类型
if (string.IsNullOrEmpty(columnType))
{
columnType = "";
}
string t = columnType.Split('(')[0].ToLower();
var cs = ConfigHelper.DbTypeDictionary[ConfigHelper.PostgreSqlCSharp].FirstOrDefault(f => f.Name == t);
if (cs != null)
{
model.CsType = cs.To;
}
else
{
model.CsType = ConfigHelper.UnKnowDbType;
}
var java = ConfigHelper.DbTypeDictionary[ConfigHelper.PostgreSqlJava].FirstOrDefault(f => f.Name == t);
if (java != null)
{
model.JavaType = java.To;
}
else
{
model.JavaType = ConfigHelper.UnKnowDbType;
}
model.DbType = item.type;
if (ConfigHelper.ColumnComment)
{
model.Comment = ddd.detext; //说明
}
model.AllowNull = ddd.cannull; //是否允许空
model.DefaultValue = ddd.defaultval; //默认值
columnList.Add(model);
}
return(columnList);
}
public List <ColumnEntity> GetColumnList(TableEntity tableEntity)
{
string sql = "pragma table_info('" + tableEntity.Name + "')";
IEnumerable <dynamic> data;
using (var conn = DbHelper.GetConn())
{
data = conn.Query(sql);
}
List <ColumnEntity> columnList = new List <ColumnEntity>();
foreach (var item in data)
{
ColumnEntity model = new ColumnEntity();
model.Name = item.name; //列名
if (item.pk == 1)
{
tableEntity.KeyName = model.Name;
}
model.NameUpper = MyUtils.ToUpper(model.Name); //首字母大写
model.NameLower = MyUtils.ToLower(model.Name); //首字母小写
string columnType = item.type; //数据类型
if (string.IsNullOrEmpty(columnType))
{
columnType = "";
}
string t = columnType.ToLower();
var cs = ConfigHelper.DbTypeDictionary[ConfigHelper.SQLiteCSharp].FirstOrDefault(f => f.Name == t);
if (cs != null)
{
model.CsType = cs.To;
}
else
{
model.CsType = ConfigHelper.UnKnowDbType;
}
var java = ConfigHelper.DbTypeDictionary[ConfigHelper.SQLiteJava].FirstOrDefault(f => f.Name == t);
if (java != null)
{
model.JavaType = java.To;
}
else
{
model.JavaType = ConfigHelper.UnKnowDbType;
}
model.DbType = item.type;
model.AllowNull = Convert.ToString(item.notnull); //是否允许空
model.DefaultValue = item.dflt_value; //默认值
columnList.Add(model);
}
return(columnList);
}
public List <ColumnEntity> GetColumnList(TableEntity tableEntity)
{
string sql = "SHOW FULL COLUMNS FROM " + tableEntity.Name;
IEnumerable <dynamic> data;
using (var conn = DbHelper.GetConn())
{
data = conn.Query(sql);
}
List <ColumnEntity> columnList = new List <ColumnEntity>();
foreach (var item in data)
{
ColumnEntity model = new ColumnEntity();
model.Name = item.Field; //列名
if (!string.IsNullOrEmpty(item.Key) && item.Key == "PRI")
{
tableEntity.KeyName = model.Name;
if (!string.IsNullOrEmpty(item.Extra) && item.Extra == "auto_increment")
{
tableEntity.IsIdentity = "true";
}
}
model.NameUpper = MyUtils.ToUpper(model.Name); //首字母大写
model.NameLower = MyUtils.ToLower(model.Name); //首字母小写
string columnType = item.Type; //数据类型
if (string.IsNullOrEmpty(columnType))
{
columnType = "";
}
string t = columnType.Split('(')[0].ToLower();
var cs = ConfigHelper.DbTypeDictionary[ConfigHelper.MySqlCSharp].FirstOrDefault(f => f.Name == t);
if (cs != null)
{
model.CsType = cs.To;
}
else
{
model.CsType = ConfigHelper.UnKnowDbType;
}
var java = ConfigHelper.DbTypeDictionary[ConfigHelper.MySqlJava].FirstOrDefault(f => f.Name == t);
if (java != null)
{
model.JavaType = java.To;
}
else
{
model.JavaType = ConfigHelper.UnKnowDbType;
}
model.DbType = item.Type;
if (ConfigHelper.ColumnComment)
{
model.Comment = item.Comment; //说明
}
model.AllowNull = item.Null; //是否允许空
model.DefaultValue = item.Default; //默认值
columnList.Add(model);
}
return(columnList);
}
public List <ColumnEntity> GetColumnList(TableEntity tableEntity)
{
string sql = "SELECT C.COLUMN_ID AS \"Id\",C.TABLE_NAME AS \"TableId\",C.COLUMN_NAME AS \"Name\",C.DATA_TYPE AS \"DbType\",C.DATA_LENGTH AS \"DataLength\",NVL(CC.COMMENTS,C.COLUMN_NAME) AS \"Description\",";
sql += "to_number(CASE C.NULLABLE WHEN 'N' THEN '0' ELSE '1' END) AS \"IsNullable\",";
sql += "to_number('0') AS \"AutoIncrement\",";
sql += "to_number(CASE WHEN P.COLUMN_NAME = C.COLUMN_NAME THEN '1' ELSE '0' END) AS \"IsPrimaryKey\" ";
sql += "FROM USER_TAB_COLUMNS C ";
sql += "LEFT JOIN USER_COL_COMMENTS CC ON C.TABLE_NAME = CC.TABLE_NAME AND C.COLUMN_NAME = CC.COLUMN_NAME ";
sql += "LEFT JOIN ( ";
sql += "SELECT CU.COLUMN_NAME FROM USER_CONS_COLUMNS CU ";
sql += "LEFT JOIN USER_CONSTRAINTS AU ON CU.CONSTRAINT_NAME = AU.CONSTRAINT_NAME ";
sql += "WHERE CU.TABLE_NAME = :name AND AU.CONSTRAINT_TYPE='P' ";
sql += ")P ON C.COLUMN_NAME = P.COLUMN_NAME ";
sql += "WHERE C.TABLE_NAME = :name ORDER BY C.COLUMN_ID";
IEnumerable <dynamic> data;
using (var conn = DbHelper.GetConn())
{
data = conn.Query(sql, new { name = tableEntity.Name });
}
List <ColumnEntity> columnList = new List <ColumnEntity>();
foreach (var item in data)
{
ColumnEntity model = new ColumnEntity();
model.Name = item.Name; //列表
model.NameUpper = MyUtils.ToUpper(model.Name); //首字母大写
model.NameLower = MyUtils.ToLower(model.Name); //首字母小写
if (item.IsPrimaryKey.ToString() == "1")
{
tableEntity.KeyName = model.Name;
if (item.AutoIncrement.ToString() == "1")
{
tableEntity.IsIdentity = "true";
}
}
string t = item.DbType;//数据类型
if (string.IsNullOrEmpty(t))
{
t = "";
}
var cs = ConfigHelper.DbTypeDictionary[ConfigHelper.OracleCSharp].FirstOrDefault(f => f.Name == t);
if (cs != null)
{
model.CsType = cs.To;
}
else
{
model.CsType = ConfigHelper.UnKnowDbType;
}
var java = ConfigHelper.DbTypeDictionary[ConfigHelper.OracleJava].FirstOrDefault(f => f.Name == t);
if (java != null)
{
model.JavaType = java.To;
}
else
{
model.JavaType = ConfigHelper.UnKnowDbType;
}
model.DbType = item.DbType + "," + item.DataLength;
if (ConfigHelper.ColumnComment)
{
model.Comment = item.Description; //说明
}
model.AllowNull = item.IsNullable.ToString(); //是否允许空
model.DefaultValue = item.DefaultValue; //默认值
columnList.Add(model);
}
return(columnList);
}