Oracle根据连接字符串获取库下的表列表、获取表结构

Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=182.168.10.240)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)));User Id=JG_BASEPLATFORM;Password=123456 

#region 获取表信息
        /// <summary>
        /// 获取表信息
        /// </summary>
        /// <param name="ConnStr">连接字符串</param>
        /// <param name="Owner">登录表用户名</param>
        /// <param name="TableSpace">表空间，可为空</param>
        /// <returns></returns>
        public DataTable GetDBTable(string ConnStr, string Owner, string TableSpace)
        {
            DataTable dt = new DataTable();
            Hzjg.Common.DB.DBHelper oProxy = new Hzjg.Common.DB.DBHelper(ConnStr, "System.Data.OracleClient");
            string sql = @"SELECT A.TABLE_NAME AS 对象名,B.COMMENTS AS 对象说明 FROM USER_TABLES A LEFT JOIN ALL_TAB_COMMENTS B ON A.TABLE_NAME = B.TABLE_NAME WHERE B.OWNER = '" + Owner.ToUpper() + "'";
            if (TableSpace != "")
            {
                sql += " AND A.TABLESPACE_NAME ='" + TableSpace + "'";
            }
            DataSet ds = oProxy.EntityBase.ExecuteDataSet(System.Data.CommandType.Text, sql);
            if (ds != null && ds.Tables.Count > 0)
                dt = ds.Tables[0];
            return dt;
        }
        #endregion

 

#region 获取字段信息
        /// <summary>
        /// 获取字段信息
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="ConnStr">连接字符串</param>
        /// <param name="TableSpace">表空间，可为空</param>
        /// <returns></returns>
        public DataTable GetDBColumn(string TableName, string ConnStr, string TableSpace)
        {
            DataTable dt = new DataTable();
            Hzjg.Common.DB.DBHelper oProxy = new Hzjg.Common.DB.DBHelper(ConnStr, "System.Data.OracleClient");
            TableName = TableName.ToUpper();
            string sql = @"SELECT A.TABLE_NAME AS 表名,
                            A.COLUMN_NAME AS 字段名,
                            A.DATA_TYPE AS 类型,
                            A.CHAR_COL_DECL_LENGTH AS 长度,
                            A.DATA_PRECISION AS 小数位数,
                            CASE A.COLUMN_NAME WHEN (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 = '" + TableName + @"') THEN '√'END AS 主键,
                            CASE A.NULLABLE WHEN 'Y' THEN '√'END AS 允许空,
                            A.DATA_DEFAULT AS 默认值,
                            B.COMMENTS AS 字段说明 
                            FROM USER_TAB_COLUMNS A LEFT JOIN USER_COL_COMMENTS B ON A.COLUMN_NAME = B.COLUMN_NAME AND A.TABLE_NAME = B.TABLE_NAME
                            LEFT JOIN USER_TABLES C ON A.TABLE_NAME = C.TABLE_NAME 
                            WHERE A.TABLE_NAME='" + TableName + @"'";
            if (TableSpace != "")
            {
                sql += " AND C.TABLESPACE_NAME='" + TableSpace + "'";
            }
            sql += "ORDER BY COLUMN_ID ASC";
            DataSet ds = oProxy.EntityBase.ExecuteDataSet(System.Data.CommandType.Text, sql);
            if (ds != null && ds.Tables.Count > 0)
                dt = ds.Tables[0];
            return dt;
        }

        #endregion