A method instance in a C # class is invoked in SQL Server, using the. NET assembly) _mssql

The demand is like this, I encrypt some field data when I manipulate data in a. NET program. These data are a lot of systems shared, one of the Delphi program also need to use, and need to decrypt the data, because I am in the. NET encryption method is more special, in the Delphi program decryption more cumbersome and to consume a lot of time, so have to let Sqlser Ver the way the assembly is invoked to resolve the problem.

Here is just one example, posted out to share.

Create a dll,class with the following code:

Copy Code code as follows:

Namespace Mydll
{
public partial class MyClass
{
[Sqlmethod]
public static SqlString UrlDecode (string value)
{
return new SqlString (Httputility.urldecode (value));
}
}
}

On the database server.

Then run the following T-SQL code:

Copy Code code as follows:

EXEC sp_configure ' show advanced options ', ' 1 ';
Go
Reconfigure;
Go
EXEC sp_configure ' clr enabled ', ' 1 '
Reconfigure;
Go

ALTER DATABASE dbname SET trustworthy on
Go

CREATE ASSEMBLY
[System.Web] From
' C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll '
With permission_set = UNSAFE
Go

CREATE ASSEMBLY Sql_clr_url
From ' C:\MyDLL.dll '
With permission_set = UNSAFE

Go

CREATE FUNCTION Sqlurldecode (@urlstr NVARCHAR (4000))
RETURNS NVARCHAR (4000)
As
EXTERNAL NAME Sql_clr_url. [Sqlclr.myclass]. UrlDecode
Go

You can then call this function in T-SQL.

Copy Code code as follows:

SELECT dbo. Sqlurldecode (Name) from Table