65.9K
CodeProject 正在变化。 阅读更多。
Home

对 NetTiers 代码生成器模板的修改 <br>使生成的搜索存储过程更灵活

matrix_resident
starIconstarIconstarIcon
emptyStarIcon
starIcon
emptyStarIcon

3.94/5 (9投票s)

2006年10月25日

5分钟阅读

viewsIcon

45890

downloadIcon

290

对 nettiers 代码生成器模板的修改 <br>使生成的搜索存储过程更强大（支持 Transact-sql 的 like 操作符），并更智能地搜索 datetime/smalldatetime 数据类型的列

引言

本文的代码是对 nettiers 代码模板的一个非常小的修改，该模板与著名的 Code Smith 代码生成器一起用于自动生成 SQL Server 的数据访问层（创建存储过程）。此修改增加了生成的搜索/查找存储过程的功能，解决了支持“like”操作符的问题，并使该过程能够更智能地搜索 datetime/smalldatetime 数据类型的列。

背景（可选）

我假设您了解 C#、Visual Studio .Net 2005、Sql Server 2005、Transact-sql、Code Smith 代码生成器，并经历过 nettiers 为您生成的搜索/查找存储过程存在的问题。

使用代码

要使用修改后的 nettiers 版本，请执行以下操作
  1. 下载与本文相关的 zip 文件，然后解压。您会发现 nettiers 代码生成器模板中有两个文件夹，其中包含修改后的文件。
  2. 将这些文件复制到您计算机上 nettiers 根文件夹中的相同文件夹。
  3. 使用 Code Smith 代码生成器程序编译 nettiers。
  4. 最后，使用 nettiers 为您生成数据访问层。

就这样……！！现在，您可以享受 Find/Search 存储过程，而无需对其进行任何修改即可支持 Transact-sql 世界中的“like”操作符，还可以智能地搜索 datetime/smalldatetime 数据类型的列……！！

代码背后的思路

作为代码的介绍，我必须说明 nettiers 在 `StoredProceduresXml.cst` 文件中构造包含数据访问层存储过程创建的 SQL 查询文件，该文件位于 nettiers 模板根目录的 `DataAccessLayer.SqlClient` 文件夹下。这是通过一个名为 `CommonSqlCode` 的 C# 辅助类完成的，该类继承自 Code Smith 引擎的 `CodeTemplate` 类。`CommonSqlCode` 类实现在 `CommonSqlCode.cs` 文件中，该文件位于 nettiers 计算机根目录的 `TemplateLib` 文件夹下。所以您可以说，对任何过程的任何修改，nettiers 都会在这些文件周围生成。

现在，为了使生成的 Find/Search 存储过程满足支持 like 操作符的第一个要求，它已被修改为从过程的构造参数中构建一个大的 SQL 语句，然后使用 SQL Server 自带的 `sp_executesql` 内部存储过程来运行它。

对于每个可查找的列（nvarchar、varchar 等），nettiers 会创建一个搜索参数，在修改后的版本中会检查该参数的值是否包含“%”，这意味着使用 like 操作符而不是 = 操作符……！！

以下是使用我的修改版本生成的 Find/Search 过程的一部分，它满足了第一个要求。
注意：这里的代码注释不是 SQL 注释，而是对代码的解释（生成器不会生成它们）
-- this line generated once on top of the procedure 
-- it is the big sql statement holder variable
 declare @sql nvarchar(max) 

