Search code examples
c#sql-serverencryptionsql-server-2012sqlclr

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 columns A,B,C of table T to be replaced with Encrypt(A), Encrypt(B) and Encrypt(C) where Encrypt is a part of a C# library. I could have done it in a simple console application, but I have to do this process for a lot of columns in lot of tables.

Could I use a SQLCLR stored procedure / query to do this process in SQL Server Management Studio? It will be really great if someone could assist in this.

public class SP
{
[Microsoft.SqlServer.Server.SqlFunction()]
public static void Enc()
{
 using (SqlConnection connection = new SqlConnection("context connection=true"))
    {            
        connection.Open();
        SqlCommand command;
        SqlCommand command1;
        for (int i = 0; i < 1; i++)
        {                
            command = new SqlCommand("SELECT " + tableFieldArray[i, 1].ToString() + " FROM " + tableFieldArray[i, 0].ToString(), connection);

            SqlDataReader reader = command.ExecuteReader();                
            using (reader)
            {
                while (reader.Read())
                {

                    if (!reader.IsDBNull(0) && !String.IsNullOrEmpty(reader.GetString(0)))
                    {                            
                            //SqlContext.Pipe.Send("Data = " + reader.GetString(0) + "; Encrypted = " + Encrypt(reader.GetString(0)));
                            SqlContext.Pipe.Send("UPDATE " + tableFieldArray[i, 0].ToString() + " SET "
                                                                 + tableFieldArray[i, 1].ToString() + " = '" + Encrypt(reader.GetString(0)) + "' "
                                                                 + "WHERE " + tableFieldArray[i, 1].ToString() + " = '" + reader.GetString(0) + "'");                             
                            //query = "UPDATE " + tableFieldArray[i, 0].ToString() + " SET "
                            //                                     + tableFieldArray[i, 1].ToString() + " = '" + Encrypt(reader.GetString(0)) + "' "
                            //                                     + "WHERE " + tableFieldArray[i, 1].ToString() + " = '" + reader.GetString(0) + "'";                                                                                        
                            command1 = new SqlCommand("UPDATE " + tableFieldArray[i, 0].ToString() + " SET "
                                                                 + tableFieldArray[i, 1].ToString() + " = '" + Encrypt(reader.GetString(0)) + "' "
                                                                 + "WHERE " + tableFieldArray[i, 1].ToString() + " = '" + reader.GetString(0) + "'",connection);
                    }                                                                                                
                }                    
            }

            SqlCommand command1 = new SqlCommand(query , connection);
            command1.ExecuteNonQuery();
        }

        connection.Close();
    }
}
public static string Encrypt(string TextFromForm)
{
    //implementation
}
}
}
Solution

  • 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:

    public class SP
{
  [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true)]
  public static 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: