SQLCLR、SQL Server 2005 中的触发器和 Web 服务

引言

当今应用程序中来自不同服务器/平台的数据集成发挥着重要作用。Web 服务的出现向我们展示了一个全新的世界。开发人员比以往任何时候都更需要跟上最新工具提供的新技术。

在本教程中，我们将讨论 SQL Server 2005 中发布的一项技术——SQL CLR，以及它与 Web 服务的集成。

开发

我们将使用 Microsoft 的 Northwind 数据库。如果您尚未安装它（因为此数据库不随 SQL Server 2005 一起提供），请访问 http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&DisplayLang=en。这些脚本适用于 SQL Server 2000，但它们也适用于 2005 版本。

如果您不知道 SQL CLR 是什么，它是 SQL Server 2005 与 Visual Studio 的 CLR 的集成，因此我们可以使用我们喜欢的语言（这里是 C#）创建触发器和存储过程。

我将展示的示例并非基于 Microsoft 的最佳实践和设计，仅用于说明 SQL Server 的一种可能性。实际上，由于性能方面的考虑，通过触发器访问 Web 服务并非好事。

要执行此示例，我们需要以下软件：

- Visual Studio 2005 Professional

- SQL Server 2005 Developer

让我们设想以下场景：我们拥有 Northwind Traders SQL 数据库，并且每次我们将美元价格更改为其他币种时，我们都希望更新另一个列（我们将创建）名为“PriceInReal”，其中包含来自互联网的当前巴西雷亚尔汇率。那么可能会出现以下问题：为什么这个转换不是由访问该应用程序的客户端应用程序完成的？这是因为我们假设有一个 Windows Forms 应用程序和一个 Web 应用程序。这样，我们就必须从应用程序内部更改雷亚尔价格，这很不实用。好吧，正如我之前所说，这只是一个示例！因此，我们的触发器将捕获来自互联网的汇率，并将自动更新所有雷亚尔价格。为了实现这一点，我们将使用一个我很久以前使用过的免费 Web 服务，它非常易于使用。该网站是 www.webservicex.net。

准备好了吗？

首先，我们需要在 Northwind 数据库中创建一个名为“PriceInReal”的列。这应该很容易完成，只需使用 SQL Server Management Studio，打开我们的数据库，然后向 Products 表添加一个新列。

所以我们将创建一个 SQL Server 项目。

我们将项目命名为 SQLCLRTest。

接下来，您将被要求选择一个数据库。选择 Northwind。

现在您应该添加对货币转换器网站的引用。

您应该输入：http://www.webservicex.net/CurrencyConvertor.asmx

点击“Go”并“Add Reference”。

好了！现在我们有了我们的 Web 服务！

接下来，右键单击 Solution，选择 Add > Trigger。

我们将我们的触发器命名为“UpdateRates.cs”。

这将是我们的类代码。

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using SQLCLRTest.net.webservicex.www;

public partial class Triggers
{
    // Enter existing table or view for the target and uncomment the attribute line
    [Microsoft.SqlServer.Server.SqlTrigger (Name="UpdateRates", Target="Products", Event="FOR UPDATE")]
    public static void UpdateRates()
    {
        SqlTriggerContext ctx = SqlContext.TriggerContext;
        if (ctx.IsUpdatedColumn(5))
        {
            CurrencyConvertor cc = new CurrencyConvertor();
            Double rate = cc.ConversionRate(Currency.USD, Currency.BLR);
            SqlConnection cnn = new SqlConnection("context connection=true");
            cnn.Open();
            SqlCommand cmd = new SqlCommand("UPDATE Products SET PriceInReal=" + rate + "*UnitPrice", cnn);
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
    }
}

现在我将逐步解释上面的代码。

首先，我们需要 [SqlTrigger] 属性，它会告诉 Vistual Studio 将此类视为触发器。

接下来，我们需要创建一个名为 ctx 的变量来确定被修改的列，即第 5 列（单价）。然后我们将调用货币转换器从 USD 转换为 BLR。

然后我们调用更新，没什么神秘的！

最复杂的部分是使该代码正常工作，因为 SQL Server 2005 具有调用 Web 服务的安全机制。为了做到这一点，我们需要将我们的程序集定义为“不安全”。

所以让我们看看出现的问题，因为我希望您参与其中。

既然上面的代码可以编译，我们将对解决方案进行“部署”，这样我们的 DLL 就会在数据库中。

右键单击 Solution，选择“Deploy”，由于我们很懒，我们将无需手动注册 DLL！

现在如果我们尝试修改 UnitPrice 的价格，我们会得到转换结果，对吗？

请放轻松，您会收到以下错误：

这是因为 SQL Server 需要另一个程序集来访问 Web 服务，一个“Serialization Assembly”。我该怎么做？很简单，转到项目的属性，并将“generates serialization assembly”功能设置为“on”。此外，您还应设置“Allow unsafe code”选项。

重要提示：转到“Database”选项卡，并将“Permission Level”设置更改为“Unsafe”。请注意，由于您正在访问外部 Web 服务，SQL Server 将此视为潜在威胁，因此您需要完成所有这些工作……

还有什么需要做的吗？是的，您应该修改 SQL Server 上一个名为 Trustworthy 的属性。如何？只需打开一个新的查询并输入：

ALTER DATABASE Northwind SET TRUSTWORTHY ON<code>

按 F5。

完成了吗？还没有，因为我们需要注册我们的 Serialized Assembly DLL……首先，我们可以生成解决方案。请注意，您可能需要修改 DLL 路径。

<code>CREATE ASSEMBLY xmlSerializer FROM 'D:\Visual Studio 2005\Projects\SQLCLRTest\SQLCLRTest\bin\Debug\SQLCLRTest.XmlSerializers.dll' WITH PERMISSION_SET=EXTERNAL_ACCESS <code>

谢天谢地，就是这样了！！如果您按照上述步骤操作，一切都应该正常工作。

现在是测试阶段，最有趣的部分。

打开 products 表，修改任何产品的价格，然后按“Execute Query”刷新更新后产品的列表。

我知道所有这些配置都很无聊，直到您找到正确的解决方案。我花了好几个小时才发现它们！

祝你好运，回头见！