private DataTable dt; //表以及对应的描述信息
public OracleDAL(string conn)
{
help = new OracleHelp(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 order by ut.table_name";
dt = help.ExecuteSql(strSql);
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 when exists ( select col.column_name from user_constraints con,user_cons_columns col
where con.constraint_name=col.constraint_name and con.constraint_type='P' and col.table_name=ucc.table_name and col.column_name = utc.column_name )
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
order by utc.table_name,序号";
dtStruct = help.ExecuteSql(strSql);
}
public List <DataTable> GetTableData(List <string> tables)
{
List <DataTable> lst = new List <DataTable>();
foreach (var table in tables)
{
//避免取出来的数据过大
var dt = help.ExecuteSql("select * from " + table + " where ROWNUM < 100 ");
dt.TableName = table;
lst.Add(dt);
}
return(lst);
}
private DataTable dt; //表以及对应的描述信息
//Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.22) (PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=pcuenca)));Persist Security Info=True;User Id=cawy_cas; Password=ECU911_db
public OracleDAL(string conn)
{
//conn = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=issdb.amb.ecu911.gob) (PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=r4th)));Persist Security Info=True;User Id=cawy_cas; Password=ECU911_db";
//conn = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=issdb.santd.ecu911.gob) (PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=pmorona)));Persist Security Info=True;User Id=cawy_cas; Password=ECU911_db";
//conn = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=issdb.esme.ECU911.GOB) (PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=pesmeral)));Persist Security Info=True;User Id=cawy_cas; Password=ECU911_db";
help = new OracleHelp(conn);
//var strSql = "select ROWNUM as \"序号\" ,ut.table_name \"表名\",utc.comments \"表说明\" from user_tables ut left join user_tab_comments utc on ut.table_name = utc.table_name order by ut.table_name";
//dt = help.ExecuteSql(strSql);
//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 when exists ( select col.column_name from user_constraints con,user_cons_columns col " +
// " where con.constraint_name=col.constraint_name and con.constraint_type='P' and col.table_name=ucc.table_name and col.column_name = utc.column_name ) " +
// " 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 " +
// " order by utc.table_name,\"序号\" ";
var strSql = "select ROWNUM as \"序号\" ,ut.table_name \"表名\",utc.comments \"表说明\" from user_tables ut left join user_tab_comments utc on ut.table_name = utc.table_name order by ut.table_name";
//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 order by ut.table_name";
dt = help.ExecuteSql(strSql);
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 when exists ( select col.column_name from user_constraints con,user_cons_columns col " +
" where con.constraint_name=col.constraint_name and con.constraint_type='P' and col.table_name=ucc.table_name and col.column_name = utc.column_name ) " +
" 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 " +
" order by utc.table_name,\"序号\" ";
// strSql = @" select row_number()over( partition by utc.table_name order by utc.COLUMN_ID, ROWNUM ) as xh,
// utc.table_name as bm,
// utc.column_name as lm,
// utc.data_type as sjlx,
// utc.data_length as cl,
// utc.data_precision as jl,
// utc.data_Scale xsws,
// case when exists ( select col.column_name from user_constraints con,user_cons_columns col
// where con.constraint_name=col.constraint_name and con.constraint_type='P' and col.table_name=ucc.table_name and col.column_name = utc.column_name )
// then 'Y' else '' end as zj,
// case when utc.nullable = 'Y' then 'Y' else '' end as yxk,
// utc.data_default as mrzh,
// ucc.comments as lshm
// from
// user_tab_columns utc,user_col_comments ucc
// where utc.table_name = ucc.table_name and utc.column_name = ucc.column_name
// order by utc.table_name,xh";
dtStruct = help.ExecuteSql(strSql);
}