PetaPoco框架下，模拟EF的sql语法方法

最新推荐文章于 2024-11-26 19:37:15 发布

原创最新推荐文章于 2024-11-26 19:37:15 发布 · 514 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#PetePoco 拼接SQL

C# 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了在PetaPoco框架中，由于SQL语句直接暴露，作者通过封装SQL语句来提高代码维护性和可读性，避免手误。分享了一种封装SQL的方法，包括定义接口、实现接口以及为每个表创建包含表名和字段的文件，以简化SQL查询的编写。示例仅展示了简单查询的拼接，读者可根据需求扩展。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

最近做个小项目，朋友推荐用PetePoco框架，这个框架里面的SQL语句是直接暴露出来的，没有封装，个人觉得后期不好维护，也在开发的过程中容易手误写错字段名称，开发过程中也很难记得全部表表名和字段名称，所以就想到弄个封装SQL语句的类。
直接看结果：

string sql = s.From(Damo_ProType.TableName).Select().AndWhere(Damo_ProType.IsDel,"!=",99).AndWhere(Damo_ProType.UID, UID).OderBy(Damo_ProType.Ord, "desc").ToString();

如此封装之后，后期代码维护可以减少一点，代码的可读性也高点，也不容易手误写错一个字段名称啊之类的。
直接贴封装的代码，代码是网上找的自己改了改，写在这里是为了方便我自己后期使用的同时，也方便大家。

先见一个拼接Sql的接口文件：

public interface IsqlCode
    {
        /// <summary>
        /// From 视图  表名
        /// </summary>
        /// <param name="Object"></param>
        /// <returns></returns>
        IsqlCode From(object Object);

        /// <summary>
        /// 查询字段 默认为*
        /// </summary>
        /// <param name="Fields"></param>
        /// <returns></returns>
        IsqlCode Select(string Fields = "*");

        /// <summary>
        /// Top语句
        /// </summary>
        /// <param name="TopNum"></param>
        /// <returns></returns>
        IsqlCode Top(int TopNum);


        /// <summary>
        /// 求和
        /// </summary>
        /// <param name="Filed"></param>
        /// <returns></returns>
        IsqlCode SumNumber(string Filed);

        /// <summary>
        /// 求平均数
        /// </summary>
        /// <param name="Filed"></param>
        /// <returns></returns>
        IsqlCode AvgNumber(string Fields);

        /// <summary>
        /// 最大数
        /// </summary>
        /// <param name="Fields"></param>
        /// <returns></returns>
        IsqlCode MaxNumber(string Fields);

        /// <summary>
        /// 最小数
        /// </summary>
        /// <param name="Fields"></param>
        /// <returns></returns>
        IsqlCode MinNumber(string Fields);

        /// <summary>
        /// and的条件查询语句
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <returns></returns>
        IsqlCode AndWhere(string sql);

        /// <summary>
        /// and的等于条件查询
        /// </summary>
        /// <param name="Fields">字段名称</param>
        /// <param name="Value">值</param>
        /// <returns></returns>
        IsqlCode AndWhere(string Fields, object Value);

        /// <summary>
        /// and的非等式查询条件
        /// </summary>
        /// <param name="Fields">字段名称</param>
        /// <param name="Dengshi">大于，小于，大于等于，小于等于</param>
        /// <param name="Value"></param>
        /// <returns></returns>
        IsqlCode AndWhere(string Fields, object Dengshi, object Value);

        /// <summary>
        /// 模糊查询
        /// </summary>
        /// <param name="Fields"></param>
        /// <param name="Value"></param>
        /// <returns></returns>
        IsqlCode AndWLike(string Fields, object Value);

        /// <summary>
        /// 左模糊查询
        /// </summary>
        /// <param name="Fields"></param>
        /// <param name="Value"></param>
        /// <returns></returns>
        IsqlCode AndWLeftLike(string Fields, object Value);

        /// <summary>
        /// 右模糊查询
        /// </summary>
        /// <param name="Fields"></param>
        /// <param name="Value"></param>
        /// <returns></returns>
        IsqlCode AndWRightLike(string Fields, object Value);





        /// <summary>
        /// and的条件查询语句
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <returns></returns>
        IsqlCode OrWhere(string sql);

        /// <summary>
        /// and的等于条件查询
        /// </summary>
        /// <param name="Fields">字段名称</param>
        /// <param name="Value">值</param>
        /// <returns></returns>
        IsqlCode OrWhere(string Fields, object Value);

        /// <summary>
        /// and的非等式查询条件
        /// </summary>
        /// <param name="Fields">字段名称</param>
        /// <param name="Dengshi">大于，小于，大于等于，小于等于</param>
        /// <param name="Value"></param>
        /// <returns></returns>
        IsqlCode OrWhere(string Fields, object Dengshi, object Value);

        /// <summary>
        /// 模糊查询
        /// </summary>
        /// <param name="Fields"></param>
        /// <param name="Value"></param>
        /// <returns></returns>
        IsqlCode OrWLike(string Fields, object Value);

        /// <summary>
        /// 左模糊查询
        /// </summary>
        /// <param name="Fields"></param>
        /// <param name="Value"></param>
        /// <returns></returns>
        IsqlCode OrWLeftLike(string Fields, object Value);

        /// <summary>
        /// 右模糊查询
        /// </summary>
        /// <param name="Fields"></param>
        /// <param name="Value"></param>
        /// <returns></returns>
        IsqlCode OrWRightLike(string Fields, object Value);


        /// <summary>
        /// 排序
        /// </summary>
        /// <param name="Fields"></param>
        /// <param name="Value">asc desc</param>
        /// <returns></returns>
        IsqlCode OderBy(string Fields, object Value);

        string ToString();

        /// <summary>
        /// 清空缓存
        /// </summary>
        /// <returns></returns>
        IsqlCode Clear();

        IsqlCode CreateCode(string sql);


        void Dispose();

    }

