自动智能拼装生成常用sql语句代码,C#代码库,德仔网

代码语言
代码分类
【C#】自动智能拼装生成常用sql语句
作者:笑红 / 发布于2016/6/29/ 652
该类用于处理一般常用的sql 语句封装，至于用到数据库系统函数等的这个类还不支持，下面是以MySQL 为例做的，同时也支持sql server ，如果是access或其它数据库只要修改实现的部分即可
---测试用例---
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();
        }
    }

}
评论列表
本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。
试试其它关键字
同语言下
可能有用的
笑红贡献的其它代码(14)