Les common table expression són taules temporals residents en memòria enlloc de la tempdb. Això fa que el seu rendiment sigui molt bo.

De fet són tan temporals que només es poden fer servir en la sentència SQL següent a on es declaren. La sintaxi de la declaració és senzilla ja que només es posa el nom de les columnes sense tipus (ja l'agafen del resultat de la consulta en la que es basa). En el següent exemple es pot veure l'ús d'un CTE en combinació amb la funció ROW_NUMBER, ordenació, i posterior obtenció d'una pàgina dels resultats.

ALTER PROCEDURE [dbo].[DI_EXP_ORG_QUERY]
@pPK_Org int, @pSortColumn NVARCHAR(50) = null,
@pPageSize INT = 10,
@pPageNum INT = 1
AS
BEGIN
SET NOCOUNT ON;
--Declaració del CTE
WITH PagingCTE
(--Les columnes van sense tipus, només el nom
Row_ID,
PK_Org,
Nom,
Descripcio)
AS
(--La consulta en la que es basa va entre parentesis
SELECT
ROW_NUMBER()--Aqui es fa servir la funció Row_Number, i s'especifica aqui el ORDER BY
OVER(ORDER BY
CASE WHEN @pSortColumn = 'PK_OrgASC' THEN DI_EXP_ORG.PK_Org END ASC,
CASE WHEN @pSortColumn = 'PK_OrgDESC' THEN DI_EXP_ORG.PK_Org END DESC,
CASE WHEN @pSortColumn = 'NomASC' THEN DI_EXP_ORG.Nom END ASC,
CASE WHEN @pSortColumn = 'NomDESC' THEN DI_EXP_ORG.Nom END DESC,
CASE WHEN @pSortColumn = 'DescripcioASC' THEN DI_EXP_ORG.Descripcio END ASC,
CASE WHEN @pSortColumn = 'DescripcioDESC' THEN DI_EXP_ORG.Descripcio END DESC
)
AS [Row_ID],
DI_EXP_ORG.PK_Org,
DI_EXP_ORG.Nom,
DI_EXP_ORG.Descripcio
 FROM DI_EXP_ORG WHERE ((@pPK_Org IS NULL) OR (DI_EXP_ORG.PK_Org=@pPK_Org))
)--Aqui acaba el CTE, es fa servir just en la consulta següent
SELECT
(SELECT COUNT(*) FROM PagingCTE) AS NumRegs,
PK_Org,
Nom,
Descripcio FROM PagingCTE
--En el CTE ja s'ha fet el filtrat de registres, en el seu WHERE,
--En aquest altra WHERE el que es fa és obtenir un pàgina dels registres (la mida de la pag. ve donada per el pagesize)
WHERE Row_ID >= (@pPageSize * @pPageNum) - (@pPageSize -1) AND Row_ID <= @pPageSize * @pPageNum
 ORDER BY Row_ID
END

Com es pot veure la consulta és senzilla, no té cap JOIN, la taula té pocs registres i el WHERE és limitat. Per tant es pot fer servir la combinació CTE-ROW_NUMBER sense problema.

Aquestes funcions d'SQL Server serveixen per numerar registres per tal de posteriorment extreure'ls paginats. Funcionen de forma similar, però tenen una subtil diferència que fa que a vegades haguem de fer servir una o l'altra. El primer de tot és entendre el seu funcionament, comencem per la Row_Number.

El Row_Number numera els registres consecutivament, 1, 2, 3, 4... Si els registres són iguals (mateixos valors) el seu Row_Number és diferent. Es poden donar registres amb els mateixos valors si, per exemple, la consulta té un JOIN. Llavors el problema es dóna quan es fa un GROUP BY o un DISTINCT per eliminar els duplicats, com el ROW_NUMBER és diferent per tots els registres aleshores el GROUP BY/DISTINCT no elimina duplicats.

Per altra banda tenim el Dense_Rank. Requereix més càlculs (és més costos) ja que només assigna un número nou a un registre si té valors diferents. Per tant encara que hagi duplicats, al tenir el mateix valor de Dense_Rank un GROUP BY/DISTINCT posterior eliminarà aquests registres sense problema. També té un altra problema (al menys el tenia quan el vaig fer servir) que amb taules amb molts registres (>500.000) no calculava bé el seu valor.

Aquest post forma part d'un conjunt de posts que defineixen la infraestructura SQLServer de la classe d'accés a dades.

Fins ara totes les stored procedures han estat senzilles. Amb el Query arriba la complexitat. Aquesta stored és l'encarregada d'obtenir N registres, corresponents a una pàgina concreta, i quan la taula està ordenada d'una determinada manera (per un camp concret i amb un sentit (Asc/Desc).

Com que hi ha varies operacions es poden dur a terme de diferents formes. Aquestes diferents formes de fer el mateix depèn del tipus de consulta (amb o sense JOIN), del volum de dades (hi ha funcions d'SQLServer com el DenseRank que té un número de files a partir de la que no funciona bé), de la quantitat de paràmetres del WHERE. Millor una imatge.

Entrarem amb més detall en els diferents tipus de la stored QUERY en altres posts: CTE Row_Number, CTE Dense_Rank, Taula temporal/subquery, Taula temporal dinàmic.

Aquest post forma part d'un conjunt de posts que defineixen la infraestructura SQLServer de la classe d'accés a dades.

Per fer el CRUD sencer aquesta també és necessària. El DEL fa servir els mateixos paràmetres de sel·lecció de registres que el GET, sempre i quan pertanyin a la taula sobre la que es fa l'operació. D'aquesta manera es cobreixen més operacions que la simple eliminació per PK (encara que també es fa servir).

I com sempre l'exemple del codi:

DELETE FROM DI_EXP_DESTRUCCIO
 WHERE
 ((@pPK_Destruccio IS NULL) OR (PK_Destruccio=@pPK_Destruccio)) AND
 ((@pFK_TAAD IS NULL) OR (DI_EXP_DESTRUCCIO.FK_TAAD = @pFK_TAAD))
 AND ((@pFK_Estat IS NULL) OR (DI_EXP_DESTRUCCIO.FK_Estat = @pFK_Estat))
 AND ((@pDataInici IS NULL) OR (DI_EXP_DESTRUCCIO.Data_Destruccio BETWEEN @pDataInici AND @pDataFi))
 AND ((@pFK_Arxiu IS NULL) OR (DI_EXP_DESTRUCCIO.FK_Arxiu = @pFK_Arxiu))

Aquest post forma part d'un conjunt de posts que defineixen la infraestructura SQLServer de la classe d'accés a dades.

Aquesta stored s'encarrega d'actualitzar un únic registre. Per fer UPDATES masius s'ha de fer fora d'storeds, en el programa que s'estigui fent. De forma similar a l'INS, aquesta stored rep tots els camps de la taula, però la clau primària (PK) va informada d'entrada enlloc de ser un paràmetre de sortida, així es posa en el WHERE per afectar només un registre.

El codi queda així:

ALTER PROCEDURE [dbo].[DI_EXP_DESTRUCCIO_UPD]
 @pPK_Destruccio int,
 @pFK_TAAD int,
 @pFK_Arxiu int,
 @pAny_obertura int,
 @pAny_tancament int,
 @pMetres decimal(5, 1),
 @pData_Destruccio datetime,
 @pObservacions varchar(2000),
 @pbCataleg nchar(20),
 @pSerie_documental varchar(500),
 @pMK_UsuariAlta varchar(50),
 @pMK_DataAlta datetime,
 @pMK_UsuariModif varchar(50),
 @pMK_DataModif datetime, @pFK_Estat int,
 @pNumExpedients int,
 @pFK_Classificacio int,
 @pFK_Especifica int
 AS BEGIN
 UPDATE DI_EXP_DESTRUCCIO SET
--Tots els camps menys la PK
 FK_TAAD=@pFK_TAAD, FK_Arxiu=@pFK_Arxiu,
 Any_obertura=@pAny_obertura, Any_tancament=@pAny_tancament,
 Metres=@pMetres, Data_Destruccio=@pData_Destruccio,
 Observacions=@pObservacions, bCataleg=@pbCataleg,
 Serie_documental=@pSerie_documental,
 MK_UsuariAlta=@pMK_UsuariAlta, MK_DataAlta=@pMK_DataAlta,
 MK_UsuariModif=@pMK_UsuariModif, MK_DataModif=@pMK_DataModif,
 FK_Estat=@pFK_Estat, NumExpedients=@pNumExpedients,
 FK_Classificacio=@pFK_Classificacio, FK_Especifica=@pFK_Especifica
--En el WHERE va la PK
 WHERE (PK_Destruccio=@pPK_Destruccio)
END

Aquest post forma part d'un conjunt de posts que defineixen la infraestructura SQLServer de la classe d'accés a dades.

L'stored procedure que s'ocupa d'insertar dades a la taula és força senzilla. Té tants paràmetres d'entrada com camps té la taula. La clau primària (PK) en cas de ser una identitat és un paràmetre de sortida. Sort que tot això es genera de forma automàtica que fer-ho a mà i que cuadri tot...

El codi no rebesteix cap dificultat:

ALTER PROCEDURE [dbo].[DI_EXP_DESTRUCCIO_INS]
--La PK va com a OUTPUT
@pPK_Destruccio int OUTPUT,
@pFK_TAAD int, 
@pFK_Arxiu int, 
@pAny_obertura int, 
@pAny_tancament int, 
@pMetres decimal(5, 1), @pData_Destruccio datetime, 
@pObservacions varchar(2000), 
@pbCataleg nchar(20), 
@pSerie_documental varchar(500), 
@pMK_UsuariAlta varchar(50), 
@pMK_DataAlta datetime, @pMK_UsuariModif varchar(50), 
@pMK_DataModif datetime, @pFK_Estat int, 
@pNumExpedients int, 
@pFK_Classificacio int, 
@pFK_Especifica int AS
BEGIN
 INSERT INTO DI_EXP_DESTRUCCIO (
 FK_TAAD, 
 FK_Arxiu, 
 Any_obertura, 
 Any_tancament, 
 Metres, 
 Data_Destruccio, 
 Observacions, 
 bCataleg, 
 Serie_documental, 
 MK_UsuariAlta, 
 MK_DataAlta, 
 MK_UsuariModif, 
 MK_DataModif, 
 FK_Estat, 
 NumExpedients, 
 FK_Classificacio, 
 FK_Especifica
 ) VALUES (
--La PK no es posa aqui
 @pFK_TAAD, 
 @pFK_Arxiu, 
 @pAny_obertura, 
 @pAny_tancament, 
 @pMetres, 
 @pData_Destruccio, 
 @pObservacions, 
 @pbCataleg, 
 @pSerie_documental, 
 @pMK_UsuariAlta, 
 @pMK_DataAlta, 
 @pMK_UsuariModif, 
 @pMK_DataModif, 
 @pFK_Estat, 
 @pNumExpedients, 
 @pFK_Classificacio, 
 @pFK_Especifica
 );
--Aqui es recupera la PK que s'ha generat (només amb les identitats)
SET @pPK_Destruccio = IDENT_CURRENT('DI_EXP_DESTRUCCIO')
END

Aquest post forma part d'un conjunt de posts que defineixen la infraestructura SQLServer de la classe d'accés a dades.

GET: obté un registre (o conjunt de registres), té com paràmetre la primary key (PK) de la taula (si està informat segur que només torna un (o cap) registre). També pot tenir algun paràmetre extra, que en cas de ser de tipus nvarchar/nchar es farà un "like" al "where", això és necessari pels autocompletes. A vegades també és necessari incloure alguna dada que no està en la propia taula. Per exemple quan es recupera un municipi, ens interessa el nom de la comarca (no només el idcomarca), aleshores es fa un "left join". Tots els paràmetres són opcionals. El GET està disponible en 2 implementacions, no és el mateix fer un GET on el "where" té 3 paràmetres que un on té 20 paràmetres (es a dir es pot filtrar per 20 camps diferents de la taula).

Veiem el codi d'un GET senzill:

ALTER PROCEDURE [dbo].[T_MUNICIPI_GET] @pIDMUNICIPI bigint , @pIDCOMARCA int, @pNOMMUNICIPI nvarchar(50) AS BEGIN
 SELECT T_MUNICIPI.IDMUNICIPI,
T_MUNICIPI.IDCOMARCA,
T_MUNICIPI.IDDIPUTACIO,
T_MUNICIPI.NOMMUNICIPI,
T_MUNICIPI.CODMUNICIPI,
T_MUNICIPI.CODPOSTAL,
T_MUNICIPI.NOMCURTMUN,
T_MUNICIPI.NOMALT1MUN,
T_MUNICIPI.NOMALT2MUN,
T_MUNICIPI.NOMALT3MUN,
T_MUNICIPI.CAPITALCOM,
T_MUNICIPI.CAPITALDIP,
T_MUNICIPI.CODIMUN6,
T_COMARCA.NOMCOMARCA
 FROM T_MUNICIPI  LEFT OUTER JOIN T_COMARCA ON T_MUNICIPI.IDCOMARCA=T_COMARCA.IDCOMARCA
 WHERE ((@pIDMUNICIPI IS NULL) OR (IDMUNICIPI=@pIDMUNICIPI))
 AND ((@pIDCOMARCA IS NULL) OR (T_COMARCA.IDCOMARCA=@pIDCOMARCA))
 AND ((@pNOMMUNICIPI IS NULL) OR (T_MUNICIPI.NOMMUNICIPI LIKE @pNOMMUNICIPI + '%'))
 
ORDER BY T_MUNICIPI.NOMMUNICIPI
 
END

Pel que fa a un GET amb molts paràmetres al WHERE, el millor per no matar el rendiment es construir el SQL de forma dinàmica posant al WHERE només els paràmetres que estan informats. Perque encara que siguin opcionals (com en l'exemple anterior) penalitzen el rendiment. També és recomanable fer servir la sp_executesql ja que guardarà el pla d'execució i validarà els paràmetres.

ALTER PROCEDURE [dbo].[DI_EXP_EXPEDIENTARXIU_GET] 
--Aquest SELECT té un munt de paràmetres per els que filtrar, es munta dinàmicament
@pPK_ExpedientArxiu int, @pTitol varchar(255),
 @pExped_I varchar(50),
 @pAny_Obertura int,
 @pExped_F varchar(50),
 @pFK_LLocDiposit int,
 @pFK_Classificacio int,
 @pFK_Destruccio int,
 @pFK_Procedencia int,
 @pFK_Especifica int,
 @pAny_Tancament int,
 @pFK_Transferencia int,
 @pFK_Estat int,
 @pFK_Arxiu int,
 @pFK_TAAD int,
 @pFK_Uniforme int,
 @pFK_Capsa int,
 @pAny_OberturaMesGran int,
 @pAny_TancamentMenor int,
 @pCapsa varchar(30) AS
 BEGIN
SET NOCOUNT ON
DECLARE @strSQL nvarchar(MAX)
DECLARE @strPARAMS nvarchar(MAX)

SET @strSQL = 'SELECT DISTINCT dbo.DI_EXP_EXPEDIENTARXIU.PK_ExpedientArxiu,
dbo.DI_EXP_EXPEDIENTARXIU.FK_Procedencia,
dbo.DI_EXP_EXPEDIENTARXIU.FK_Classificacio,
dbo.DI_EXP_EXPEDIENTARXIU.FK_Uniforme,
dbo.DI_EXP_EXPEDIENTARXIU.FK_Especifica,
dbo.DI_EXP_EXPEDIENTARXIU.FK_Transferencia,
dbo.DI_EXP_EXPEDIENTARXIU.FK_LLocDiposit,
dbo.DI_EXP_EXPEDIENTARXIU.FK_Municipi,
dbo.DI_EXP_EXPEDIENTARXIU.FK_Estat,
dbo.DI_EXP_EXPEDIENTARXIU.FK_TransferenciaAH,
dbo.DI_EXP_EXPEDIENTARXIU.Titol,
dbo.DI_EXP_EXPEDIENTARXIU.Exped_I,
dbo.DI_EXP_EXPEDIENTARXIU.Exped_F,
dbo.DI_EXP_EXPEDIENTARXIU.Dia_Obertura,
dbo.DI_EXP_EXPEDIENTARXIU.Mes_Obertura,
dbo.DI_EXP_EXPEDIENTARXIU.Any_Obertura,
dbo.DI_EXP_EXPEDIENTARXIU.Dia_Tancament,
dbo.DI_EXP_EXPEDIENTARXIU.Mes_Tancament,
dbo.DI_EXP_EXPEDIENTARXIU.Any_Tancament,
dbo.DI_EXP_EXPEDIENTARXIU.U_Instal_I,
dbo.DI_EXP_EXPEDIENTARXIU.U_Instal_F,
dbo.DI_EXP_EXPEDIENTARXIU.Observacions_Unitat,
dbo.DI_EXP_EXPEDIENTARXIU.Volum,
dbo.DI_EXP_EXPEDIENTARXIU.Descripcio_AC,
dbo.DI_EXP_EXPEDIENTARXIU.Observacions_AC,
dbo.DI_EXP_EXPEDIENTARXIU.CodiTransferencia,
dbo.DI_EXP_EXPEDIENTARXIU.Adreca,
dbo.DI_EXP_EXPEDIENTARXIU.CP,
dbo.DI_EXP_EXPEDIENTARXIU.C,
dbo.DI_EXP_EXPEDIENTARXIU.MK_UsuariAlta,
dbo.DI_EXP_EXPEDIENTARXIU.MK_DataAlta,
dbo.DI_EXP_EXPEDIENTARXIU.MK_UsuariModif,
dbo.DI_EXP_EXPEDIENTARXIU.MK_DataModif,
dbo.DI_EXP_EXPEDIENTARXIU.SigTop_I,
dbo.DI_EXP_EXPEDIENTARXIU.SigTop_F,
dbo.DI_EXP_EXPEDIENTARXIU.FK_Destruccio,
dbo.DI_EXP_CLASSIFICACIO.Codi_TC,
dbo.DI_EXP_UNIFORME.Uniforme_TC,
dbo.DI_EXP_ESPECIFICA.Especifica_TC,
dbo.DI_EXP_PROCEDENCIA.Codi_TC AS Codi_Procedencia,
dbo.DI_EXP_PROCEDENCIA.N_NivellP,
dbo.DI_EXP_LLOCDIPOSIT.Nom AS Diposit,
dbo.DI_EXP_EXPEDIENTARXIU.FK_Arxiu,
dbo.DI_EXP_CAPSES.Capsa,
dbo.DI_EXP_LLOCDIPOSIT.Sigla,
dbo.DI_EXP_EXPEDIENTARXIU.CapsaHist,
dbo.DI_EXP_ESTAT.Nom AS Estat
 FROM DI_EXP_EXPEDIENTARXIU INNER JOIN DI_EXP_CLASSIFICACIO ON 
DI_EXP_EXPEDIENTARXIU.FK_Classificacio=DI_EXP_CLASSIFICACIO.PK_Classificacio
  LEFT OUTER JOIN DI_EXP_ESPECIFICA ON DI_EXP_EXPEDIENTARXIU.FK_Especifica=DI_EXP_ESPECIFICA.PK_Especifica 
 LEFT OUTER JOIN DI_EXP_UNIFORME ON DI_EXP_EXPEDIENTARXIU.FK_Uniforme=DI_EXP_UNIFORME.PK_Uniforme 
 INNER JOIN DI_EXP_PROCEDENCIA ON DI_EXP_EXPEDIENTARXIU.FK_Procedencia=DI_EXP_PROCEDENCIA.PK_Procedencia 
 LEFT OUTER JOIN DI_EXP_TAAD_REL ON DI_EXP_CLASSIFICACIO.PK_Classificacio=DI_EXP_TAAD_REL.FK_Classificacio 
 LEFT OUTER JOIN DI_EXP_CAPSA_EXPEDIENT ON
DI_EXP_EXPEDIENTARXIU.PK_ExpedientArxiu=DI_EXP_CAPSA_EXPEDIENT.FK_ExpedientArxiu 
 LEFT OUTER JOIN DI_EXP_CAPSES ON DI_EXP_CAPSA_EXPEDIENT.FK_Capsa=DI_EXP_CAPSES.PK_Capsa 
 LEFT OUTER JOIN DI_EXP_SIGTOP ON DI_EXP_CAPSES.PK_Capsa=DI_EXP_SIGTOP.FK_Capsa 
 LEFT OUTER JOIN DI_EXP_LLOCDIPOSIT ON DI_EXP_SIGTOP.FK_LlocDiposit=DI_EXP_LLOCDIPOSIT.PK_LLocDiposit 
 INNER JOIN DI_EXP_ESTAT ON DI_EXP_EXPEDIENTARXIU.FK_Estat=DI_EXP_ESTAT.PK_Estat 
 WHERE 1=1 '
--Segons si està informat el paràmetre es posa al WHERE o no
IF @pPK_ExpedientArxiu IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.PK_ExpedientArxiu=@lPK_ExpedientArxiu'
END

IF @pTitol IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.Titol LIKE @lTitol + ''%'''
END

IF @pExped_I IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.Exped_I LIKE @lExped_I + ''%'''
END

IF @pAny_Obertura IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.Any_Obertura = @lAny_Obertura'
END

IF @pExped_F IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.Exped_F LIKE @lExped_F + ''%'''
END

IF @pFK_LLocDiposit IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_SIGTOP.FK_LLocDiposit = @lFK_LLocDiposit'
END

IF @pFK_Classificacio IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Classificacio = @lFK_Classificacio'
END

IF @pFK_Destruccio IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Destruccio = @lFK_Destruccio'
END

IF @pFK_Procedencia IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Procedencia = @lFK_Procedencia'
END

IF @pFK_Especifica IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Especifica = @lFK_Especifica'
END

IF @pAny_Tancament IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.Any_Tancament = @lAny_Tancament'
END

IF @pFK_Transferencia IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Transferencia = @lFK_Transferencia'
END

IF @pFK_Estat IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Estat = @lFK_Estat'
END

IF @pFK_Arxiu IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Arxiu = @lFK_Arxiu'
END

IF @pFK_TAAD IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_TAAD_REL.FK_TAAD = @lFK_TAAD'
END

IF @pFK_Uniforme IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Uniforme = @lFK_Uniforme'
END

IF @pFK_Capsa IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_CAPSA_EXPEDIENT.FK_Capsa = @lFK_Capsa'
END

IF @pAny_OberturaMesGran IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.Any_Obertura >= @lAny_OberturaMesGran'
END

IF @pAny_TancamentMenor IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.Any_Tancament <= @lAny_TancamentMenor'
END

IF @pCapsa IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_CAPSES.Capsa LIKE @lCapsa + ''%'''
END

SET @strSQL = @strSQL + ' ORDER BY DI_EXP_CLASSIFICACIO.Codi_TC, Exped_I, Any_Obertura'


--PARAMS--
SET @strPARAMS = '@lPK_ExpedientArxiu int, 
@lTitol varchar(255), 
@lExped_I varchar(50), 
@lAny_Obertura int, 
@lExped_F varchar(50), 
@lFK_LLocDiposit int, 
@lFK_Classificacio int, 
@lFK_Destruccio int, 
@lFK_Procedencia int, 
@lFK_Especifica int, 
@lAny_Tancament int, 
@lFK_Transferencia int, 
@lFK_Estat int, 
@lFK_Arxiu int, 
@lFK_TAAD int, 
@lFK_Uniforme int, 
@lFK_Capsa int, 
@lAny_OberturaMesGran int, 
@lAny_TancamentMenor int, 
@lCapsa varchar(30)'

--EXECUTE--
EXECUTE sp_executesql @strSQL, @strPARAMS,
 @pPK_ExpedientArxiu,
 @pTitol, @pExped_I,
 @pAny_Obertura,
 @pExped_F,
 @pFK_LLocDiposit,
 @pFK_Classificacio,
 @pFK_Destruccio,
 @pFK_Procedencia,
 @pFK_Especifica,
 @pAny_Tancament,
 @pFK_Transferencia,
 @pFK_Estat,
 @pFK_Arxiu,
 @pFK_TAAD,
 @pFK_Uniforme,
 @pFK_Capsa,
 @pAny_OberturaMesGran,
 @pAny_TancamentMenor,
 @pCapsa;
END

Les següents storeds les veurem en altres posts.

Aquest és el primer post que tracta de la capa d'acces a BBDD que he desenvolupat per fer aplicacions web (o no web). La gràcia que té es que està generada automàticament per un programa, es a dir, donada una configuració guardada en un fitxer XML es generen les "stored procedures" automàticament, les classes d'accés a dades també automàticament (en C#) (un exemple), tot gestionat per la clsDades. La primera vegada es generen les stored a partir dels camps de les taules, només cal afegir la lógica de negoci. Per explicar aquest muntatge he pensat que el millor per començar és el que està més aprop de la BBDD, i que es comunica directament amb les dades. En el nostre cas són les "stored procedures", posteriorment la clase de C# que les gestiona.

Tota aquesta infraestructura que es descriu al llarg d'aquests posts serveix per desenvolupar aplicacions, habitualment web. Hi ha una descripció de tot plegat aquí.

Cada taula té associats un conjunt d'SP que permeten accedir a les dades: GET, GETTOP, COUNT, INS, UPD, DEL, QUERY. Veurem en detall cada una d'elles, encara que pel nom ja es pot intuir que fan. Com són les mateixes per cada taula tenen el nom de la taula davant. Tindriem llavors CLIENTS_GET, CLIENTS_INS, CLIENTS_UPD, CLIENTS_DEL...

Ok, ja tenim les imatges sel·leccionades, posades en una estructura FormData i enviades al servidor. Ara doncs és el moment de guardar-les on toca i posar-les a la BBDD (només el path). Sempre he estat de la filosofia que qui millor guarda els fitxers són els sistemes de fitxers, les BBDD les deixo sempre per dades (cadenes de text, números etc... no fitxers). Així doncs la rutina següent fa:

1. Gestió de la carpeta on es guarden els fitxers.
2. Un bucle que per cada imatge...
3. La guarda a disc
4. Insereix les seves dades a la BBDD

        private void GetFileAndSave()
        {
            try
            {
                Resp.ContentType = "plain/text";
                string Arrel = Servr.MapPath("/");
                string Any = DateTime.Now.Year.ToString();
                string Mes = DateTime.Now.Month.ToString();

                clsImatges Imatges = new clsImatges(ConnStr);
                Dictionary<string, string> Params = new Dictionary<string, string>();
                string Imatge;
                string IdPost = Req["IdPost"];
                string HDDPathImgs;

                //Arrel acaba en / per això no cal concatenar-lo
                HDDPathImgs = Arrel + PathImgs + "/" + Any + "/" + Mes;

                if (!Directory.Exists(HDDPathImgs))
                {
                    Directory.CreateDirectory(HDDPathImgs);
                }

                foreach (string strFile in Req.Files)
                {
                    HttpPostedFile postedFile = Req.Files[strFile];
                    Imatge = HDDPathImgs + "/" + postedFile.FileName;
                    if (!File.Exists(Imatge))
                    {
                        postedFile.SaveAs(Imatge);

                        Params.Add("Imatge", Imatge);
                        Params.Add("IdPost", IdPost);
                        Imatges.INS(Params);
                        Params.Clear();
                    }
                }
                Resp.Write("OK");

            }
            catch (Exception Err)
            {
                Resp.Write("ERROR: " + Err.Message);
            }
        }

Un cop guardades les imatges al servidor, el javascript pregunta quines imatges té associades el post per posar-les i que es puguin fer servir. Es fa servir una clase per després serialitzar en JSON. D'això s'encarrega la següent rutina:

    public class clsImatgeJSON
    {
        public string IdImatge { get; set; }
        public string PathImatge { get; set; }
    }
        private void LoadData()
        {
            Resp.ContentType = "application/json";
            clsImatges Imatges = new clsImatges(ConnStr);
            Dictionary<string, string> Params = new Dictionary<string, string>();
            DataTable tblImatges;
            string IdPost = Req["IdPost"];
            List<clsImatgeJSON> ImatgesJSON = new List<clsImatgeJSON>();
            clsImatgeJSON ImatgeJSON;
            string Arrel = Servr.MapPath("/");

            Params.Add("IdPost", IdPost);
            tblImatges = Imatges.GET(Params);
            foreach (DataRow DR in tblImatges.Rows)
            {
                ImatgeJSON = new clsImatgeJSON();
                ImatgeJSON.IdImatge = DR["IdImatge"].ToString();
                ImatgeJSON.PathImatge = DR["Imatge"].ToString().Replace(Arrel, "/");

                ImatgesJSON.Add(ImatgeJSON);
            }

            Resp.Write(clsJSON.Serialize(ImatgesJSON));
        }

Una altra funcionalitat que cobreix la part servidora és la eliminació de fitxers (peticions que també arriben via Ajax). S'ha d'eliminar de la BBDD i del sistema de fitxers.

        private void Delete()
        {
            try
            {
                Resp.ContentType = "plain/text";
                string IdImatge = Req["IdImatge"];
                clsImatges Imatges = new clsImatges(ConnStr);
                Dictionary<string, string> Params = new Dictionary<string, string>();
                DataTable tblImatges;

                Params.Add("IdImatge", IdImatge);
                tblImatges = Imatges.GET(Params);
                if (File.Exists(tblImatges.Rows[0]["Imatge"].ToString()))
                {
                    File.Delete(tblImatges.Rows[0]["Imatge"].ToString());
                    Imatges.DEL(Params);
                    Resp.Write("OK");
                }
                else
                {
                    Resp.Write("ERROR: Fitxer no trobat");
                }
                
            }
            catch (Exception Err)
            {
                Resp.Write("ERROR: " + Err.Message);
            }
        }

L'acces a BBDD es fa mitjançant la capa d'acces a BBDD que faig servir habitualment, de la que ja parlaré en un altra post.

M'agrada citar el que diuen en altres llocs, però a vegades posar un enllaç no és gaire útil perque has d'anar a l'altra pàgina (que amb els anys ves a saber si funciona) i clar, fer un copy&paste del text d'altres dins del teu sense que es distingeixi doncs queda lleig. Sembla que estiguis atribuint-te l'autoria del text quan en realitat no és així.

Per això existeix el tag blockquote, ja existia en HTML4 però ara han refinat i ajustat millor el seu us. Juntament amb CSS3 s'aconsegueix un efecte que està força bé (almenys ho he vist en FFox i IE10). També han fet que s'hagi d'afegir el tag <footer> (peu) i dins del peu posar un tag <cite> per posar un enllaç a l'autor de la cita.

Veiem les definicions actuals dels elements i aprofitarem per posar uns exemples.

The blockquote element represents content that is quoted from another source, optionally with a citation which must be within a footer or cite element, and optionally with in-line changes such as annotations and abbreviations.
Content inside a blockquote other than citations and in-line changes must be quoted from another source, whose address, if it has one, may be cited in the cite attribute.

The cite element represents a reference to a creative work. It must include the title of the work or the name of the author (person, people or organization) or an URL reference, which may be in an abbreviated form as per the conventions used for the addition of citation metadata.