c# 使用oracle表、列备注信息实现高级 查询

利用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;
        }
    }
}