Now that 3DES is phasing out, a new signature is need to replace the veteran X9.19. So here comes the brand new (from 2006!) AES CMAC signature. It uses AES instead of 3DES, but externally it uses the same 128bit long keys. I've programmed, with help from StackOverflow, a version in C#, and another version using the BouncyCastle library. Both ways return the same result, and I don't know which one performs better. The program is easy to understand, the only difficult step lays in the subkey generation. I've cut&pasted the RFC part that explains it to be sure I've done the correct thing.

byte[] AESEncrypt(byte[] key, byte[] iv, byte[] data)
{
    using (MemoryStream ms = new MemoryStream())
    {
	AesCryptoServiceProvider aes = new AesCryptoServiceProvider();

	aes.Mode = CipherMode.CBC;
	aes.Padding = PaddingMode.None;

	using (CryptoStream cs = new CryptoStream(ms, aes.CreateEncryptor(key, iv), CryptoStreamMode.Write))
	{
	    cs.Write(data, 0, data.Length);
	    cs.FlushFinalBlock();

	    return ms.ToArray();
	}
    }
}

/// <summary>
/// This function does:
/// x << 1
/// Left-shift of the string x by 1 bit.
/// The most significant bit disappears, and a zero
/// comes into the least significant bit.
/// 10010001 << 1 is 00100010.
/// </summary>
/// <param name="b">Array de bytes a fer shift</param>
/// <returns>El byte shiftejat</returns>
byte[] Rol(byte[] b)
{
    byte[] r = new byte[b.Length];
    byte carry = 0;

    for (int i = b.Length - 1; i >= 0; i--)
    {
	ushort u = (ushort)(b[i] << 1);
	r[i] = (byte)((u & 0xff) + carry);
	carry = (byte)((u & 0xff00) >> 8);
    }

    return r;
}

byte[] AESCMAC(byte[] key, byte[] data)
{
    /*
    // SubKey generation
    Figure 2.2 specifies the subkey generation algorithm.
 

    ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ +
    +Algorithm Generate_Subkey +
    +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    + +
    +Input    : K(128 - bit key) +
    +Output   : K1(128 - bit first subkey) +
    +K2(128 - bit second subkey) +
    +-------------------------------------------------------------------+
    + +
    +Constants: const_Zero is 0x00000000000000000000000000000000 +
    +const_Rb is 0x00000000000000000000000000000087 +
    +Variables: L          for output of AES - 128 applied to 0 ^ 128 +
    + +
    +Step 1.L := AES - 128(K, const_Zero); +
    +Step 2.  if MSB(L) is equal to 0 +
    +then    K1:= L << 1; +
    +            else    K1:= (L << 1) XOR const_Rb; +
    +Step 3.  if MSB(K1) is equal to 0 +
    +then    K2:= K1 << 1; +
    +            else    K2:= (K1 << 1) XOR const_Rb; +
    +Step 4.  return K1, K2; +
    + +
    +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

    Figure 2.2.Algorithm Generate_Subkey

    In step 1, AES - 128 with key K is applied to an all-zero input block.

       In step 2, K1 is derived through the following operation:

		If the most significant bit of L is equal to 0, K1 is the left - shift
       of L by 1 bit.

       Otherwise, K1 is the exclusive - OR of const_Rb and the left - shift of L
       by 1 bit.

       In step 3, K2 is derived through the following operation:

		If the most significant bit of K1 is equal to 0, K2 is the left - shift
       of K1 by 1 bit.

       Otherwise, K2 is the exclusive - OR of const_Rb and the left - shift of
	   K1 by 1 bit.

       In step 4, (K1, K2) := Generate_Subkey(K) is returned.
       */


    // step 1, AES-128 with key K is applied to an all-zero input block.
    byte[] L = AESEncrypt(key, new byte[16], new byte[16]);

    // step 2, K1 is derived through the following operation:
    byte[] FirstSubkey = Rol(L); //If the most significant bit of L is equal to 0, K1 is the left-shift of L by 1 bit.
    if ((L[0] & 0x80) == 0x80)
	FirstSubkey[15] ^= 0x87; // Otherwise, K1 is the exclusive-OR of const_Rb and the left-shift of L by 1 bit.

    // step 3, K2 is derived through the following operation:
    byte[] SecondSubkey = Rol(FirstSubkey); // If the most significant bit of K1 is equal to 0, K2 is the left-shift of K1 by 1 bit.
    if ((FirstSubkey[0] & 0x80) == 0x80)
	SecondSubkey[15] ^= 0x87; // Otherwise, K2 is the exclusive-OR of const_Rb and the left-shift of K1 by 1 bit.

    // MAC computing
    if (((data.Length != 0) && (data.Length % 16 == 0)) == true)
    {
	// If the size of the input message block is equal to a positive multiple of the block size (namely, 128 bits),
	// the last block shall be exclusive-OR'ed with K1 before processing
	for (int j = 0; j < FirstSubkey.Length; j++)
	    data[data.Length - 16 + j] ^= FirstSubkey[j];
    }
    else
    {
	//S'afegeix padding a l'ultim bloc, però en realitat és afegir padding a les dades
	data = AddPaddingAES(data);
	/*
	// Otherwise, the last block shall be padded with 10^i
	byte[] padding = new byte[16 - data.Length % 16];
	padding[0] = 0x80;

	data = data.Concat<byte>(padding.AsEnumerable()).ToArray();
	*/

	// and exclusive-OR'ed with K2, la SecondSubkey té 16 de longitud
	for (int j = 0; j < 16; j++) data[data.Length - 16 + j] ^= SecondSubkey[j];
    }

    // The result of the previous process will be the input of the last encryption.
    byte[] encResult = AESEncrypt(key, new byte[16], data);

    byte[] HashValue = new byte[16];
    Array.Copy(encResult, encResult.Length - HashValue.Length, HashValue, 0, HashValue.Length);

    return HashValue;
}