--checks the paramter value if it has the '%' that means the query wanna use the like operator
 <% 
                for (int i = 0; i < cols.Count; i++)
                {
                    if(cols[i].NativeType == "datetime" || cols[i].NativeType == "smalldatetime"){
                        Response.Write("\t\t\t\t" + GetSqlParameterXmlNodeForDateTimeColumns(cols[i], cols[i].Name, false, true) + Environment.NewLine);        
                    }else{
                        Response.Write("\t\t\t\t" + GetSqlParameterXmlNode(cols[i], cols[i].Name, false, true) + Environment.NewLine);
                    }
                }
 %><![CDATA[
                <%= GetSetTransactionIsolationLevelStatement() %>
  DECLARE @SQL NVARCHAR(MAX)
  IF ISNULL(@SearchUsingOR, 0) <>if(charindex('%',ParameterName)>=0)
   begin
    set @sql = @sql + 'ParameterName like ' + ParameterValue
   end
  else 
    begin
      @sql = @sql + 'ParameterName = ' + ParameterValue
    end

-- this lines generated once at the buttom of the stored procedure
 exec sp_executesql @sql
 select @@rowcount

对于搜索 datetime/smalldatetime 数据类型列更智能的第二个要求，由我的修改版 nettiers 生成的关于 datetime/smalldatetime 数据类型列的最终存储过程代码可能如下所示

--for each column of datatype datetime or smalldatetime two input paramters generated on top of the stored procedure
-- the type of those paramters is nvarchar cause i will concatinate it to the @sql statement

@columnName_From nvarchar (30) = null,      
@columnName_To nvarchar (30) = null 

-- the [From Date] Parameter Supplied and the [To Date]Parameter not supplied(=null) 
-- means find recored after or equal to that date
if(@columnName_From is not null AND @columnName_To is null)   
    Begin 
          SET @sql = @sql + 'AND([columnName] >=''' + @columnName_From + ''')' 
    End 

-- the [From Date] Parameter not Supplied (=null) and [To Date] Parameter Supplied means 
-- find recoreds before or equal to that date
if(@columnName_From is null AND @columnName_To is not null)     
    Begin 
        SET @sql = @sql + 'AND([columnName] <=''' + @columnName_To +''')' 
    End 

  -- both paramters supplied searching within a period starts from the [From Date] to the [To Date]
if(@columnName_From is not null AND @columnName_To is not null) 
-- the From Date Paramter Supplied means find recored after or equal 
to that date

    Begin 
        SET @sql = @sql + 'AND([columnName] >=''' + @columnName_From + 
          ''' AND [columnName] <=''' + @columnName_To + ''')' 
    End 


-- this lines generated once at the buttom of the stored procedure
 exec sp_executesql @sql
 select @@rowcount
--to have results of exact date u have to supply the same value for the two paramters
我亲爱的读者会注意到连接到 `@sql` 变量的语句前面有一个 AND 操作符……事实上，nettiers 会生成一个名为 `@SearchUsingOR` 的智能参数，它控制搜索结果的限制，这意味着使用 AND 或使用 OR 来构造存储过程。当然，修改后的版本也有这个功能，但我只提供生成的存储过程的这部分代码以求简洁。您可以查看使用修改后的 nettiers 生成的 Find/Search 过程，以了解修改后的模板将做什么的整体情况。

注意：要搜索精确日期，您只需为两个参数提供相同的值……！！！！

代码修改

StoredProcedureXml.cst 后置代码修改

  • Find/Search 存储过程的策略已更改为使用 `sp_executesql` 内部存储过程，这要求对 nettiers 原始版本中某行之后的代码进行大量修改。
    if(IncludeFind){%>
  • 已添加对 datetime/smalldatetime 列的检查，以确定如何构造过程的参数列表。
    这是因为正如我告诉您的关于满足第二个要求一样，对于任何 datetime/smalldatetime 数据类型的列，我们需要两个参数而不是像其他可查找数据类型列那样只需要一个。

代码如下所示

if(cols[i].NativeType == "datetime" || cols[i].NativeType == "smalldatetime"){
      Response.Write("\t\t\t\t" + GetSqlParameterXmlNodeForDateTimeColumns(cols[i], cols[i].Name, false, true) +
      Environment.NewLine);        
}else{
      Response.Write("\t\t\t\t" + GetSqlParameterXmlNode(cols[i], cols[i].Name, false, true) + Environment.NewLine);
}

StoredProcedureXml.cst 文件中使用的任何方法都在 CommonSqlCode.cs 文件中实现。

上面代码中使用的 `GetSqlParameterXmlNodeForDateTimeColumns` 是修改后的 CommonSqlCode.cs 文件中 `GetSqlParameterXmlNode` 方法的新重载……！！

CommonSqlCode.cs 修改

* 添加了一个名为 `GetSqlParameterXmlNodeForDateTimeColumns` 的新方法，用于生成每个 datetime 数据类型列所需的等于参数的行。

/// Get a SqlParameter statements for The DateTime AND smalldatetime DataType column
/// </summary>
/// <param name= column >Column for which to get the Sql parameter statements</param>
/// <param name= parameterName >the name of the parameter?</param>
/// <param name= isOutput >indicates the direction</param>
/// <param name = nullDefaults >indicates whether to give each parameter a null or empty default.
///  (used mainly for Find sp's)</param>
/// <returns>the xml Sql Parameter statement</returns>
public string GetSqlParameterXmlNodeForDateTimeColumns(ColumnSchema column, 
                           string parameterName, bool isOutput, bool nullDefaults)
{            
string formater = "<parameter name=\"@{0}_From\"  type=\"{1}\"  direction=\"{2}\"  size=\"30\" 
 precision=\"1\"  scale=\"0\"  param=\" 30\"  nulldefault=\" {3}\" /> ;                        
formater += Environment.NewLine +  <parameter name=\" @{0}_To\"  type=\" {1}\"  
direction=\" {2}\"  size=\" 30\"  precision=\" 1\"  scale=\" 0\"  param=\" (30)\"  nulldefault=\ "{3}\" /> ;    
tring nullDefaultValue =   ;
if (nullDefaults)
{
nullDefaultValue =  null ;
}

bool isReal = false;
if (column.NativeType.ToLower() ==  "real") // SQL doesn't like precision or scale on Real
{
isReal = true;
}

return string.Format(formater, GetPropertyName(parameterName), "nvarchar"  ,  "Input" , 
nullDefaultValue,GetSqlParameterParam(column));
}

* 添加了另一个名为 `GetFProcedureLineOfColumn` 的新方法，该方法负责为 Find/Search 存储过程的每个输入参数逐行生成过程。
public string GetFProcedureLineOfColumn(ColumnSchema column,string ProcText,string AND_OR){
 if(column.NativeType.IndexOf("int") >=0 ){ 
     ProcText += string.Format("\tif(@{0} is not null){1}\tBegin{1}" ,GetPropertyName(column.Name), 
                 Environment.NewLine); 
     ProcText += "\t\t\tSET @sql = @sql +" + string.Format("\t'{2}([{0}] = ''' + 
                 convert(nvarchar(255),@{0}) + ''')'{1}",GetPropertyName(column.Name), 
                 Environment.NewLine,AND_OR)+ Environment.NewLine; 
     ProcText += "\tEnd" + Environment.NewLine;
      return ProcText; 
 } 
 if(column.NativeType == "uniqueidentifier" ){ 
     ProcText += string.Format("\tif(@{0} is not null){1}\tBegin{1}" ,GetPropertyName(column.Name), 
             Environment.NewLine); 
     ProcText += "\t\t\tSET @sql = @sql +" + string.Format("\t'{2}(Convert(char(255),[{0}]) = ''' + 
            Convert(char(255),@{0}) + ''')'{1}" ,GetPropertyName(column.Name),Environment.NewLine,AND_OR) +
            Environment.NewLine; ProcText += "\tEnd" + Environment.NewLine; 
            return ProcText;
 } if(column.NativeType == "xml"){ 
    ProcText += string.Format("\tif(@{0} is not null){1}\tBegin{1}" ,GetPropertyName(column.Name), 
                 Environment.NewLine); 
    ProcText += Environment.NewLine; 
    ProcText += "\tif (charindex('%',convert(varchar(max),@" + GetPropertyName(column.Name) + "))>0)" + 
                 Environment.NewLine; ProcText += "\t\tBegin" + Environment.NewLine; 
    ProcText += "\t\t\tSET @sql = @sql +" + string.Format("\t'{2}([{1}] Like ''' + 
                 convert(nvarchar(max),@{1}) + ''')'{0}", Environment.NewLine, GetPropertyName(column.Name),AND_OR) + 
                 Environment.NewLine ; ProcText += "\t\tEnd" + Environment.NewLine; 
    ProcText += "\tElse" + Environment.NewLine; 
    ProcText += "\t\tBegin" + Environment.NewLine; 
    ProcText += "\t\t\tSET @sql = @sql +" + string.Format("\t'{2}([{1}] = ''' + convert(nvarchar(max),@{1}) + ''')'{0}", 
                 Environment.NewLine, GetPropertyName(column.Name),AND_OR) + Environment.NewLine; 
    ProcText += "\t\tEnd" + Environment.NewLine; 
    ProcText += "\tEnd" + Environment.NewLine; 
      return ProcText; 
} 
//adjust the sql output for the datetime two var probability 
if(column.NativeType == "datetime" || column.NativeType=="smalldatetime"){
     ProcText += string.Format("\tif(@{0}_From is not null AND @{0}_To is null){1}\tBegin{1}" ,
                 GetPropertyName(column.Name),Environment.NewLine); 
    ProcText += "\t\t\tSET @sql = @sql +" + string.Format("\t'{2}([{1}] >=''' + @{1}_From + ''')'{0}", 
               Environment.NewLine, GetPropertyName(column.Name),AND_OR) + Environment.NewLine ; 
    ProcText += "\tEnd" + Environment.NewLine; 
    ProcText += string.Format("\t\tif(@{0}_From is null AND @{0}_To is not null){1}\tBegin{1}" ,
               GetPropertyName(column.Name),Environment.NewLine); 
    ProcText += "\t\t\tSET @sql = @sql +" + string.Format("\t'{2}([{1}] <=''' + @{1}_To +''')'{0}", Environment.NewLine, 
               GetPropertyName(column.Name),AND_OR) + Environment.NewLine ; 
    ProcText += "\tEnd" + Environment.NewLine; 
    ProcText += string.Format("\tif(@{0}_From is not null AND @{0}_To is not null){1}\tBegin{1}" ,
        GetPropertyName(column.Name),Environment.NewLine); 
    ProcText += "\t\t\tSET @sql = @sql +" + string.Format("\t'{2}([{1}] >=''' + @{1}_From + ''' AND [{1}] <=''' + 
        @{1}_To + ''')'{0}", Environment.NewLine, GetPropertyName(column.Name),AND_OR) + 
        Environment.NewLine ; ProcText += "\tEnd" + Environment.NewLine; 
     return ProcText; 
} 
ProcText += string.Format("\tif(@{0} is not null){1}\tBegin{1}" ,GetPropertyName(column.Name), Environment.NewLine); 
ProcText += Environment.NewLine; 
ProcText += "\tif (charindex('%',@" + GetPropertyName(column.Name) + ")>0)" + 
Environment.NewLine; 
ProcText += "\t\tBegin" + 
Environment.NewLine; 
ProcText += "\t\t\tSET @sql = @sql +" + string.Format("\t'{2}([{1}] Like ''' + 
@{1} + ''' OR ''' + @{1} + ''' is null)'{0}", Environment.NewLine, GetPropertyName(column.Name),AND_OR) + 
Environment.NewLine ; ProcText += "\t\tEnd" + Environment.NewLine; ProcText += "\tElse" + Environment.NewLine; 
ProcText += "\t\tBegin" + Environment.NewLine; 
ProcText += "\t\t\tSET @sql = @sql +" + string.Format("\t'{2}([{1}] = ''' + @{1} + ''' OR ''' + 
@{1} + ''' is null)'{0}", Environment.NewLine, GetPropertyName(column.Name),AND_OR) + 
Environment.NewLine; ProcText += "\t\tEnd" + Environment.NewLine; 
ProcText += "\tEnd" + Environment.NewLine; return ProcText; }

警告……！！

以这种方式实现的 Find/Search 存储过程会使您的数据库系统暴露于 SQL 注入攻击……您必须注意搜索表单中的用户输入，方法是强制用户从预定义值中选择，或通过检查用户输入并查看其是否会损害构造的 SQL 语句来简单地防御此类攻击……

一种常用技术是替换 (') 为 ('')，如果参数值中包含任何……！！

结论

我希望在座的所有人都能找到这篇文章的用处，让您的编程生活更轻松。

历史

修改通知

修改日期：2006年11月1日，星期三
  • 已对生成的 C# 代码进行修改，以适应使用 Find/Search 存储过程的函数。

为了使上述代码能与 nettiers 的前端一起工作，必须对 `SqlEntityProviderBase.generated.cst` 模板文件进行修改。该文件已上传到文章的 nettiers 修改文件组合压缩包中。

  • nettiers 生成的 find 方法的语法如下所示
DataGrid1.DataSource=DataRepository.YourClassNameProvider.Find("columnName=value AND/OR columnName=value");
如果您正在搜索 datetime 或 small datetime，那么根据文章，您应该知道已经为这种类型的列创建了两个参数，这两个参数的名称是 `yourColumnName_from` 和 `yourColumnName_to`。然后，如果您想在两个 datetime 值之间搜索一个 date 类型列，您只需在查询中键入如下内容：
DataGrid1.DataSource=DataRepository.YourClassNameProvider.Find("columnName_from=datetimevalue AND columnName_to=datetimevalue");

如果您想获取具有相同日期的记录，只需在查询中为两个变量分配相同的值即可。

© . All rights reserved.