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

最新推荐文章于 2024-11-09 21:51:58 发布

weixin_30512043

最新推荐文章于 2024-11-09 21:51:58 发布

阅读量62

点赞数

文章标签：数据库网络

原文链接：http://www.cnblogs.com/zecVip/p/5546826.html

版权

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

转载于:https://www.cnblogs.com/zecVip/p/5546826.html