Stránkování v ASP.NET a MS SQL univerzálně - pomocí komponenty PagingRepeater
- Vložil Trupík 1/24/2007 7:22:33 PM
-
Č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
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