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...