private DataTable dt; //表以及对应的描述信息 public OracleDAL(string conn) { help = new DbHelp(DbProviderFactories.GetFactory("System.Data.OracleClient"), conn); var strSql = @"select ROWNUM 序号 ,ut.table_name 表名,utc.comments 表说明 from user_tables ut left join user_tab_comments utc on ut.table_name = utc.table_name where utc.table_name not like '%$%' order by ut.table_name "; help.BeginTransation(); //查询所有表名 dt = help.ExecuteSql(strSql); //Oracle 主键关系表,使用临时表来管理 var keyTempTable = @"CREATE GLOBAL TEMPORARY TABLE table_key_constraints as SELECT col.table_name, col.column_name from user_constraints con,user_cons_columns col WHERE con.constraint_name=col.constraint_name and con.constraint_type='P' AND 1<>1"; var dropTempTable = @"DROP TABLE table_key_constraints"; var exportTmpTable = @"INSERT INTO table_key_constraints SELECT col.table_name, col.column_name from user_constraints con,user_cons_columns col WHERE con.constraint_name=col.constraint_name and con.constraint_type='P' "; //删除临时表 try { help.ExecuteNonQuery(dropTempTable); } catch (Exception) { help.BeginTransation(); } //创建临时表 help.ExecuteNonQuery(keyTempTable); //导入临时表数据 help.ExecuteNonQuery(exportTmpTable); strSql = @" select row_number()over( partition by utc.table_name order by utc.COLUMN_ID, ROWNUM ) as 序号, utc.table_name as 表名, utc.column_name as 列名, utc.data_type as 数据类型, utc.data_length as 长度, utc.data_precision as 精度, utc.data_Scale 小数位数, case ( select count(*) from table_key_constraints where column_name = utc.COLUMN_NAME and table_name=upper(utc.table_name) ) when 0 then '' else '√' end as 主键, case when utc.nullable = 'Y' then '√' else '' end as 允许空, utc.data_default as 默认值, ucc.comments as 列说明 from user_tab_columns utc,user_col_comments ucc where utc.table_name = ucc.table_name and utc.column_name = ucc.column_name and utc.table_name not like '%$%' and utc.table_name not like '%plsql%' order by utc.table_name,序号"; dtStruct = help.ExecuteSql(strSql); //删除临时表 help.ExecuteNonQuery(dropTempTable); help.Commit(); }
public List <DataTable> GetTableData(List <string> tables) { List <DataTable> lst = new List <DataTable>(); help.BeginTransation(); foreach (var table in tables) { //避免取出来的数据过大 var dt = help.ExecuteSql("select * from \"" + table + "\" where ROWNUM < 50 "); dt.TableName = table; lst.Add(dt); } help.Commit(); return(lst); }