All that code from above, that seems very complex, can be reduced to a few lines using BouncyCastle. But you know, in the code lays the truth, and with the library you don't see it...

//Using BouncyCastle
Org.BouncyCastle.Crypto.Engines.AesEngine aes = new Org.BouncyCastle.Crypto.Engines.AesEngine();
Org.BouncyCastle.Crypto.Macs.CMac cMac = new Org.BouncyCastle.Crypto.Macs.CMac(aes, 64);
Org.BouncyCastle.Crypto.Parameters.KeyParameter kp = new Org.BouncyCastle.Crypto.Parameters.KeyParameter(key);
cMac.Init(kp);
int MacLenght = cMac.GetMacSize();
byte[] output = new byte[MacLenght];
cMac.BlockUpdate(data, 0, data.Length);
cMac.DoFinal(output, 0);

txtBase64.Text = ConvertByteArrayToHexString(output);

There are some examples in a RFC.

//https://tools.ietf.org/html/rfc4493
/*
CMAC EXAMPLE:
-------------
NIST publication 800 - 38B: http://csrc.nist.gov/publications/nistpubs/800-38B/Updated_CMAC_Examples.pdf

>>> key 2b7e151628aed2a6abf7158809cf4f3c
>>> plaintext 6bc1bee22e409f96e93d7e117393172a
<<< CMAC 070a16b46b4d4144f79bdd9dd04a287c

>>> key 2B7E151628AED2A6ABF7158809CF4F3C
>>> pt: 6BC1BEE22E409F96E93D7E117393172AAE2D8A57
<<< CMAC 7D85449E A6EA19C8 23A7BF78 837DFADE

>>> key 2B7E151628AED2A6ABF7158809CF4F3C
>>> pt: 6BC1BEE22E409F96E93D7E117393172AAE2D8A571E03AC9C9EB76FAC45AF8E5130C81C46A35CE411E5FBC1191A0A52EFF69F2445DF4F9B17AD2B417BE66C3710
<<< CMAC 51F0BEBF7E3B9D92FC49741779363CFE
*/

And that's all, another signature done!

In IIS is very easy to hide folders from HTTP browsing, this means that all the files and subfolders won't be accessible from the URL.

<configuration>
   <system.webServer>
       <security>
          <requestFiltering>
               <hiddenSegments>
                   <add segment="My_Directory" />
               </hiddenSegments>
           </requestFiltering>
       </security>
   </system.webServer>
</configuration>

Recently I had to implement a MAC ANSI X9.19 (MAC = Message Authentication Code) signature. And like all security related standards there is little information online.

A MAC signature of this type is usefull to verify that a message has not been tampered and that we receive it as it was sent. If there is a network problem and a bit flips it will be detected, or if someone "bad" tries to modify the message also will be detected. They can't calculate the signature for the modified payload because they don't know the key. The receiver of the message can verify it because he knows the key.

The signature MAC ANSI X9.19 is a banking standard made in USA. It's an evolution from X9.9 and it is also known as Retail-MAC. It's a cipher based in DES-CBC, here are the links to the wiki for DES that means "Data Encryption Standard" and CBC that means"Cipher Block Chaining". An international norm exists, that is very similar but a little more permisive called ISO 9807, it allows to use another cipher algorithms. ANSI X9.19 is a subset of ISO 9797, when the cipher block is 64 bits, MAC length is 32 and DES is used.

DES is a very old cipher algorithm that uses 56 bit keys but X9.19 uses 128bit keys like 3DES. Here are the process steps:

1. The incoming message is padded with 00. As padding will not be removed later there's no need of adding an special padding so 00 is ok. So we add 00 until the message length is multiple of 8 bytes.
2. The 128 bit key is divided into two sub-keys. Left part is called K, and right part K'.
3. Message is divided in 8 byte blocks.
4. Firts block is cyphered with K key.
5. A XOR is applied to the result with the next block (it's the CBC).
6. Result is ciphered with K key. If it isn't the last block return to previous step.
   If it is then move to the next step.
7. To this last result a decryption is applied with K' key (nothing coherent is obtained).
8. Finally encrypt with K key. The result is the MAC.

Better an image than a thousand words:

I've made two implementations in C#, one doing things by hand and the other with system libraries. Both offer the same result (I don't know which performs better). In code comments there is an example with correct values to check if it works.

public byte[] SubArray(byte[] data, int index, int length)
{
    byte[] result = new byte[length];
    Array.Copy(data, index, result, 0, length);
    return result;
}

private void btnFirma_Click(object sender, EventArgs e)
{
    try
    {

	if (!ValidarClau()) return;

	NetejaTXTs();

	byte[] IV = new byte[8]; //empty byte array
	byte[] key = ConvertHexStringToByteArray(txtKey.Text);
	byte[] LeftKey = SubArray(key, 0, 8);
	byte[] RightKey = SubArray(key, 8, 8);
	byte[] data;

	if (chkHexString.Checked)
	{
	    data = ConvertHexStringToByteArray(Neteja(txtOriginal.Text));
	}
	else
	{
	    data = Encoding.ASCII.GetBytes(Neteja(txtOriginal.Text));
	}
	//Exemple
	//Dades = 4E6F77206973207468652074696D6520666F7220616C6C20
	//Clau = 0123456789ABCDEFFEDCBA9876543210
	//Firma = A1C72E74EA3FA9B6

	DES DESalg = DES.Create();
	DESalg.Mode = CipherMode.CBC;
	DESalg.Padding = PaddingMode.None;
	ICryptoTransform SimpleDES_Encriptador = DESalg.CreateEncryptor(LeftKey, IV);
	ICryptoTransform SimpleDES_Desencriptador = DESalg.CreateDecryptor(RightKey, IV);

	byte[] result = new byte[8];
	byte[] datablock = new byte[8];
	int remain = data.Length % 8;
	int LoopCount = data.Length / 8;

	/*
	//Padding a 0
	if (remain != 0)
	{
	    int extra = 8 - (data.Length % 8);
	    int newLength = data.Length + extra;
	    byte[] newData = new byte[newLength];
	
	    Array.Copy(data, newData, data.Length);
	    data = newData;
	}
	result = SimpleDES_Encriptador.TransformFinalBlock(data, 0, data.Length);

	byte[] block = new byte[8];
	
	// Agafem l'ultim block
	Array.Copy(result, result.Length - 8, block, 0, 8);

	// Desencriptar l'ultim bloc a la K'
	block = SimpleDES_Desencriptador.TransformFinalBlock(block, 0, 8);

	// Encriptar altra cop el resultat amb K
	block = SimpleDES_Encriptador.TransformFinalBlock(block, 0, 8);

	result = block;
	*/

	//Si el que s'ha de firmar és multiple de 8...
	if (remain == 0)
	{
	    LoopCount--;
	    remain = 8;
	}

	//Primer block
	Array.Copy(data, 0, datablock, 0, 8);
	result = EncryptBlock(SimpleDES_Encriptador, datablock);

	for (int i = 1; i < LoopCount; i++)
	{
	    datablock = new byte[8];
	    Array.Copy(data, i * 8, datablock, 0, 8);
	    //Això fa el CBC
	    datablock = XorArray(datablock, result);
	    result = EncryptBlock(SimpleDES_Encriptador, datablock);
	}

	//Ultim block
	byte[] LastBlock = new byte[8];

	//Això fa padding a zeros
	Array.Copy(data, data.Length - remain, LastBlock, 0, remain);
	LastBlock = XorArray(LastBlock, result);

	result = EncryptBlock(SimpleDES_Encriptador, LastBlock);

	result = EncryptBlock(SimpleDES_Desencriptador, result);

	result = EncryptBlock(SimpleDES_Encriptador, result);

	if (!chkHexString.Checked) txtBase64.Text = ConvertByteArrayToHexString(data);
	txtEncrypted.Text = ConvertByteArrayToHexString(result);
	   
    }
    catch(Exception E)
    {
	MessageBox.Show(E.Message);
    }
}

public byte[] XorArray(byte[] buffer1, byte[] buffer2)
{
    for (int i = 0; i < buffer1.Length; i++)
	buffer1[i] ^= buffer2[i];
    return buffer1;
}

private byte[] EncryptBlock(ICryptoTransform crypt, byte[] toEncrypt)
{
    try
    {
	MemoryStream mStream = new MemoryStream();

	CryptoStream cStream = new CryptoStream(mStream,
	    crypt,
	    CryptoStreamMode.Write);
	cStream.Write(toEncrypt, 0, toEncrypt.Length);
	cStream.FlushFinalBlock();
	byte[] ret = mStream.ToArray();
	cStream.Close();
	mStream.Close();

	Console.WriteLine("DES OUTPUT : " + ConvertByteArrayToHexString(ret));
	return ret;
    }

    catch (CryptographicException e)
    {
	Console.WriteLine("A Cryptographic error occurred: {0}", e.Message);
	return null;
    }
}

