利用oracle中的备注信息实现高级 查询，原理如下：利用oracle的列注释、表注释作为需要查询表的显示 名称和列列名称，拼接sql条件语句。
实现比较简单，两百多行代码，基本功能可实现，由于使用拼接sql的方式一些oracle安全性验证做的不多。也请各位多多指导。接触oracle不是很久。
效果图：
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Collections; using System.Data.OracleClient; using DbHelp; namespace gjcx { public partial class Form1 : Form { public Form1() { InitializeComponent(); } DataTable mUds_cel; Hashtable mHashTables = new Hashtable(); DataView mDv = null; DataTable mDt_sql = new DataTable(); OracleConnection conn = null; private void btn_add_Click(object sender, EventArgs e) { gridView_cel.AddNewRow(); } private void btn_del_Click(object sender, EventArgs e) { gridView_cel.DeleteRow(gridView_cel.FocusedRowHandle); } private void item_table_EditValueChanged(object sender, EventArgs e) { DevExpress.XtraEditors.LookUpEdit mLueEdits = (DevExpress.XtraEditors.LookUpEdit)sender; mDv.RowFilter = "表名='" + mLueEdits.EditValue.ToString() + "'"; } private void Form1_Load(object sender, EventArgs e) { try { conn = OracleHelper.GetConnection(); conn.Open(); } catch (Exception ex) { MessageBox.Show("连接数据库失败 " + ex.Message); } //别名，表名 查询涉及的别名和表名。由各个窗口传入 mHashTables.Add("b", "usershp"); //构造显示的grid的表结构 表名+列名+条件符+值+关系符 DataColumn mDc = null; mDc = new DataColumn("表注释", System.Type.GetType("System.String")); mDt_sql.Columns.Add(mDc); mDc = new DataColumn("列注释", System.Type.GetType("System.String")); mDt_sql.Columns.Add(mDc); mDc = new DataColumn("tjf", System.Type.GetType("System.String")); mDt_sql.Columns.Add(mDc); mDc = new DataColumn("value", System.Type.GetType("System.String")); mDt_sql.Columns.Add(mDc); mDc = new DataColumn("gxf", System.Type.GetType("System.String")); mDt_sql.Columns.Add(mDc); mDc = new DataColumn("kh_l", System.Type.GetType("System.String")); mDt_sql.Columns.Add(mDc); mDc = new DataColumn("kh_r", System.Type.GetType("System.String")); mDt_sql.Columns.Add(mDc); grid_cel.DataSource = mDt_sql; //根据hashtable表名查询数据表列注释和数据类型。每次item_table变更时做dataview的rowfilter //根据hashtable生成查询语句 string mCondion = ""; foreach (DictionaryEntry objDE in mHashTables) { mCondion = mCondion + "'" + objDE.Value + "',"; } if (mCondion.Length > 1) { mCondion = mCondion.Substring(0, mCondion.Length - 1); } mCondion = mCondion.ToUpper(); string mSql_table =@"select t.table_name as 表名, t.comments as 表注释 from user_tab_comments t where t.table_name in ( "+mCondion+")"; //uDataSet mUds_table = new uDataSet(mSql_table, new uParameter[] { }); DataTable mdt_query = OracleHelper.ReadTable(conn, CommandType.Text, mSql_table, null); item_table.DisplayMember = "表注释"; item_table.ValueMember = "表名"; item_table.DataSource = mdt_query; string mSql_cel = @"select c.column_name as 列名, --SUBSTR(c.comments,3) as 列注释 实现显示条件的排序注释内容为 AA_注释, c.comments as 列注释, TC.DATA_TYPE as 数据类型, t.table_name as 表名 from user_tab_comments t, user_col_comments c, USER_TAB_COLUMNS tc where t.table_name = c.table_name and t.TABLE_NAME = TC.TABLE_NAME AND C.table_name = TC.TABLE_NAME AND C.column_name = TC.COLUMN_NAME AND TC.DATA_TYPE <> 'LONG' AND C.comments IS NOT NULL and t.table_name in ( " + mCondion+") ORDER BY C.comments"; mUds_cel = OracleHelper.ReadTable(conn, CommandType.Text, mSql_cel, null); mDv = new DataView(mUds_cel); mDv.RowFilter = "1=2"; item_cel.DataSource = mDv.Table; item_cel.DisplayMember = "列注释"; item_cel.ValueMember = "列名"; } private void btn_sc_Click(object sender, EventArgs e) { //拼接sql语句 关系符 +左括号+ 格式为别名.列名 +条件项+"'"+value+"'"+右括号 DataView mDv_pj = (DataView)gridView_cel.DataSource; DataTable mDt_pj = mDv_pj.Table; //涉及的几项验证 //1、左右括号个数一致 2、值一栏中不能有‘.3几项非空验证 int mCount_kh_l = 0; int mCount_kh_r = 0; string mValueErro = ""; string mErro = ""; for (int i = 0; i < mDt_pj.Rows.Count; i++) { if (mDt_pj.Rows[i]["kh_l"].ToString() == "(") { mCount_kh_l = mCount_kh_l + 1; } if (mDt_pj.Rows[i]["kh_r"].ToString() == ")") { mCount_kh_r = mCount_kh_r + 1; } if (mDt_pj.Rows[i]["value"].ToString().Contains("'")) { mValueErro = "值中包含‘,请删除后重试"; } if (string.IsNullOrEmpty(mDt_pj.Rows[i]["gxf"].ToString())) { mErro = "关系符不能为空"; } if (string.IsNullOrEmpty(mDt_pj.Rows[i]["表注释"].ToString())) { mErro = "表名不能为空"; } if (string.IsNullOrEmpty(mDt_pj.Rows[i]["列注释"].ToString())) { mErro = "列名不能为空"; } if (string.IsNullOrEmpty(mDt_pj.Rows[i]["tjf"].ToString())) { mErro = "条件项不能为空"; } //为空 选择 is null if (mDt_pj.Rows[i]["tjf"].ToString().ToUpper() == "IS NULL" && !string.IsNullOrEmpty(mDt_pj.Rows[i]["value"].ToString())) { mErro = "条件项为IS NULL时,值不用书写 "; } if (mDt_pj.Rows[i]["tjf"].ToString().ToUpper() != "IS NULL" && string.IsNullOrEmpty(mDt_pj.Rows[i]["value"].ToString())) { mErro = "值不能为空 "; } } if (mCount_kh_l != mCount_kh_r) { MessageBox.Show("左右括号不一致,请查询条件"); return; } if (!string.IsNullOrEmpty(mValueErro)) { MessageBox.Show(mValueErro); return; } if (!string.IsNullOrEmpty(mErro)) { MessageBox.Show(mErro); return; } string mTableBm = ""; string mSql = ""; for (int i = 0; i < mDt_pj.Rows.Count; i++) { mSql = mSql + " " + mDt_pj.Rows[i]["gxf"].ToString(); mSql = mSql + " " + mDt_pj.Rows[i]["kh_l"].ToString(); //将表名转为别名 foreach (DictionaryEntry de in mHashTables) { if (de.Value.ToString().ToUpper() == mDt_pj.Rows[i]["表注释"].ToString()) { mTableBm = de.Key.ToString(); } } mSql = mSql + " " + mTableBm + "." + mDt_pj.Rows[i]["列注释"].ToString(); mSql = mSql + " " + mDt_pj.Rows[i]["tjf"].ToString(); if (mDt_pj.Rows[i]["tjf"].ToString().ToUpper() == "LIKE") { mSql = mSql + " " + "'%" + mDt_pj.Rows[i]["value"].ToString() + "%'"; } else if (mDt_pj.Rows[i]["tjf"].ToString().ToUpper() == "IS NULL") { } else { mSql = mSql + " " + "'" + mDt_pj.Rows[i]["value"].ToString() + "'"; } mSql = mSql + " " + mDt_pj.Rows[i]["kh_r"].ToString(); } memoEdit1.Text = mSql; } } }