Aquesta funció de la clsDades s'encarrega de transformar els paràmetres informats a paràmetres SqlServer i invocar a la stored procedure GET, obtenint els resultats en un taula (objecte DataTable). Per fer-ho fa servir la funció de creació de paràmetres amb la informació de la variable ParamsGET que és tipus clsCamp (es pot veure un exemple aquí.).

Si no està informat l'objecte connexió a BBDD en crea una nova fent servir l'string CadConn, i la tanca posteriorment (es a dir, per un accés puntual es pot fer servir directament). Si, al contrari, ja hi ha un objecte connexió creat el fa servir sense modificar-ne l'estat. Això és important ja que si es fan servir múltiples objectes de la clsDades i es vol que tots facin servir la mateixa connexió, sense anar obrint i tancant, caldrà informar-los la connexió (i de forma anàloga la transacció) abans de fer servir l'objecte. Com sempre poso el codi per veure que fa.

public DataTable GET(Dictionary<string, string> Params)
{
    SqlDataReader Reader = null;
    DataTable Result = new DataTable();
    bool LocalConn;
    SqlConnection Conn = null;

    //SQLConn és una variable SqlConnection de la clase, si no està instanciada cal crear-la
    if (SQLConn == null)
    {
	LocalConn = true;
	Conn = new SqlConnection(CadConnBBDD);
	Conn.Open();
    }
    else
    {
	LocalConn = false;
	Conn = SQLConn;
    }
    SqlCommand SqlComm = new SqlCommand();
    SqlParameter SqlParam;

    SqlComm.Connection = Conn;
    if (SQLTrans != null) SqlComm.Transaction = SQLTrans;

    SqlComm.CommandType = CommandType.StoredProcedure;
    SqlComm.CommandText = spGET;

    //Es creen tots els paràmetres i informant els valors dels que existeixen
    foreach (clsCamp Camp in ParamsGET)
    {
	SqlParam = CreaParametre(Camp, false);
	if (Params.Keys.Contains<string>(Camp.NomCamp))
	{
	    if (string.IsNullOrEmpty(Params[Camp.NomCamp])) SqlParam.Value = DBNull.Value;
	    else SqlParam.Value = Params[Camp.NomCamp];
	}
	else SqlParam.Value = DBNull.Value;
	SqlComm.Parameters.Add(SqlParam);
    }

    
    Reader = SqlComm.ExecuteReader();

    Result.Load(Reader);

    Reader.Close();

    if (LocalConn)
    {
	Conn.Close();
	Conn.Dispose();
	Conn = null;
    }
    return Result;
}

Dins de la clsDades hi ha aquesta és una petita funció que s'encarrega de crear un paràmetre de SQLServer a partir de la informació guardada en un objecte clsCamp. L'única variació que té és en el INSERT, que quan es tracta d'una identitat cal recuperar el valor que es genera. Per això en aquest cas es posa el paràmetre de sortida (output). Aquesta funció es fa servir en la resta de funcions que veurem, les que invoquen a les seves corresponents stored procedures. Poso el codi a continuació, es pot seguir fàcilment.

private SqlParameter CreaParametre(clsCamp Camp, bool INSERT)
{
    SqlParameter SqlParam;
    SqlParam = new SqlParameter();

    //Si és un insert interessa recuperar el valor de la identitat, el paràmetre ha de ser output.
    if (INSERT)
    {
	if ((TeIdentitat) && (Camp.NomCamp == Identitat)) SqlParam.Direction = ParameterDirection.Output;
	else SqlParam.Direction = ParameterDirection.Input;
    } //Tots els altres sempre input.
    else SqlParam.Direction = ParameterDirection.Input;

    SqlParam.ParameterName = Prefix + Camp.NomCamp;

    switch (Camp.Tipus)
    {
	case Tipus.bit:
	    SqlParam.SqlDbType = SqlDbType.Bit;
	    break;
	case Tipus.tint:
	    SqlParam.SqlDbType = SqlDbType.Int;
	    break;
	case Tipus.bigint:
	    SqlParam.SqlDbType = SqlDbType.BigInt;
	    break;
	case Tipus.nvarchar:
	    SqlParam.SqlDbType = SqlDbType.NVarChar;
	    SqlParam.Size = Camp.Longitud;
	    break;
	case Tipus.date:
	    SqlParam.SqlDbType = SqlDbType.Date;
	    break;
	case Tipus.time:
	    SqlParam.SqlDbType = SqlDbType.Time;
	    break;
	case Tipus.datetime:
	    SqlParam.SqlDbType = SqlDbType.DateTime;
	    break;
	case Tipus.nchar:
	    SqlParam.SqlDbType = SqlDbType.NChar;
	    SqlParam.Size = Camp.Longitud;
	    break;
	case Tipus.dec:
	    SqlParam.SqlDbType = SqlDbType.Decimal;
	    SqlParam.Precision = Camp.Precisio;
	    SqlParam.Scale = Camp.Escala;
	    break;
	case Tipus.chr:
	    SqlParam.SqlDbType = SqlDbType.Char;
	    SqlParam.Size = Camp.Longitud;
	    break;
	case Tipus.varchar:
	    SqlParam.SqlDbType = SqlDbType.VarChar;
	    SqlParam.Size = Camp.Longitud;
	    break;
    }
    return SqlParam;
}

La clase clsDades, que veurem en detall, conté els mètodes per invocar les SP i les declaracions de variables que fan servir aquests mètodes. En canvi una classe derivada només té les inicialitzacions d'aquestes variables. El programa que genera la capa de dades crea automàticament aquestes inicialitzacions. Es a dir que el següent tros de programa està generat, no picat. Poso un exemple qualsevol, és només indicatiu.

