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