Can I use SQLCLR stored procedure to update a column of a database table

Question

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 ex, 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 some 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
       }
   }
}

**Exact duplicate** of [Can I use SQLCLR stored procedure to update a column of a database table ( using some compiled dll)](http://stackoverflow.com/questions/28502697/can-i-use-sqlclr-stored-procedure-to-update-a-column-of-a-database-table-using) - please **don't** keep asking the same question over and over again! (the other one was just 40 minutes ago ......) — marc_s, Feb 13 '15 at 16:09
@marc_s , i am new to stack , dont knwo where to close the previous one from, my earlier question got downvoted (without any explanation) even before i could gather code and make edits on it. so i posted a new one thinking previos one will not be considered. this was totally unintentional. It takes time to draft a clear question — Shweta Saxena, Feb 13 '15 at 16:14
Shweta, this one can be closed. I am already answering the other one so you don't need to worry about that. I will copy my answer updates to the other question. — Solomon Rutzky, Feb 13 '15 at 16:15
Well, one recommendation: draft the question **off-line** in your favourite text editor. Indent code and sample data sections by four spaces - that's really all it takes. Once you're done - **then** post it here to avoid these kind of negative experiences from happening again — marc_s, Feb 13 '15 at 16:17
Thanks @marc_s, i sjall do so from now on, how to close the question though? — Shweta Saxena, Feb 13 '15 at 16:19

score 1 · Answer 1 · edited May 23 '17 at 11:43

For some reason this question is a complete duplicate of ( Can I use SQLCLR stored procedure to update a column of a database table ( using some compiled dll) ), but assuming that other one will be closed (it should be), my answer is the same:

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:

yes it is, i am new to stackoverflow and thought my question would not be looked upon because it was downvoted without any explanation even before I could gather code to be added to it. Thanks a ton for responding. — Shweta Saxena, Feb 13 '15 at 16:03
how do i use these inbuilt encrypt/decrypt methods? I am using AES managed class, would that be available in it? — Shweta Saxena, Feb 13 '15 at 16:05
@ShwetaSaxena I have updated with links to the built-in functions. If none of those work for you, the structure I posted for a basic function would be the way to go. — Solomon Rutzky, Feb 13 '15 at 16:10

Can I use SQLCLR stored procedure to update a column of a database table

1 Answers1