IT民工——新年到，送大家一份好礼！一起玩转.net 2.0的语法糖！

前言

本文分析了.net 2.0的一些语法特点，尝试写一个一个拼SQL的简单引擎。为各位看官提供些思路。

这个拼SQL的引擎主要使用了链式编程，当然还有一些.NET特有的语法糖。 

 

正文

最近在重构系统，写SQL写的很烦，特别对于很长的SQL，即使我很小心去写，可是心里还是没有底，担心各种拼接错误。

而这些错误是不能在编译中发现的，于是我想起了以前做过的链式编程，尝试了一下一个简单的拼SQL引擎。

 

首先介绍一下.NET的语法糖“this” 关键字。使用this关键字，能够直接用中括号去调用方法，而不需要声明方法体，例如：

using System;
using System.Collections.Generic;
using System.Text;

namespace Pixysoft
{
    class Class1
    {
        public void test()
        {
            Console.WriteLine(new HelloWorld()["hello"]);
        }
    }

    public class HelloWorld
    {
        public string this[string name]
        {
            get  {   return name;  }
        }
    }
}

 

这么简单的东西我就不解释了。 如果我稍稍改变一下 this的返回值，那么就变成了一下形式：

    public class HelloWorld
    {
        public HelloWorld this[int name]
        {
            get
            {
                return new HelloWorld();
            }
        }
    }

    public class TestCase
    {
        public void test()
        {
            Console.WriteLine(new HelloWorld()[1][2][3][4][5][6][7][8].ToString());
        }
    }

 

这个就是一个有趣的语法糖。我通过灵活使用this和属性，就能够构造出一个简单的SQL引擎。

 

构造前，需要先温习一下SQL的词法分析，详情可以看一下链接：

http://www.h2database.com/html/grammar.html

 

这个应该是标准的SQL的有限状态机了，最后一点我没有帖全。（不过对于部分细节，例如GROUP BY expression有点疑问） 。

我就根据这个SQL的语法+状态机原理+链式编程，构造出一个简单的SQL拼接引擎。由于是DEMO阶段，暂时不支持复杂的SQL。

 

分析过程比较简单，就是分析当前的状态+转移到下一个状态+状态转移的关键字是什么。

 

源码最后放出，先看看效果：

        public void test()
        {
            //SELECT USERCODE FROM USR_PROFILE WHERE CREATEDATE = 2010 AND USRBOXCODE IN  SELECT USRBOXCODE FROM USR_BOX WHERE CREATEDATE = 2010 

            SqlCreator.Select["USERCODE"].From["USR_PROFILE"].Where["CREATEDATE"].Equal["2010"].And["USRBOXCODE"].InSelect["USRBOXCODE"].From["USR_BOX"].Where["CREATEDATE"].Equal["2010"].GetResult();
        }

 

 可以看到基本上和平时写SQL非常接近了。

 一个INNER JOIN的例子：

        public void test()
        {
            //SELECT USERCODE FROM USR_PROFILE INNER JOIN USR_BOX ON USRBOXCODE = USRBOXCODE WHERE CREATEDATE = 2010 

            SqlCreator.Select["USERCODE"].From["USR_PROFILE"].InnerJoin["USR_BOX"].On["USRBOXCODE"].Equal["USRBOXCODE"].Where["CREATEDATE"].Equal["2010"].GetResult();
        }

 

 

最后，我再利用代码生成器，把数据库的表结构全部转化为对象+字符串，那么拼SQL就不会错了。

 

后续

最后，各位看官别急着CTRL+C / CTRL+V。 我给出的代码是不能用在实际项目中的，不过您只要稍微修改一下，就可以了。比如对this的传入参数动动、对括号问题改改，就完成了最终成品了。

 

当然，如果您需要一个完整的版本，不想费脑子，那么请去我的淘宝拍个1元项目就可以了。拍的时候留下您的EMAIL，我就发给您。哈哈，新年过年就算是封个利是给我吧！

 

完整商业版源码：http://www.boxcn.net/shared/ol26ct9e10

祝大家虎年财运亨通！ 

 

源码在这里：

 

using System;
using System.Collections.Generic;
using System.Text;
//using Pixysoft.SQL.Core;

namespace Pixysoft.SQL
{
    public class SqlCreator
    {
        public static SqlSelect Select
        {
            get
            {
                return new SqlSelect();
            }
        }
    }

    public class SqlSelect
    {
        StringBuilder builder = new StringBuilder();

        public SqlSelect()
        {
        }

        public SqlSelect(StringBuilder builder)
        {
            this.builder = builder;
        }

        public SqlSelectExpression this[string name]
        {
            get
            {
                builder.Append("SELECT ");

                builder.Append(name);

                builder.Append(" ");

                return new SqlSelectExpression(builder);
            }
        }
    }

    public class SqlSelectExpression
    {
        StringBuilder builder = new StringBuilder();

        public SqlSelectExpression(StringBuilder builder)
        {
            this.builder = builder;
        }

        public SqlSelectExpression this[string name]
        {
            get
            {
                builder.Append(name);

                builder.Append(", ");

                return new SqlSelectExpression(builder);
            }
        }

