SQL Server ed ALM su database


Il blog di Alessandro Alpi
Archivio Posts
Anno 2018

Anno 2017

Anno 2016

Anno 2015

Anno 2014

Anno 2013

Anno 2012

Anno 2011

Anno 2010

Anno 2009

Anno 2008

Anno 2007

Anno 2006

Filtro dinamico senza Dynamic SQL

A volte può capitare di dover scrivere applicazioni che diano all'utente finale la possibilità di eseguire ricerche sempre più approfondite e dettagliate.
Molto spesso il cliente ha bisogno di filtrare quasi per intero i dati inseriti in una tabella.
Ad esempio, pensando ad una biblioteca, potremmo voler filtrare l'anagrafica dei libri sia per titolo, che per autore, che per categoria, che per tanti altre informazioni disponibili.
Sia lato applicazione, sia lato database, risulta scomodo gestire con tanti "if" la creazione del criterio di filtro.
Grazie ad un mio collega, Marco Rossi (che si chiama così davvero, non è un'identità generica  ) si è arrivati ad utilizzare una tecnica che riduce lo spreco di codice.
Ma prendiamo l'esempio della biblioteca. Andiamo a creare una tabella e ad inserirvi dati concreti.

 
CREATE TABLE Books
(
 Id int
identity (1,1) primary key,
 [Name]
varchar(100),
 Description
varchar(1000),
 IdCategory
int,
 IdAuthor
int,
 IdPublisher
int,
 Pages
smallint
)

INSERT Books
SELECT 'SQL Server for dummies','Un libro per chi è alle prime armi con SQL Server', 1, 1, 1, 150
INSERT Books
SELECT 'Inside SQL Server: T-SQL Programming','Un libro per programmare in T-SQL', 1, 2, 2, 300
INSERT Books
SELECT 'Inside SQL Server: T-SQL Querying','Un ibro per ottimizzare le query', 1, 2, 2, 300
INSERT Books
SELECT 'Le cronache del mondo emerso - Vol.1','Un fantasy italiano', 1, 3, 3, 400
INSERT Books
SELECT 'Le cronache del mondo emerso - Vol.2','Un fantasy italiano', 1, 3, 3, 400
 
Una volta fatto questo, ipotizziamo di creare un'applicazione utile a ricercare dei libri in una biblioteca virtuale. L'utente, potrà scegliere di filtrare per Nome, Categoria, Autore, Casa editrice e numero di pagine.
Una delle soluzioni potrebbe essere quella di creare un SQL dinamico in base ai parametri valorizzati nell'applicativo. Ma sappiamo che è preferibile evitare l'ausilio dell'esecuzione di stringhe dinamiche in SQL..
Di conseguenza, proviamo a procedere come segue..
Creiamo una stored procedure che si aspetta i parametri di filtro:

CREATE PROCEDURE GetBooks
(
 @Name
varchar(100) = NULL,
 @Category
int = NULL,
 @Author
int = NULL,
 @Publisher
int = NULL,
 @Pages
smallint = NULL
)
AS
BEGIN

 SET NOCOUNT ON;
 SELECT
  *
 FROM
  Books
 WHERE
  (@Name
IS NULL OR [Name] = @Name) AND
  (@Category
IS NULL OR IdCategory = @Category) AND
  (@Author
IS NULL OR IdAuthor = @Author) AND
  (@Publisher
IS NULL OR IdPublisher = @Publisher) AND
  (@Pages
IS NULL OR Pages = @Pages)
END
 
Notiamo che tutti i parametri, sono impostati con default a NULL, in modo tale che l'applicazione non sia obbligata a passare valori.
Cosa succede con una tecnica di questo tipo? Se un criterio (ad esempio il valore di un TextBox o di un DropDownList) non è indicato, il parametro non viene passato alla chiamata della stored procedure ed i dati relativi a quel parametro non sono filtrati. Se tutti i parametri non sono passati, otteniamo tutto il resultset, viceversa, otteniamo i dati filtrati per ogni parametro indicato.
Ovviamente è possibile fare in modo che, se non si seleziona alcun criterio, il resultset sia vuoto, ma questo rimane a discrezione dello sviluppatore che decide di utilizzare la tecnica.
I vantaggi che conseguono sono vari:
- Innanzitutto ci si sposta sul database, lasciando l'applicazione sgombra di codice impuro e addirittura Hard Coded.
- Essendo all'interno di una stored procedure, l'accesso ai dati è più veloce, poichè il piano di esecuzione è già pronto sul database.
- Il traffico di righe di codice relativo alla chiamata della stored procedure è molto ridotto, in quanto si tratta di una semplice EXEC.
- Non servono casistiche per comporre il filtro. La select è una sola e la where pure.
- Non appesantisce il piano di esecuzione della query

Uno svantaggio che può emergere è quello di non avere un SQL leggerissimo, poichè ingrandisce all'aumentare dei filtri definiti.
Ovviamente, per ottenere prestazioni elevate è necessario definire correttamente gli indici sulla/e tabella/e.

Ecco l'esempio:


-- Tutti

exec GetBooks

 

-- ricerca per nome

exec GetBooks     @Name = 'SQL Server for dummies'

 

-- ricerca per numero di pagine

exec GetBooks     @Pages = 300

 

-- ricerca per tutti i valori

exec GetBooks     @Name = 'SQL Server for dummies',@Category = 1, @Author = 1, @Publisher = 1, @Pages = 150

 
Ed ecco i resultset:



 

Categoria: Transact-SQL
domenica, 05 nov 2006 Ore. 21.28
Statistiche
  • Views Home Page: 485.526
  • Views Posts: 875.184
  • Views Gallerie: 500.849
  • n° Posts: 484
  • n° Commenti: 273



















Copyright © 2002-2007 - Blogs 2.0
dotNetHell.it | Home Page Blogs
ASP.NET 2.0 Windows 2003