Custom paging,sorting and filtering data with Dynamic query

Sometime it becomes very essential for us to write dynamic query for custom paging,sorting and filtering data. Today I want to share simple one which may help others to fulfill their requirements.


Sample stored procedure:


CREATE PROCEDURE [dbo].[spGetAllTag]
@SearchText varchar(50),
@PageIndex INT = 1,//page number like 1,2,3
@PageSize INT =10,
@SortCol varchar(100),
@TotalCount int output
As
Begin

;WITH PagingCTE (Row_ID,ID,Tags,Weight,CreatedBy)
     AS
      (
      SELECT
            ROW_NUMBER()
                  OVER(ORDER BY
                         CASE WHEN @SortCol='Name DESC' THEN [NAME] END DESC,
                         CASE WHEN @SortCol='Name ASC'  THEN [NAME] END ASC,
                         CASE WHEN @SortCol='CreatedBy DESC' THEN CreatedBy END DESC,
                         CASE WHEN @SortCol='CreatedBy ASC'  THEN CreatedBy END ASC,
                         CASE WHEN @SortCol='Weight ASC'   THEN Weight  END ASC,
                         CASE WHEN @SortCol='Weight DESC'  THEN Weight  END DESC

                        ) AS [Row_ID],
ID,
[NAME],
Weight,
CreatedBy

FROM TabularWeight
WHERE [Name] LIKE @SearchText + '%'
         )

       SELECT
            Row_ID,
            ID,
            [NAME],
            Weight,
            CreatedBy
         
      FROM PagingCTE
      WHERE Row_ID >= (@PageSize * @PageIndex) - (@PageSize -1) AND
            Row_ID <= @PageSize * @PageIndex

select @TotalCount = count(1) from dbo.TabularWeight where [Name] LIKE @SearchText + '%'  
   
End
GO

Comments

Well, I don't think this will work in a paging scenario because the query that you run is limited by the page size.

Popular posts from this blog

The model backing the 'MyDBContext' context has changed since the database was created. Either manually delete/update the database, or call Database.SetInitializer with an IDatabaseInitializer instance. For example, the DropCreateDatabaseIfModelChanges strategy will automatically delete and recreate the database, and optionally seed it with new data.

How can I get a value of a property from an anonymous type (C#)

Check/Uncheck all items in a CheckBoxList using Javascript