Poznámka: články jsou již mnoho let staré, doba se posunula, mnoho věcí v nich doporučovaných je již dnes překonané - berte s rezervou!


Stránkování v ASP.NET a MS SQL univerzálně - pomocí komponenty PagingRepeater

Článek (v pozměněné podobě) původně vyšel na serveru www.netstudent.cz, ale protože vyhledávače tento server prakticky ignorují, rozhodl jsem se ho uveřejnit také na Trupíkově weBlogu. Pokud se zajímáte o programování v C#, najdete na onom serveru jistě mnoho užitečných článků a návodů.

Komponenta PagingRepeater byla také přihlášena do soutěže
Komponenta roku 2006

Pokud řešíte problém stránkování, ale pod PHP a MySQL, zabývá se jím můj předchozí článek - Stránkování v PHP a MySQL pohodlně a univerzálně.

Co myslím pod pojmem "stránkování"? Když výsledek nějakého SQL dotazu obsahuje tolik záznamů, že se nehodí vypsat všechny najednou. Místo toho se rozdělí do "stránek" a vypisuje se vždy jen jedna. Na webu je stránkování potřeba každou chvíli. Rozhodl jsem se proto vytvořit komponentu PagingRepeater (jejíž jádrem bude ASP.NET Repeater), která se o stránkování postará. A musí to být inteligentní stránkování a ne ta parodie, kterou požívá defaultně DataGridView - to totiž vždy natáhne z databáze všechny záznamy (což může být obrovské množství) a až potom je rozdělí do stránek a jednu z nich zobrazí. Moje komponenta vždy natáhne jen ty záznamy, které potřebuje.

Začnu příkladem, ať vidíte, co můžete čekat. Dejme tomu, že máme tabulku 'Jmena' se sloupci 'id' a 'jmeno' a chceme je stránkovaně vypsat. Stačí takovýto kód

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %> <%@ Register TagPrefix="TC" Namespace="TrupixControls" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Untitled Page</title> <style type="text/css"> .currentPage { font-weight: bold; text-decoration: none; } </style> </head> <body> <form id="form1" runat="server"> <div> <TC:PagingRepeater ID="PagingRepeater1" runat="server" ConnectionString="..." CurrentPageClass="currentPage" SelectCommandText="SELECT * FROM jmena" PrimaryKey="id" OrderBy="jmeno" UrlParameterName="stranka"> <HeaderTemplate> <h3>Seznam uživatelů: </h3> </HeaderTemplate> <ItemTemplate> <span style="color: Red;" > <%# DataBinder.Eval(Container.DataItem, "id") %> <%# DataBinder.Eval(Container.DataItem, "jmeno")%> </span> </ItemTemplate> <SeparatorTemplate> <br /> </SeparatorTemplate> <AlternatingItemTemplate> <span style="color: Purple;"> <%# DataBinder.Eval(Container.DataItem, "id") %> <%# DataBinder.Eval(Container.DataItem, "jmeno") %> </span> </AlternatingItemTemplate> <FooterTemplate> </FooterTemplate> </TC:PagingRepeater> </div> </form> </body> </html>

Na začátku stránky se registruje soubor s komponentou a pak už jen deklarativně nastaví odpovídající SQL příkaz a parametry stránkování (počet záznamů ve stránce apod.). Výsledek pak vypadá třeba takto (odkazy jsou dole jen pro ilustraci, tady nikam nevedou):

Seznam uživatelů:

5: Ales
11 Bara
4: David
12 Eva
1: Jakub
7 Jana
13: Jouda
8 Lucka
3: Martin
2 Petr
<< < 1 2 > >>

Příklad použití v praxi můžete také vidět na titulní stránce tohoto blogu, kde jsou PagingRepeatery hned dva.

A jak to vlastně pracuje? Komponenta není odděděná od Repeateru (toto jsem zavrhnul, protože nechci, aby byly vidět vlastnosti Repeateru jako DataSource). Repeater ale v sobě obsahuje. Při volání metody DataBind si komponenta zjistí, kterou stránku má zobrazit, spojí se s databází, předá číslo požadované stránky a počet stránek, a výslednou sadu předá internímu Repeateru. Při vykonávání metody Render se o vykreslení vlastních záznamů stará interní Repeater, třída PagingRepeater se postará o vykreslení odkazů na další stránky.

