我可以使用SQLCLR存储过程来更新数据库表的列

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.

我想使用查询或存储过程更新数据库表的几列的值,但是想使用我的C#库来更改值。

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.

例如,我希望将表T的列A,B,C替换为加密(A),加密(B)和加密(C),其中加密是C#库的一部分。

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?

我本可以在一个简单的控制台应用程序中完成它,但我必须在很多表中为很多列执行此过程。我可以使用一些SQLCLR存储过程/查询在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
       }
   }
}

1 个解决方案

#1

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:

由于某种原因,这个问题完全重复(我可以使用SQLCLR存储过程来更新数据库表的列(使用一些编译的dll)),但假设其他一个将被关闭(它应该是),我的答案是相同:

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:

您可以使用SQLCLR从C#调用加密,但这是错误的方法。如果需要执行自定义算法,则应将其封装到SQLCLR函数中,以便可以在UPDATE语句中使用,甚至可以在INSERT或SELECT中使用。就像是:

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:

但是,在编写自定义加密算法之前,您可能需要查看几个内置的配对ENCRYPTBY / DECRYPTBY函数,这些函数可能完全符合您的需要:

ENCRYPTBYASYMKEY / DECRYPTBYASYMKEY

ENCRYPTBYASYMKEY / DECRYPTBYASYMKEY
ENCRYPTBYCERT / DECRYPTBYCERT

ENCRYPTBYCERT / DECRYPTBYCERT
ENCRYPTBYKEY / DECRYPTBYKEY

ENCRYPTBYKEY / DECRYPTBYKEY
ENCRYPTBYPASSPHRASE / DECRYPTBYPASSPHRASE

ENCRYPTBYPASSPHRASE / DECRYPTBYPASSPHRASE

#1

public class SP
{
  [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true)]
  public static SqlString EncryptByAES(SqlString TextToEncrypt)
  {
     return DoSomething(TextToEncrypt.Value);
  }
}