然后实现这个接口

public class SqlCode : IsqlCode
    {
        string Object;
        StringBuilder ExcuteString = new StringBuilder();
        static string[] s = { "select", "delect", "update", "insert" };

        public SqlCode() { }
        public IsqlCode From(object Object)
        {
            Type t = Object.GetType();
            if (t.Name.ToLower().Equals("string"))
            {
                this.Object = Object.ToString();
            }
            else
            {
                this.Object = t.Name;
            }

            return this;
        }

        public IsqlCode Select(string Fields)
        {
            if (this.Object.Length <= 0)
                return this;
            if (!Check(0))
                return this;
            ExcuteString.AppendLine("select " + Fields + " from " + this.Object);
            ExcuteString.AppendLine(" where 1 = 1 ");
            return this;
        }

        bool Check(int Type)
        {
            int flag = 0;
            string b = ExcuteString.ToString();
            for (int i = 0; i < s.Length; i++)
                if (i != Type)
                    flag += b.Contains(s[i]) ? 1 : 0;
            return flag == 0;
        }

        public IsqlCode Top(int TopNum)
        {
            if (!ExcuteString.ToString().Contains(s[0]))
                return this;
            ExcuteString.Replace("select", "select top " + TopNum + " ");
            return this;
        }

        public IsqlCode SumNumber(string Filed)
        {
            if (!ExcuteString.ToString().Contains(s[0]))
                return this;
            ExcuteString.Replace("select", "select sum（ " + Filed + " ）as ResultValue");
            return this;
        }

        public IsqlCode AvgNumber(string Fields)
        {
            if (!ExcuteString.ToString().Contains(s[0]))
                return this;
            ExcuteString.Replace("select", "select Avg（ " + Fields + " ）as ResultValue");
            return this;
        }

        public IsqlCode MaxNumber(string Fields)
        {
            if (!ExcuteString.ToString().Contains(s[0]))
                return this;
            ExcuteString.Replace("select", "select Max（ " + Fields + " ）as ResultValue");
            return this;
        }

        public IsqlCode MinNumber(string Fields)
        {
            if (!ExcuteString.ToString().Contains(s[0]))
                return this;
            ExcuteString.Replace("select", "select Min（ " + Fields + " ）as ResultValue");
            return this;
        }

        public IsqlCode AndWhere(string sql)
        {
            ExcuteString.AppendLine(string.Format(" and {0}", sql));
            return this;
        }

        public IsqlCode AndWhere(string Fields, object Value)
        {
            Type t = Value.GetType();
            if (t.Name.ToLower().Equals("string"))
            {
                ExcuteString.AppendLine(string.Format("and {0}='{1}'", Fields, Value));
            }
            else
            {
                ExcuteString.AppendLine(string.Format("and {0}={1}", Fields, Value));
            }

            return this;
        }

        public IsqlCode AndWhere(string Fields, object Dengshi, object Value)
        {
            ExcuteString.AppendLine(string.Format("and {0} {1} '{2}'", Fields, Dengshi, Value));
            return this;
        }

        public IsqlCode AndWLike(string Fields, object Value)
        {
            ExcuteString.AppendLine(string.Format("and {0} like '%{1}%'", Fields, Value));
            return this;
        }

        public IsqlCode AndWLeftLike(string Fields, object Value)
        {
            ExcuteString.AppendLine(string.Format("and {0} like '%{1}'", Fields, Value));
            return this;
        }

        public IsqlCode AndWRightLike(string Fields, object Value)
        {
            ExcuteString.AppendLine(string.Format("and {0} like '{1}%'", Fields, Value));
            return this;
        }

        public IsqlCode OrWhere(string sql)
        {
            ExcuteString.AppendLine(string.Format(" or {0}", sql));
            return this;
        }

        public IsqlCode OrWhere(string Fields, object Value)
        {
            ExcuteString.AppendLine(string.Format("or {0}='{1}'", Fields, Value));
            return this;
        }

        public IsqlCode OrWhere(string Fields, object Dengshi, object Value)
        {
            ExcuteString.AppendLine(string.Format("or {0} {1} '{2}'", Fields, Dengshi, Value));
            return this;
        }

        public IsqlCode OrWLike(string Fields, object Value)
        {
            ExcuteString.AppendLine(string.Format("or {0} like '%{1}%'", Fields, Value));
            return this;
        }

        public IsqlCode OrWLeftLike(string Fields, object Value)
        {
            ExcuteString.AppendLine(string.Format("or {0} like '%{1}'", Fields, Value));
            return this;
        }

        public IsqlCode OrWRightLike(string Fields, object Value)
        {
            ExcuteString.AppendLine(string.Format("or {0} like '{1}%'", Fields, Value));
            return this;
        }

        public IsqlCode OderBy(string Fields, object Order)
        {
            ExcuteString.AppendLine(string.Format(" Order By {0} {1}", Fields, Order));
            return this;
        }

        public override string ToString()
        {
            return this.ExcuteString.ToString();
        }

        public IsqlCode Clear()
        {
            throw new NotImplementedException();
        }

        public IsqlCode CreateCode(string sql)
        {
            throw new NotImplementedException();
        }

        void IsqlCode.Dispose()
        {
            throw new NotImplementedException();
        }
    }

再然后根据自己的需要，为每个表建立一个列出表名及字段的文件

public class Damo_ProType
    {
        public const string TableName = "Pro_Type";
        public const string ID = "ID";
        public const string UID = "UID";
        public const string TName = "TName";
        public const string TRemarks = "TRemarks";
        public const string IsEnable = "IsEnable";
        public const string CengJi = "CengJi";
        public const string Ticon = "Ticon";
        public const string Ord = "Ord";
        public const string IsDel = "IsDel";

    }

再然后表模型，就不列了
如此就可以用开头的的方式，将拼接自己需要的SQL语句了。
这里只列出来简单的SQL查询语句的拼接方法，如有需要可以根据自身的需求扩充。