综合应用WPF/WCF/WF/LINQ之二十七：代码生成器之DBMLToProcedure

本存储过程代码生成器能处理如下几种形式的存储过程：
　　1、形如InsertTableName的存储过程，用于插入某条记录。
　　2、形如UpdateTableNameByFieldName的存储过程，用于根据某个字段更新某条记录。
　　3、形如DeleteTableNameByFieldName的存储过程，用于根据某个字段删除某条记录。
　　4、形如GetTableNames的存储过程，用于查询所有记录。
　　5、形如GetTableNamesByFieldName的存储过程，用于根据某个字段查询记录。
　　6、形如GetTableNamesByPage的存储过程，用于根据页码查询记录。
　　7、形如GetTableNamesByFilter的存储过程，用于根据过滤条件、页码查询记录。
　　在存储过程没有过多业务逻辑的情况下，一般上述集中形式的存储过程即能满足一般系统的需要。如果您需要支持更多的形式，请自行修改代码。
　　各种形式的存储过程举例如下：
　　1、形如InsertTableName的存储过程。对该存储过程，我们主要需要获取该表的字段名、类型和长度等。在以前，我们需要通过读取数据的Schema的方式来获得字段的这些属性，现在有了LINQ，我就可以直接从DBML文件中获取了。

    1  IF EXISTS ( SELECT *  FROM sysobjects  WHERE type =  'P'  AND name =  'InsertFunction')

    2      BEGIN

    3          DROP Procedure [InsertFunction]

    4      END

    5 

    6 GO

    7 

    8  CREATE Procedure [InsertFunction]

    9     (

   10         @FunctionId  Int =  NULL,

   11         @FunctionName  NVarChar(50) =  NULL,

   12         @FunctionDescription  NVarChar(255) =  NULL

   13     )

   14 

   15  AS

   16 

   17      BEGIN

   18          INSERT INTO [Function] ([FunctionId],

   19                                 [FunctionName],

   20                                 [FunctionDescription])

   21          VALUES (@FunctionId,

   22                 @FunctionName,

   23                 @FunctionDescription)

   24      END

   25 

   26 GO

　　获取了足够的信息后，这个存储过程的拼凑就可以完成了。主体拼凑代码如下，其中24和16用于生成空格，以便看到上面每个字段分一行的整齐代码。

    1         strContentes +=  string.Format( "        INSERT INTO [{0}] ({1})" +  "\n", table, strColumns.GetColumns(24 + table.Length));

    2         strContentes +=  string.Format( "        VALUES ({0})" +  "\n", strColumns.GetVariables(16));

　　2、形如GetTableNamesByFilter的存储过程。在有很多可选过滤条件的情况下，我们不可能为每一种组合创建一个存储过程，这时，我们可以采用拼凑Where后面的SQL语句的方式实现查询。这时，我们除了跟上一个存储过程一样获取字段信息外，还需要处理翻页、拼凑SQL语句等。我们可以采用嵌套Select语句的方式实现拼凑和翻页。

    1  IF EXISTS ( SELECT *  FROM sysobjects  WHERE type =  'P'  AND name =  'GetFunctionsByFilter')

    2      BEGIN

    3          DROP Procedure [GetFunctionsByFilter]

    4      END

    5 

    6 GO

    7 

    8  CREATE Procedure [GetFunctionsByFilter]

    9     (

   10         @FunctionName  NVarChar(50) =  NULL,

   11         @PageSize  Int =  NULL,

   12         @PageIndex  Int =  NULL,

   13         @RecordCount  Int =  NULL OUTPUT

   14     )

   15 

   16  AS

   17 

   18      BEGIN

   19          DECLARE @MinIndex  Int

   20          DECLARE @MaxIndex  Int

   21          SET @MinIndex = (@PageIndex - 1) * @PageSize + 1

   22          SET @MaxIndex = @MinIndex + @PageSize - 1

   23 

   24          DECLARE @Where  NVarChar( MAX)

   25          SET @Where =  '0 = 0'

   26          IF @FunctionName  IS NOT NULL

   27              SET @Where = @Where +  ' AND [Function].[FunctionName] LIKE ''%' + @FunctionName +  '%'''

   28 

   29          DECLARE @Record  NVarChar( MAX)

   30          SET @Record =  'SELECT ROW_NUMBER() OVER(ORDER BY [Function].[FunctionId]) AS [Index],

   31                                [Function].[FunctionId],

   32                                [Function].[FunctionName],

   33                                [Function].[FunctionDescription]

   34                         FROM [Function]

   35                         WHERE' +  ' ' + @Where

   36 

   37          DECLARE @Sql  NVarChar( MAX)

   38          SET @Sql =  'SELECT @RecordCount = COUNT(*)

   39                      FROM (' + @Record +  ') DERIVEDTBL

   40 

   41                      SELECT [FunctionId],

   42                             [FunctionName],

   43                             [FunctionDescription]

   44                      FROM (' + @Record +  ') DERIVEDTBL

   45                      WHERE [Index] >= @MinIndex AND [Index] <= @MaxIndex'

   46 

   47          DECLARE @Parameter  NVarChar( MAX)

   48          SET @Parameter =  '@MinIndex Int, @MaxIndex Int, @RecordCount Int OUTPUT'

   49 

   50          EXEC sp_executesql @Sql, @Parameter, @MinIndex, @MaxIndex, @RecordCount  OUTPUT

   51      END

   52 

   53 GO

