Advertisements

Archive

Posts Tagged ‘Paging’

SQL : Paging in Stored Procedure

November 1, 2017 Leave a comment

Create simple Student table using following script

CREATE TABLE [dbo].[Student]( [ID] [int] NOT NULL, [Name] [varchar](50) NOT NULL) ON [PRIMARY]GO

The OFFSET-FETCH clause is used to fetch specif records. OFFSET-FETCH can be used only with the ORDER BY clause.

Stored Procedure :

SP

Limitations in Using OFFSET-FETCH

  • ORDER BY is mandatory to use OFFSET and FETCH clause.
  • OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
  • TOP cannot be combined with OFFSET and FETCH in the same query expression.
  • The OFFSET/FETCH rowcount expression can be any arithmetic, constant, or parameter expression that will return an integer value. The rowcount expression does not support scalar sub-queries.
Advertisements
Categories: Sql Server Tags: , ,

Comparing ListView with GridView,DataList and Repeater

April 13, 2012 Leave a comment

The ListView control is a new data presentation control that was added in ASP.Net 3.5.You may wonder why its added to the framework , and what it provide .

From what i have seen, ListView control was added to provide The following functionalities :

  1. A very flexible and customizable layout.
  2. A built in data paging support with the new DataPager control.
  3. Support data grouping (repeating items) in a flexible way.
  4. Built in support for deleting,inserting,paging,sorting,and updating the data.

Now , to compare the ListView control with the dataList,GridView and repeater control , lets look at the table below :

Supported Funcationalities
Control Paging Data Grouping Provide Flexible Layout Update,Delete Insert Sorting
ListView supported supported supported supported supported supported
GridView supported Not supported Not Supported supported Not Supported supported
DataList Not supported supported supported Not supported Not supported Not supported
Repeater Not supported Not supported supported Not supported Not supported Not supported


* Supported: means that it’s provided out of the box without any custom code or hacks.

* Not Supported: means that it’s not provided out of the box by the control but it could be possible to implement it using custom code \ hacks.

The GridView : it supports paging but it doesn’t provide a flexible layout , since its mainly used to display the data in a table based layout.And If we looked at data inserting , the Gridview doesn’t have a built in support for inserting data( since it doesn’t call the insert method of it underlying data source when you click on a button with a CommadName set to “Insert” ).

The DataList : it support data grouping ( through its RepeatColumns property) , but it doesn’t have a built in support for paging,inserting ,deleting , updating the data. and if you looked at its laout , you will find that by default  the datalist renders as html table and you will have to set its flowLayout to “Flow” to stop that behaviour.

The Repeater control : you will find that it provides a flexible layout but it doesn’t support data grouping ,inserting,deleting , updating  and paging through the data .

Summary :

The ListView control was added to provide a rich data control that can support all the required functionalities at the same time , so now you can easily display a fully customizable layout that supports Grouping,paging , inserting , deleting , updating and sorting the data.