Efficient way to call a T-SQL built-in function from a .NET CLR UDF?

Question

From a CLR UDF (C# syntax) is it possible to acquire a more or less direct handle to a built-in TSql function, instead of trying to kludge it into an ADO.NET SQL text like so?

// 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 textual SQL way seems inefficient if the method is being run multiple times. I suppose it would depend on the SQL Server integration features available in the .NET Framework Class Library when creating the UDF.

Maybe this is already the most efficient means available considering the APIs. I don't know.

Using SQL Server 2005.

---

Example of Built-in

An example of a built-in function I've been using and this can serve as the example, is SQL Server's EncryptByKey(..) function.

Encryption is called multiple times on many values and fields in a secure database. When it's used it's often highly used, unlike some other functions that are just called sporadically.

It cannot be called like a custom UDF with dbo because it's not owned, it's part of SQL Server.

select dbo.EncryptByKey(..) << SQL Server error

Instead it must be called unqualified like so

select EncryptByKey(..). << OK

It would be nice to call this more efficiently when it's used in a CLR UDF like above, considering its usage is so prevalent and constant.

Answer 1

Using the SqlCommand might seem to be inefficient, but it isn't really, because the commands are all being executed on the context connection. Unless you're doing this in a tight loop (in which case you should probably think about redesigning the UDF), the overhead is going to be minimal; most of the overhead involved in traditional database access tends to be in bringing up the connection and traversing the network, neither of which are issues here.

If you're just worried about all the extra code you need to write, then use a helper method:

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

Then build on that if you have especially common functions:

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

Try it and see - if you're not doing this thousands of times then you shouldn't notice any difference in performance. (Note also that I'm not worrying about SQL injection here because we're already in the database - there's no way to to get to this UDF from the outside world.)

To my knowledge there isn't going to be any way that's more "efficient". You have to remember that your CLR UDF isn't truly "inside" the database connection, it's running in its own AppDomain which has been injected with a SqlContext, so there's no such thing as "direct" access to the SQL syntax - you have to actually issue those commands over the context connection, and any simplification would be mere syntactic sugar.

If you find yourself doing this with a very high frequency then I would consider whether or not it's really appropriate to be doing all the work you're trying to do from within a CLR UDF. The main purpose of a CLR UDF is to extend the functionality that's already available in SQL Server. If you're doing excessive data access or executing an inordinate number of commands just to get to the built-in functions, it could be a sign that you've inverted your dependencies and would be better off writing a normal UDF that branches out to a few utility CLR UDFs. Just something to think about.