Azure Lei Zhang的博客

weibo: LeiZhang的微博/QQ: 185165016/QQ群:319036205/邮箱:leizhang1984@outlook.com/TeL:139-161-22926

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::

/// <summary>
        /// 查询方法
        /// </summary>
        /// <param name="tabNames">TableName</param>
        /// <returns>数据集</returns>
        public DataSet Query(string tabNames,string columnName)
        {
            using (OracleHelper orahp = new OracleHelper())
            {
                string[] tabs = tabNames.Split(',');
                DataSet data = new DataSet();
                orahp.Open();
                for (int i = 0; i < tabs.Length; i++)
                {
                    string tabTemp = tabs[i];
                    string sql = "select NVL(v.外键表,'" + tabTemp + "') as 外键表,ac.column_name 列名,ac.data_type 类型,ac.data_length 长度,v.主键表,v.主键列,p.主键列 PK,v.多选,ac.NULLABLE from ";
                    sql += "(select s.table_name,s.COLUMN_NAME,s.data_type,s.data_length,s.NULLABLE from all_tab_columns s where ";
                    if (columnName.Length > 0)
                        sql += "s.column_name='" + columnName + "'";
                    else
                        sql+="s.column_name<>'OBJECTID'";
                    sql+=" and s.owner='"+ dbOwner +"' and s.TABLE_NAME='"+tabTemp+"') ac";
                    sql += " left outer join (select * from view_fk where 外键表='"+ tabTemp +"') v on v.外键列=ac.column_name";
                    sql += " left outer join (select * from view_pk where 表名='" + tabTemp + "') p on ac.COLUMN_NAME=p.主键列";
                    if (columnName.Length < 1)
                    sql += " union select v.外键表,v.外键列 as 列名,'NVARCHAR2' as 类型,100 as 长度,v.主键表,v.主键列,null as PK,v.多选,'N' as NULLABLE from(select * from view_fk where 主键表='" + tabTemp + "') v";

                    OracleCommand comd = new OracleCommand(sql);
                    DataTable dataTab = new DataTable();
                    dataTab.TableName = tabTemp;
                    if (data.Tables.Contains(tabTemp))
                        continue;
                    orahp.Execute(comd, dataTab);
                    //data.Tables.Add(dataTab);
                    data = ChangeDt(dataTab);
                }
                orahp.Close();

                return data;
            }
        }

posted on 2010-04-18 16:53  Lei Zhang的博客  阅读(1123)  评论(1编辑  收藏  举报