Next step of programming

We can utilize the query of listing foreign key with respective table and fields to generate the alter query for adding constraint. For that we will use the XML and temp table and will generate the list of the alter queries

SELECT TABS.NAME AS [REFTABLE],
   (SELECT NAME
       FROM SYS.TABLES AS CTAB
       WHERE (OBJECT_ID = FK.PARENT_OBJECT_ID)) AS [PARENTTABLE], 
          FK.NAME AS [KEYNAME],
          AC.NAME AS [COLUMNNAME] INTO #TEMPTABLE
FROM SYS.TABLES AS TABS INNER JOIN             
SYS.FOREIGN_KEYS AS FK ON FK.REFERENCED_OBJECT_ID = TABS.OBJECT_ID INNER JOIN             
SYS.FOREIGN_KEY_COLUMNS AS FKC ON FK.OBJECT_ID = FKC.CONSTRAINT_OBJECT_ID INNER JOIN             
SYS.ALL_COLUMNS AS AC ON AC.COLUMN_ID = FKC.PARENT_COLUMN_ID
		     AND AC.OBJECT_ID = FKC.PARENT_OBJECT_ID

SELECT 'ALTER TABLE ' + [PARENTTABLE] + ' ADD CONSTRAINT ' + 
	[KEYNAME] + ' FOREIGN KEY (' + REPLACE((SELECT 
[COLUMNNAME]  AS 'data()' FROM  #TEMPTABLE AS TT 
WHERE TT.[KEYNAME] = #TEMPTABLE.[KEYNAME] FOR XML PATH('')),' ',',')
+ ') REFERENCES (' + REPLACE((SELECT [COLUMNNAME]  AS 'data()' 
FROM  #TEMPTABLE AS TT WHERE TT.[KEYNAME] = #TEMPTABLE.[KEYNAME] 
FOR XML PATH('')),' ',',') + ') ON UPDATE CASCADE ON DELETE CASCADE'    
FROM 
	#TEMPTABLE GROUP BY [KEYNAME], [PARENTTABLE], [REFTABLE]

DROP TABLE #TEMPTABLE

To list the all the foreign keys available in database and there associated tables and fields we can utilize following query

SELECT tabs.name AS [Reference Table],
   (SELECT name FROM sys.tables AS ctab
       WHERE (object_id = fk.parent_object_id)) AS [Parent Table], 
       fk.name AS [Key Name],
        ac.name AS [Column Name] 
FROM sys.tables AS tabs INNER JOIN             
sys.foreign_keys AS fk ON fk.referenced_object_id = tabs.object_id inner join             
sys.foreign_key_columns as fkc on fk.object_id = fkc.constraint_object_id inner join             
sys.all_columns as ac on ac.column_id = fkc.parent_column_id
		     and ac.object_id = fkc.parent_object_id 
ORDER BY tabs.name, 2

Here is one sample class which can be used for 4 different kind of symmetric encryption
1. DES
2. RC2
3. Rijndeal
4. TripleDES

Before using the class there is one important thing to know which is the size of the key. It varies as you change the encryption type for example

• DES support 64 bits.
• RC2 40 to 128 Bits with increment of 8 Bits only.
• Rijndeal support key lengths of 128, 192, or 256 bits.
• and TripleDES supports 128 bits to 192 bits in increments of 64 bits.

using System;
using System.IO;
using System.Text;
using System.Security.Cryptography;

namespace Crypto
{
    public enum SymmetricAlgorimthType{ DES, RC2, Rijndael, TripleDES };

    class SymmetricCryptogarphy    
    {
        private SymmetricAlgorimthType _algorithmType = 
SymmetricAlgorimthType.DES;

        public SymmetricCryptogarphy(SymmetricAlgorimthType algorithmKey)
        {
            _algorithmType = algorithmKey;
        }

        public SymmetricAlgorimthType AlgorithmType
        {
            get { return _algorithmType; }
        }

        public SymmetricAlgorithm GenerateKey()
        {
            SymmetricAlgorithm algoKey;

            algoKey = GetCryptoServiceProvider(_algorithmType);
            algoKey.GenerateKey();
            algoKey.GenerateIV();
            return algoKey;
        }

        public SymmetricAlgorithm GenerateKey(int keySize)
        {
            SymmetricAlgorithm algoKey;

            algoKey = GetCryptoServiceProvider(_algorithmType);
            algoKey.KeySize = keySize;
            algoKey.GenerateKey();
            algoKey.GenerateIV();
            return algoKey;
        }

        public SymmetricAlgorithm GenerateKey(int blockSize, int keySize)
        {
            SymmetricAlgorithm algoKey;

            algoKey = GetCryptoServiceProvider(_algorithmType);

            algoKey.BlockSize = blockSize;
            algoKey.KeySize = keySize;
            algoKey.GenerateKey();
            algoKey.GenerateIV();
            return algoKey;
        }

        public byte[] EncryptData(SymmetricAlgorithm key, string data)
        {
            MemoryStream memoryStream = new MemoryStream();
            CryptoStream encStream = new CryptoStream(memoryStream,
              key.CreateEncryptor(), CryptoStreamMode.Write);
            StreamWriter sw = new StreamWriter(encStream);

            sw.WriteLine(data);
            sw.Close();
            encStream.Close();

            byte[] buffer = memoryStream.ToArray();
            memoryStream.Close();

            return buffer;
        }

        public string DecryptData(SymmetricAlgorithm key, byte[] data)
        {
            MemoryStream memoryStream = new MemoryStream(data);

            CryptoStream encStream = new CryptoStream(memoryStream,
              key.CreateDecryptor(),CryptoStreamMode.Read);
              StreamReader sr = new StreamReader(encStream);

            string val = sr.ReadLine();
            sr.Close();
            encStream.Close();
            memoryStream.Close();

            return val;
        }

        private SymmetricAlgorithm GetCryptoServiceProvider(SymmetricAlgorimthType algorithmType)
        {
            switch (algorithmType)
            {
                case SymmetricAlgorimthType.DES:
                    return new DESCryptoServiceProvider();
                case SymmetricAlgorimthType.RC2:
                    return new RC2CryptoServiceProvider();
                case SymmetricAlgorimthType.Rijndael:
                    return new RijndaelManaged();
                case SymmetricAlgorimthType.TripleDES:
                    return new TripleDESCryptoServiceProvider();
                default:
                    return new DESCryptoServiceProvider();
            }
        }
    }
}