        public SqlTableExpressionStart From
        {
            get
            {
                builder.Append("FROM ");

                return new SqlTableExpressionStart(builder);
            }
        }
    }

    public class SqlTableExpressionStart
    {
        StringBuilder builder = new StringBuilder();

        public SqlTableExpressionStart(StringBuilder builder)
        {
            this.builder = builder;
        }

        public SqlTableExpression this[string name]
        {
            get
            {
                builder.Append(name);

                builder.Append(" ");

                return new SqlTableExpression(builder);
            }
        }
    }

    public class SqlTableExpression
    {
        StringBuilder builder = new StringBuilder();

        public SqlTableExpression(StringBuilder builder)
        {
            this.builder = builder;
        }

        public SqlTableExpressionStart InnerJoin
        {
            get
            {
                builder.Append("INNER JOIN ");

                return new SqlTableExpressionStart(builder);
            }
        }

        public SqlMidExpressionStart On
        {
            get
            {
                builder.Append("ON ");

                return new SqlMidExpressionStart(builder);
            }
        }

        public SqlExpressionStart Where
        {
            get
            {
                builder.Append("WHERE ");

                return new SqlExpressionStart(builder);
            }
        }
    }

    public class SqlMidExpressionStart
    {
        StringBuilder builder = new StringBuilder();

        public SqlMidExpressionStart(StringBuilder builder)
        {
            this.builder = builder;
        }

        public SqlMidExpression this[string name]
        {
            get
            {
                builder.Append(name);

                builder.Append(" ");

                return new SqlMidExpression(builder);
            }
        }
    }

    public class SqlMidExpression
    {
        StringBuilder builder = new StringBuilder();

        public SqlMidExpression(StringBuilder builder)
        {
            this.builder = builder;
        }

        public SqlMidExpressionRightStart Equal
        {
            get
            {
                builder.Append("= ");

                return new SqlMidExpressionRightStart(builder);
            }
        }
    }

    public class SqlMidExpressionRightStart
    {
        StringBuilder builder = new StringBuilder();

        public SqlMidExpressionRightStart(StringBuilder builder)
        {
            this.builder = builder;
        }

        public SqlMidExpressionRight this[string name]
        {
            get
            {
                builder.Append(name);

                builder.Append(" ");

                return new SqlMidExpressionRight(builder);
            }
        }
    }

    public class SqlMidExpressionRight
    {
        StringBuilder builder = new StringBuilder();

        public SqlMidExpressionRight(StringBuilder builder)
        {
            this.builder = builder;
        }

        public SqlMidExpressionStart And
        {
            get
            {
                builder.Append("AND");

                builder.Append(" ");

                return new SqlMidExpressionStart(builder);
            }
        }

        public SqlMidExpressionStart Or
        {
            get
            {
                builder.Append("OR");

                builder.Append(" ");

                return new SqlMidExpressionStart(builder);
            }
        }

        public SqlTableExpressionStart InnerJoin
        {
            get
            {
                builder.Append("INNER JOIN");

                builder.Append(" ");

                return new SqlTableExpressionStart(builder);
            }
        }

        public SqlExpressionStart Where
        {
            get
            {
                builder.Append("WHERE");

                builder.Append(" ");

                return new SqlExpressionStart(builder);
            }
        }
    }

    public class SqlExpressionStart
    {
        StringBuilder builder = new StringBuilder();

        public SqlExpressionStart(StringBuilder builder)
        {
            this.builder = builder;
        }

        public SqlExpression this[string name]
        {
            get
            {
                builder.Append(name);

                builder.Append(" ");

                return new SqlExpression(builder);
            }
        }
    }

    public class SqlExpression
    {
        StringBuilder builder = new StringBuilder();

        public SqlExpression(StringBuilder builder)
        {
            this.builder = builder;
        }

        public SqlSelect InSelect
        {
            get
            {
                builder.Append("IN ");

                builder.Append(" ");

                return new SqlSelect(builder);
            }
        }

        public SqlExpressionRightStart Equal
        {
            get
            {
                builder.Append("=");

                builder.Append(" ");

                return new SqlExpressionRightStart(builder);
            }
        }
    }

    public class SqlExpressionRightStart
    {
        StringBuilder builder = new StringBuilder();

        public SqlExpressionRightStart(StringBuilder builder)
        {
            this.builder = builder;
        }

        public SqlExpressionRight this[string name]
        {
            get
            {
                builder.Append(name);

                builder.Append(" ");

                return new SqlExpressionRight(builder);
            }
        }
    }

    public class SqlExpressionRight
    {
        StringBuilder builder = new StringBuilder();

        public SqlExpressionRight(StringBuilder builder)
        {
            this.builder = builder;
        }

        public SqlExpressionStart And
        {
            get
            {
                builder.Append("AND");

                builder.Append(" ");

                return new SqlExpressionStart(builder);
            }
        }

        public SqlExpressionStart Or
        {
            get
            {
                builder.Append("OR");

                builder.Append(" ");

                return new SqlExpressionStart(builder);
            }
        }

        public void GetResult()
        {
            Console.WriteLine(builder.ToString());
        }
    }
}