Can I Use Sqlclr Stored Procedure To Update A Column Of A Database Table ( Using Some Compiled Dll)

I wanted to update the values of a few columns of a database table, using queries or stored procedure, but wanted to use my C# library to alter the value. For eg, I want the colum

Solution 1:

You can use SQLCLR to call encryption from C#, though this is the wrong approach. If you need to do a custom algorithm, you should encapsulate that into a SQLCLR function so that it can be used in an UPDATE statement or even an INSERT or SELECT or anywhere. Something like:

publicclassSP
{
  [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true)]
  publicstatic SqlString EncryptByAES(SqlString TextToEncrypt)
  {
     return DoSomething(TextToEncrypt.Value);
  }
}

Then you can use that function as follows:

UPDATE tb
SET    tb.FieldA = EncryptByAES(tb.FieldA)
FROM   dbo.TableName tb
WHERE  tb.FieldA some_test_to_determine_that_FieldA_is_not_alreay_encrypted;

BUT, before you write a custom encryption algorithm, you might want to check out the several built-in paired ENCRYPTBY / DECRYPTBY functions that might do exactly what you need:

• ENCRYPTBYASYMKEY / DECRYPTBYASYMKEY

• ENCRYPTBYCERT / DECRYPTBYCERT

• ENCRYPTBYKEY / DECRYPTBYKEY

• ENCRYPTBYPASSPHRASE / DECRYPTBYPASSPHRASE