在基于 CLR 的表值函数中执行 Web 服务， 并将 IEnumerable 类型输出表示为 SQL Server 中的表

• 下载源代码 - 32.58 KB

引言

在本文中，我将介绍如何在基于CLR的表值函数(TVF)中调用Web服务，然后如何将该Web服务的IEnumerable输出直接表示为SQL Server 2005中的表。我将提出两种执行Web服务的方法来克服CLR基于环境的安全限制；第一种是通过SQL Server用户上下文向Web服务传递显式凭据；第二种是将SQL Server用户模拟为本地服务帐户用户。

背景

几周前，我接到一项任务，需要找到一种方法，将第三方Concept Search (CAAT)的结果与我们数据库中的搜索结果集成起来。我们已经基于数据库中存储的搜索项文本创建了CAAT索引，但项的元数据存储在数据库中而不是搜索索引中。因此，每次执行CAAT搜索时，我们都需要从数据库中获取找到项的元数据。CAAT搜索以Web服务形式公开其所有功能，其搜索结果以SearchItem对象数组的形式返回，类型为IEnumerable。因此，基于输出类型，我们需要找到一种方法，可以轻松地将IEnumerable表示为SQL Server中的表。感谢Microsoft使基于CLR的表值函数输出也采用IEnumerable形式，这一功能使我们能够直接将CAAT搜索的输出表示为SQL Server中的表。现在，解决方案是在基于CLR的TVF中执行CAAT搜索Web服务，并以表格形式遍历返回ItemId的结果对象。当这些ItemId返回时，我们可以用它们与数据库中的其他表进行进一步的连接，以获取存储在数据库中的找到项的元数据。

代码

代码是一个Visual Studio 2008解决方案，包含两个项目。一个项目类型为**数据库项目-->Microsoft SQL Server-->SQL CLR**，第二个项目是一个ASP.NET Web服务，供CLR项目中的TVF调用。该Web服务并非实际的CAAT搜索Web服务，而是一个示例Web服务，仅用于说明目的。

CLR项目

此项目包含两个基于CLR的表值函数。它们都调用Web服务并以IEnumerable类型数组的形式提供类似的输出，它们之间的唯一区别在于调用Web服务的方式是使用显式凭据还是模拟SQL Server的本地服务帐户，如引言中所述。因此，第一种方法是传递显式凭据。

/// <summary>
/// The TVF calling the web service with explicit credentials.
/// </summary>
/// <returns>
[SqlFunction(FillRowMethodName = "FillRow",
       TableDefinition = "ItemID int")]
public static IEnumerable TVFWithCredentials()
{
    IEnumerable items;
    // Create the instance of a web service to be called. Remember this is not actual 
    // CAAT search web service but a similar fake web service just for testing.
    CLRTVFService service = new CLRTVFService();
    // Passing the credentials explicitly because the code is running within the 
    // context of a SQL Server user that cannot access some external resources.
    // There is possibility that we could access a web service deployed locally
    // without passing these credentials but when its deployed remotely then we 
    // need to pass the network credentials explicitly.
    ICredentials cr = new NetworkCredential("YourUserName", "YourPassword", 
	"YourDomainName");
    service.Credentials = cr;
    // Execute the web service and get the IEnumerable type results
    items = service.PerformConceptSearch();
    return items;
}
public static void FillRow(object row, out int ItemID)
{
    ItemID = ((SearchItem)row).ItemID;
}	

[注意:] 上面列表中名为“FillRow”的第二种方法是每个行的事件处理程序。当IEnumerable中的每一行都被遍历以将输出发送给调用者时，它就会工作。

通过模拟本地服务帐户用户来调用Web服务的第二种方法是。

/// <summary>
/// The TVF calling a web service by impersonating the local service account user.
/// </summary>
/// 
[SqlFunction(FillRowMethodName = "FillRow",
        TableDefinition = "ItemID int")]
public static IEnumerable TVFWithImpersonation()
{
    WindowsIdentity windowsIdentity = null;
    WindowsImpersonationContext userImpersonated = null;
    windowsIdentity = SqlContext.WindowsIdentity;
    IEnumerable items = null;

    // Switch the context to local service account user (a domain user) 
    // by getting its identity in order to call the web service
    userImpersonated = windowsIdentity.Impersonate();
    if (userImpersonated != null)
    {
        // Create the instance of a web service to be called. 
        // Remember this is not actual 
        // CAAT search web service but a similar fake web service just for testing.
        CLRTVFService service = new CLRTVFService();
        // Execute the web service and get the IEnumerable type results
        items = service.PerformConceptSearch();
        // Switch the context back to the SQL Server
        userImpersonated.Undo();
    }
    return items;
} 

