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

update your package-lock.json according to what you have specified in the package.json file

  The objective of the   npm update   command is to update your   package-lock.json   according to what you have specified in the   package.json   file. This is the normal behavior. If you want to update your package.json file, you can use  npm-check-updates :  npm install -g npm-check-updates . You can then use these commands: ncu  Checks for updates from the package.json file ncu -u  Update the package.json file npm update --save  Update your package-lock.json file from the package.json file
Post a Comment
Read more

FTP(File Transfer Protocol ) configuration and testing in Windows Server 2008 R2

First of all you have to know  what the FTP is " File Transfer Protocol ( FTP ) is a standard network protocol used to transfer files from one host or to another host over a TCP -based network". FTP Installation & Configuration: Step 1: Install the Web Server role with the IIS Management Console and FTP Server role services: Step 2: Add a new FTP Site Step 3: Setup the site with the default bindings and choose Allow SSL to avoid deploying a certificate:     Step 4: Configure user permissions and basic or anonymous permission. If your server is connected to your domain you can specify domain users, otherwise they must be local user accounts: Note: Finally you’ll have to configure your server’s firewall rules to allow access.Disregard any existing FTP firewall rules; although they should be enabled, they don’t actually allow access! Run Allow a Program Through Windows Firewall and grant access to C:\Windows\System32\svchost.exe T...
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