The class clsData contains all methods needed to invoque all the stored procedures (SP) and the declaration of all the variables used by those methods. On the other hand a derived class contains only the initialization of those variables but no declarations. The program that generates the clsData only creates those derived classes filling the initializations automatically. So the next piece of code is generated by a program, not hand-made. For explanation purposes I put here an example.

public class clsClassificacio : clsData
{
	/// <summary>
	/// Class Constructor
	/// </summary>
	/// <param name="pCadConnBBDD">Connection string</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";
		HasIdentity = true;
		Identity = "PK_Classificacio";
		TableName = "DI_EXP_CLASSIFICACIO";
		UseCache = false;
		CadConnBBDD = pCadConnBBDD;
		ParamsGET = new clsColumn[7];
		for (int i = 0; i < 7; i++) ParamsGET[i] = new clsColumn();
	#region Llista params GET
		ParamsGET[0].ColName = "PK_Classificacio";
		ParamsGET[0].DB_Type = DBType.tint;
		ParamsGET[0].PK = true;
		ParamsGET[0].Identity = true;
		ParamsGET[0].Nulable = false;
		ParamsGET[1].ColName = "Nom";
		ParamsGET[1].DB_Type = DBType.nvarchar;
		ParamsGET[1].Len = 200;
		ParamsGET[1].Nulable = false;
		ParamsGET[2].ColName = "PK_Arxiu";
		ParamsGET[2].DB_Type = DBType.tint;
		ParamsGET[3].ColName = "Codi_TC";
		ParamsGET[3].DB_Type = DBType.nvarchar;
		ParamsGET[3].Len = 5;
		ParamsGET[3].Nulable = false;
		ParamsGET[4].ColName = "Nivell_Codi";
		ParamsGET[4].DB_Type = DBType.nvarchar;
		ParamsGET[4].Len = 100;
		ParamsGET[4].Nulable = false;
		ParamsGET[5].ColName = "IdPare";
		ParamsGET[5].DB_Type = DBType.tint;
		ParamsGET[5].Nulable = false;
		ParamsGET[6].ColName = "FK_TAAD";
		ParamsGET[6].DB_Type = DBType.tint;
	#endregion
		ParamsINS = new clsColumn[10];
		for (int i = 0; i < 10; i++) ParamsINS[i] = new clsColumn();
	#region Llista params INS
		ParamsINS[0].ColName = "PK_Classificacio";
		ParamsINS[0].DB_Type = DBType.tint;
		ParamsINS[0].PK = true;
		ParamsINS[0].Identity = true;
		ParamsINS[0].Nulable = false;
		ParamsINS[1].ColName = "Codi_TC";
		ParamsINS[1].DB_Type = DBType.varchar;
		ParamsINS[1].Len = 5;
		ParamsINS[1].Nulable = false;
		ParamsINS[2].ColName = "Observacions";
		ParamsINS[2].DB_Type = DBType.varchar;
		ParamsINS[2].Len = 3000;
		ParamsINS[3].ColName = "DataAlta";
		ParamsINS[3].DB_Type = DBType.datetime;
		ParamsINS[4].ColName = "DataBaixa";
		ParamsINS[4].DB_Type = DBType.datetime;
		ParamsINS[5].ColName = "Any_inici";
		ParamsINS[5].DB_Type = DBType.tint;
		ParamsINS[5].Nulable = false;
		ParamsINS[6].ColName = "Any_final";
		ParamsINS[6].DB_Type = DBType.tint;
		ParamsINS[6].Nulable = false;
		ParamsINS[7].ColName = "IdPare";
		ParamsINS[7].DB_Type = DBType.tint;
		ParamsINS[7].Nulable = false;
		ParamsINS[8].ColName = "Nom";
		ParamsINS[8].DB_Type = DBType.nvarchar;
		ParamsINS[8].Len = 200;
		ParamsINS[8].Nulable = false;
		ParamsINS[9].ColName = "Nivell_Codi";
		ParamsINS[9].DB_Type = DBType.varchar;
		ParamsINS[9].Len = 100;
		ParamsINS[9].Nulable = false;
	#endregion
		ParamsUPD = new clsColumn[10];
		for (int i = 0; i < 10; i++) ParamsUPD[i] = new clsColumn();
	#region Llista params UPD
		ParamsUPD[0].ColName = "PK_Classificacio";
		ParamsUPD[0].DB_Type = DBType.tint;
		ParamsUPD[0].PK = true;
		ParamsUPD[0].Identity = true;
		ParamsUPD[0].Nulable = false;
		ParamsUPD[1].ColName = "Codi_TC";
		ParamsUPD[1].DB_Type = DBType.varchar;
		ParamsUPD[1].Len = 5;
		ParamsUPD[1].Nulable = false;
		ParamsUPD[2].ColName = "Observacions";
		ParamsUPD[2].DB_Type = DBType.varchar;
		ParamsUPD[2].Len = 3000;
		ParamsUPD[3].ColName = "DataAlta";
		ParamsUPD[3].DB_Type = DBType.datetime;
		ParamsUPD[4].ColName = "DataBaixa";
		ParamsUPD[4].DB_Type = DBType.datetime;
		ParamsUPD[5].ColName = "Any_inici";
		ParamsUPD[5].DB_Type = DBType.tint;
		ParamsUPD[5].Nulable = false;
		ParamsUPD[6].ColName = "Any_final";
		ParamsUPD[6].DB_Type = DBType.tint;
		ParamsUPD[6].Nulable = false;
		ParamsUPD[7].ColName = "IdPare";
		ParamsUPD[7].DB_Type = DBType.tint;
		ParamsUPD[7].Nulable = false;
		ParamsUPD[8].ColName = "Nom";
		ParamsUPD[8].DB_Type = DBType.nvarchar;
		ParamsUPD[8].Len = 200;
		ParamsUPD[8].Nulable = false;
		ParamsUPD[9].ColName = "Nivell_Codi";
		ParamsUPD[9].DB_Type = DBType.varchar;
		ParamsUPD[9].Len = 100;
		ParamsUPD[9].Nulable = false;
	#endregion
		ParamsDEL = new clsColumn[7];
		for (int i = 0; i < 7; i++) ParamsDEL[i] = new clsColumn();
	#region Llista params DEL
		ParamsDEL[0].ColName = "PK_Classificacio";
		ParamsDEL[0].DB_Type = DBType.tint;
		ParamsDEL[0].PK = true;
		ParamsDEL[0].Identity = true;
		ParamsDEL[0].Nulable = false;
		ParamsDEL[1].ColName = "Nom";
		ParamsDEL[1].DB_Type = DBType.nvarchar;
		ParamsDEL[1].Len = 200;
		ParamsDEL[1].Nulable = false;
		ParamsDEL[2].ColName = "PK_Arxiu";
		ParamsDEL[2].DB_Type = DBType.tint;
		ParamsDEL[3].ColName = "Codi_TC";
		ParamsDEL[3].DB_Type = DBType.nvarchar;
		ParamsDEL[3].Len = 5;
		ParamsDEL[3].Nulable = false;
		ParamsDEL[4].ColName = "Nivell_Codi";
		ParamsDEL[4].DB_Type = DBType.nvarchar;
		ParamsDEL[4].Len = 100;
		ParamsDEL[4].Nulable = false;
		ParamsDEL[5].ColName = "IdPare";
		ParamsDEL[5].DB_Type = DBType.tint;
		ParamsDEL[5].Nulable = false;
		ParamsDEL[6].ColName = "FK_TAAD";
		ParamsDEL[6].DB_Type = DBType.tint;
	#endregion
		ParamsQUERY = new clsColumn[7];
		for (int i = 0; i < 7; i++) ParamsQUERY[i] = new clsColumn();
	#region Llista params QUERY
		ParamsQUERY[0].ColName = "PK_Classificacio";
		ParamsQUERY[0].DB_Type = DBType.tint;
		ParamsQUERY[0].PK = true;
		ParamsQUERY[0].Identity = true;
		ParamsQUERY[0].Nulable = false;
		ParamsQUERY[1].ColName = "Nom";
		ParamsQUERY[1].DB_Type = DBType.nvarchar;
		ParamsQUERY[1].Len = 200;
		ParamsQUERY[1].Nulable = false;
		ParamsQUERY[2].ColName = "PK_Arxiu";
		ParamsQUERY[2].DB_Type = DBType.tint;
		ParamsQUERY[3].ColName = "Codi_TC";
		ParamsQUERY[3].DB_Type = DBType.nvarchar;
		ParamsQUERY[3].Len = 5;
		ParamsQUERY[3].Nulable = false;
		ParamsQUERY[4].ColName = "Nivell_Codi";
		ParamsQUERY[4].DB_Type = DBType.nvarchar;
		ParamsQUERY[4].Len = 100;
		ParamsQUERY[4].Nulable = false;
		ParamsQUERY[5].ColName = "IdPare";
		ParamsQUERY[5].DB_Type = DBType.tint;
		ParamsQUERY[5].Nulable = false;
		ParamsQUERY[6].ColName = "FK_TAAD";
		ParamsQUERY[6].DB_Type = DBType.tint;
	#endregion
		ResultColumns = new clsColumn[10];
		for (int i = 0; i < 10; i++) ResultColumns[i] = new clsColumn();
	#region Result columns
		ResultColumns[0].ColName = "PK_Classificacio";
		ResultColumns[0].DB_Type = DBType.tint;
		ResultColumns[0].PK = true;
		ResultColumns[0].Identity = true;
		ResultColumns[0].Nulable = false;
		ResultColumns[1].ColName = "Codi_TC";
		ResultColumns[1].DB_Type = DBType.varchar;
		ResultColumns[1].Len = 5;
		ResultColumns[1].Nulable = false;
		ResultColumns[2].ColName = "Observacions";
		ResultColumns[2].DB_Type = DBType.varchar;
		ResultColumns[2].Len = 3000;
		ResultColumns[3].ColName = "DataAlta";
		ResultColumns[3].DB_Type = DBType.datetime;
		ResultColumns[4].ColName = "DataBaixa";
		ResultColumns[4].DB_Type = DBType.datetime;
		ResultColumns[5].ColName = "Any_inici";
		ResultColumns[5].DB_Type = DBType.tint;
		ResultColumns[5].Nulable = false;
		ResultColumns[6].ColName = "Any_final";
		ResultColumns[6].DB_Type = DBType.tint;
		ResultColumns[6].Nulable = false;
		ResultColumns[7].ColName = "IdPare";
		ResultColumns[7].DB_Type = DBType.tint;
		ResultColumns[7].Nulable = false;
		ResultColumns[8].ColName = "Nom";
		ResultColumns[8].DB_Type = DBType.nvarchar;
		ResultColumns[8].Len = 200;
		ResultColumns[8].Nulable = false;
		ResultColumns[9].ColName = "Nivell_Codi";
		ResultColumns[9].DB_Type = DBType.varchar;
		ResultColumns[9].Len = 100;
		ResultColumns[9].Nulable = false;
	#endregion
		Serialitzar = new clsColumn[10];
		for (int i = 0; i < 10; i++) Serialitzar[i] = new clsColumn();
	#region Serializable columns
		Serialitzar[0].ColName = "PK_Classificacio";
		Serialitzar[0].DB_Type = DBType.tint;
		Serialitzar[1].ColName = "Codi_TC";
		Serialitzar[1].DB_Type = DBType.varchar;
		Serialitzar[1].Len = 5;
		Serialitzar[2].ColName = "Observacions";
		Serialitzar[2].DB_Type = DBType.varchar;
		Serialitzar[2].Len = 3000;
		Serialitzar[3].ColName = "DataAlta";
		Serialitzar[3].DB_Type = DBType.datetime;
		Serialitzar[4].ColName = "DataBaixa";
		Serialitzar[4].DB_Type = DBType.datetime;
		Serialitzar[5].ColName = "Any_inici";
		Serialitzar[5].DB_Type = DBType.tint;
		Serialitzar[6].ColName = "Any_final";
		Serialitzar[6].DB_Type = DBType.tint;
		Serialitzar[7].ColName = "IdPare";
		Serialitzar[7].DB_Type = DBType.tint;
		Serialitzar[8].ColName = "Nom";
		Serialitzar[8].DB_Type = DBType.nvarchar;
		Serialitzar[8].Len = 200;
		Serialitzar[9].ColName = "Nivell_Codi";
		Serialitzar[9].DB_Type = DBType.varchar;
		Serialitzar[9].Len = 100;
	#endregion
		Deserialitzar = new clsColumn[9];
		for (int i = 0; i < 9; i++) Deserialitzar[i] = new clsColumn();
	#region Deserializable columns
		Deserialitzar[0].ColName = "Codi_TC";
		Deserialitzar[0].DB_Type = DBType.varchar;
		Deserialitzar[0].Len = 5;
		Deserialitzar[1].ColName = "Observacions";
		Deserialitzar[1].DB_Type = DBType.varchar;
		Deserialitzar[1].Len = 3000;
		Deserialitzar[2].ColName = "DataAlta";
		Deserialitzar[2].DB_Type = DBType.datetime;
		Deserialitzar[3].ColName = "DataBaixa";
		Deserialitzar[3].DB_Type = DBType.datetime;
		Deserialitzar[4].ColName = "Any_inici";
		Deserialitzar[4].DB_Type = DBType.tint;
		Deserialitzar[5].ColName = "Any_final";
		Deserialitzar[5].DB_Type = DBType.tint;
		Deserialitzar[6].ColName = "IdPare";
		Deserialitzar[6].DB_Type = DBType.tint;
		Deserialitzar[7].ColName = "Nom";
		Deserialitzar[7].DB_Type = DBType.nvarchar;
		Deserialitzar[7].Len = 200;
		Deserialitzar[8].ColName = "Nivell_Codi";
		Deserialitzar[8].DB_Type = DBType.varchar;
		Deserialitzar[8].Len = 100;
	#endregion
	}
}

