|
/*
* Copyright � 2005-2006 Danilo Mendez <danilo.mendez@kontac.net>
* Adolfo Socorro <ajs@esolutionspr.com>
* www.kontac.net
* All rights reserved.
* Released under both BSD license and Lesser GPL library license.
* Whenever there is any discrepancy between the two licenses,
* the BSD license will take precedence.
*/
using System;
using System.Collections.Generic;
using System.Text;
using SmartCode.Template;
using SmartCode.Templates.Core.SQLServer.Utils;
using SmartCode.Model;
namespace SmartCode.Templates.Core.SQLServer
{
public class SearchBy : TemplateBase
{
// Constructor.
public SearchBy()
{
this.createFile = true;
this.description = "Generates a stored procedure to retrieve Rows by fields";
this.name = "Retrieve Rows by Fields";
this.outputFolder = @"Stored Procedures\SearchBy";
}
public override string OutputFileName()
{
return Table.Name + "_SearchBy.sql";
}
public override void ProduceCode()
{
bool runTemplate = false;
foreach (Column column in Table.AllColumns())
{
runTemplate |= column.CustomProperties.SearchBy;
}
if (runTemplate)
{
WriteLine("SET QUOTED_IDENTIFIER ON ");
WriteLine("GO");
WriteLine("SET ANSI_NULLS ON ");
WriteLine("GO");
string spName = Common.SP_NAME_PREFIX + Table.Code + "_SearchBy";
string spPurpose = "Get Rows from the table " + Table.Name;
WriteLine("-- Stored Procedure " + spName);
WriteLine("-- Purpose: " + spPurpose);
WriteLine("-- Parameters:");
// First, delete the stored procedure, if it exists
WriteLine(Common.GetStoredProcedureDelete(Domain.ConnectionInfo.Database, spName));
WriteLine(Common.GetSimpleStoredProcedureHeader("--", this, spPurpose));
WriteLine(" CREATE PROCEDURE " + spName);
string inputParameters = String.Empty;
foreach (Column column in Table.AllColumns())
{
if (column.CustomProperties.SearchBy)
{
inputParameters += " " + Common.GetSqlParameterLine(column);
}
}
inputParameters = Common.Substring(inputParameters, Environment.NewLine.Length + 1);
WriteLine(" ({0} {1} {0})", Environment.NewLine, inputParameters);
WriteLine(" AS");
WriteLine(Common.GetComplexSelect(this.Table));
WriteLine(" WHERE ");
bool isFirst = true;
foreach (Column column in Table.AllColumns())
{
if (column.CustomProperties.SearchBy)
{
if (! isFirst)
{
WriteLine(" AND ");
}
WriteLine(" (@{0} = '%' OR @{0} IS NULL OR [{1}].[{2}] LIKE @{0}) ", column.Code, Table.Name, column.Name );
isFirst = false;
}
}
WriteLine();
WriteLine(" GO ");
WriteLine();
WriteLine("-- End Procedure");
}
else
{
WriteLine("-- Entity " + Entity.Name + " does not have a search by assigned property.");
}
}
}
}
|
By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.
If a file you wish to view isn't highlighted, and is a text file (not binary), please
let us know and we'll add colourisation support for it.
This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.
A list of licenses authors might use can be found here
Danilo is the creator of
SmartRules, a Business Rules Engine. He is an industry consultant working primarily with companies interested in implementing dynamic rules programming concepts to add flexibility to their architectures on web, CE, and desktop platforms. He operates his own website,
Kontac, where you will find more information.
To contact Danilo, email him at danilo.mendez@gmail.com.