Encrypt Data in one "slot" in database

I read over a great tutorial on how to encrypt the data using a symmetrical key in one column. Click here to view it.

This has helped me set up a column for encryption, which is what I need. My problem is that I will constantly be updating rows in my database - so copying over one whole original column to the encrypted columm like the tutorial shows doesn't work. Is there a wa开发者_运维问答y that I can insert a value that is given to me (using C#/asp) and direclty encrypt it as I insert it into the database, rather than having to place it in one column and copying it over, and then dropping the other column?

---

Just call EncryptByKey on your data to encrypt You don't need another column. In this case it has to be included in your SQL which should be a parameterized query or a stored procedure.

Insert into Whatever YourEncryptedData Values ( EncryptByKey(...)) You may have top open your key first depending on your implementation.

http://msdn.microsoft.com/en-us/library/ms174361.aspx

---

You need to have the certificate open do do the encryption. This is from the page you linked to:

OPEN SYMMETRIC KEY TestTableKey
DECRYPTION BY CERTIFICATE EncryptTestCert

UPDATE TestTable
SET EncryptSecondCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SecondCol)

Inserting works the same:

OPEN SYMMETRIC KEY TestTableKey
DECRYPTION BY CERTIFICATE EncryptTestCert

INSERT INTO TestTable (FirstCol, EncryptSecondCol)
VALUES (6, ENCRYPTBYKEY(KEY_GUID('TestTableKey'),'Sixth'))

You can execute the open key command before doing your insert or create a stored procedure to do the insert.