Streaming Table-Valued function via CLR function

We can't use dynamic SQL on a Table-Valued Function, as we cannot call stored procedures inside a function. The solution is to create Table-Valued Function via CLR function.

Without further ado, this is the complete code:

using System.Collections;

using System.Data.SqlTypes;
using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

namespace FirstClrFunctionProject
{
    public partial class TheUserDefinedFunctions
    {
        private class Person
        {
            public SqlInt32 PersonId;
            public SqlString PersonName;

            public Person(SqlInt32 personId, SqlString personName)
            {
                PersonId = personId;
                PersonName = personName;
            }
        }


        [SqlFunction(
           DataAccess = DataAccessKind.Read,
           FillRowMethodName = "FindBandMembers_FillRow",
           TableDefinition = "PersonId int, PersonName nvarchar(4000)")]
        public static IEnumerable FindBandMembers(SqlString s)
        {
            bool tryTheAwesome = true;

            string field = s.Value;

            if (tryTheAwesome)
            {
                string connectionString; // "context connection=true"; // can't work on streaming, i.e. this connection string doesn't work if we are using: yield return

                // Must use this instead:
                connectionString = "data source=localhost;initial catalog=AdventureWorks2012;integrated security=SSPI;enlist=false";

                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();

                    using (var personsFromDb = new SqlCommand("select BusinessEntityId, " +  field + " from Person.Person", connection))
                    using (var personsReader = personsFromDb.ExecuteReader())
                    {
                        while (personsReader.Read())
                        {
                            yield return new Person(personId: personsReader.GetInt32(0), personName: personsReader.GetString(1));
                        }
                    }//using                    
                }//using
            }//if

            yield return new Person(personId: 1, personName: "John");
            yield return new Person(personId: 2, personName: "Paul");
            yield return new Person(personId: 3, personName: "George");
            yield return new Person(personId: 4, personName: "Ringo");
            yield return new Person(personId: 5, personName: "Francisco");
            yield return new Person(personId: 6, personName: "Nino");
            yield return new Person(personId: 7, personName: "Marc");
            yield return new Person(personId: 8, personName: "Michael");


        }

        public static void FindBandMembers_FillRow(object personObj, out SqlInt32 personId, out SqlString personName)
        {
            var p = (Person)personObj;

            personId = p.PersonId;
            personName = p.PersonName;
        }
    }
}

Sign the assembly, I just named the strong name key same as the assembly name. No need to put a password on strong name key:




For the SQL:

use master;

go


sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO


IF EXISTS (SELECT * from sys.asymmetric_keys where name = 'MyDllKey') begin
    drop LOGIN MyDllLogin;
    drop ASYMMETRIC KEY MyDllKey;
end;

go



IF NOT EXISTS (SELECT * from sys.asymmetric_keys where name = 'MyDllKey') begin
    CREATE ASYMMETRIC KEY MyDllKey FROM EXECUTABLE FILE = 'c:\x\FirstClrFunctionProject.dll';
    -- http://stackoverflow.com/questions/7503603/cannot-find-the-asymmetric-key-because-it-does-not-exist-or-you-do-not-have-p
    CREATE LOGIN MyDllLogin FROM ASYMMETRIC KEY MyDllKey;
    GRANT EXTERNAL ACCESS ASSEMBLY TO MyDllLogin;
end;

go



use AdventureWorks2012;


IF EXISTS (SELECT name FROM sysobjects WHERE name = 'FindBandMembers')
   DROP FUNCTION FindBandMembers;
go

IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'FirstClrFunctionProject')
   DROP ASSEMBLY FirstClrFunctionProject;
go





CREATE ASSEMBLY FirstClrFunctionProject FROM 'c:\x\FirstClrFunctionProject.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO




-- Thanks Stackoverflow! 
-- http://stackoverflow.com/questions/7823488/sql-server-could-not-find-type-in-the-assembly

CREATE FUNCTION FindBandMembers(@hmm nvarchar(4000)) 
RETURNS TABLE (
   PersonId int,
   PersonName nvarchar(4000)
)
AS EXTERNAL NAME FirstClrFunctionProject.[FirstClrFunctionProject.TheUserDefinedFunctions].[FindBandMembers];
go

