虽然应用抽象工厂模式能很好解决数据库的变化不给业务层带来影响，但前提是在业务层不能涉及到具体的sql语句，sql语句只能出现在数据操作层，不同数据库具体的操作层的写法会有差异，主要是sql语句的拼装或存储过程的写法上。但一般的数据表对应的实体类的方法比较固定，有些查询变化比较多，如果针对每个变化都在各层加实现方法也不是很好，有的是另外加一个通用的数据操作类，主要是为了应对sql的自由拼装。
说明：该类用于处理一般常用的sql 语句封装，至于用到数据库系统函数等的这个类还不支持，下面是以mysql 为例做的，同时也支持sql server ，如果是access或其它数据库 只要修改实现的部分即可
生成部分只所以不用if或case判断来直接拼装是为了方便扩展，但处理list的部分还是处理得不是很好，增加不同的类型得增加判断，如果有更好的处理方法请告知，再改进下，呵呵
---测试用例---
private void button26_Click(object sender, EventArgs e)
{
DataSet ds1 = new DataSet();
DataSet ds2 = new DataSet();
//条件实体list
List<PLAA.Model.SqlUnit> listmodel = new List<PLAA.Model.SqlUnit>();
BLL.CommonDB bll = new PLAA.BLL.CommonDB();
//select * from SysUser where UserName='admin';
PLAA.Model.SqlUnit s1 = new PLAA.Model.SqlUnit();
s1.FieldName = "UserName";
s1.FieldValue1 = "admin";
s1.DataType = PLAA.Model.SqlUnitDataType.String;
s1.FieldValue2 = "";
s1.OprateSymbol = PLAA.Model.SqlUnitOprateSymbol.Equal;
s1.SplitKeyword = "";
listmodel.Add(s1);
ds1 = bll.GetList("SysUser", "*", listmodel, "", "");
//select * from SysUser where (SysUserID BETWEEN 2 and 5) or UserName='admin';
listmodel.Clear();
PLAA.Model.SqlUnit s01 = new PLAA.Model.SqlUnit();
s01.FieldName = "";
s01.FieldValue1 = "(";
s01.DataType = PLAA.Model.SqlUnitDataType.Symbol;
s01.FieldValue2 = "";
s01.OprateSymbol = PLAA.Model.SqlUnitOprateSymbol.Equal;
s01.SplitKeyword = "";
listmodel.Add(s01);
PLAA.Model.SqlUnit s2 = new PLAA.Model.SqlUnit();
s2.FieldName = "SysUserID";
s2.FieldValue1 = "2";
s2.DataType = PLAA.Model.SqlUnitDataType.Int;
s2.FieldValue2 = "5";
s2.OprateSymbol = PLAA.Model.SqlUnitOprateSymbol.Between;
s2.SplitKeyword = "";
listmodel.Add(s2);
PLAA.Model.SqlUnit s02 = new PLAA.Model.SqlUnit();
s02.FieldName = "";
s02.FieldValue1 = ")";
s02.DataType = PLAA.Model.SqlUnitDataType.Symbol;
s02.FieldValue2 = "";
s02.OprateSymbol = PLAA.Model.SqlUnitOprateSymbol.Equal;
s02.SplitKeyword = "Or";
listmodel.Add(s02);
PLAA.Model.SqlUnit s3 = new PLAA.Model.SqlUnit();
s3.FieldName = "UserName";
s3.FieldValue1 = "admin";
s3.DataType = PLAA.Model.SqlUnitDataType.String;
s3.FieldValue2 = "";
s3.OprateSymbol = PLAA.Model.SqlUnitOprateSymbol.Equal;
s3.SplitKeyword = "";
listmodel.Add(s3);
ds2 = bll.GetList("SysUser", "*", listmodel, "", "");
}
---PLAA.Model.SqlUnit---
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace PLAA.Model
{
/// <summary>
/// 一个sql where 条件的语句单位 如 Username ='value' and
/// </summary>
[Serializable]
public partial class SqlUnit
{
private string _fieldName;
private SqlUnitDataType _dataType;
private SqlUnitOprateSymbol _OprateSymbol;
private string _fieldValue1;
private string _fieldValue2;
private string _splitKeyword;
/// <summary>
/// 字段名 不能是sql 关键字
/// </summary>
public string FieldName
{
get
{
return _fieldName;
}
set
{
_fieldName = value;
}
}
/// <summary>
/// 字段对应的值
/// </summary>
public string FieldValue1
{
get
{
return _fieldValue1;
}
set
{
_fieldValue1 = value;
}
}
/// <summary>
/// 字段对应的值2 用在between
/// </summary>
public string FieldValue2
{
get
{
return _fieldValue2;
}
set
{
_fieldValue2 = value;
}
}
/// <summary>
/// 用于与下一条件的连接关键字 and 或 or
/// </summary>
public string SplitKeyword
{
get
{
return _splitKeyword;
}
set
{
_splitKeyword = value;
}
}
/// <summary>
/// 字段对应的数据类型 string 或 int symble为特殊类型主要是处理左右括号 ( )
/// </summary>
public SqlUnitDataType DataType
{
get
{
return _dataType;
}
set
{
_dataType = value;
}
}
/// <summary>
/// 字段名与值的关系 = > >= between in 小于，小于等于 like
/// </summary>
public SqlUnitOprateSymbol OprateSymbol
{
get
{
return _OprateSymbol;
}
set
{
_OprateSymbol = value;
}
}
}
public enum SqlUnitDataType
{
String,
Int,
Symbol //用于特殊的情况 左右括号(,)
}
public enum SqlUnitOprateSymbol
{
Equal, //等于
GT, //大于
GTandEqual, //大于等于
LT, //小于
LTandEqual, //小于等于
Between, //介于...之间
In, //in
Like //like
}
}
---PLAA.BLL.CommonDB.GetList---
/// <summary>
/// 根据条件查询数据
/// </summary>
/// <param name="TableViewName">列或视图名</param>
/// <param name="Selectstr">查询字段列表，最后带 From </param>
/// <param name="sqlUnitList">条件的组装部分的list</param>
/// /// <param name="Orderby">group by 部分 </param>
/// <param name="Orderby">order by 部分，desc,asc也要含在里面</param>
/// <returns>dataset</returns>
public DataSet GetList(string TableViewName, string Selectstr, List<Model.SqlUnit> sqlUnitList, string Groupby, string Orderby)
{
return dal.GetList(TableViewName, Selectstr, sqlUnitList, Groupby, Orderby);
}
---PLAA.MySQLDAL.CommonDB.GetList---
/// <summary>
/// 根据条件查询数据
/// </summary>
/// <param name="TableViewName">列或视图名</param>
/// <param name="Selectstr">查询字段列表，最后带 From </param>
/// <param name="sqlUnitList">条件的组装部分的list</param>
/// <param name="Orderby">group by 部分 </param>
/// <param name="Orderby">order by 部分，desc,asc也要含在里面</param>
/// <returns>dataset</returns>
public DataSet GetList(string TableViewName, string Selectstr, List<Model.SqlUnit> sqlUnitList,string Groupby,string Orderby)
{
DataSet ds = null;
string strsql = "";
string strselect = "Select ";
string strwhere = " Where ";
string strOrderby = " Order By ";
string strGroupby = " Group By ";
//生成where 部分
string strNewWhere = CreatSqlWhere.CreatWhere(sqlUnitList);
if (strNewWhere.Length > 1)
{
strwhere += strNewWhere;
}
else
{
strwhere = "";
}
//order by 部分没有则去掉
if (Orderby.Length > 0)
{
strOrderby += Orderby;
}
else
{
strOrderby = "";
}
//group by 没有则去掉
if (Groupby.Length > 0)
{
strGroupby += Orderby;
}
else
{
strGroupby = "";
}
//组装成完整的sql
strsql = strselect + Selectstr +" From "+ TableViewName + strwhere + strGroupby+ strOrderby;
ds = DbHelperMySQL.Query(strsql);
//清空list
sqlUnitList.Clear();
sqlUnitList = null;
return ds;
}
---
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace PLAA.MySQLDAL
{
/// <summary>
/// 根据sqlunit list 拼装sql 的条件部分
/// </summary>
[Serializable]
public partial class CreatSqlWhere
{
/// <summary>
/// 根据sqlunit list 拼装sql 的条件部分
/// </summary>
/// <param name="sqlUnitList"></param>
/// <returns></returns>
public static string CreatWhere(List<Model.SqlUnit> sqlUnitList)
{
string strWhere = "";
if (sqlUnitList == null)
{
return strWhere;
}
//循环处理每个部分
foreach (Model.SqlUnit model in sqlUnitList)
{
if (model.DataType == PLAA.Model.SqlUnitDataType.Symbol)
{
//处理左右括号
strWhere += model.FieldValue1 + " " + model.SplitKeyword + " " ;
}
else
{
switch (model.OprateSymbol)
{
//等于处理
case PLAA.Model.SqlUnitOprateSymbol.Equal:
ICreateSWOFactory operFactory = new SWOEqualeFactory();
CreatSqlWhereOperation oper = operFactory.CreateSWOFactory();
//取得对应的条件部分并加上与下一语句对应的关键字，如果是最后一个splitkeyword 传空格
strWhere += oper.GetWhere(model)+ " "+model.SplitKeyword +" ";
break;
//大于处理
case PLAA.Model.SqlUnitOprateSymbol.GT:
ICreateSWOFactory operFactory2 = new SWOGTFactory();
CreatSqlWhereOperation oper2 = operFactory2.CreateSWOFactory();
strWhere += oper2.GetWhere(model) + " " + model.SplitKeyword + " ";
break;
//大于等于
case PLAA.Model.SqlUnitOprateSymbol.GTandEqual:
ICreateSWOFactory operFactory3 = new SWOGTandEqualeFactory();
CreatSqlWhereOperation oper3 = operFactory3.CreateSWOFactory();
strWhere += oper3.GetWhere(model) + " " + model.SplitKeyword + " ";
break;
//小于
case PLAA.Model.SqlUnitOprateSymbol.LT:
ICreateSWOFactory operFactory4 = new SWOLTFactory();
CreatSqlWhereOperation oper4 = operFactory4.CreateSWOFactory();
strWhere += oper4.GetWhere(model) + " " + model.SplitKeyword + " ";
break;
//小于等于
case PLAA.Model.SqlUnitOprateSymbol.LTandEqual:
ICreateSWOFactory operFactory5 = new SWOLTandEqualeFactory();
CreatSqlWhereOperation oper5 = operFactory5.CreateSWOFactory();
strWhere += oper5.GetWhere(model) + " " + model.SplitKeyword + " ";
break;
//between
case PLAA.Model.SqlUnitOprateSymbol.Between:
ICreateSWOFactory operFactory6 = new SWOBetweenFactory();
CreatSqlWhereOperation oper6 = operFactory6.CreateSWOFactory();
strWhere += oper6.GetWhere(model) + " " + model.SplitKeyword + " ";
break;
//in
case PLAA.Model.SqlUnitOprateSymbol.In:
ICreateSWOFactory operFactory7 = new SWOInFactory();
CreatSqlWhereOperation oper7 = operFactory7.CreateSWOFactory();
strWhere += oper7.GetWhere(model) + " " + model.SplitKeyword + " ";
break;
//like
case PLAA.Model.SqlUnitOprateSymbol.Like:
ICreateSWOFactory operFactory8 = new SWOLikeFactory();
CreatSqlWhereOperation oper8 = operFactory8.CreateSWOFactory();
strWhere += oper8.GetWhere(model) + " " + model.SplitKeyword + " ";
break;
}
}
}
return strWhere;
}
}
/// <summary>
/// 生成各部分的抽象类
/// </summary>
public abstract class CreatSqlWhereOperation
{
//private Model.SqlUnit model;
public abstract string GetWhere(Model.SqlUnit model);
}
public class CreatSqlWhereOperationEquale : CreatSqlWhereOperation
{
public override string GetWhere(Model.SqlUnit model)
{
string strWhere = "";
if (model.DataType == PLAA.Model.SqlUnitDataType.Int)
{
strWhere = model.FieldName + "=" + model.FieldValue1;
}
else
{
strWhere = model.FieldName + " = '" + model.FieldValue1+"' ";
}
return strWhere;
}
}
public class CreatSqlWhereOperationLT : CreatSqlWhereOperation
{
public override string GetWhere(Model.SqlUnit model)
{
string strWhere = "";
if (model.DataType == PLAA.Model.SqlUnitDataType.Int)
{
strWhere = model.FieldName + "<" + model.FieldValue1;
}
else
{
strWhere = model.FieldName + " < '" + model.FieldValue1 + "' ";
}
return strWhere;
}
}
public class CreatSqlWhereOperationLTandEquale : CreatSqlWhereOperation
{
public override string GetWhere(Model.SqlUnit model)
{
string strWhere = "";
if (model.DataType == PLAA.Model.SqlUnitDataType.Int)
{
strWhere = model.FieldName + " <= " + model.FieldValue1;
}
else
{
strWhere = model.FieldName + " <= '" + model.FieldValue1 + "' ";
}
return strWhere;
}
}
public class CreatSqlWhereOperationGT : CreatSqlWhereOperation
{
public override string GetWhere(Model.SqlUnit model)
{
string strWhere = "";
if (model.DataType == PLAA.Model.SqlUnitDataType.Int)
{
strWhere = model.FieldName + " > " + model.FieldValue1;
}
else
{
strWhere = model.FieldName + " > '" + model.FieldValue1 + "' ";
}
return strWhere;
}
}
public class CreatSqlWhereOperationGTandEquale : CreatSqlWhereOperation
{
public override string GetWhere(Model.SqlUnit model)
{
string strWhere = "";
if (model.DataType == PLAA.Model.SqlUnitDataType.Int)
{
strWhere = model.FieldName + " >= " + model.FieldValue1;
}
else
{
strWhere = model.FieldName + " >= '" + model.FieldValue1 + "' ";
}
return strWhere;
}
}
public class CreatSqlWhereOperationBetween : CreatSqlWhereOperation
{
public override string GetWhere(Model.SqlUnit model)
{
string strWhere = "";
if (model.DataType == PLAA.Model.SqlUnitDataType.Int)
{
strWhere = model.FieldName + " Between " + model.FieldValue1 + " And "+ model.FieldValue2+"";
}
else
{
strWhere = model.FieldName + " Between '" + model.FieldValue1 + "' And '" + model.FieldValue2 + "'";
}
return strWhere;
}
}
public class CreatSqlWhereOperationIn : CreatSqlWhereOperation
{
public override string GetWhere(Model.SqlUnit model)
{
string strWhere = "";
if (model.DataType == PLAA.Model.SqlUnitDataType.Int)
{
strWhere = model.FieldName + " In (" + model.FieldValue1 +")";
}
else
{
//需要的单引号由外界组好
strWhere = model.FieldName + " In (" + model.FieldValue1 + ")";
}
return strWhere;
}
}
public class CreatSqlWhereOperationLike : CreatSqlWhereOperation
{
public override string GetWhere(Model.SqlUnit model)
{
string strWhere = "";
if (model.DataType == PLAA.Model.SqlUnitDataType.Int)
{
strWhere = model.FieldName + " Like '%" + model.FieldValue1 + "%'";
}
else
{
//需要的单引号由外界组好
strWhere = model.FieldName + " Like '%" + model.FieldValue1 + "%'";
}
return strWhere;
}
}
/// <summary>
/// 工厂接口
/// </summary>
public interface ICreateSWOFactory
{
CreatSqlWhereOperation CreateSWOFactory();
}
public class SWOEqualeFactory : ICreateSWOFactory
{
public CreatSqlWhereOperation CreateSWOFactory()
{
return new CreatSqlWhereOperationEquale();
}
}
public class SWOGTFactory : ICreateSWOFactory
{
public CreatSqlWhereOperation CreateSWOFactory()
{
return new CreatSqlWhereOperationGT();
}
}
public class SWOGTandEqualeFactory : ICreateSWOFactory
{
public CreatSqlWhereOperation CreateSWOFactory()
{
return new CreatSqlWhereOperationGTandEquale();
}
}
public class SWOLTFactory : ICreateSWOFactory
{
public CreatSqlWhereOperation CreateSWOFactory()
{
return new CreatSqlWhereOperationLT();
}
}
public class SWOLTandEqualeFactory : ICreateSWOFactory
{
public CreatSqlWhereOperation CreateSWOFactory()
{
return new CreatSqlWhereOperationLTandEquale();
}
}
public class SWOBetweenFactory : ICreateSWOFactory
{
public CreatSqlWhereOperation CreateSWOFactory()
{
return new CreatSqlWhereOperationBetween();
}
}
public class SWOInFactory : ICreateSWOFactory
{
public CreatSqlWhereOperation CreateSWOFactory()
{
return new CreatSqlWhereOperationIn();
}
}
public class SWOLikeFactory : ICreateSWOFactory
{
public CreatSqlWhereOperation CreateSWOFactory()
{
return new CreatSqlWhereOperationLike();
}
}
}
224
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