　　获取了足够的信息后，这个存储过程的拼凑就可以完成了。主体拼凑代码如下。值得注意的是，当可选参数为String类型时，您需要处理脚本注入的问题（笔者这里并未处理）。

    1     strContentes = strContentes +  "        DECLARE @MinIndex Int" +  "\n";

    2     strContentes = strContentes +  "        DECLARE @MaxIndex Int" +  "\n";

    3     strContentes = strContentes +  "        SET @MinIndex = (@PageIndex - 1) * @PageSize + 1" +  "\n";

    4     strContentes = strContentes +  "        SET @MaxIndex = @MinIndex + @PageSize - 1" +  "\n";

    5     strContentes = strContentes +  "        " +  "\n";

    6     strContentes = strContentes +  "        DECLARE @Where NVarChar(MAX)" +  "\n";

    7     strContentes = strContentes +  "        SET @Where = '0 = 0'" +  "\n";

    8 

    9      for ( int i = 0; i < method.GetParameters().Length - 3; i++)

   10     {

   11         strContentes +=  string.Format( "        IF @{0} IS NOT NULL" +  "\n", method.GetParameters()[i].GetName());

   12 

   13          if (method.GetParameters()[i].ParameterType.ToSqlDbType().ToString() ==  "NVarChar")

   14         {

   15             strContentes +=  string.Format( "            SET @Where = @Where + ' AND [{0}].[{1}] LIKE ''%' + @{2} + '%'''" +  "\n", table, method.GetParameters()[i].GetName(), method.GetParameters()[i].GetName());

   16         }

   17          else  if (method.GetParameters()[i].ParameterType.ToSqlDbType().ToString() ==  "DateTime" && method.GetParameters()[i].GetName().StartsWith( "Begin") ==  true)

   18         {

   19             strContentes +=  string.Format( "            SET @Where = @Where + ' AND [{0}].[{1}] >= ''' + CONVERT(NVarChar, @{2}, 101) + ' ' + '00:00:00' + ''''" +  "\n", table, method.GetParameters()[i].GetName().Substring(5), method.GetParameters()[i].GetName());

   20         }

   21          else  if (method.GetParameters()[i].ParameterType.ToSqlDbType().ToString() ==  "DateTime" && method.GetParameters()[i].GetName().StartsWith( "End") ==  true)

   22         {

   23             strContentes +=  string.Format( "            SET @Where = @Where + ' AND [{0}].[{1}] <= ''' + CONVERT(NVarChar, @{2}, 101) + ' ' + '23:59:59' + ''''" +  "\n", table, method.GetParameters()[i].GetName().Substring(3), method.GetParameters()[i].GetName());

   24         }

   25          else

   26         {

   27             strContentes +=  string.Format( "            SET @Where = @Where + ' AND [{0}].[{1}] = ''' + CONVERT(NVarChar, @{2}) + ''''" +  "\n", table, method.GetParameters()[i].GetName(), method.GetParameters()[i].GetName());

   28         }

   29     }

   30 

   31     strContentes = strContentes +  "        " +  "\n";

   32     strContentes = strContentes +  "        DECLARE @Record NVarChar(MAX)" +  "\n";

   33     strContentes +=  string.Format( "        SET @Record = 'SELECT ROW_NUMBER() OVER(ORDER BY [{0}].[{1}]) AS [Index]," +  "\n", table, strKey);

   34     strContentes +=  string.Format( "                              {0}" +  "\n", strColumns.GetColumns(table, infos, 30,  true));

   35     strContentes +=  string.Format( "                       FROM {0}" +  "\n", infos.GetFroms(table, 23));

   36     strContentes = strContentes +  "                       WHERE' + ' ' + @Where" +  "\n";

   37     strContentes = strContentes +  "        " +  "\n";

   38     strContentes = strContentes +  "        DECLARE @Sql NVarChar(MAX)" +  "\n";

   39     strContentes = strContentes +  "        SET @Sql = 'SELECT @RecordCount = COUNT(*)" +  "\n";

   40     strContentes = strContentes +  "                    FROM (' + @Record + ') DERIVEDTBL" +  "\n";

   41     strContentes = strContentes +  "                    " +  "\n";

   42     strContentes +=  string.Format( "                    SELECT {0}" +  "\n", strColumns.GetColumns(table, infos, 27,  false));

   43     strContentes = strContentes +  "                    FROM (' + @Record + ') DERIVEDTBL" +  "\n";

   44     strContentes = strContentes +  "                    WHERE [Index] >= @MinIndex AND [Index] <= @MaxIndex'" +  "\n";

   45     strContentes = strContentes +  "        " +  "\n";

   46     strContentes = strContentes +  "        DECLARE @Parameter NVarChar(MAX)" +  "\n";

   47     strContentes = strContentes +  "        SET @Parameter = '@MinIndex Int, @MaxIndex Int, @RecordCount Int OUTPUT'" +  "\n";

   48     strContentes = strContentes +  "        " +  "\n";

   49     strContentes = strContentes +  "        EXEC sp_executesql @Sql, @Parameter, @MinIndex, @MaxIndex, @RecordCount OUTPUT" +  "\n";

　　其它类型的存储过程我就不一一列举。
　　在开始使用这个代码生成器之前，请先手工完成Eallies.OA.DAL.Interface这个项目，这个项目包含所有的DAL层需要实现的接口。这个代码生成将根据这个接口生成相应名称、参数等的存储过程。
　　本存储过程代码生成器的调用方法请参考Eallies.OA.Generator目录下的GenerateProcedure.bat文件。

本文转自 Eallies 51CTO博客，原文链接：http://blog.51cto.com/eallies/79013，如需转载请自行联系原作者