基于Lambda表达式的SQL语句生成器SqlLink V0.1

一、SQL语句最基础的有四种，增删改查，以T-SQL为例，如下：

INSERT INTO Table(Id,Name) VALUES(1,'Test')
DELETE FROM Table WHERE Id=1
UPDATE Table SET Name='Test2' WHERE Id=1
SELECT * FROM Table

在这里，我们需要做的是将表达式Expression转化为SQL语句，定义SQL语句父类，

public abstract class SqlStatement {
        public string FromClause { set; get; }
        public string WhereClause { set; get; }
        public string FieldClause { set; get; }
        public string TopClause { set; get; }
        public string OrderByClause { set; get; }
        public string GroupByClause { set; get; }
        public string HavingClause { set; get; }
        public string JoinClause { set; get; }
        public string AggregateFunctionClause { set; get; }
        public abstract SqlStatementType SqlStatementType { get; }
        public abstract string ToSql();
    }

定义四种SQL语句类继承上面父类，在方法ToSql生成最终的SQL返回，

public class InsertSqlStatement : SqlStatement {
        public override SqlStatementType SqlStatementType => SqlStatementType.Insert;
        public override string ToSql() {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("INSERT INTO {0}", FromClause);
            sb.AppendFormat("({0})", FieldClause);
            sb.Append(" VALUES ");
            sb.AppendFormat("({0})", "@" + FieldClause.Replace(",", ",@"));
            return sb.ToString();
        }
    }
......

二、Expression表达式解析

可以用.NET提供的ExpressionVisitor处理，也可以自己写函数递归处理

protected virtual void VisitExpression(Expression expr, StringBuilder sb) {
            switch (expr.NodeType) {
                case ExpressionType.Lambda: {
                        LambdaExpression le = expr as LambdaExpression;
                        VisitExpression(le.Body, sb);
                    }
                    break;
                case ExpressionType.Constant: {
                        ConstantExpression ce = expr as ConstantExpression;
                        if (ce.Type.Name == "String")
                            sb.Append("'" + ce.Value.ToString() + "'");
                        else
                            sb.Append(ce.Value.ToString());
                    }
                    break;
                case ExpressionType.Parameter: {
                        ParameterExpression pe = expr as ParameterExpression;
                        sb.Append(pe.Name);
                    }
                    break;
                case ExpressionType.New: {
                        NewExpression ne = expr as NewExpression;
                        for (int i = 0; i < ne.Arguments.Count; i++) {
                            if (i > 0)
                                sb.Append(",");
                            VisitExpression(ne.Arguments[i], sb);
                        }
                    }
                    break;
                case ExpressionType.Convert:
                case ExpressionType.Quote: {
                        UnaryExpression ue = expr as UnaryExpression;
                        VisitExpression(ue.Operand, sb);
                    }
                    break;
............................
}

三、链式方法

用过LINQ的一定对它的链式查询很熟悉，例如：

db.User.Where(u=>u.ID==3).OrderBy(o=>o.ID).Select(f=>new { f.ID, f.Name });

这样定义，方法里返回对象自己：

public class SqlLink<T> {
    public SqlLink<T> Where(Expression<Func<T, bool>> whereExpr)
    {
        return this;
    }
}

SqlLink使用，

using (SqlLink<User> link = new SqlLink<User>()) {
                //增
                SqlCommand comm = link.Insert(f => new { f.UserId, f.UserName, f.DpId }).Build();
                WriteLine(comm);
                //删
                string uname = "小明";
                comm = link.Delete(w => w.DpId == 1 && w.UserName == uname).Where(f => f.Sex).Build();
                WriteLine(comm);
                //改
                int sex = 1;
                comm = link.Update(f => new { f.UserName }).Where(f => f.UserId).Where(w => w.Sex == sex).Build();
                WriteLine(comm);
                //查
                comm = link.Select<Dp, Company>((u, d, c) => new { u.UserId, u.UserName, d.DpName, c.CompanyName })
                    .Join<Dp>((u, d) => u.DpId == d.DpId).Join<Dp, Company>((u, d, c) => d.CompanyId == c.CompanyId)
                    .Where(w => w.UserId == 3).Where<Dp, Company>((u, d, c) => d.DpName.Contains("IT") && c.CompanyName.Contains("科技"))
                    .Top(5)
                    .Build();
                WriteLine(comm);
                //GroupBy
                comm = link.Select<Dp>((u, d) => new { d.DpName }).Count(u => u.UserId)
                    .Join<Dp>((u, d) => u.DpId == d.DpId)
                    .GroupBy<Dp>((u, d) => d.DpId)
                    .OrderBy<Dp>((u, d) => d.DpId)
                    .Build();
                WriteLine(comm);
                //Having
                comm = link.Select<Company>((u, c) => new { c.CompanyId, c.CompanyName }).Count(u => u.UserId)
                    .Join<Dp>((u, d) => u.DpId == d.DpId).Join<Dp, Company>((u, d, c) => d.CompanyId == c.CompanyId)
                    .GroupBy<Company>((u, c) => c.CompanyId)
                    .Having(u => (int)u.Count(u.UserId) > 50)
                    .OrderBy<Company>((u, c) => c.CompanyId)
                    .Build();
                WriteLine(comm);
            }

输出如下

CommandText:INSERT INTO User(UserId,UserName,DpId) VALUES (@UserId,@UserName,@DpId)
Parameters:

CommandText:DELETE FROM User WHERE (DpId=1) AND (UserName=@Para0) AND Sex=@Sex
Parameters:[Para0, 小明]

CommandText:UPDATE User SET UserName=@UserName WHERE UserId=@UserId AND Sex=@Para0
Parameters:[Para0, 1]

CommandText:SELECT TOP 5 t0.UserId,t0.UserName,t1.DpName,t2.CompanyName FROM User t0 INNER JOIN Dp t1 ON t0.DpId=t1.DpId INNER JOIN Company t2 ON t1.CompanyId=t2.CompanyId WHERE t0.UserId=3 AND (t1.DpName LIKE '%'+'IT'+'%') AND (t2.CompanyName LIKE '%'+'科技'+'%')
Parameters:

CommandText:SELECT t1.DpName,COUNT(t0.UserId) FROM User t0 INNER JOIN Dp t1 ON t0.DpId=t1.DpId GEOUP BY t1.DpId ORDER BY t1.DpId
Parameters:

CommandText:SELECT t1.CompanyId,t1.CompanyName,COUNT(t0.UserId) FROM User t0 INNER JOIN Dp t2 ON t0.DpId=t2.DpId INNER JOIN Company t1 ON t2.CompanyId=t1.CompanyId GEOUP BY t1.CompanyId HAVING COUNT(t0.UserId)>50 ORDER BY t1.CompanyId
Parameters:

源码下载,有什么问题、意见、建议、bug欢迎留言交流。