SELECT * FROM dbo.FindBandMembers('FirstName') order by personId;
SELECT * FROM dbo.FindBandMembers('LastName') order by personId;
go


-- http://stackoverflow.com/questions/6901811/sql-clr-streaming-table-valued-function-results

If you'll notice, the FirstClrFunctionProject is repeated on this expression:

AS EXTERNAL NAME FirstClrFunctionProject.[FirstClrFunctionProject.TheUserDefinedFunctions].[FindBandMembers];

If you done it this way...

AS EXTERNAL NAME FirstClrFunctionProject.TheUserDefinedFunctions.[FindBandMembers];

...it would have an error, as SQL Server will look for TheUserDefinedFunctions class without a namespace, however TheUserDefinedFunctions is enclosed in a namespace, hence you will get the following error when you run the SQL with the expression above:

Could not find Type 'TheUserDefinedFunctions' in assembly 'FirstClrFunctionProject'.

This...

AS EXTERNAL NAME FirstClrFunctionProject.[FirstClrFunctionProject.TheUserDefinedFunctions].[FindBandMembers];

...is interpreted as:

AS EXTERNAL NAME AssemblyName.[NamespaceName.Classname].[FunctionName];

If you have this kind of error when you run the SQL:

The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

Chances are the certificate is not existing, or the asymmetric key is pointing to a path that SQL Server can't access. The latter case happened to me, I tried pointing the asymmetric key directly on the output path, e.g.

CREATE ASYMMETRIC KEY MyDllKey FROM EXECUTABLE FILE = 'C:\Users\Michael\Documents\GitHub\FirstClrFunctionProject\FirstClrFunctionSolution\FirstClrFunctionProject\bin\Debug\FirstClrFunctionProject.dll';

If that's the case, try to copy the FirstClrFunctionProject.DLL and the FirstClrFunctionProject.snk file to other directories.


Another source of error might be if you are using .NET Framework 4.0 and up, and your SQL Server is version 2008, recompile your solution with .NET 3.5 Framework


If you have an error like the following, which seems to effect only today, remove this Windows update: KB2840628

Msg 6260, Level 16, State 1, Line 2
An error occurred while getting new row from user defined Table Valued Function : 
System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlConnection' threw an exception. ---> System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlConnectionFactory' threw an exception. ---> System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlPerformanceCounters' threw an exception. ---> System.MethodAccessException: Attempt by method 'System.Configuration.TypeUtil.CreateInstanceRestricted(System.Type, System.Type)' to access method 'System.Diagnostics.SwitchElementsCollection..ctor()' failed. ---> System.Security.SecurityException: Request failed.
System.Security.SecurityException: 
   at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(RuntimeAssembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandleInternal rmh, SecurityAction action, Object demand, IPermission permThatFailed)
   at System.Security.CodeAccessSecurityEngine.CheckSetHelper(PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandleInternal rmh, Object assemblyOrString, SecurityAction action, Boolean throwException)
   at System.Security.PermissionListSet.CheckSetDemandWithModification(PermissionSet pset, PermissionSet& alteredDemandSet, RuntimeMethodHandleInternal rmh)
   at System.Security.PermissionListSet.CheckSetDemand(PermissionSet pset, RuntimeMethodHandleInternal rmh)
   at System.Security.PermissionListSet.DemandFlagsOrGrantSet(Int32 flags, PermissionSet grantSet)
   at System.Security.CodeAccessSecurityEngine.ReflectionTargetDemandHelper(Int32 permission, PermissionSet targetGrant)
System.MethodAccessException: 
   at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, 
 ...
System.TypeInitializationException: 
   at System.Data.SqlClient.SqlConnection..ctor()
   at System.Data.SqlClient.SqlConnection..ctor(String connectionString)
  ...

If you think using dynamic SQL on table-valued function is ought to be this hard, try to have a different perspective: http://www.anicehumble.com/2013/07/seamless-dynamic-sql-using-postgresql.html


Download the full code and SQL from: https://github.com/MichaelBuen/FirstClrFunctionProject

Happy Coding! ツ