流式表值函数结果

本文关键字：函数 结果 | 更新日期: 2023-09-27 18:04:23

我的问题和这个问题非常相似。

然而，我使用SQL Server 2005服务包2 (SP2) (v9.0.3042)和解决方案张贴在那里不适合我。我尝试使用两个连接字符串。其中一个在我的代码中被注释掉了。

我意识到我可以将所有结果存储在内存中的列表或数组列表中并返回。我成功地做到了这一点，但这不是我的目标。目标是能够在结果可用时流式传输结果。

是否可以使用我的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"时，我得到这个错误:

从用户定义的表中获取新行时发生错误有值函数:系统。InvalidOperationException:不允许数据访问这个上下文。上下文是未标记的函数或方法DataAccessKind。读取或SystemDataAccessKind。读，是回调从表值函数的FillRow方法获取数据，或者是一个UDT验证方法。

当我使用其他连接字符串时，我得到这个错误:

从用户定义的表中获取新行时发生错误有值函数:System.Security.SecurityException:请求权限"System.Data.SqlClient类型。SqlClientPermission、系统。数据,版本=2.0.0.0，文化=中性，PublicKeyToken=b77a5c561934e089'失败了。

流式表值函数结果

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

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

说起来容易做起来难，但这是一个很好的起点。简单地使用这一行来代替permission_set=safe，就会产生错误:

为程序集"assemblyName"创建程序集失败'assemblyName'未被授权使用PERMISSION_SET = EXTERNAL_ACCESS。当下列条件之一为真时，该程序集被授权数据库所有者(DBO)具有EXTERNAL ACCESS ASSEMBLY权限和数据库具有可信数据库属性;或者大会是的证书或非对称密钥签名使用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";

它有效!

最初的问题是由于在函数中使用yield关键字，如本问题所述:尽管有DataAccessKind, SqlFunction仍未能打开上下文连接。读到现在。

如果您避免使用yield(将结果存储在中间数组中，最后返回整个数组)，问题就解决了。

或者你可以按照你描述的那样做，避免使用上下文连接，但是如果你这样做，你必须像你描述的那样将你的程序集标记为外部访问。我认为最好将其描述为一种变通方法，而不是解决方案，因为您失去了上下文连接提供的一些好处，并且您必须跳过所有额外的障碍。

在许多情况下，能够使用流行为(yield)的好处确实超过了这种痛苦，但仍然值得考虑这两种选择。

连接的bug: http://connect.microsoft.com/SQLServer/feedback/details/442200/sql-server-2008-clr-tvf-data-access-limitations-break-existing-code

google:

在此上下文中不允许访问数据。上下文是没有标记DataAccessKind的函数或方法。读或SystemDataAccessKind。Read是一个从FillRow获取数据的回调函数方法，或者是UDT验证方法。

把我带到了这一页，但没有我需要的答案。
我终于知道是什么了。
在我的CLR函数中，我调用了另一个方法并传入了函数接收到的值。

听起来无害，但我所做的是使用相同的数据类型(sqlchar、SqlBoolean、SqlInt32)作为我添加的方法的输入参数。

private static ArrayList FlatFile(SqlChars Delimeter, SqlChars TextQualifier)

显然，将这些数据类型用于CLR SqlFunction或SqlProcedure以外的任何东西有时会给您这种类型的隐式错误。

一旦我在我的新方法上删除了这些数据类型，并使用c#的数据类型(string, bool, int)，错误最终消失了。

private static ArrayList FlatFile(string Delimeter, string TextQualifier)

注意:当我使用模拟从另一个域抓取文件时，这个只有错误。
当我将文件流式传输到本地域时，我没有收到此错误，这就是让我失望的原因。

我希望这能在你需要的时候帮到你。我花了很多时间来解决这个问题
相关文章：