title: How to Encrypt and Decrypt Data in SQL Server 2008 r2 from C#?
body: <p>Hi I am able to do Encryption and Decryption for a specified string and Saving in to the database. But i want to search that Data.But it is not possible to search data for an Encrypted column. There is any Way to that. In front i am using AES, the below method is used for encryption.</p>
  
  <pre><code>  private string Encrypt(string Text)
      {
          string EncryptionKey = "abcdef@123";
          byte[] clearBytes = Encoding.Unicode.GetBytes(Text);
          using (Aes encryptor = Aes.Create())
          {
              Rfc2898DeriveBytes pdb = new Rfc2898DeriveBytes(EncryptionKey, new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
              encryptor.Key = pdb.GetBytes(32);
              encryptor.IV = pdb.GetBytes(16);
              using (MemoryStream ms = new MemoryStream())
              {
                  using (CryptoStream cs = new CryptoStream(ms, encryptor.CreateEncryptor(), CryptoStreamMode.Write))
                  {
                      cs.Write(clearBytes, 0, clearBytes.Length);
                      cs.Close();
                  }
                  Text = Convert.ToBase64String(ms.ToArray());
              }
          }
          return Text;
      }
  </code></pre>
  
  <p>the below method is used for Decryption</p>
  
  <pre><code>     private string Decrypt(string Text)
      {
          string EncryptionKey = "abcdef@123";
          byte[] cipherBytes = Convert.FromBase64String(Text);
          using (Aes encryptor = Aes.Create())
          {
              Rfc2898DeriveBytes pdb = new Rfc2898DeriveBytes(EncryptionKey, new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
              encryptor.Key = pdb.GetBytes(32);
              encryptor.IV = pdb.GetBytes(16);
              using (MemoryStream ms = new MemoryStream())
              {
                  using (CryptoStream cs = new CryptoStream(ms, encryptor.CreateDecryptor(), CryptoStreamMode.Write))
                  {
                      cs.Write(cipherBytes, 0, cipherBytes.Length);
                      cs.Close();
                  }
                  Text = Encoding.Unicode.GetString(ms.ToArray());
              }
          }
          return Text;
      }
  </code></pre>
  
  <p>can anybody help me based on this.., </p>