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());
        }
    }
}

 

 

转载于:https://www.cnblogs.com/zc22/archive/2010/02/16/1668658.html