SQL CLR：流式处理表值函数结果

Question

我使用的是SQL Server 2005 Service Pack2（SP2）（v9.0.3042），那里发布的解决方案对我不起作用。我尝试使用两个连接字符串。我的代码中有一个被注释掉了。

我意识到我可以将所有结果存储在内存中的List或ArrayList中，然后将其返回。我已经成功地做到了，但这不是这里的目标。目标是能够在结果可用时流式传输。

使用我的SQL Server版本可以吗？

这是我的代码：（请注意，当前实际上并没有使用这些参数。我这样做是为了进行调试）

public static class StoredProcs
{
    [SqlFunction(
        DataAccess = DataAccessKind.Read,
        SystemDataAccess=SystemDataAccessKind.Read,
        FillRowMethodName="FillBaseline",
        TableDefinition = "[baseline_id] [int], [baseline_name] [nvarchar](256), [description] [nvarchar](max), [locked] [bit]"
        )]
    public static IEnumerable fnGetBaselineByID(SqlString projectName, SqlInt32 baselineID)
    {
        string connStr = "context connection=true";
        //string connStr = "data source=.;initial catalog=DBName;integrated security=SSPI;enlist=false";
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand(String.Format(@"
                SELECT *
                FROM [DBName].[dbo].[Baseline] WITH (NOLOCK)
            "), conn))
            {
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        yield return new Baseline(reader);
                    }
                }
            }
        };
    }

    public static void FillBaseline(Object obj, out SqlInt32 id, out SqlString name, out SqlString description, out bool locked)
    {
        Baseline baseline = (Baseline)obj;
        id = baseline.mID;
        name = baseline.nName;
        description = baseline.mDescription;
        locked = baseline.mLocked;
    }
}

这是我的SQL部署脚本的一部分：

CREATE ASSEMBLY [MyService_Stored_Procs]
FROM 'C:\temp\assemblyName.dll'
WITH PERMISSION_SET = SAFE

当我使用连接字符串“ context connection = true”时，出现此错误：

从用户定义的表值的函数获取新行时发生错误：System.InvalidOperationException：在此上下文中不允许数据访问。上下文是未用DataAccessKind.Read或SystemDataAccessKind.Read标记的函数或方法，是从表值函数的FillRow方法获取数据的回调，还是UDT验证方法。

当我使用其他连接字符串时，出现此错误：

从用户定义的表值的函数获取新行时发生错误：System.Security.SecurityException：请求类型为’System.Data.SqlClient.SqlClientPermission，System.Data，Version
= 2.0.0.0，Culture = neutral，PublicKeyToken的权限= b77a5c561934e089’失败。

Answer 1

经过进一步的研究和反复试验，我找到了解决方案。我在这里提到的文章说

您的程序集必须使用Permission_set = external_access创建

说起来容易做起来难，但这是一个很好的起点。只需使用该行代替permission_set = safe就会出现错误：

为程序集“ assemblyName”创建组件失败，因为程序集“ assemblyName”未获得PERMISSION_SET =
EXTERNAL_ACCESS的授权。如果满足以下任一条件，则对程序集进行授权：数据库所有者（DBO）具有EXTERNAL ACCESS
ASSEMBLY权限，并且数据库具有TRUSTWORTHY数据库属性。或用具有相应登录名并具有EXTERNAL ACCESS
ASSEMBLY权限的相应证书或非对称密钥对程序集进行签名。

因此，我要做的第一件事是对我的dll文件进行签名。为此，请在Visual Studio
2010中转到项目属性的“签名”选项卡，然后选中“对程序集进行签名”并为其命名。对于此示例，名称为MyDllKey。我选择不使用密码保护它。然后，当然，我将dll文件复制到了sql
server：C：\ Temp

使用此页面作为参考，我使用以下3个命令基于上述键创建了SQL登录名：

CREATE ASYMMETRIC KEY MyDllKey FROM EXECUTABLE FILE = 'C:\Temp\MyDll.dll'
CREATE LOGIN MyDllLogin FROM ASYMMETRIC KEY MyDllKey
GRANT EXTERNAL ACCESS ASSEMBLY TO MyDllLogin

如上创建登录名后，我现在可以使用以下命令创建程序集：

CREATE ASSEMBLY [MyDll]
FROM 'C:\Temp\MyDll.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS

现在剩下要做的就是使用正确的连接字符串。显然不能enlist=false与结合使用connection=true。这是我使用的连接字符串的示例。

string connStr = @"data source=serverName\instanceName;initial catalog=DBName;integrated security=SSPI;enlist=false";

而且有效！