我可以使用 SQLCLR 存储过程更新数据库表的列吗(使用一些编译的 dll) [英] Can I use SQLCLR stored procedure to update a column of a database table ( using some compiled dll)

问题描述

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

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.

例如，我希望将表 T 的 A、B、C 列替换为 Encrypt(A)、Encrypt(B) 和 Encrypt(C)，其中 Encrypt 是 C# 库的一部分.我本可以在一个简单的控制台应用程序中完成它，但是我必须对许多表中的许多列执行此过程.

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.

我可以使用 SQLCLR 存储过程/查询在 SQL Server Management Studio 中执行此过程吗?如果有人能在这方面提供帮助，那就太好了.

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

推荐答案

您可以使用 SQLCLR 从 C# 调用加密，尽管这是错误的方法.如果您需要执行自定义算法，则应将其封装到 SQLCLR 函数中，以便它可以在 UPDATE 语句甚至 INSERT 或 SELECT 或任何地方使用.类似的东西:

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;

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

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/解密密码

这篇关于我可以使用 SQLCLR 存储过程更新数据库表的列吗(使用一些编译的 dll)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！