public class clsClassificacio : clsDades
{
	/// <summary>
	/// Constructor de la clase que gestiona l'acces a dades
	/// </summary>
	/// <param name="pCadConnBBDD">Cadena conexio</param>
	public clsClassificacio(string pCadConnBBDD)
	{
		spINS = "DI_EXP_CLASSIFICACIO_INS";
		spUPD = "DI_EXP_CLASSIFICACIO_UPD";
		spGET = "DI_EXP_CLASSIFICACIO_GET";
		spCOUNT = "DI_EXP_CLASSIFICACIO_COUNT";
		spQUERY = "DI_EXP_CLASSIFICACIO_QUERY";
		spDEL = "DI_EXP_CLASSIFICACIO_DEL";
		TeIdentitat = true;
		Identitat = "PK_Classificacio";
		NomTaula = "DI_EXP_CLASSIFICACIO";
		TeCache = false;
		CadConnBBDD = pCadConnBBDD;
		ParamsGET = new clsCamp[7];
		for (int i = 0; i < 7; i++) ParamsGET[i] = new clsCamp();
	#region Llista params GET
		ParamsGET[0].NomCamp = "PK_Classificacio";
		ParamsGET[0].Tipus = Tipus.tint;
		ParamsGET[0].PK = true;
		ParamsGET[0].Identitat = true;
		ParamsGET[0].Nulable = false;
		ParamsGET[1].NomCamp = "Nom";
		ParamsGET[1].Tipus = Tipus.nvarchar;
		ParamsGET[1].Longitud = 200;
		ParamsGET[1].Nulable = false;
		ParamsGET[2].NomCamp = "PK_Arxiu";
		ParamsGET[2].Tipus = Tipus.tint;
		ParamsGET[3].NomCamp = "Codi_TC";
		ParamsGET[3].Tipus = Tipus.nvarchar;
		ParamsGET[3].Longitud = 5;
		ParamsGET[3].Nulable = false;
		ParamsGET[4].NomCamp = "Nivell_Codi";
		ParamsGET[4].Tipus = Tipus.nvarchar;
		ParamsGET[4].Longitud = 100;
		ParamsGET[4].Nulable = false;
		ParamsGET[5].NomCamp = "IdPare";
		ParamsGET[5].Tipus = Tipus.tint;
		ParamsGET[5].Nulable = false;
		ParamsGET[6].NomCamp = "FK_TAAD";
		ParamsGET[6].Tipus = Tipus.tint;
	#endregion
		ParamsINS = new clsCamp[10];
		for (int i = 0; i < 10; i++) ParamsINS[i] = new clsCamp();
	#region Llista params INS
		ParamsINS[0].NomCamp = "PK_Classificacio";
		ParamsINS[0].Tipus = Tipus.tint;
		ParamsINS[0].PK = true;
		ParamsINS[0].Identitat = true;
		ParamsINS[0].Nulable = false;
		ParamsINS[1].NomCamp = "Codi_TC";
		ParamsINS[1].Tipus = Tipus.varchar;
		ParamsINS[1].Longitud = 5;
		ParamsINS[1].Nulable = false;
		ParamsINS[2].NomCamp = "Observacions";
		ParamsINS[2].Tipus = Tipus.varchar;
		ParamsINS[2].Longitud = 3000;
		ParamsINS[3].NomCamp = "DataAlta";
		ParamsINS[3].Tipus = Tipus.datetime;
		ParamsINS[4].NomCamp = "DataBaixa";
		ParamsINS[4].Tipus = Tipus.datetime;
		ParamsINS[5].NomCamp = "Any_inici";
		ParamsINS[5].Tipus = Tipus.tint;
		ParamsINS[5].Nulable = false;
		ParamsINS[6].NomCamp = "Any_final";
		ParamsINS[6].Tipus = Tipus.tint;
		ParamsINS[6].Nulable = false;
		ParamsINS[7].NomCamp = "IdPare";
		ParamsINS[7].Tipus = Tipus.tint;
		ParamsINS[7].Nulable = false;
		ParamsINS[8].NomCamp = "Nom";
		ParamsINS[8].Tipus = Tipus.nvarchar;
		ParamsINS[8].Longitud = 200;
		ParamsINS[8].Nulable = false;
		ParamsINS[9].NomCamp = "Nivell_Codi";
		ParamsINS[9].Tipus = Tipus.varchar;
		ParamsINS[9].Longitud = 100;
		ParamsINS[9].Nulable = false;
	#endregion
		ParamsUPD = new clsCamp[10];
		for (int i = 0; i < 10; i++) ParamsUPD[i] = new clsCamp();
	#region Llista params UPD
		ParamsUPD[0].NomCamp = "PK_Classificacio";
		ParamsUPD[0].Tipus = Tipus.tint;
		ParamsUPD[0].PK = true;
		ParamsUPD[0].Identitat = true;
		ParamsUPD[0].Nulable = false;
		ParamsUPD[1].NomCamp = "Codi_TC";
		ParamsUPD[1].Tipus = Tipus.varchar;
		ParamsUPD[1].Longitud = 5;
		ParamsUPD[1].Nulable = false;
		ParamsUPD[2].NomCamp = "Observacions";
		ParamsUPD[2].Tipus = Tipus.varchar;
		ParamsUPD[2].Longitud = 3000;
		ParamsUPD[3].NomCamp = "DataAlta";
		ParamsUPD[3].Tipus = Tipus.datetime;
		ParamsUPD[4].NomCamp = "DataBaixa";
		ParamsUPD[4].Tipus = Tipus.datetime;
		ParamsUPD[5].NomCamp = "Any_inici";
		ParamsUPD[5].Tipus = Tipus.tint;
		ParamsUPD[5].Nulable = false;
		ParamsUPD[6].NomCamp = "Any_final";
		ParamsUPD[6].Tipus = Tipus.tint;
		ParamsUPD[6].Nulable = false;
		ParamsUPD[7].NomCamp = "IdPare";
		ParamsUPD[7].Tipus = Tipus.tint;
		ParamsUPD[7].Nulable = false;
		ParamsUPD[8].NomCamp = "Nom";
		ParamsUPD[8].Tipus = Tipus.nvarchar;
		ParamsUPD[8].Longitud = 200;
		ParamsUPD[8].Nulable = false;
		ParamsUPD[9].NomCamp = "Nivell_Codi";
		ParamsUPD[9].Tipus = Tipus.varchar;
		ParamsUPD[9].Longitud = 100;
		ParamsUPD[9].Nulable = false;
	#endregion
		ParamsDEL = new clsCamp[7];
		for (int i = 0; i < 7; i++) ParamsDEL[i] = new clsCamp();
	#region Llista params DEL
		ParamsDEL[0].NomCamp = "PK_Classificacio";
		ParamsDEL[0].Tipus = Tipus.tint;
		ParamsDEL[0].PK = true;
		ParamsDEL[0].Identitat = true;
		ParamsDEL[0].Nulable = false;
		ParamsDEL[1].NomCamp = "Nom";
		ParamsDEL[1].Tipus = Tipus.nvarchar;
		ParamsDEL[1].Longitud = 200;
		ParamsDEL[1].Nulable = false;
		ParamsDEL[2].NomCamp = "PK_Arxiu";
		ParamsDEL[2].Tipus = Tipus.tint;
		ParamsDEL[3].NomCamp = "Codi_TC";
		ParamsDEL[3].Tipus = Tipus.nvarchar;
		ParamsDEL[3].Longitud = 5;
		ParamsDEL[3].Nulable = false;
		ParamsDEL[4].NomCamp = "Nivell_Codi";
		ParamsDEL[4].Tipus = Tipus.nvarchar;
		ParamsDEL[4].Longitud = 100;
		ParamsDEL[4].Nulable = false;
		ParamsDEL[5].NomCamp = "IdPare";
		ParamsDEL[5].Tipus = Tipus.tint;
		ParamsDEL[5].Nulable = false;
		ParamsDEL[6].NomCamp = "FK_TAAD";
		ParamsDEL[6].Tipus = Tipus.tint;
	#endregion
		ParamsQUERY = new clsCamp[7];
		for (int i = 0; i < 7; i++) ParamsQUERY[i] = new clsCamp();
	#region Llista params QUERY
		ParamsQUERY[0].NomCamp = "PK_Classificacio";
		ParamsQUERY[0].Tipus = Tipus.tint;
		ParamsQUERY[0].PK = true;
		ParamsQUERY[0].Identitat = true;
		ParamsQUERY[0].Nulable = false;
		ParamsQUERY[1].NomCamp = "Nom";
		ParamsQUERY[1].Tipus = Tipus.nvarchar;
		ParamsQUERY[1].Longitud = 200;
		ParamsQUERY[1].Nulable = false;
		ParamsQUERY[2].NomCamp = "PK_Arxiu";
		ParamsQUERY[2].Tipus = Tipus.tint;
		ParamsQUERY[3].NomCamp = "Codi_TC";
		ParamsQUERY[3].Tipus = Tipus.nvarchar;
		ParamsQUERY[3].Longitud = 5;
		ParamsQUERY[3].Nulable = false;
		ParamsQUERY[4].NomCamp = "Nivell_Codi";
		ParamsQUERY[4].Tipus = Tipus.nvarchar;
		ParamsQUERY[4].Longitud = 100;
		ParamsQUERY[4].Nulable = false;
		ParamsQUERY[5].NomCamp = "IdPare";
		ParamsQUERY[5].Tipus = Tipus.tint;
		ParamsQUERY[5].Nulable = false;
		ParamsQUERY[6].NomCamp = "FK_TAAD";
		ParamsQUERY[6].Tipus = Tipus.tint;
	#endregion
		CampsResultat = new clsCamp[10];
		for (int i = 0; i < 10; i++) CampsResultat[i] = new clsCamp();
	#region Camps resultat
		CampsResultat[0].NomCamp = "PK_Classificacio";
		CampsResultat[0].Tipus = Tipus.tint;
		CampsResultat[0].PK = true;
		CampsResultat[0].Identitat = true;
		CampsResultat[0].Nulable = false;
		CampsResultat[1].NomCamp = "Codi_TC";
		CampsResultat[1].Tipus = Tipus.varchar;
		CampsResultat[1].Longitud = 5;
		CampsResultat[1].Nulable = false;
		CampsResultat[2].NomCamp = "Observacions";
		CampsResultat[2].Tipus = Tipus.varchar;
		CampsResultat[2].Longitud = 3000;
		CampsResultat[3].NomCamp = "DataAlta";
		CampsResultat[3].Tipus = Tipus.datetime;
		CampsResultat[4].NomCamp = "DataBaixa";
		CampsResultat[4].Tipus = Tipus.datetime;
		CampsResultat[5].NomCamp = "Any_inici";
		CampsResultat[5].Tipus = Tipus.tint;
		CampsResultat[5].Nulable = false;
		CampsResultat[6].NomCamp = "Any_final";
		CampsResultat[6].Tipus = Tipus.tint;
		CampsResultat[6].Nulable = false;
		CampsResultat[7].NomCamp = "IdPare";
		CampsResultat[7].Tipus = Tipus.tint;
		CampsResultat[7].Nulable = false;
		CampsResultat[8].NomCamp = "Nom";
		CampsResultat[8].Tipus = Tipus.nvarchar;
		CampsResultat[8].Longitud = 200;
		CampsResultat[8].Nulable = false;
		CampsResultat[9].NomCamp = "Nivell_Codi";
		CampsResultat[9].Tipus = Tipus.varchar;
		CampsResultat[9].Longitud = 100;
		CampsResultat[9].Nulable = false;
	#endregion
		Serialitzar = new clsCamp[10];
		for (int i = 0; i < 10; i++) Serialitzar[i] = new clsCamp();
	#region Camps a serialitzar
		Serialitzar[0].NomCamp = "PK_Classificacio";
		Serialitzar[0].Tipus = Tipus.tint;
		Serialitzar[1].NomCamp = "Codi_TC";
		Serialitzar[1].Tipus = Tipus.varchar;
		Serialitzar[1].Longitud = 5;
		Serialitzar[2].NomCamp = "Observacions";
		Serialitzar[2].Tipus = Tipus.varchar;
		Serialitzar[2].Longitud = 3000;
		Serialitzar[3].NomCamp = "DataAlta";
		Serialitzar[3].Tipus = Tipus.datetime;
		Serialitzar[4].NomCamp = "DataBaixa";
		Serialitzar[4].Tipus = Tipus.datetime;
		Serialitzar[5].NomCamp = "Any_inici";
		Serialitzar[5].Tipus = Tipus.tint;
		Serialitzar[6].NomCamp = "Any_final";
		Serialitzar[6].Tipus = Tipus.tint;
		Serialitzar[7].NomCamp = "IdPare";
		Serialitzar[7].Tipus = Tipus.tint;
		Serialitzar[8].NomCamp = "Nom";
		Serialitzar[8].Tipus = Tipus.nvarchar;
		Serialitzar[8].Longitud = 200;
		Serialitzar[9].NomCamp = "Nivell_Codi";
		Serialitzar[9].Tipus = Tipus.varchar;
		Serialitzar[9].Longitud = 100;
	#endregion
		Deserialitzar = new clsCamp[9];
		for (int i = 0; i < 9; i++) Deserialitzar[i] = new clsCamp();
	#region Camps a Deserialitzar
		Deserialitzar[0].NomCamp = "Codi_TC";
		Deserialitzar[0].Tipus = Tipus.varchar;
		Deserialitzar[0].Longitud = 5;
		Deserialitzar[1].NomCamp = "Observacions";
		Deserialitzar[1].Tipus = Tipus.varchar;
		Deserialitzar[1].Longitud = 3000;
		Deserialitzar[2].NomCamp = "DataAlta";
		Deserialitzar[2].Tipus = Tipus.datetime;
		Deserialitzar[3].NomCamp = "DataBaixa";
		Deserialitzar[3].Tipus = Tipus.datetime;
		Deserialitzar[4].NomCamp = "Any_inici";
		Deserialitzar[4].Tipus = Tipus.tint;
		Deserialitzar[5].NomCamp = "Any_final";
		Deserialitzar[5].Tipus = Tipus.tint;
		Deserialitzar[6].NomCamp = "IdPare";
		Deserialitzar[6].Tipus = Tipus.tint;
		Deserialitzar[7].NomCamp = "Nom";
		Deserialitzar[7].Tipus = Tipus.nvarchar;
		Deserialitzar[7].Longitud = 200;
		Deserialitzar[8].NomCamp = "Nivell_Codi";
		Deserialitzar[8].Tipus = Tipus.varchar;
		Deserialitzar[8].Longitud = 100;
	#endregion
	}
}

Com es pot veure picar això a mà seria mortal, per aquest motiu hi ha un software que ho genera. Els paràmetres de les SP els obté de les pròpies SP, es a dir, han d'estar generades prèviament, abans de generar aquesta classe. La resta de llistes les obté a partir dels camps de la taula que estan al software generador. Si es marca un camp per serialitzar apareixerà a la llista de "Serialitzar", si es marca per deserialitzar... ja es veu la idea. Llavors la capa d'accés a BBDD es compon de la clsDades i un munt de classes derivades.

En aquesta clase, com cobreix molta funcionalitat, hi ha parts que només serveixen per projectes web i en canvi per projectes d'un altra tipus (per exemple WinForms) donen errors de compilació (per que no hi ha el HttpRequest). Per això he fet servir la compilació condicional, típica.

Aquesta compilació condicional afecta a la serialització, deserialització ja que aquests mètodes no es fan servir en projectes no web. Per això es defineix una constant (o símbol) a Projecte -> Propietats -> Build -> Conditional Compilation symbols. Cal vigilar de definir aquest símbol en totes les configuracions (Debug, Release...).

La clsDades conté les declaracions de les diferents variables que fa servir. La gran majoria són arrays de clsCamps. Poso el codi comentat on es veu clarament que fa cada un d'aquestes variables. En les classes que hereten de la clsDades s'inicialitzen les variables amb els valors corresponents a una taula concreta de la BBDD.

    public class clsDades
    {
        /// <summary>
        /// Prefix dels paràmetres de les stored procedures, 
        /// útil per distingir-los de variables declarades dins de la SP.
        /// </summary>
        public string Prefix = "p";

        /// <summary>
        /// Paràmetres del GET, també són params del GETTOP
        /// </summary>
        public clsCamp[] ParamsGET; 

        /// <summary>
        /// Paràmetres del INS
        /// </summary>
        public clsCamp[] ParamsINS;

        /// <summary>
        /// Paràmetres del UPD
        /// </summary>
        public clsCamp[] ParamsUPD;

        /// <summary>
        /// Paràmetres del DEL
        /// </summary>
        public clsCamp[] ParamsDEL;

        /// <summary>
        /// Paràmetres del QUERY
        /// </summary>
        public clsCamp[] ParamsQUERY;

        /// <summary>
        /// Camps que retorna el GET i que seràn columnes del DataTable resultant
        /// </summary>
        public clsCamp[] CampsResultat;

        /// <summary>
        /// Cadena de connexió a BBDD
        /// </summary>
        public string CadConnBBDD;

        /// <summary>
        /// Nom de la SP que fa el GET, p. ex: CLIENTS_GET
        /// </summary>
        public string spGET;

        /// <summary>
        /// Nom de la SP que fa el INS, p. ex: CLIENTS_INS
        /// </summary>
        public string spINS;

        /// <summary>
        /// Nom de la SP que fa el UPD, p. ex: CLIENTS_UPD
        /// </summary>
        public string spUPD;

        /// <summary>
        /// Nom de la SP que fa el DEL, p. ex: CLIENTS_DEL
        /// </summary>
        public string spDEL;

        /// <summary>
        /// Nom de la SP que fa el QUERY, p. ex: CLIENTS_QUERY
        /// </summary>
        public string spQUERY;

        /// <summary>
        /// Nom de la SP que fa el COUNT, p. ex: CLIENTS_COUNT
        /// </summary>
        public string spCOUNT;

        /// <summary>
        /// Objecte conexió a BBDD. Útil per si es vol fer servir
        /// tota l'estona la mateixa conexió, sense obrir i tancar cada cop.
        /// </summary>
        public SqlConnection SQLConn = null;

        /// <summary>
        /// Objecte que conté la transacció. És opcional fer-ne servir.
        /// </summary>
        public SqlTransaction SQLTrans = null;

        /// <summary>
        /// Indica que la taula té un camp identat
        /// </summary>
        public bool TeIdentitat = false;

        /// <summary>
        /// Indica si la taula fa servir una cache de JSON
        /// </summary>
        public bool TeCache = false;

        /// <summary>
        /// Indica si un INS/DEL/UPD netegen la cache automàticament
        /// </summary>
        public bool AutoNetejaCache = true;

        /// <summary>
        /// Nom de la taula
        /// </summary>
        public string NomTaula = "";

        /// <summary>
        /// Nom del camp identitat
        /// </summary>
        public string Identitat = "";

        /// <summary>
        /// Llista dels camps que es serialitzen per enviar al navegador
        /// </summary>
        public clsCamp[] Serialitzar = null;

        /// <summary>
        /// Llista de camps que es deserialitzen quan arriben del navegador
        /// </summary>
        public clsCamp[] Deserialitzar = null;

        //Canviar de projecte en projecte, 
        //ha de tenir el parametre Taula varchar(50) i el parametre URL varchar(500)
        private string SP_Cache = "DI_EXP_CACHE_DEL";
}

