/// <summary>
/// 查询用户所有可操作的表
/// </summary>
/// <returns></returns>
public static System.Data.DataTable QueryTables()
{
System.Data.DataTable dt = new System.Data.DataTable();
using (InterfaceDataBase db = DataBaseManager.GetDataBase())
{
try
{
string strSql = "select USER_TABLES.TABLE_NAME,USER_TAB_COMMENTS.COMMENTS from user_tables left join user_tab_comments on USER_TABLES.TABLE_NAME = USER_TAB_COMMENTS.TABLE_NAME ";
switch (DataBaseManager.GetDataBaseType())
{
case DataBaseManager.DataBaseType.MSSqlServer:
break;
case DataBaseManager.DataBaseType.Oracle:
break;
case DataBaseManager.DataBaseType.DB2:
strSql = "select tabNAME as table_name, SYSCAT.TABLES.REMARKS as comments from syscat.tables where tabschema = current schema or tabschema='ES' or tabschema='TTA'";
break;
case DataBaseManager.DataBaseType.Default:
break;
default:
break;
}
dt = db.RunSqlQuery(strSql);
}
catch (Exception ex)
{
throw ex;
}
}
return(dt);
}
/// <summary>
/// 查询表中数据
/// </summary>
/// <param name="tableName">表名称</param>
/// <returns></returns>
public static System.Data.DataTable QueryDataByTableName(string tableName)
{
if (tableName.Trim().Equals(string.Empty))
{
return(null);
}
System.Data.DataTable dt = new System.Data.DataTable();
using (InterfaceDataBase db = DataBaseManager.GetDataBase())
{
try
{
//string strSqlOra = "select * from @TABLE_NAME ";
//Dictionary<string, object> parameter = new Dictionary<string, object>();
//parameter.Add("TABLE_NAME", tableName);
//dtLogResult = db.RunSqlQuery(strSqlOra, parameter);
string strSql = "select * from " + tableName;
dt = db.RunSqlQuery(strSql);
dt.TableName = tableName;
}
catch (Exception ex)
{
throw ex;
}
}
return(dt);
}
public static DataTable ExecuteSqlQuery(string strSql)
{
DataTable result;
using (InterfaceDataBase db = DataBaseManager.GetDataBase())
{
try
{
result = db.RunSqlQuery(strSql);
}
catch (Exception ex)
{
throw ex;
}
}
return(result);
}
public static DataTable QueryColumnInfoByColName(string colName)
{
System.Data.DataTable dt = new System.Data.DataTable();
using (InterfaceDataBase db = DataBaseManager.GetDataBase())
{
try
{
string strSql = "";
switch (DataBaseManager.GetDataBaseType())
{
case DataBaseManager.DataBaseType.MSSqlServer:
break;
case DataBaseManager.DataBaseType.Oracle:
strSql = string.Format(" select t1.column_name as item_name,t2.comments as item_desc, t1.data_type as item_type,t1.data_length as item_len, "
+ " t1.data_scale as item_scale, t1.data_precision as ITEM_PRECISION ,t1.data_default "
+ " from ALL_TAB_COLUMNS T1 ,ALL_COL_COMMENTS T2 "
+ " where T1.OWNER = T2.OWNER AND T1.TABLE_NAME = T2.TABLE_NAME AND T1.COLUMN_NAME = T2.COLUMN_NAME"
+ " and t1.column_name = '{0}' and rownum = 1 order by column_id ", colName.ToUpper());
break;
case DataBaseManager.DataBaseType.DB2:
strSql = string.Format(" select t1.name as item_name,t1.remarks as item_desc, t1.typename as item_type,t1.length as item_len,"
+ " t1.scale as item_scale,t1.length as ITEM_PRECISION,t1.default as data_default ,t1.keyseq "
+ " from Sysibm.syscolumns t1 where 1=1"
+ " and t1.name = '{0}' order by colNo ", colName.ToUpper());
break;
case DataBaseManager.DataBaseType.Default:
break;
default:
break;
}
dt = db.RunSqlQuery(strSql);
}
catch (Exception ex)
{
throw ex;
}
}
return(dt);
}
/// <summary>
/// 获取表的所有列名称以及列名称描述
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public static System.Data.DataTable QueryColumnByTableName(string tableName)
{
System.Data.DataTable dt = new System.Data.DataTable();
using (InterfaceDataBase db = DataBaseManager.GetDataBase())
{
try
{
//string strSqlOra = "select USER_TABLES.TABLE_NAME as TABLE_NAME,USER_COL_COMMENTS.COMMENTS ,USER_COL_COMMENTS.COLUMN_NAME from user_tables left join user_col_comments on USER_TABLES.TABLE_NAME = user_col_comments.TABLE_NAME "
// + " where USER_TABLES.TABLE_NAME=@TABLE_NAME ";
string strSqlOra = "select table_name,column_name,comments from all_col_comments where TABLE_NAME =@TABLE_NAME ";
Dictionary <string, object> parameter = new Dictionary <string, object>();
parameter.Add("TABLE_NAME", tableName);
string strSql = strSqlOra;
switch (DataBaseManager.GetDataBaseType())
{
case DataBaseManager.DataBaseType.MSSqlServer:
break;
case DataBaseManager.DataBaseType.Oracle:
strSql = strSqlOra;
break;
case DataBaseManager.DataBaseType.DB2:
strSql = "select name as column_name, REMARKS as comments from Sysibm.syscolumns where TBNAME =@TABLE_NAME ";
break;
case DataBaseManager.DataBaseType.Default:
break;
default:
strSql = strSqlOra;
break;
}
dt = db.RunSqlQuery(strSql, parameter);
}
catch (Exception ex)
{
throw ex;
}
}
return(dt);
}
/// <summary>
/// 查询用户所有可操作的表
/// </summary>
/// <returns></returns>
public static System.Data.DataTable QueryResxTables(string tableName)
{
System.Data.DataTable dt = new System.Data.DataTable();
using (InterfaceDataBase db = DataBaseManager.GetDataBase())
{
try
{
string strSqlOra = "select table_name,comments from all_tab_comments ";
//string strSqlOra = "select USER_TABLES.TABLE_NAME,USER_TAB_COMMENTS.COMMENTS from user_tables left join user_tab_comments on USER_TABLES.TABLE_NAME = USER_TAB_COMMENTS.TABLE_NAME where USER_TABLES.TABLE_NAME like '%'||@TABLENAME||'%' ";
//Dictionary<string, object> parameter = new Dictionary<string, object>();
//parameter.Add("TABLENAME", tableName);
string strSqlDB2 = "select tabNAME as table_name, SYSCAT.TABLES.REMARKS as comments from syscat.tables where tabschema = current schema or tabschema='ES' or tabschema='TTA'";
string strSql = strSqlOra;
switch (DataBaseManager.GetDataBaseType())
{
case DataBaseManager.DataBaseType.MSSqlServer:
break;
case DataBaseManager.DataBaseType.Oracle:
strSql = strSqlOra;
break;
case DataBaseManager.DataBaseType.DB2:
strSql = strSqlDB2;
break;
case DataBaseManager.DataBaseType.Default:
strSql = strSqlOra;
break;
default:
break;
}
dt = db.RunSqlQuery(strSql);
}
catch (Exception ex)
{
throw ex;
}
}
return(dt);
}