本来想用存储过程实现动态查询,但是我们直接拼接的sql还是无法避免sql注入攻击,并且不能使用绑定变量。思前想后还是写了个DynamicSQL的类用来实现动态拼接SQL。具体的怎么拼接通过实体的特性来实现。这个类代替我们原先的ParameterClass类,用来实现动态查询。
下面大致说下特性的使用:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
[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; }
}
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();
}
{
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);
}
/// 复核登记查询
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public DataTable FuHeDJ_ChaXun(Entity.BiaoWuGL.FuHeDJSearchDTO dto)
{
return DynamicSQL.GetTable(dto);
}
下面是部分代码的实现,写的比较仓卒,应该会有bug,碰到问题在修改了:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
//
********************************************************************************
//
// 创建日期:<创建日期,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;
}
}
}
// 创建日期:<创建日期,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;
}
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
//
********************************************************************************
//
// 创建日期:<创建日期,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
}
}
// 创建日期:<创建日期,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
}
}