Efficient way to call T-SQL inline function from .NET CLR UDF?

Question

From the CLR UDF (C # syntax), you can get a more or less direct descriptor into the TSql built-in function, instead of trying to clone it into ADO.NET SQL code like that?

// Programming .inside NET CLR UDF:

// Is there a more efficient way than this command?
SqlCommand cmd... = new SqlCommand( ...'SELECT EncryptByKey(@Param, @Param2...) );
cmd.Execute();

The above SQL text method seems inefficient if the method is run multiple times. I believe this will depend on the SQL Server integration capabilities available in the .NET Framework class library when creating UDF.

Perhaps these are already the most effective tools available with the API in mind. I dont know.

Using SQL Server 2005.

---

Built-in example

, , , SQL Server EncryptByKey (.. ).

. , , , .

UDF dbo, , SQL Server.

select dbo.EncryptByKey(..) < SQL  

select EncryptByKey(..). & ; < OK

, CLR UDF, , , .

Answer 1

SqlCommand , , . ( , , UDF), ; , , , , .

, , :

public static TResult Execute<TResult>(SqlConnection connection, string cmdText)
{
    using (SqlCommand cmd = new SqlCommand(cmdText, connection))
    {
        return (TResult)cmd.ExecuteScalar();
    }
}

, :

public static byte[] EncryptByKey(SqlConnection connection,
    string keyName, string clearText)
{
    return Execute<byte[]>(connection,
        string.Format("SELECT ENCRYPTBYKEY(KEY_GUID('{0}'), '{1}')"));
}

- , . ( , SQL- , - UDF .)

, "". , CLR UDF "" , AppDomain, SqlContext, , "" SQL - , .

, , , , , CLR UDF. CLR UDF - , SQL Server. , , , , UDF, CLR UDF. .