Writing all this code by hand is error prone and very boring (a lethal task), that's the reason why a program generates it. SP parameters are obtained from the meta information of the SP that is stored in the database, but this means that the SPs must be in place when this class is generated. The other parameters are obtained from the user interface of the generator software. If a column is checked to serialize then it will appear in the list "Serialitzar", and so on. From this example one can see that the database access layer is formed by the clsData and a ton of derived classes (one for each table).

clsData is a class that covers a lot of functionality. There are parts that deal with web projects, but cause some troubles when used in other kind of project (WinForms for example). For that reason I use conditional compilation.

This conditional compilation affects to serialization and deserialization because those to features are not used in projects that are not web. For this reason I define a symbol in Project -> Properties -> Build -> Conditional Compilation symbols. The symbol must be defined in all configurations (Debug, Release...).

clsData holds the declaration of all variables involved in stored procedure calling and some more. Most of them are arrays of clsColumn. We can review the code to see the purpose of each variable. Classes that inherite from clsData initialices all those variables with the correct values, need to invoke a SP. An inherited class usually belongs to a one table.

    public class clsData
    {
        /// <summary>
        /// Prefix used in stored procedures parameters, 
        /// used to differentiate them from variables deffined inside SP.
        /// </summary>
        public string Prefix = "p";

        /// <summary>
        /// GET SP parameters, are the same of GETTOP.
        /// </summary>
        public clsColumn[] ParamsGET; 

        /// <summary>
        /// INS SP parameters
        /// </summary>
        public clsColumn[] ParamsINS;

        /// <summary>
        /// UPD SP parameters
        /// </summary>
        public clsColumn[] ParamsUPD;

        /// <summary>
        /// DEL SP parameters
        /// </summary>
        public clsColumn[] ParamsDEL;

        /// <summary>
        /// QUERY SP parameters
        /// </summary>
        public clsColumn[] ParamsQUERY;

        /// <summary>
        /// Columns returned by GET SP as a result of the SELECT
        /// </summary>
        public clsColumn[] ResultColumns;

        /// <summary>
        /// Connection string
        /// </summary>
        public string CadConnBBDD;

        /// <summary>
        /// GET SP full name, p. ex: CLIENTS_GET
        /// </summary>
        public string spGET;

        /// <summary>
        /// INS SP full name, p. ex: CLIENTS_INS
        /// </summary>
        public string spINS;

        /// <summary>
        /// UPD SP full name, p. ex: CLIENTS_UPD
        /// </summary>
        public string spUPD;

        /// <summary>
        /// DEL SP full name, p. ex: CLIENTS_DEL
        /// </summary>
        public string spDEL;

        /// <summary>
        /// QUERY SP full name, p. ex: CLIENTS_QUERY
        /// </summary>
        public string spQUERY;

        /// <summary>
        /// COUNT SP full name, p. ex: CLIENTS_COUNT
        /// </summary>
        public string spCOUNT;

        /// <summary>
        /// SqlConnection object. this is used if the programmer wants
        /// to use the same connection all time, without closing and opening it every time.
        /// </summary>
        public SqlConnection SQLConn = null;

        /// <summary>
        /// Transaction object. Optional, if null is not used.
        /// </summary>
        public SqlTransaction SQLTrans = null;

        /// <summary>
        /// Boolean that indicates if the table has an identity column
        /// </summary>
        public bool HasIdentity = false;

        /// <summary>
        /// Boolean that indicates if the table uses a the cache system.
        /// </summary>
        public bool UseCache = false;

        /// <summary>
        /// Boolean that indicates if the cache is automanaged.
        /// </summary>
        public bool AutoManageCache = true;

        /// <summary>
        /// Table name
        /// </summary>
        public string TableName = "";

        /// <summary>
        /// Name of the identity column
        /// </summary>
        public string Identity = "";

        /// <summary>
        /// List of columns that are serialized and send to the browser
        /// </summary>
        public clsColumn[] Serialitzar = null;

        /// <summary>
        /// List of columns that are deserialized when values arrive from the browser
        /// </summary>
        public clsColumn[] Deserialitzar = null;

        //SP Cache name, usually changes from project to project.
        //It must have the following parameters: Tablename varchar(50) and URL varchar(500)
        private string SP_Cache = "DI_EXP_CACHE_DEL";
}

When developing web applications an infraestructure to access the database is needed. In other words, a piece to connect the bussines logic to the database, to invoke the stored procedures (SP) that works with data. This layer has to be easy to work with, automated at maximum to avoid messing with the boring task of building the parameter list of the SPs by hand (a process also prone to errors). For this reason I've build the clsData.

clsData is the principal class of the data layer, it holds all of the methods to acces the database. It invokes the SPs, but as every SP has different parameters, other clases creates the definition for those parameters and inherite from clsData to add the methods to invoke the SPs.

So, clsData has the methods to acces and other clases that use inheritance has the definition needed to fill the methods of clsData. For this reason clsData has the declaration of the variables that later the other classes will initialize with the correct values. Those variables, in fact, are lists of objects that has information to build a SQL parameter. First of all I use a simple class to store type information (and when I say type I'm refering to database type), the DBType class.

    public static class DBType
    {
        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;
    }

There's also a class that describes a datatable column (that generates a SP parameter of the same type and name): clsColumn

    public class clsColumn
    {
        public string ColName= "";
        public bool Nullable = true;
        public int DB_Type;
        public string Format = "";
        public int Len = 0;
        public bool PK = false;
        public bool Identity = false;
        public byte Precision = 0;
        public byte Scale = 0;
    }

From now on, I will describe with more detail all the variables involved in the class clsData, and its inner workings.

Every time that I write a CREATE TABLE SQL command and there is an index in the sentece the same doubt assault me. What is the difference between a clustered index and a non-clustered one?

To understand this first I have to explain what is an index. Indexs are database objects that helps to find records inside the database. As the data in the database tables grows a mechanism is needed to improve the information search performance. Here is when the indexs comes to help in searchs. Indexs have an structure like a tree, instead of doing a sequential read of the table we can jump between nodes in the index to arrive to the desired data. This way there are less reads to disk and the result is obtained faster.

Now that we know what is an index is time to see the diference between them. Let's start with a clustered one. In a clustered index data is stored in the leaves of the tree, so when we arrive at a leave that we are searching for data is already there, available to collect.

On the other hand non-clustered indexs doesn't have the data on the leaves, they have a pointer to the data, then it is necessary to do a bookmark lookup to actually get the data.

Obviously non-clustered indexs are slower than clustered ones, they have to perform an extra step. But in a table you can only have one clustered index (there's only one way to store data) and you can have multiple non-clustered indexs. From SQL Server 2005 onwards theres the possibility to stored certain data columns in a non-clustered indexs avoiding the bookmark lookup, there's an article on the TechNet that explains that.

There are some points to consider regarding indexs:

• Identity columns that are primary key (PK) usually belongs to the clustered index of the table. This is done to avoid page split.
• If certain searches repeat often then is recommended to create a non-clustered index. If the result columns are just a few they can be included in the index.
• If searches are done by multiple columns then a non-clustered index is recommended.

This post is part of the QUERY series.

This type of QUERY is the one that supports more use cases. It's performance is not as good as it's CTE counterparts but allows some use cases that CTE versions don't allow. It supports its use against tables with millions of records and with queries that has JOINs and a ton of filtering parameters.

It is divided in three parts. The first part is the temporal table creation. Two temporal tables are needed, one for filtering and the other for ordering. The ordering table is also used to retrieve a page of rows. Once created the temporal tables the second part fills the first temporal table with records that comply certain criteria (filtering them). To do this, a dynamic query is built in a string, in the WHERE part only are informed the filter parameters that actually have a value. This way although 19 filter parameters are used there's no performance penalty.

Once this first table is full of records is time to order them, so a ROW_NUMBER function is used in the third and last part of the query. It works well because in the first table a DISTINCT is used to avoid duplicate rows. In the WHERE part of this second temporal table a page of rows is obtained.

A stored procedure this long is a bit slow with tables that have 1.6 million of rows, and in a not very good server it can last from 16 to 26 seconds. In order to reduce this time a cache is needed but this will be explained in the C# post of clsData.

As usual the full code of the SP:

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;

--Creation of the first table, used for filtering
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)
)
--Creation of the second temporal table, the one used in sorting
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)

