inline search query easy to code and perfomeance better than the best

#region Namespaces
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

#endregion

namespace DAL
{
public class Search
{
#region GetSearchResult

//// Method Name : GetSearchResult
//// Purpose : To get search result for front end.
//// Created By : Kushan
//// Created Date : 31/5/2008

//public static DataSet GetSearchResult(int PageID, int PageSize, string ShortField, string ShortBy, int? SectionId, int? CategoryId, int? BrandId, string Keyword)
//{
// DataSet ds = new DataSet();
// ds=DAL.SqlHelper.ExecuteDataset(CommandType.StoredProcedure,
// "GetSearchResult",
// new SqlParameter[]
// {
// new SqlParameter("@PageId",PageID),
// new SqlParameter("@PageSize",PageSize),
// new SqlParameter("@ShortField",ShortField),
// new SqlParameter("@ShortBy",ShortBy),

// new SqlParameter("@SectionId",SectionId),
// new SqlParameter("@CategoryId",CategoryId),
// new SqlParameter("@BrandId",BrandId),
// new SqlParameter("@Keyword",Keyword),
// }
// );
// return ds;
//}

#endregion

#region GetSearchResult

// Method Name : GetSearchResult
// Purpose : To get search result for front end.
// Created By : Kushan
// Created Date : 05/07/2008

public static DataSet GetSearchResult(int PageID, int PageSize, string SortField, string SortBy, int? SectionId, int? CategoryId, int? BrandId, string Keyword)
{
string[] Keyword_split = null;
string Name = "", Description = "", TotalPages = "", CategoryName = "", BrandName = "", ArticlesTital="", ArticlesDescription="";
string Query = "", From = "";
int startRowIndex = 0;

DataSet ds = new DataSet();
StringBuilder Where = new StringBuilder();
StringBuilder Product = new StringBuilder();
StringBuilder Category = new StringBuilder();
StringBuilder Brand = new StringBuilder();
StringBuilder Articles = new StringBuilder();
Query =
" Select" +
" ROW_NUMBER() OVER (ORDER BY Product." + SortField + " " + SortBy + ") AS ROW_NUM," +
" Product.Id," +
" Product.Name," +
" Product.Description," +
" Product.size," +
" Product.Image," +
" Section.SectionUrl AS SectionName," +
" Category.CategoryUrl AS CategoryName," +
" Product.ProductUrl + '-' + CASE WHEN EXISTS (SELECT Id FROM Relations WHERE SourceType = 'P' AND SourceId = Product.Id AND DestType='P') THEN 'Variant' ELSE 'Leaflet' END + '.htm' AS ProductUrl," +
" Brands.DisplayName as BrandName," +
" dbo.GetSpecialOfferText(Product.Id,getdate()) as SpecialOfferText," +
" convert(int,dbo.GetSpecialOfferBuyQty(Product.Id,getdate())) as SpecialOfferBuyQty," +
" convert(decimal(10,2),Product.Prize) as Price," +
" case when Product.CutPrice>'0.00' then 'false' else 'true' end as Pricevisibility," +
" case when Product.CutPrice>'0.00' then convert(decimal(10,2),Product.CutPrice) else convert(decimal(10,2),Product.Prize) end as CutPrice," +
" case when Product.CutPrice>'0.00' then 'true' else 'false' end as CutPricevisibility";
From =
" from Product," +
" Section," +
" Category," +
" Brands"
;

Where.Append(
" where" +
" Product.IsDeleted='false' and" +
" Product.Active='true' and" +
" section.Id=Product.Section_Id and" +
" Category.Id = Product.Category_Id and" +
" Brands.Id=Product.Brand_Id and dbo.CheckParentCategoryActive(Product.Category_Id)=1 and section.status=1 and" +
" Brands.active='true' and Brands.Isdeleted='false' "
);
if (Keyword != null && Keyword != "")
{
Keyword_split = Keyword.Split(' ');
if (Keyword_split.Length > 0)
{
for (int i = 0; i < Keyword_split.Length; i++)
{
if (i == Keyword_split.Length - 1)
{
Name += " Product.Name like '%" + Keyword_split[i].ToString() + "%' ";
Description += "Product.Description like '%" + Keyword_split[i].ToString() + "%' ";
CategoryName += "Category.Name like '%" + Keyword_split[i].ToString() + "%' ";
BrandName += "DisplayName like '%" + Keyword_split[i].ToString() + "%' ";
ArticlesTital += "Title like '%" + Keyword_split[i].ToString() + "%' ";
ArticlesDescription += "Description like '%" + Keyword_split[i].ToString() + "%' ";
}
else
{
Name += " Product.Name like '%" + Keyword_split[i].ToString() + "%' OR ";
Description += "Product.Description like '%" + Keyword_split[i].ToString() + "%' or ";
CategoryName += "Category.Name like '%" + Keyword_split[i].ToString() + "%' or ";
BrandName += "DisplayName like '%" + Keyword_split[i].ToString() + "%' or ";
ArticlesTital += "Title like '%" + Keyword_split[i].ToString() + "%' or ";
ArticlesDescription += "Description like '%" + Keyword_split[i].ToString() + "%' or ";
}
}
Where.Append(
" and ((" + Name + ") OR " +
" (" + Description + "))");
Category.Append(
" select Category.id," +
" Category.Name," +
" Category.CategoryUrl AS CategoryName," +
" section.SectionUrl as SectionName " +
" from Category," +
" Section " +
" where Section.Id=Category.Section_Id and" +
" dbo.CheckParentCategoryActive(Category.Id)=1 and" +
" section.status=1 and " +
" (" + CategoryName + ") " +
" order by Name asc"
);
Brand.Append
(
" select " +
" Brands.id," +
" DisplayName," +
" BrandUrl" +
" from Brands " +
" where Active='true' and" +
" isdeleted='false' and" +
" (" + BrandName + ")" +
" order by DisplayName asc"
);
Articles.Append
(
" SELECT Id,"+
" Title,"+
" 'Health-Info/' + (SELECT H.Title FROM dbo.HealthInfo H WHERE H.Active=1 AND H.IsTopic=1 AND H.Id=HF.ParentId) + '/' + HF.HealthInfoUrl + '.htm' AS URL"+
" FROM dbo.HealthInfo HF WHERE"+
" Active=1 AND"+
" IsTopic=0 and "+
" ((" + ArticlesTital + ") or (" + ArticlesDescription + "))"
);
}
else
{
Where.Append(
" and (Product.Name like '%'" + Keyword + "'%' OR " +
" Product.Description like '%'" + Keyword + "'%')");
Category.Append(
" select Category.id," +
" Category.Name," +
" Category.CategoryUrl AS CategoryName," +
" section.SectionUrl as SectionName " +
" from Category," +
" Section " +
" where Section.Id=Category.Section_Id and" +
" dbo.CheckParentCategoryActive(Category.Id)=1 and" +
" section.status=1 and " +
" (" + "Category.Name like '%" + Keyword + "%' " + ") " +
" order by Name asc"
);
Brand.Append
(
" select " +
" Brands.id," +
" DisplayName," +
" BrandUrl" +
" from Brands " +
" where Active='true' and" +
" isdeleted='false' and" +
" (" + "DisplayName like '%" + Keyword + "%' " + ") " +
" order by DisplayName asc"
);
Articles.Append
(
" SELECT Id," +
" Title," +
" 'Health-Info/' + (SELECT H.Title FROM dbo.HealthInfo H WHERE H.Active=1 AND H.IsTopic=1 AND H.Id=HF.ParentId) + '/' + HF.HealthInfoUrl + '.htm' AS URL" +
" FROM dbo.HealthInfo HF WHERE" +
" Active=1 AND" +
" IsTopic=0 and " +
" ((" + "Title like '%" + Keyword + "%' " + ") or (" + "Description like '%" + Keyword + "%'))"
);
}
}
if (SectionId != null)
{
Where.Append(" and Product.Section_id = " + SectionId.ToString());
}
if (CategoryId != null)
{
Where.Append(" and Product.Category_id = " + CategoryId.ToString());
}

if (BrandId != null)
{
Where.Append(" and Product.Brand_id = " + BrandId.ToString());
}

if (PageSize != 0)
{
Product.Append
(
"SELECT * FROM ( " + Query + From + Where.ToString() + " ) AS tmpTable WHERE ROW_NUM BETWEEN " + (startRowIndex + 1).ToString() + " AND " + (startRowIndex + PageSize).ToString()
);
}
else
{
Product.Append(Query.ToString());
}

TotalPages = " DECLARE @TotalRecord int" +
" DECLARE @no int" +
" set @TotalRecord = (select count(*) " + From + " " + Where.ToString() + ")" +
" if(" + PageSize.ToString() + " <> 0)" +
" begin" +
" SET @no = (@TotalRecord/ " + PageSize.ToString() + ")" +
" IF ((@TotalRecord%" + PageSize.ToString() + ")>0)" +
" BEGIN" +
" SET @no = @no+1" +
" END" +
" end" +
" SELECT @no AS Total_Pages, @TotalRecord AS TotalRecords";


SqlHelper.FillDataset(CommandType.Text, Product.ToString() + TotalPages + Category.ToString() + Brand.ToString()+Articles.ToString(), ds, new string[] { "1", "2", "3", "4" });
return ds;
}

#endregion
}
}