En el desenvolupament d'aplicacions web cal una infraestructura per accedir a les dades a la BBDD, es a dir les stored procedures (SP) i també cal una forma fàcil per invocar-les. I a ser possible que NO sigui muntar les crides a mà. Per això he muntat la clsDades.

La clsDades és la classe principal que conté tots els métodes d'accés a BBDD. S'encarrega d'invocar les SP en funció de la definició concreta per cada taula. Es per això que la resta de classes hereden d'aquesta, i l'amplien definint amb detall els diferents paràmetres de cada SP.

Per tan la clsDades, de fet, conté els mètodes, mentre que la definició la tenen les classes particulars corresponents a cada taula. Per això la clsDades conté la declaració de les llistes de paràmetres i la inicialització es troba separada.

El primer que cal definir són les classes de suport que fa servir la clsDades, es a dir la classe Tipus i la classe clsCamp. Concretament la clsDades conté vectors d'objectes clsCamp destinats a construir els paràmetres de les SP. Per això detallaré aquestes classes primer, a demés que són força simples.

La clase Tipus indica el tipus (valgui la redundància) de BBDD, es farà servir per indicar el tipus en la clase Camp i després en el codi queda més clar.

    public static class Tipus
    {
        public const int bit = 1;
        public const int tint = 2;
        public const int bigint = 3;
        public const int nvarchar = 4;
        public const int datetime = 5;
        public const int nchar = 6;
        public const int dec = 7;
        public const int varchar = 8;
        public const int chr = 9;
        public const int date = 10;
        public const int time = 11;
    }

