外键表 主键表

/// <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;
            }
        }

分类:  Oracle

本文转自Lei Zhang的博客博客园博客，原文链接：http://www.cnblogs.com/threestone/archive/2010/04/18/1714797.html，如需转载请自行联系原作者