/// <summary> /// 读取My Sql /// </summary> /// <returns></returns> private static List <TableInfo> ReaderMySQL(string dbName) { using (DBSession db = DBSession.TryGet()) { //获取所有table dynamic dytables = db.GetDynamicList("select table_name,table_comment from information_schema.TABLES where table_schema=?", dbName); List <TableInfo> tables = new List <TableInfo>(); foreach (dynamic item in dytables) { TableInfo table = new TableInfo(); table.Name = item.TABLE_NAME; table.Desc = TBFieldHelper.FormatDesc(item.TABLE_COMMENT); table.Fields = new List <TBField>(); //获取字段 string sql = string.Format(@" select column_name,data_type,column_key,column_comment from information_schema.columns where table_schema=? and table_name=? order by column_key desc"); dynamic fields = db.GetDynamicList(sql, dbName, table.Name); foreach (dynamic dyF in fields) { TBField field = new TBField(); field.Name = dyF.COLUMN_NAME; field.Desc = TBFieldHelper.FormatDesc(dyF.COLUMN_COMMENT); field.IsPrimaryKey = dyF.COLUMN_KEY == "PRI" ? true : false; string typestr = dyF.DATA_TYPE; //获取数据类型 field.FieldType = TBFieldHelper.GetFieldType(field.Name, typestr, DataBaseType.MySQL); table.Fields.Add(field); } tables.Add(table); } return(tables); } }
/// <summary> /// 读取SQL Server /// </summary> /// <returns></returns> private static List <TableInfo> ReaderSQLServer() { #region sql /// <summary> /// 获取字段信息以及注释等sql语句 /// </summary> string sql = @"select sysobjects.name as tablename,syscolumns.name as filedname,systypes.name as datatype, ispk= case when exists(select 1 from sysobjects where xtype='PK' and parent_obj=syscolumns.id and name in (select name from sysindexes where indid in(select indid from sysindexkeys where id = syscolumns.id AND colid=syscolumns.colid))) then 1 else 0 end,columndescription= isnull(sys.extended_properties.[value],''), tabledescription=case when (select count(*) from sys.extended_properties where major_id=sysobjects.id and minor_id=0)=1 then (select [value] from sys.extended_properties where major_id=sysobjects.id and minor_id=0) else '' end from sysobjects inner join syscolumns on sysobjects.id = syscolumns.id left join systypes on syscolumns.xtype = systypes.xusertype left join sys.extended_properties on syscolumns.id = sys.extended_properties.major_id and syscolumns.colid = sys.extended_properties.minor_id where (sysobjects.xtype='U' or sysobjects.xtype='V') and sysobjects.name <>'sysdiagrams' and sysobjects.id in (?)"; #endregion using (DBSession db = DBSession.TryGet()) { //获取所有table dynamic dyTables = db.GetDynamicList("select name,id from sysobjects where (xtype='U' or xtype='V')"); List <TableInfo> tables = new List <TableInfo>(); foreach (dynamic item in dyTables) { TableInfo table = new TableInfo(); table.Name = item.NAME; table.Fields = new List <TBField>(); //获取字段 dynamic fields = db.GetDynamicList(sql, item.ID); foreach (dynamic dyF in fields) { table.Desc = TBFieldHelper.FormatDesc(dyF.TABLEDESCRIPTION); TBField field = new TBField(); field.Name = dyF.FILEDNAME; field.Desc = TBFieldHelper.FormatDesc(dyF.COLUMNDESCRIPTION); field.IsPrimaryKey = dyF.ISPK == 1 ? true : false; //获取数据类型 field.FieldType = TBFieldHelper.GetFieldType(field.Name, dyF.DATATYPE, DataBaseType.SQLServer); table.Fields.Add(field); } tables.Add(table); } return(tables); } }
/// <summary> /// 读取Oracle /// </summary> /// <returns></returns> private static List <TableInfo> ReaderOracle() { #region sql string sql = @" select x.*,(case when y.COLUMN_NAME is null then 0 else 1 end)IsPrimaryKey from ( select A.COLUMN_NAME,a.data_type,a.data_length,a.data_precision,a.data_scale,B.COMMENTS from user_tab_cols a left join user_col_comments b on a.COLUMN_NAME=B.COLUMN_NAME where a.TABLE_NAME='{0}' and b.TABLE_NAME='{0}' )x left join ( select COLUMN_NAME from user_constraints a left join user_cons_columns b on A.CONSTRAINT_NAME=B.CONSTRAINT_NAME where a.CONSTRAINT_TYPE = 'P' and a.table_name='{0}' )y on x.COLUMN_NAME=y.COLUMN_NAME"; #endregion using (DBSession db = DBSession.TryGet()) { //获取所有table dynamic dytables = db.GetDynamicList("select TABLE_NAME,COMMENTS from user_tab_comments"); List <TableInfo> tables = new List <TableInfo>(); foreach (dynamic item in dytables) { TableInfo table = new TableInfo(); table.Name = item.TABLE_NAME; table.Desc = TBFieldHelper.FormatDesc(item.COMMENTS); table.Fields = new List <TBField>(); //获取字段 dynamic fields = db.GetDynamicList(string.Format(sql, table.Name.ToUpper())); foreach (dynamic dyF in fields) { TBField field = new TBField(); field.Name = dyF.COLUMN_NAME; field.Desc = TBFieldHelper.FormatDesc(dyF.COMMENTS); field.IsPrimaryKey = dyF.ISPRIMARYKEY == 1 ? true : false; string typestr = dyF.DATA_TYPE; //获取数据类型 field.FieldType = TBFieldHelper.GetFieldType(field.Name, typestr, DataBaseType.Oracle, () => { return(Getdynamic <int>(dyF.DATA_PRECISION)); }, () => { return(Getdynamic <int>(dyF.DATA_SCALE)); }); table.Fields.Add(field); } tables.Add(table); } return(tables); } }