La classe camp també és força senzilla, conté informació d'un camp de BBDD.

    public class clsCamp
    {
        public string NomCamp = "";
        public bool Nulable = true;
        public int Tipus;
        public string Format = "";
        public int Longitud = 0;
        public bool PK = false;
        public bool Identitat = false;
        public byte Precisio = 0;
        public byte Escala = 0;
    }

A partir d'aquí començarem a desgranar poc a poc la clsDades. Però en futurs posts:

• Declaració de variables
• Una clase derivada, per omplir amb valors les variables.
• Creació de paràmetres a SqlServer
• Invocació de la SP GET.
• Invocació de la SP GETTOP.
• Invocació de la SP INS.
• Invocació de la SP UPD.
• Invocació de la SP DEL.
• Invocació de la SP COUNT.
• Invocació de la SP QUERY.
• Serialitzar.
• Deserialitzar.
• Actualitzar una DataTable.
• Gestió de la cache.

Aquesta eina del Visual Studio potser en algun escenari és útil, ja que recol·lecta un munt d'informació del que fa el programa que estem fent. Però com contrapartida alenteix molt el Debug. Però que molt. És cert que es pot escollir el que es vol capturar, però de moment no he tingut la necessitat. De moment l'he deshabilitat i ja torno a debugar a tota velocitat.

Hem dedico a això, a fer aplicacions web, fent servir tecnologia Microsoft. El que abans eren Winforms, aplicacions d'escriptori ara són aplicacions web. Aquestes han sofert grans canvis des de l'any 2000 on es feien en ASP Clàssic, a ara on l'HTML5 va entrant i sembla que té bona pinta (facilita la vida que enlloc de fer servir components, el navegador suporti directament el que vols fer amb tags).

