CODEWITHSUNDEEP
c#kentico
Philipp Hofmann
Philipp Hofmann

Reputation: 3478

How to use a configurable Where Clause in a Kentico Query

I currently assign the sql statement to a NVarchar and then execute it. But I don't like the way how this works.

SQL

DECLARE @sqlStatement NVARCHAR(4000)

SET @sqlStatement = N'
     SELECT *
     FROM CMS_User usr
     WHERE
     (    
          usr.LastName LIKE ''%' + @Search + '%'' OR
     )'

IF(@SearchWhereClause IS NOT NULL)
BEGIN
     SET @sqlStatement = @sqlStatement+  N' AND (' + @SearchWhereClause + ')'
END

SET @sqlStatement = @sqlStatement+  N' ORDER BY usr.LastName'

EXEC sp_executesql @statement = @sqlStatement

Code

 QueryDataParameters parameters = new QueryDataParameters();
 parameters.Add("@Search", prefixText);

 //set configurabel searchWhereClause
 string searchWhereClause = SettingsKeyProvider.GetStringValue("WhereClause");
 if (string.IsNullOrEmpty(searchWhereClause))
 {
      searchWhereClause = null;
 }
 parameters.Add("@SearchWhereClause", searchWhereClause);

 DataSet ds = ConnectionHelper.ExecuteQuery("custom.DocType.Query", parameters);

Upvotes: 0

Views: 1977

Answers (1)

Philipp Hofmann
Philipp Hofmann

Reputation: 3478

Instead you can use the Kentico macros for queries:

SQL

SELECT * 
FROM CMS_User usr
WHERE 
(   
    usr.LastName LIKE '%' + @Search + '%' OR 
)
AND ( usr.UserIsHidden IS NULL OR usr.UserIsHidden = 0 )
AND ( ##WHERE## )
ORDER BY usr.LastName

Code

ConnectionHelper.ExecuteQuery(string queryName, QueryDataParameters parameters,
   string where);

Upvotes: 1

Related Questions