[注意:] 本地服务帐户用户在SQL Server登录中列为“NT AUTHORITY\SYSTEM”。它是一个域用户，在安装了SQL Server的机器上拥有本地管理员权限，并且它是安装SQL Server的同一域用户。为了调用外部Web服务，我们需要模拟该用户。另请注意，必须使用以下T-SQL命令将此用户映射到需要模拟它的数据库。

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'NT AUTHORITY\SYSTEM')
CREATE USER [NT AUTHORITY\SYSTEM] FOR LOGIN [NT AUTHORITY\SYSTEM] _
	WITH DEFAULT_SCHEMA=[db_owner]

Web服务项目

此项目是一个ASP.NET Web服务，为了使其类似于实际的CAAT搜索，它只包含一个名为“PerformConceptSearch”的方法。该方法仅创建一个IEnumerable类型数组，包含5个硬编码的假SearchItem类对象，该类也在同一项目中定义。这是PerformConceptSearch方法的代码。

/// <summary>
/// The method returns a hard coded IEnumerable array of 5 SearchItem class objects 
/// just to illustrate the CAAT search concept because we don't 
/// have actual CAAT search here.
/// </summary>
/// 
[WebMethod]
public SearchItem[] PerformConceptSearch()
{
    SearchItem[] items = new SearchItem[5];
    for (int i = 0; i <= 4; i++)
    {
        items[i] = new SearchItem();
        items[i].ItemID = i + 1;
    }
    return items;
}

/// <summary>
/// A fake SearchItem class.
/// </summary>
public class SearchItem
{
    public int ItemID { get; set; }
}

部署和执行

在将基于CLR的程序集部署到SQL Server之前，我们需要满足以下先决条件。

1. 启用目标数据库上的CLR执行，该功能默认是禁用的。启用CLR执行的T-SQL命令如下：

   sp_configure 'clr enabled', 1
   reconfigure
   GO

2. 使数据库TRUSTWORTHY。这是必需的，因为我们的CLR项目将以EXTERNAL_ACCESS权限部署，因为它调用外部Web服务，并且对于具有EXTERNAL_ACCESS权限的程序集，我们需要将数据库设置为TRUSTWORTHY。将数据库设置为TRUSTWORTHY的命令是：

   ALTER DATABASE [CLRTVF] SET TRUSTWORTHY ON
   GO

最后是部署。要部署Web服务，只需打开附加的解决方案，右键单击“CLRTVFWebService”项目，然后单击属性。在项目属性页面上，选择Web选项卡，如下所示：

请选中“使用本地IIS Web服务器”复选框，然后按“创建虚拟目录”按钮，它将在您的计算机上自动创建一个具有Web服务的虚拟目录。请不要更改创建的虚拟目录的名称，因为此默认地址在CLR项目Web引用中使用，如果更改了，我们需要更新CLR项目中的Web引用并重新编译。

为了部署CLR项目，我在附加解决方案的主文件夹中名为“数据库脚本”的文件夹中创建了两个数据库脚本。首先执行名为“CLRTVF.sql”的脚本以创建测试数据库，然后执行“CLRTVF_Deployment.sql”以部署CLR项目。请记住在部署脚本中-我使用了“LOCATE_YOUR_BIN_PATH_HERE”字符串作为您机器上的路径。它将指向您下载的项目“CLRTVF”的位置，例如，您可能将附加的项目下载到“E:\CLRTVF”。现在，您的情况下的完整路径将如下所示：“E:\CLRTVF\CLRTVF\bin\Debug\”，这将指向要部署到SQL Server的程序集的DLL。

最后是执行。我们可以像下面给出的SQL语句一样测试任何TVF。

SELECT ItemID FROM [TVFWithCredentials]()
-- OR 
SELECT ItemID FROM [TVFWithImpersonation]()

我们通过与Items表连接来获取搜索项元数据的意图是这样的：

SELECT Items.ItemID, Items.ItemName, Items.ItemMetaData FROM [Items] _
	INNER JOIN [TVFWithCredentials]() AS TVF ON Items.ItemID = TVF.ItemID

结论

基于CLR的表值函数的IEnumerable类型输出使我们能够直接在SQL Server中将任何IEnumerable类型数组表示为表，这使得当我们希望将外部数组与SQL Server集成时，生活变得更加轻松。