Així doncs cal trobar una forma de portar la informació amunt i avall. A més a més de fer-ho eficient també cal que el desenvolupador (a.k.a jo mateix) no mori en l'intent.

Pel tema de la eficiència en les comunicacions es fa servir JSON per les dades i HTML per la presentació. Ambdós viatgen separats, i el JSON normalment per AJAX. Es a dir, L'HTML es transporta un cop, quan es carrega, i després només van amunt i avall les dades en JSON. Això fa que mantenir l'estat de la pantalla sigui molt fàcil, ja que no canvia (només les dades). És el que jo anomeno part client.

Per afavorir que el desenvolupador no mori en l'intent de recuperar els valors de 40 camps, posar-los en els seus corresponents paràmetres, etc... Es fa servir la serialització/deserialització de valors del formulari. Aquesta operació la fa una eina que genera codi automàticament. Per tant arribat a aquest punt el nombre de camps que contingui un formulari és indiferent alhora de recuperar-ne els valors. Únicament cal afinar el disseny, posar calendaris, si hi ha alguna lògica de negoci, combos en cascada, etc...

Per accedir a la BBDD es fan servir stored procedures (SP) i per gestionar aquestes SP en C# he fet una capa d'accés a dades, la clsDades (seguint els corresponents enllaços hi ha la documentació pertinent).

Per implementar la comunicació entre la part client i la clsDades faig servir ASHX amb molt bons resultats.

La impressió, sempre és el punt feble de les aplicacions web. La solució que he trobat és fer servir PDFs. A partir de la llibreria de Winnovative es pot convertir HTML a PDF, i funciona molt bé, tema liquidat. Si la generació d'un PDF triga molt temps també és pot fer una aplicació d'escriptori complementària que es dediqui només a això.

Més o menys queda una arquitectura com la següent

Aquesta modalitat de QUERY és la que suporta més casos, es a dir aguanta de tot. El seu rendiment no és tan bo com les versions en CTE però aguanta situacions on les CTE no funcionen bé. Concretament aguanta consultes contra taules amb milions de registres, amb molts JOINs i molts paràmetres de cerca.

Està dividida en varies parts, primer creació de les taules temporals. Es necessiten dues d'aquestes taules temporals, una per filtrar i una altra per ordenar els resultats i extreure una pàgina. Un cop creades la següent part omple la primera taula. Ho fa amb els registres obtinguts de filtrar la taula original. Per fer-ho munta una consulta de forma dinàmica i afegeix al WHERE només els paràmetres que estan informats. D'aquesta manera encara que hi hagi 19 paràmetres de cerca si només s'informa un no hi ha penalització en el rendiment.

Un cop plena la taula amb els resultats del filtre cal ordenar-los. Llavors es fa servir la funció Row_Number, funcionarà correctament per que durant el filtrat ja es fa un DISTINCT. Dins d'aquesta mateixa sentència d'ordenació ja s'agafa la pàgina que toca amb la mida que es passa per paràmetre.

Aquesta sentència és un xic lenta per taules amb milions de registres, triga uns 16 a 26 segons en una taula amb 1.315.000 registres i un servidor que pel que hem van comentar no era gaire bo. Cal fer un mecanisme de cache per reduir aquest temps. Però la CACHE de consultes ho tractarem en el seu post i està relacionada amb la capa de dades (clsDades) en C#.

Com sempre el codi comentat

