Finding and Decrypting a Record
Since the encryption scheme uses a shared IV on the LastName field, if a user types in a last name exactly the same as a previously encrypted value, it will yield the same encrypted value, thus making it searchable. The stored procedure below performs a simple SELECT statement on the LastNameShared column using the @LastName parameter as a varbinary value rather than the text value you would use for an un-encrypted column.

   CREATE PROCEDURE [dbo].[FindClient] 
   @LastName varbinary(100)
   AS
   BEGIN
      SELECT ClientID, Vector, FirstNameCrypted, LastNameCrypted, LastNameShared, ShelterName
         FROM Clients WHERE LastNameShared=@LastName
   END
   GO
   

In the application's code, you take the value the user typed in the last name field, encrypt it using the shared IV and pass it to the FindClient stored procedure. If you get a result, then the first thing you will need to do is get the IV stored on that record and create a new instance of SimpleAES using the encryption key and the IV that you got from the database for that record. You will then use this instance of SimpleAES to decrypt each of the columns and display the result to the user.

Author's Note: While you can create indexes on varbinary columns, keep in mind that the encrypted columns for which you're using a private IV can't be searched; therefore it's a waste of time to index them. Only index the columns that use a shared IV.

   
   private void FindByLastNameButton_Click(object sender, System.EventArgs e)
   {
      using(SqlDataReader reader = 
         SqlHelper.ExecuteReader(DatabaseConnectionString, 
         "FindClient",
         new SqlParameter("@LastName", 
            lib.Encrypt(this.LastNameField.Text))))
      {
      if(reader.Read())
      {
      byte[] PrivateVector = 
         reader.GetSqlBinary(
         reader.GetOrdinal("Vector")).Value;
      SimpleAES privateAES = new SimpleAES(
         this.Key, PrivateVector);
      this.ClientIDField.Text=reader.GetValue(
         reader.GetOrdinal("ClientID")).ToString();
      this.FirstNameField.Text=privateAES.Decrypt(
         reader.GetSqlBinary(
         reader.GetOrdinal("FirstNameCrypted")).Value);
      this.LastNameField.Text=privateAES.Decrypt(
         reader.GetSqlBinary(
         reader.GetOrdinal("LastNameCrypted")).Value);
      this.ShelterNameField.Text=privateAES.Decrypt(
         reader.GetSqlBinary(
         reader.GetOrdinal("ShelterName")).Value);
      }
      reader.Close();
      }
   }

Taking It Further
Feel free to use the downloadable source code and database included with this article for study, but keep in mind that you have considerable work still to do before putting encrypted columns into practice, most notably determining where to store and manage your private encryption keys. It's definitely a lot more work to design your application to support encrypted columns, but if you're storing data that could cost someone their life if revealed, it's a critical must.