关于Oracle的动态查询

本来想用存储过程实现动态查询，但是我们直接拼接的sql还是无法避免sql注入攻击，并且不能使用绑定变量。思前想后还是写了个DynamicSQL的类用来实现动态拼接SQL。具体的怎么拼接通过实体的特性来实现。这个类代替我们原先的ParameterClass类，用来实现动态查询。

下面大致说下特性的使用：

View Code

    [EnitityMapping(TableName="bw_fuhedj_view")]
    public class FuHeDJSearchDTO
    {
        /// <summary>
        /// 站点号
        /// </summary>
        [DataMember]
        public string S_ST { get; set; }

        /// <summary>
        /// 册本号
        /// </summary>
        [DataMember]
        public string S_CH { get; set; }

        /// <summary>
        /// 用户编号
        /// </summary>
        [DataMember]
        public string S_CID { get; set; }

        /// <summary>
        /// 客户编号
        /// </summary>
        [EnitityMapping(ColumnName="s_kehuid")]
        [DataMember]
        public string S_KeHuBH { get; set; }

        /// <summary>
        /// 户名
        /// </summary>
        [EnitityMapping(ColumnName = "s_hm", Operater = Operater.Like)]
        [DataMember]
        public string S_HuMing { get; set; }

        /// <summary>
        /// 地址
        /// </summary>
        [EnitityMapping(ColumnName = "s_dz",Operater= Operater.Like )]
        [DataMember]
        public string S_DiZhi { get; set; }

    }

首先在类上加入需要mapping的表名，如果是多表关联的则输入对应的视图名。entity中的字段名如果和查询的列名一致则什么都不用写，如果不一致则需要mapping对应的列名。如果操作是等于操作则不用修改，否则在Operater使用其他枚举。目前还不支持in、is等操作符，等到用到再说吧。字段的连接都使用and连接。如果以后有or操作的话可能在操作类型里面加个Operater.Other，然后自己写对应的sql了。目前暂不支持or操作。

写完Entity后调用DynamicSQL的GetTable方法就可以了。注意代码中的DataMember特性是标识实体是WCF的数据成员可以不要，使用也没有问题。最终的检验代码也可以用特性实现。

调用方法表现层：

        private void btnSearch_Click(object sender, EventArgs e)
        {
            BiaoWuGLService.BiaoWuGLClient bw = new BiaoWuGLService.BiaoWuGLClient();
            Entity.BiaoWuGL.FuHeDJSearchDTO dto = new Entity.BiaoWuGL.FuHeDJSearchDTO
            {
                S_CID = txtZhangHao.Text,
                S_HuMing = txtYongHuH.Text,
                S_DiZhi = txtDiZhi.Text
            };
            DataTable dt = bw.FuHeDJ_ChaXun(dto);
            bw.Close();
        }

应用层：

        /// <summary>
        /// 复核登记查询
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public DataTable FuHeDJ_ChaXun(Entity.BiaoWuGL.FuHeDJSearchDTO dto)
        {
            return DynamicSQL.GetTable(dto);
        }

下面是部分代码的实现，写的比较仓卒，应该会有bug，碰到问题在修改了：

View Code

//********************************************************************************//
//创建日期：<创建日期，2012-05-27>
//创建作者：<张易，zhangyi@shanghai3h.com>
//功能说明：动态拼接SQL，防注入攻击，绑定变量
//********************************************************************************//
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Platform.Data;
using Oracle.DataAccess.Client;
using System.Reflection;
using CSMS2.Entity;

