A modification To the NetTiers Code Generator Template <br>Makes the Generated Search Stored Procedures More Flexible

• Download source files - 35.5 Kb

Introduction

This article's code is simply a very tiny small modification made to the nettiers code template that is used with the famous Code Smith code generator to generate the data access layer automatically on sql server (creating stored procedures). The modification increases the capability of the generated Search/Find stored procedures, which solves the problem of supporting the "like" operator & made the procedure capable of searching the datetime/smalldatetime datatyped columns more intelligently.

Background (optional)

I suppose you know about C#, Visual Studio .Net 2005, Sql Server 2005, Transact-sql, Code Smith code generator and have experienced the problems of the generated Search/Find stored procedure that the nettiers produces for you.

Using the code

Do the following to use the modified version of nettiers:

1. Download the zip file associated with the article then unzip it you will find two folders of the netiers code generator template that contains the modified version of files.
2. Copy those files to the same folders in your nettiers root folder on your computer.
3. Compile nettiers using code smith code generator program.
4. Finally, use nettiers to generate the dataaccess layer for you.

That's it...!! Now enjoy using the Find/Search stored procedure without any modification for it to suppor the "like" operator of the Transact-sql's world and also enjoy smartly searching the datetime/smalldatetime datatyped columns....!!

Ideas behind the code

As introduction to the code, I have to tell that nettiers constructs the sql query file that contains the stored procedures creation of the data access layer in file with the name StoredProceduresXml.cst located under the DataAccessLayer.SqlClient folder in the root directory of the nettiers template and that is done with the help of a helper c# class named CommonSqlCode that inherites from the code smith engine's CodeTemplate class. the CommonSqlCode Class Implemented in a file with the name CommonSqlCode.cs located under the TemplateLib folder in the root directory on nettiers on your computer. So you can say that any modifications made to any procedure, the nettiers would generate around those files.

Now, to let the generated Find/Search stored procedure meet the first requirment of supporting the like operator, it has been modified to construct a big sql statement from the constructed parameters of the procedure and then run it using the sp_executesql built-in stored procedure that comes with sql server.

For each findable column (nvarchar,varchar...etc) nettiers creates a Search Parameter which is being checked in the modified version if its value contains the '%' which means using the like instead of the = operator...!!

The following is a part of the Find/Search procedure being generated using my modified version meets the first requirment.
note: comments in code here is a not a sql comments,it is an explanation for you about code(it will not be generated by the generator)

-- 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

For the second requirment of searching the datetime/smalldatetime datatyped columns more smartly, the final stored procedure generated by my modified nettiers version about datetime/smalldatetime datatyped columns portion of code may look like this:

--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 

My dear readers will notice the AND operator in front of statement being concatenated to the @sql variable ... in fact nettiers generates a smart parameter named @SearchUsingORthat governs the restriction of the search results, which means using And or using Or for constructing the stored procedure, of course the modified version got this too but i am only giving this part of the generated stored procedure to be in short, you can look at your generated Find/Search Procedures using the modified version of nettiers to see the overall picture about what the modified template would do.

Note: to search an exact date you only need to supply the same value for both parameters...!!!!

Code Modifications

StoredProcedureXml.cst Code Behind Modifications:

• The strategy of the Find/Search stored procedure changed to use the sp_executesql built-in stored procedure which required a large modification to the code written after the line in the orginal version of nettiers

  if(IncludeFind){%>

• A check for the datetime/smalldatetime columns to determine how to construct the parameters list of the procedure has been added.
  The reason for this is as I told you about meeting the second requirement,for any datetime/smalldatetime datatyped columns we need two parameters not a one like the other findable datatyped columns.

The code looks like the following:

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

Any method used in the StoredProcedureXml.cst file has its implementation in the CommonSqlCode.cs file.

The GetSqlParameterXmlNodeForDateTimeColumns used in the above piece of code is a new overloading to the GetSqlParameterXmlNode method in the modified CommonSqlCode.cs file..!!

CommonSqlCode.cs Modifications:

* A new method named GetSqlParameterXmlNodeForDateTimeColumns has been added to generate the required lines for edual parameter needed for every datetime datatyped columns

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

* Another new method named GetFProcedureLineOfColumn has been added and that is responsible for generating Procedure line by line for each input parameter the Find/Search Stored Procedure has.

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

Caution..!!

The Find/Search stored procedure being implemented this way exposes your database system to sql injection attacks .. you have to take care of user input in the search forms either by forcing her/him to select from pre-defined values or simply take your defence against such attacks by checking user input and see if it will harm the sql constructed statement ...

A common technique is to replace (') with ('') if parameter values will have any..!!

Conclusion

I hope you all out there will find this article useful in the way of making your programming life easier.

History

Modification alert

modification date:wed,1-nov-2006

• A modification has been made to adjust the generated c# code for functions that use the Find/Search stored procedure.

for the above code to work with the front end of nettiers a modification had to be made to the SqlEntityProviderBase.generated.cst template file, the file is uploaded to the article combined zipped folder of nettiers modified files

• the syntax to use the find method that nettiers generates looks like

DataGrid1.DataSource=DataRepository.YourClassNameProvider.Find("columnName=value AND/OR columnName=value");

if you are searching a datetime or small date time then i think you know from the article that two parameters have been created for such kind of columns and the names of those parameters are yourColumnName_from & yourColumnName_to then in your query you simply type as the following if youu wanna search a datetype column between two datetime values

DataGrid1.DataSource=DataRepository.YourClassNameProvider.Find("columnName_from=datetimevalue AND columnName_to=datetimevalue");

if you wanna get the records that has the same date, simply assign the same value for both variables in the query