CLR表值函数如何“流化”？

CLR表值函数如何“流化”？

我假设您使用的是SQL Server2008。正如Microsoft员工在此页上所提到的，2008要求用DataAccessKind标记方法。读取的频率比2005年要高得多。其中之一是当TVF参与事务时（当我进行测试时，情况似乎总是如此）。解决方案是

enlist=false

在连接字符串中指定，但不能与结合使用

context connection=true

。这意味着您的连接字符串必须采用典型的客户端格式：

Data Source=.;Initial Catalog=MyDb;Integrated Security=sspi;Enlist=false

并且您的程序集必须

permission_set=external_access

至少使用来创建。以下作品：

using System;using System.Collections;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;namespace SqlClrTest {    public static class Test {        [SqlFunction( DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read, TableDefinition = "RowNumber int", FillRowMethodName = "FillRow" )]        public static IEnumerable MyTest(SqlInt32 databaseID) { using (var con = new SqlConnection("data source=.;initial catalog=TEST;integrated security=sspi;enlist=false")) {     con.Open();     using (var cmd = new SqlCommand("select top (100) RowNumber from SSP1 where DatabaseID = @DatabaseID", con)) {         cmd.Parameters.AddWithValue("@DatabaseID", databaseID.IsNull ? (object)DBNull.Value : databaseID.Value);         using (var reader = cmd.ExecuteReader()) {  while (reader.Read())      yield return reader.GetInt32(0);         }     } }        }        public static void FillRow(object obj, out SqlInt32 rowNumber) { rowNumber = (int)obj;        }    }}

这是F＃中的同一件事：

namespace SqlClrTestmodule Test =    open System    open System.Data    open System.Data.SqlClient    open System.Data.SqlTypes    open Microsoft.SqlServer.Server    [<SqlFunction(        DataAccess = DataAccessKind.Read,        SystemDataAccess = SystemDataAccessKind.Read,        TableDefinition = "RowNumber int",        FillRowMethodName = "FillRow"        )>]    let MyTest (databaseID:SqlInt32) =        seq { use con = new SqlConnection("data source=.;initial catalog=TEST;integrated security=sspi;enlist=false") con.Open() use cmd = new SqlCommand("select top (100) RowNumber from SSP1 where DatabaseID = @DatabaseID", con) cmd.Parameters.AddWithValue("@DatabaseID", if databaseID.IsNull then box DBNull.Value else box databaseID.Value) |> ignore use reader = cmd.ExecuteReader() while reader.Read() do     yield reader.GetInt32(0)        } :> System.Collections.IEnumerable    let FillRow (obj:obj) (rowNumber:SqlInt32 byref) =        rowNumber <- SqlInt32(unbox obj)