Can I use SQLCLR stored procedure to update a column of a database table ( using some compiled dll)

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 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
}
}
}

request: pls dont outrightly down vote the question, let concerned person make changes/add code or report to do so. it takes time to draft a clear question. — Shweta Saxena, Feb 13 '15 at 15:41

Solomon Rutzky · Accepted Answer · 2015-02-13T16:16:38.267

3

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:

edited Feb 13 '15 at 16:16

answered Feb 13 '15 at 15:58

Solomon Rutzky

46,688
9
128
171

thanks, it works. I have a custom AES manages code to do teh encryption-decryption. I have an immature-ish question though. My legacy application returns a huge dataset(over 100,000 records) on a webpage where these records are decrypted element by element, row by row. And it takes forever..they are even exported to excel sometimes and it just timesout. It doesnt look like a right thing to do, but how can i get this decryption to be faster ..? paging would help? – Shweta Saxena Feb 13 '15 at 18:14
@ShwetaSaxena 100k records doesn't seem like that much. How many fields per row? How large are the larger fields? What part times out? Maybe read them into memory from the legacy app and then decrypt from there? – Solomon Rutzky Feb 13 '15 at 20:13
I wrote a small console app to check how mcuh time it takes to decrypt 5 fields per rwo from 100k dataset. It takes a lot of time. Do you think the code for decryption could be causing it? I have put up a question for that : http://stackoverflow.com/questions/28506918/doing-operations-on-each-datarow-of-a-huge-dataset-make-a-webpage-slow-is-pagin – Shweta Saxena Feb 13 '15 at 21:47

Can I use SQLCLR stored procedure to update a column of a database table ( using some compiled dll)

1 Answers1

Linked