Tyto vlastnosti zajišťují logiku:

  • Vlastnost PagingStrategy nastaví, jakým ze tří způsobů se bude stránkovat (možnosti jsou MultipleTopClauses, StoredProcedure, RowNumberFunction). Jednotlivé způsoby jsou detailněji popsány v článku PagingRepeater na NetStudentovi
  • Vlastností SelectCommand nebo SelectCommandText se nastaví příkaz definující požadovanou množinu záznamů, která se má stránkovat. Tento nesmí obsahovat klauzuli ORDER BY na nejvnějšnější (krásné slovo) úrovni. Dále si komponenta musí zjistit počet všech záznamů (aby věděla, kolik bude stránek). Pokud se používá PagingStrategy StoredProcedure, SelectCommandText bude obsahovat jméno procedury. Stačí nastavit jen jednu z vlastností SelectCommand nebo SelectCommandText.
  • Vlastnost UrlParameterName je řetězec, který se vloží do url jako parametr (v příkladu to bude stranka=2 pro druhou stránku). Z URL komponenta zjišťuje, kterou stránku má zobrazit.
  • Vlastnost PrimaryKey - primární klíč vysledkové sady - je potřeba nastavit pro PagingStrategy MultipleTopClauses
  • Vlastnost OrderBy je potřeba nastavit pro PagingStrategy RowNumberFunction. U MultipleTopClauses nutná není. SelectCommand ale ORDER BY obsahovat nesmí (na vnější úrovni)
  • Ještě zbývá nastavit připojení - buďto jako objekt SqlConnection (vlastnost SqlConnection), nebo jako ConnectionString (vlastnost ConnectionString).
  • PagingRepeater potřebuje také spočítat záznamy. Defaultně to udělá tak, že předaný SelectCommand upraví (přidáním klausule COUNT) tak, aby vrátil počet záznamů. Pokud ale chcete, můžete vložit vlastní počítací příkaz (vlastnost CountCommandText)

Dále PagingRepeater obsahuje řadu vlastností, které nastavují vzhled (např. zda se odkazy na další stránky napíšou nahoře nebo dole, jak mají vypadat odkazy na první a poslední stránku apod. Tyto vlastnosti jsou sdružené v kategorii Appearance.

Zde si můžete stáhnout kompletní kód třídy PagingRepeater a také ukázkovou stránku, která PagingRepeater používá:
Třída PagingRepeater

Komponentu můžete vesele stahovat a používat, připomínky samozřejmě uvítám. Pokud vás zajímá víc to, jak je to udělané, čtěte dále (ale možná to není úplně "začátečnický" text).

Detaily stránkování a vnitřní logika komponenty

Hlavním problémem stránkování je to, že se vlastně nedá dost dobře vyjádřit standardními prostředky SQL. Proto se v implementaci většinou používají nějaké proprietární rozšíření v závislosti na použitém SQL serveru. Nejjednodušší to mají uživatelé MySQL. Jeho autoři totiž moc dobře věděli, jak často je na webu potřeba stránkovat záznamy (a MySQL je optimalizováno především na nasazení na webu), a proto rozšířili příkaz SELECT o klauzuli LIMIT - dvouparametrová verze klauzule LIMIT je přesně to, co je ke stránkování potřeba.

MS SQL žádnou klauzuli LIMIT nemá. Má jen trochu podobnou klauzuli TOP n, která omezí výsledek na prvních n záznamů. Vývojáři MS SQL ale nejspíš podlehli tlaku uživatelů a ve verzi MS SQL 2005 přidali novou funkci ROW_NUMBE(), která jednoduše očísluje záznamy vracené výsledkové sady. S touto funkcí je již stránkvání hračka. Další možnost jak stránkovat je použít uloženou proceduru a pomocí databázového kurzoru zjistit, které záznamy patří do dané stránky.

Klauzule TOP n

Jak jsem již napsal, použití TOP n v příkazu SELECT omezí výsledek na prvních n řádků. Toho se dá pro stránkování využít, vím o následujících možnostech (dejme tomu, že požadujeme záznamy ze stránky p):

  • Pomocí jednoho TOP n vybereme záznamy, které patří do prvních p stránek, a výsledek setřídíme sestupně. Pomocí dalšího TOP n omezíme výsledek na velikost jedné stránky (díky sestupnému třídění jsou totiž záznamy ze stránky p první), vysledek opět setřídíme vzestupně.
  • Pomocí jednoho TOP n vybereme záznamy, které patří do prvních p stránek. Pomocí jiného TOP n zvlášť vybereme záznamy, které patří do prvních p-1 stránek. Tyto dvě výsledkové sady od sebe množinově odečteme.

První řešení má tu nevýhodu, že se v něm dvakrát třídí a to může trvat dlouho (ale tato operace se dá urychlit definováním vhodných databázových indexů). Druhé řešení zase používá množinový rozdíl a to je taky dost pomalá operace.

PagingRepeater používá druhé řešení. Vím, že není nejefektivnější, ale snadno se udělá skutečně univerzální. Pokud potřebujete efektivnější řešení, musíte holt použít jinou ze stránkovacích strategií, nebo přidat vlastní. Uvnitř to funguje nějak takto:

SELECT * FROM ( SELECT TOP (@PAGESIZE * @PAGENUM) * FROM (fullSetSqlCommand) AS sq ORDER BY orderByClause ) AS sq1 WHERE NOT sq1.PrimaryKey IN ( SELECT TOP (@PAGESIZE * (@PAGENUM - 1)) PrimaryKey FROM (fullSetSqlCommand) AS sq ORDER BY orderByClause )

fullSetSqlCommand je příkaz předaný komponentě, PrimaryKey je primární klíč ve výsledkové sadě, orderByClause je definice třídění (třeba timestamp DESC, first_name ASC).

V prvním příkaze se vyberou všechny příkazy z prvních p stránek, druhý vyřadí prvních p-1 stránek (tady už není potřeba vybírat vše, ale stačí primární klíče).

Příkaz fullSetSqlCommand předaný komponentě má omezení - na vnější úrovni nesmí obsahovat klauzuli ORDER BY (aby šel použít jako vnořený příkaz). Klauzule ORDER BY se předá komponentě zvlášť (přes vlastnost OrderBy).

Použití uložené procedury

Možná se rozhodnete využít uloženou proceduru pro implementaci třídění, má to jisté výhody - když už se rozhodnete vytářet uloženou proceduru, jste v nějaké konkrétní situaci, můžete mít další informace o datech (např. máte definované, jak souvisí pořadí v záznamu s primárním klíčem). Použití uložené procedury může celý proces zefektivnit. Jednu uloženou proceduru provádějící stránkování na tabulce [jmena] ukážu (používá se kurzor). Využívám toho, že primární klíč je vzestupně setříděný.

CREATE PROCEDURE [dbo].[jmena_p] @PAGESIZE AS INT, @PAGENUM AS INT, @ITEMCOUNT INT OUTPUT AS BEGIN DECLARE @FIRSTID AS INT; DECLARE @LASTID AS INT; DECLARE @CURID AS INT; DECLARE @CURITEM_NO AS INT; DECLARE @FIRSTITEM_NO AS INT; DECLARE @LASTITEM_NO AS INT; SET @FIRSTITEM_NO = (@PAGENUM - 1) * @PAGESIZE + 1; SET @LASTITEM_NO = (@PAGENUM) * @PAGESIZE; SET @CURITEM_NO = 0; SET @FIRSTID = 0; SET @LASTID = 0; DECLARE cur_jmena CURSOR FOR SELECT id FROM jmena ; OPEN cur_jmena; SELECT @ITEMCOUNT = COUNT (*) FROM jmena; FETCH NEXT FROM cur_jmena INTO @CURID WHILE @@FETCH_STATUS = 0 BEGIN SET @CURITEM_NO = @CURITEM_NO + 1; IF (@FIRSTITEM_NO = @CURITEM_NO) SET @FIRSTID = @CURID; IF (@LASTITEM_NO = @CURITEM_NO) BEGIN SET @LASTID = @CURID; BREAK END FETCH NEXT FROM cur_jmena INTO @CURID END IF (@FIRSTID > 0 AND @LASTID = 0) SET @LASTID = @CURID; CLOSE cur_jmena; DEALLOCATE cur_jmena; SELECT * FROM jmena WHERE id BETWEEN @FIRSTID AND @LASTID END

Procedura je docela nainví, prostě jde dopředu po výsledkové sadě, dokud nenarazí na záznam, který patří do dané stránky, uloží si jeho id, jde dál a až narazí na záznam, který do stránky nepatří, uloží si id i tohoto záznamu. Potom už jen vrátí záznamy mezi uloženými id. Chtěl jsem ale hlavně předvést, jak musí procedura vypadat, aby mohla být použita s PagingRepeaterem - musí mít dva vstupní parametry, číslo požadované stránky a délku stránek, a jeden výstupní parametr - počet všech záznamů (ve všech stránkách)

Funkce ROW_NUMBER

S funkcí ROW_NUMBER se asi stránkování udělá nejsnáz. Prostě si nechám očíslovat záznamy, předem si vypočítám, která čísla chci a vyberu si jen záznamy v daném intervalu. Pokud máte MS SQL Server verzi 2005, doporučuji tuto metodu. Ve verzi 2000 musíte sáhnout po některé z dvou předešlých. Funkce ROW_NUMBER se používá následovně:

SELECT ROW_NUMBER() OVER ( ORDER BY <tridici vyrazy>) AS <alias>, <ostatní definice sloupcu> FROM ...

Závorka a v ní klauzule ORDER BY jsou povinné - ROW_NUMBER lze použít pouze když definujete třídění (v tomto pořadí se budou záznamy číslovat). V PagingRepeateru se právě sem dosazuje hodnota vlastnosti OrderBy - při této stránkovací strategii je tedy vlastnost OrderBy povinná.

Ohodnoťte prosím užitečnost článku




18
 
27
 
15
 
13
 
8
 
 
Vložit komentář:
 

 



 

 

Nepoužívejte žádné html ani texy značky, odřádkování se zachová. Pokud uvádíte zdrojový kód, můžete ho vložit mezi značky
<syntax jazyk="PHP">...</syntax>,
bude potom zformátován. Jako atribut můžete uvést PHP, C#, HTML, CSS a mnoho dalších.


opiste cislo Opište číslo:

 

7. 3. 2007 9:04:26
[1] (Rudidlo (rudidlo(at)centrum.cz))
Nepřesnost v článku odpovědět
Nemohu souhlasit s tvrzením, že DataGridView stránkuje tak, že načte veškeré záznamy a až poté zobrazí vybranou část. Nevím, zda má autor na mysli komponentu asp:DataGrid nebo asp:GridView, ale u obou těchto komponent používám stránkování s vazbou do databáze a při databindingu si grid sql serveru "řekne" vždy pouze o ty data, která odpovídají vybrané stránce.
7. 3. 2007 11:41:18
[2] (Trupik (jakub.maly(at)atlas.cz))
Re: Nepřesnost v článku odpovědět
[1]: měl jsem na mysli tu variantu, která je v MSDN popsaná takto:

If the GridView control is bound to a data source control that does not support the paging capability directly, or if the GridView control is bound to a data structure in code through the DataSource property, the GridView control will perform paging by getting all of the data records from the source, displaying only the records for the current page, and discarding the rest.

9. 3. 2007 10:22:46
[3] (Rudidlo (rudidlo(at)centrum.cz))
Re: Nepřesnost v článku odpovědět
Ale to už je o použití dané komponenty, ne toho, že to ta komponenta neumí. Když na ni nabinduji data z komponenty, která neumí stránkovat, tak jasné, že stránkování nedosáhnu.
1. 9. 2008 18:29:27
[4] (Beči (unknown18(at)seznam.cz))
Realizace na webu odpovědět
Jak zařídím to, aby se např. prvních 10 záznamů zobrazilo v první stránce a dalších 10 také?Myslím u toho posledního příkladu s Row_Number(). Díky
1. 9. 2008 23:06:27
[5] (Trupik (jakub.maly(at)atlas.cz)) www
Re: Realizace na webu odpovědět
[4] Myslíte aby na první stránce bylo prvních deset záznamů, na další prvních dvacet záznamů atd.? Nebo jinak?
25. 11. 2012 2:53:57
[6] (longchamp (longchamp(at)yahoo.com)) www
longchamp odpovědět
I just want to thank to share your information and your website or blog, this is a simple and yet useful.