Skip to main content

Fastest way of pagination SQL Query

string sql=string.Format(@"DECLARE @skipRows int = {0},
                                              @takeRows int = {1},
                                              @count int = 0

                                              ;WITH Message_cte AS (
                                              SELECT M.*,O.OrderNo AS WebOrderNo,O.OrderDate FROM [Message] M
                                              INNER JOIN [Order.Web] O ON M.OrderID=O.ID
                                              WHERE
                                              --O.ApplicationID = 3 AND
                                              (M.[SenderID] = '{2}'
                                              OR M.[ReceiverID]='{3}')
                                              )

                                            SELECT
                                                *,
                                                tCountMessages.CountMessages AS TotalRows
                                            FROM Message_cte
                                                CROSS JOIN (SELECT Count(*) AS CountMessages FROM Message_cte) AS tCountMessages
                                            ORDER BY OrderDate DESC
                                            OFFSET @skipRows ROWS
                                            FETCH NEXT @takeRows ROWS ONLY;", skipRows, pageSize, id,id);

This is my C# code chunk.Hope it helps!


Comments

Post a Comment

Popular posts from this blog

Cannot access a disposed object. A common cause of this error is disposing a context that was

  Cannot access a disposed object. A common cause of this error is disposing a context that was resolved from dependency injection and then later trying to use the same context instance elsewhere in your application. This may occur if you are calling Dispose() on the context, or wrapping the context in a using statement. If you are using dependency injection, you should let the dependency injection container take care of disposing context instances. Object name: 'PropertyContext'.  public class CustomerHub : Hub     {         public readonly ICustomerService _customerService;         private readonly IServiceScopeFactory _serviceScopeFactory;         public CustomerHub(ICustomerService service, IServiceScopeFactory serviceScopeFactory)         {             _customerService = service;             _serviceScopeFactory = serviceSco...
Post a Comment
Read more

Limit Upload File Type Extensions ASP.NET MVC 5

  //-----------------------------------------------------------------------    // <copyright file="AllowExtensionsAttribute.cs" company="None">    //     Copyright (c) Allow to distribute this code and utilize this code for personal or commercial purpose.    // </copyright>    // <author>Asma Khalid</author>    //-----------------------------------------------------------------------       namespace  ImgExtLimit.Helper_Code.Common   {        using  System;        using  System.Collections.Generic;        using  System.ComponentModel.DataAnnotations;        using  System.Linq;      ...
Post a Comment
Read more