USE [DI_REAL]
GO
/****** Object:  StoredProcedure [dbo].[DI_EXP_EXPEDIENTARXIU_QUERY]    Script Date: 11/27/2013 17:09:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DI_EXP_EXPEDIENTARXIU_QUERY]
@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),
 @pSortColumn NVARCHAR(50) = null,
@pPageSize INT = 10,
@pPageNum INT = 1
AS
BEGIN
SET NOCOUNT ON;

--Creació de les taules temporals, FILTRE
IF OBJECT_ID('tempdb..#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre') IS NOT NULL
	DROP TABLE #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre
CREATE TABLE #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre
(
	PK_ExpedientArxiu int,
	FK_Procedencia int,
	FK_Classificacio int,
	FK_Uniforme int,
	FK_Especifica int,
	FK_LLocDiposit int,
	FK_Estat int,
	Titol varchar(255),
	Exped_I varchar(50),
	Exped_F varchar(50),
	Any_Obertura int,
	Any_Tancament int,
	U_Instal_I varchar(30),
	U_Instal_F varchar(30),
	Volum int,
	Adreca varchar(250),
	SigTop_I varchar(30),
	SigTop_F varchar(30),
	Codi_TC nvarchar(5),
	Uniforme_TC nvarchar(5),
	Especifica_TC nvarchar(10),
	Codi_Procedencia nvarchar(5),
	N_NivellP nvarchar(200),
	Diposit nvarchar(200),
	Capsa varchar(30),
	Sigla varchar(6),
	CapsaHist varchar(50),
	Estat varchar(50)
)
--Creació de la taula temporal ORDENACIO
IF OBJECT_ID('tempdb..#TempTBLDI_EXP_EXPEDIENTARXIU_Ordenacio') IS NOT NULL
	DROP TABLE #TempTBLDI_EXP_EXPEDIENTARXIU_Ordenacio
CREATE TABLE #TempTBLDI_EXP_EXPEDIENTARXIU_Ordenacio
(
	NumRegs bigint,
	RowNumber bigint,
	PK_ExpedientArxiu int,
	FK_Procedencia int,
	FK_Classificacio int,
	FK_Uniforme int,
	FK_Especifica int,
	FK_LLocDiposit int,
	FK_Estat int,
	Titol varchar(255),
	Exped_I varchar(50),
	Exped_F varchar(50),
	Any_Obertura int,
	Any_Tancament int,
	U_Instal_I varchar(30),
	U_Instal_F varchar(30),
	Volum int,
	Adreca varchar(250),
	SigTop_I varchar(30),
	SigTop_F varchar(30),
	Codi_TC nvarchar(5),
	Uniforme_TC nvarchar(5),
	Especifica_TC nvarchar(10),
	Codi_Procedencia nvarchar(5),
	N_NivellP nvarchar(200),
	Diposit nvarchar(200),
	Capsa varchar(30),
	Sigla varchar(6),
	CapsaHist varchar(50),
	Estat varchar(50)
)

DECLARE @strSQL nvarchar(MAX)
DECLARE @strPARAMS nvarchar(MAX)

--Construcció del la consulta per omplir la taula filtrada
SET @strSQL = 'INSERT INTO #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre
 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_LLocDiposit,
dbo.DI_EXP_EXPEDIENTARXIU.FK_Estat,
dbo.DI_EXP_EXPEDIENTARXIU.Titol,
dbo.DI_EXP_EXPEDIENTARXIU.Exped_I,
dbo.DI_EXP_EXPEDIENTARXIU.Exped_F,
dbo.DI_EXP_EXPEDIENTARXIU.Any_Obertura,
dbo.DI_EXP_EXPEDIENTARXIU.Any_Tancament,
dbo.DI_EXP_EXPEDIENTARXIU.U_Instal_I,
dbo.DI_EXP_EXPEDIENTARXIU.U_Instal_F,
dbo.DI_EXP_EXPEDIENTARXIU.Volum,
dbo.DI_EXP_EXPEDIENTARXIU.Adreca,
dbo.DI_EXP_EXPEDIENTARXIU.SigTop_I,
dbo.DI_EXP_EXPEDIENTARXIU.SigTop_F,
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_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 '

--Construcció del WHERE només els paràmetres amb valor aniran al WHERE.
--Hi un munt!
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



-- Construcció dels paràmetres que van a la crida del SQL dinàmic
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)'



--Execució amb els paràmtres informats
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;

--Inserim les dades filtrades aplicant un ordre a la taula d'ordenació
INSERT INTO #TempTBLDI_EXP_EXPEDIENTARXIU_Ordenacio
SELECT
COUNT(*) OVER () AS NumRegs,
ROW_NUMBER()
OVER(ORDER BY
CASE WHEN @pSortColumn = 'PK_ExpedientArxiuASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.PK_ExpedientArxiu END ASC,
CASE WHEN @pSortColumn = 'PK_ExpedientArxiuDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.PK_ExpedientArxiu END DESC,
CASE WHEN @pSortColumn = 'FK_ProcedenciaASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Procedencia END ASC,
CASE WHEN @pSortColumn = 'FK_ProcedenciaDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Procedencia END DESC,
CASE WHEN @pSortColumn = 'FK_ClassificacioASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Classificacio END ASC,
CASE WHEN @pSortColumn = 'FK_ClassificacioDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Classificacio END DESC,
CASE WHEN @pSortColumn = 'FK_UniformeASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Uniforme END ASC,
CASE WHEN @pSortColumn = 'FK_UniformeDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Uniforme END DESC,
CASE WHEN @pSortColumn = 'FK_EspecificaASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Especifica END ASC,
CASE WHEN @pSortColumn = 'FK_EspecificaDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Especifica END DESC,
CASE WHEN @pSortColumn = 'TitolASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Titol END ASC,
CASE WHEN @pSortColumn = 'TitolDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Titol END DESC,
CASE WHEN @pSortColumn = 'Exped_IASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Exped_I END ASC,
CASE WHEN @pSortColumn = 'Exped_IDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Exped_I END DESC,
CASE WHEN @pSortColumn = 'Any_OberturaASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Any_Obertura END ASC,
CASE WHEN @pSortColumn = 'Any_OberturaDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Any_Obertura END DESC,
CASE WHEN @pSortColumn = 'Any_TancamentASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Any_Tancament END ASC,
CASE WHEN @pSortColumn = 'Any_TancamentDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Any_Tancament END DESC,
CASE WHEN @pSortColumn = 'U_Instal_IASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.U_Instal_I END ASC,
CASE WHEN @pSortColumn = 'U_Instal_IDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.U_Instal_I END DESC,
CASE WHEN @pSortColumn = 'U_Instal_FASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.U_Instal_F END ASC,
CASE WHEN @pSortColumn = 'U_Instal_FDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.U_Instal_F END DESC,
CASE WHEN @pSortColumn = 'SigTop_IASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.SigTop_I END ASC,
CASE WHEN @pSortColumn = 'SigTop_IDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.SigTop_I END DESC,
CASE WHEN @pSortColumn = 'SigTop_FASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.SigTop_F END ASC,
CASE WHEN @pSortColumn = 'SigTop_FDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.SigTop_F END DESC,
CASE WHEN @pSortColumn = 'CapsaASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Capsa END ASC,
CASE WHEN @pSortColumn = 'CapsaDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Capsa END DESC,
CASE WHEN @pSortColumn = 'SiglaASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Sigla END ASC,
CASE WHEN @pSortColumn = 'SiglaDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Sigla END DESC,
CASE WHEN @pSortColumn = 'CapsaHistASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.CapsaHist END ASC,
CASE WHEN @pSortColumn = 'CapsaHistDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.CapsaHist END DESC,
CASE WHEN @pSortColumn = 'EstatASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Estat END ASC,
CASE WHEN @pSortColumn = 'EstatDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Estat END DESC
)
AS RowNumber,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.PK_ExpedientArxiu,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Procedencia,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Classificacio,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Uniforme,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Especifica,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_LLocDiposit,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Estat,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Titol,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Exped_I,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Exped_F,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Any_Obertura,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Any_Tancament,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.U_Instal_I,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.U_Instal_F,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Volum,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Adreca,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.SigTop_I,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.SigTop_F,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Codi_TC,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Uniforme_TC,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Especifica_TC,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Codi_Procedencia,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.N_NivellP,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Diposit,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Capsa,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Sigla,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.CapsaHist,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Estat
 FROM #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre

--Obtenim la pàgina desitjada dins dels resultats
SELECT * FROM #TempTBLDI_EXP_EXPEDIENTARXIU_Ordenacio
WHERE RowNumber >= (@pPageSize * @pPageNum) - (@pPageSize -1) AND RowNumber <= @pPageSize * @pPageNum
ORDER BY RowNumber

--Neteja final
DROP TABLE #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre
DROP TABLE #TempTBLDI_EXP_EXPEDIENTARXIU_Ordenacio
END

De forma similar al ROW_NUMBER i fent servir un CTE, el DENSE_RANK fa la mateixa funció. Ens permet numerar els registres per després poder obtenir una pàgina dins dels resultats.

Normalment el DENSE_RANK es fa servir en conjunció amb un DISTINCT o un GROUP BY ja que aquestes funcions d'eliminació de duplicats funcionen bé amb aquest funció. Els duplicats normalment venen donats per que a la consulta hi ha un JOIN. Veiem un codi d'exemple:

ALTER PROCEDURE [dbo].[DI_EXP_CLASSIFICACIO_QUERY]
@pPK_Classificacio int,
 @pNom nvarchar(200),
 @pPK_Arxiu int,
 @pCodi_TC nvarchar(5),
 @pNivell_Codi nvarchar(100),
 @pIdPare int,
 @pFK_TAAD int,
 @pSortColumn NVARCHAR(50) = null,
@pPageSize INT = 10,
@pPageNum INT = 1
AS
BEGIN
WITH PagingCTE-- Creació del CTE
(
Row_ID,
PK_Classificacio,
Codi_TC,
Observacions,
DataAlta,
DataBaixa,
Any_inici,
Any_final,
IdPare,
Nom,
Nivell_Codi)
AS
(
SELECT DISTINCT --Aquesta select porta un DISTINCT per eliminar duplicats
DENSE_RANK()--Fem servir la funció Dense_rank, funciona sintàcticament igual que el Row_Number
OVER(ORDER BY
CASE WHEN @pSortColumn = 'Codi_TCASC' THEN DI_EXP_CLASSIFICACIO.Codi_TC END ASC,
CASE WHEN @pSortColumn = 'Codi_TCDESC' THEN DI_EXP_CLASSIFICACIO.Codi_TC END DESC,
CASE WHEN @pSortColumn = 'NomASC' THEN DI_EXP_CLASSIFICACIO.Nom END ASC,
CASE WHEN @pSortColumn = 'NomDESC' THEN DI_EXP_CLASSIFICACIO.Nom END DESC
)
AS [Row_ID],
DI_EXP_CLASSIFICACIO.PK_Classificacio,
DI_EXP_CLASSIFICACIO.Codi_TC,
DI_EXP_CLASSIFICACIO.Observacions,
DI_EXP_CLASSIFICACIO.DataAlta,
DI_EXP_CLASSIFICACIO.DataBaixa,
DI_EXP_CLASSIFICACIO.Any_inici,
DI_EXP_CLASSIFICACIO.Any_final,
DI_EXP_CLASSIFICACIO.IdPare,
DI_EXP_CLASSIFICACIO.Nom,
DI_EXP_CLASSIFICACIO.Nivell_Codi
 FROM DI_EXP_CLASSIFICACIO --Aqui estan els JOINS que causen duplicats
LEFT OUTER JOIN DI_EXP_CLASSIFICACIO_ARXIU 
ON DI_EXP_CLASSIFICACIO.PK_Classificacio=DI_EXP_CLASSIFICACIO_ARXIU.PK_Classificacio  
LEFT OUTER JOIN DI_EXP_TAAD_REL ON DI_EXP_CLASSIFICACIO.PK_Classificacio=DI_EXP_TAAD_REL.FK_Classificacio 
 WHERE ((@pPK_Classificacio IS NULL) OR (DI_EXP_CLASSIFICACIO.PK_Classificacio=@pPK_Classificacio))
 AND ((@pNom IS NULL) OR (DI_EXP_CLASSIFICACIO.Nom LIKE @pNom + '%'))
 AND ((@pPK_Arxiu IS NULL) OR (DI_EXP_CLASSIFICACIO_ARXIU.PK_Arxiu = @pPK_Arxiu))
 AND ((@pCodi_TC IS NULL) OR (DI_EXP_CLASSIFICACIO.Codi_TC LIKE @pCodi_TC + '%'))
 AND ((@pNivell_Codi IS NULL) OR (DI_EXP_CLASSIFICACIO.Nivell_Codi LIKE @pNivell_Codi + '%'))
 AND ((@pIdPare IS NULL) OR (DI_EXP_CLASSIFICACIO.IdPare = @pIdPare))
 AND ((@pFK_TAAD IS NULL) OR (DI_EXP_TAAD_REL.FK_TAAD = @pFK_TAAD))
)
SELECT
PK_Classificacio,
Codi_TC,
Observacions,
DataAlta,
DataBaixa,
Any_inici,
Any_final,
IdPare,
Nom,
Nivell_Codi FROM PagingCTE
WHERE Row_ID >= (@pPageSize * @pPageNum) - (@pPageSize -1) AND Row_ID <= @pPageSize * @pPageNum
ORDER BY Row_ID
END