--First query to fill the table with filtered results
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 '

--Dynamic WHERE
--There's a ton of possible filters
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



-- Construction of the parameters
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)'



--Execution with parameter informed
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;

--Insert filtered data applying a parametrized sort
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

--Get the desired page
SELECT * FROM #TempTBLDI_EXP_EXPEDIENTARXIU_Ordenacio
WHERE RowNumber >= (@pPageSize * @pPageNum) - (@pPageSize -1) AND RowNumber <= @pPageSize * @pPageNum
ORDER BY RowNumber

--Final cleaning
DROP TABLE #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre
DROP TABLE #TempTBLDI_EXP_EXPEDIENTARXIU_Ordenacio
END

This post is part of the QUERY series.

In a similar way that the ROW_NUMBER version works, DENSE_RANK allows us to numerate rows and then obtain a page of rows.

Usually DENSE_RANK is used with a DISTINTC or a GROUP BY. Those functions filter duplicate rows and play nice with DENSE_RANK. Duplicate rows appear in a result table because a JOIN is used. See it in the next example:

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-- CTE creation
(
Row_ID,
PK_Classificacio,
Codi_TC,
Observacions,
DataAlta,
DataBaixa,
Any_inici,
Any_final,
IdPare,
Nom,
Nivell_Codi)
AS
(
SELECT DISTINCT --This SELECT uses a DISTINCT to filter out duplicates
DENSE_RANK()--Here DENSE_RANK is used, sintactically is identical to 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 --Here are the JOINs that generate duplicates
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