namespace CSMS2.Service
{
    public static class DynamicSQL
    {
        public static DataTable GetTable<T>(T dto)
        {
            string conn = Platform.Configuration.ConfigHelper.BusinessConnString;
            string where = "";
            string sql = "select * from " + GetTableName(dto);
            OracleParameter[] oracleParameter = GetOracleParameter(dto, out where);
            //return OracleHelper.ExecuteDataset(conn, CommandType.Text, sql + " where s_cid='1003287'").Tables[0];
            return OracleHelper.ExecuteDataset(conn, CommandType.Text, sql + where, oracleParameter).Tables[0];
        }

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dto"></param>
        /// <returns></returns>
        private static OracleParameter[] GetOracleParameter<T>(T dto, out string wheresql)
        {
            int length = 0;
            wheresql = "";
            foreach (PropertyInfo p in dto.GetType().GetProperties())
            {
                if (null != p.GetValue(dto, null))
                {
                    if (p.GetValue(dto, null).ToString() != "")
                        length++;
                }
            }

            OracleParameter[] storedParams = new OracleParameter[length];
            int j = 0;
            for (int i = 0; i < dto.GetType().GetProperties().Length; i++)
            {
                PropertyInfo p = dto.GetType().GetProperties()[i];
                if (null != p.GetValue(dto, null))
                {
                    if (p.GetValue(dto, null).ToString() == "")
                        continue;
                    Operater op = GetOperaterName(p);
                    if (op == Operater.Equal)
                        wheresql += " and " + GetColumnName(p) + " = :" + p.Name;
                    else if (op == Operater.Greater)
                        wheresql = " and " + GetColumnName(p) + " > :" + p.Name;
                    else if (op == Operater.GreaterEqual)
                        wheresql += " and " + GetColumnName(p) + " >= :" + p.Name;
                    else if (op == Operater.In)
                        wheresql += " and " + GetColumnName(p) + " = :" + p.Name;
                    else if (op == Operater.Is)
                        wheresql += " and " + GetColumnName(p) + " = :" + p.Name;
                    else if (op == Operater.Less)
                        wheresql += " and " + GetColumnName(p) + " < :" + p.Name;
                    else if (op == Operater.LessEqual)
                        wheresql += " and " + GetColumnName(p) + " <= :" + p.Name;
                    else if (op == Operater.Like)
                        wheresql += " and " + GetColumnName(p) + " like :" + p.Name + "";
                    else if (op == Operater.NotEqual)
                        wheresql += " and " + GetColumnName(p) + " != :" + p.Name;
                    else if (op == Operater.NotIn)
                        wheresql += " and " + GetColumnName(p) + " = :" + p.Name;

                    OracleParameter m = new OracleParameter();
                    if (p.PropertyType == typeof(string))
                    {
                        m = new OracleParameter(GetColumnName(p), OracleDbType.Varchar2, 8000);
                    }
                    else if (p.PropertyType == typeof(int))
                    {
                        m = new OracleParameter(GetColumnName(p), OracleDbType.Int32);
                    }
                    else if (p.PropertyType == typeof(decimal))
                    {
                        m = new OracleParameter(GetColumnName(p), OracleDbType.Decimal);
                    }
                    else if (p.PropertyType == typeof(DateTime))
                    {
                        m = new OracleParameter(GetColumnName(p), OracleDbType.Date);
                    }

                    if (op == Operater.Like)
                        m.Value = p.GetValue(dto, null) + "%";
                    else
                        m.Value = p.GetValue(dto, null);
                    storedParams[j] = m;
                    j++;
                }
            }
            if (wheresql.Length > 0)
                wheresql = " where" + wheresql.Substring(4);
            return storedParams;
        }

        /// <summary>
        /// 获取列名
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        private static string GetColumnName(PropertyInfo info)
        {
            string columnname = string.Empty;
            foreach (object obj in info.GetCustomAttributes(typeof(EnitityMappingAttribute), true))
            {
                if (obj.GetType() == typeof(EnitityMappingAttribute))
                {
                    EnitityMappingAttribute attr = obj as EnitityMappingAttribute;
                    columnname = attr.ColumnName;
                    break;
                }
            }
            if (string.IsNullOrEmpty(columnname))
                columnname = info.Name;
            return columnname.ToUpper();
        }

        /// <summary>
        /// 获取列名
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        private static Operater GetOperaterName(PropertyInfo info)
        {
            Operater op = Operater.Equal;
            foreach (object obj in info.GetCustomAttributes(typeof(EnitityMappingAttribute), true))
            {
                if (obj.GetType() == typeof(EnitityMappingAttribute))
                {
                    EnitityMappingAttribute attr = obj as EnitityMappingAttribute;
                    if (null != attr.Operater)
                    {
                        op = attr.Operater;
                        break;
                    }
                }
            }
            return op;
        }

        /// <summary>
        /// 获取实体对应的表名
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dto"></param>
        /// <returns></returns>
        private static string GetTableName<T>(T dto)
        {
            string tablename = string.Empty;
            foreach (object obj in dto.GetType().GetCustomAttributes(typeof(EnitityMappingAttribute), true))
            {
                if (obj.GetType() == typeof(EnitityMappingAttribute))
                {
                    EnitityMappingAttribute attr = obj as EnitityMappingAttribute;
                    tablename = attr.TableName;
                    break;
                }
            }
            return tablename;
        }
    }
}

View Code

//********************************************************************************//
//创建日期：<创建日期，2012-05-27>
//创建作者：<张易，zhangyi@shanghai3h.com>
//功能说明：自定义特性属性或者类可用  支持继承
//********************************************************************************//
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CSMS2.Entity
{
    /// <summary>
    /// 自定义特性属性或者类可用  支持继承
    /// </summary>
    [AttributeUsage(AttributeTargets.Property | AttributeTargets.Class, Inherited = true)]
    public class EnitityMappingAttribute : Attribute
    {

        /// <summary>
        /// 实体实际对应的表名
        /// </summary>
        public string TableName
        {
            get;
            set;
        }

        /// <summary>
        /// 中文列名
        /// </summary>
        public string ColumnName
        {
            get;
            set;
        }

        public Operater Operater
        {
            get;
            set;
        }

    }

    /// <summary>
    /// 操作类
    /// </summary>
    public enum Operater
    {
        Equal,
        Greater,
        Less,
        GreaterEqual,
        LessEqual,
        Like,
        In,
        Is,
        NotEqual,
        NotIn
    }
}