在 SQL Server 2005 或更高版本中处理 CLR 对象（第二部分）

• 下载 SqlServerCLR_CSharp - 26.53 KB
• 下载 SQLServerCLR_VB - 27.86 KB

本文包含 2 部分。这是文章的第二部分。如果您想查看**第一部分，请点击此处**

目录

• 引言
• 背景
• 数据类型
• CLR 对象的类型
• 存储过程
• 用户定义函数
• 用户定义聚合函数
• 触发器
• 用户定义数据类型
• 参考文献
• 结论

引言

在撰写了第一篇关于 SQL Server CLR 对象基本介绍的文章后，让我们将主题推进到应用领域。在本文中，我将讨论您需要遵循的一些基本编码结构来构建您的第一个 CLR 对象。

错过了文章第一部分的读者，请在此处找到 [^]。希望您会喜欢。

背景

CLR 是 SQL Server 2005 的一项新功能，旨在让 .NET 开发者更好地使用 SQL Server。CLR 对象使用与 .NET Framework 相关的类，并能充分利用 .NET 带来的所有优势。如果程序集是以 EXTERNAL_ACCESS 模式创建的，它就可以调用托管类，这在文章的第一部分已经讨论过。它还可以访问注册表、文件系统、注册表等。当程序集以 PERMISSION SET=UNSAFE 创建时，甚至可以使用指针访问内存位置。

让我们深入探讨构建成功 CLR 对象的实际编码细节。

数据类型

在托管环境中，有许多数据类型包装器可用于正确处理数据并将其传回服务器。以下是一些示例：

1. SqlString：用于将字符串数据发送到 SQL Server。
2. SqlDouble：映射到 Double 数据类型。
3. SqlDateTime：表示 SQL Server 的 DateTime 类型。
4. SqlError：表示 SQL Server 中的 SQL 错误类型。用于从托管环境中引发错误。

您可以创建它们的对象并将数据直接发送到调用环境。您还可以将普通的托管变量直接分配给这些对象，例如：
SqlInt32 sint = 10;
我们可以通过 sint.Value 获取 sint 的值。

CLR 对象的类型

您可以使用 CLR 构建 5 种类型的对象。它们是：

• 存储过程
• 用户定义函数
• 用户定义聚合函数
• 触发器
• 用户定义数据类型

让我们一一讨论它们。

存储过程

存储过程用于围绕数据对象运行一系列逻辑。我们通常将逻辑放在业务类中，以利用托管类的灵活性。CLR 将这种灵活性引入数据库本身，以便您的存储过程可以存储在数据库中，并且任何客户端都可以直接使用。

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void xsp_ExportData()
    {
        SqlPipe pipe = SqlContext.Pipe;
        using (SqlConnection con = new SqlConnection("context connection=true"))
        {
            con.Open();
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM USERS", con))
            {
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
                XmlDocument document = new XmlDocument();
                XmlDeclaration xmlDeclaration = 
document.CreateXmlDeclaration("1.0", "utf-8", null);
                XmlElement rootNode = document.CreateElement("USERS");
                document.AppendChild(rootNode);
                while (reader.Read())
                {
                    XmlElement parentNode = 
document.CreateElement("USER");
                    parentNode.SetAttribute("ID", reader["userid"].ToString());
                    parentNode.InnerText = reader["FNAME"].ToString();
                    rootNode.AppendChild(parentNode);

                }
                pipe.Send(document.InnerXml); 
                // this will print the XML output
                reader.Close();
                pipe.ExecuteAndSend(cmd); 
                // This will show the result
                con.Close();
            }

        }
    }
       
};

上面的代码块将在 SQL Server 中创建一个存储过程。StoredProcedure 类有一个名为 xsp_ExportData 的静态函数，它代表实际的存储过程。

Microsoft.SqlServer.Server.SqlProcedure 属性非常重要，因为它向数据库环境指示该函数是存储过程。

首先，我们创建了 SqlPipe 对象。创建存储过程时，此对象用于将数据发送回客户端。SqlContext.Pipe 包含当前 Pipe 对象，该对象可以从托管环境发送结果。该对象是线程安全的。

我们创建一个 SqlConnection 对象，连接字符串为 "Context-connection=true"。这指示 SQL Server 使用现有上下文创建连接。因此，它不会重新登录服务器，而是使用现有的隐式连接。

我们使用 Pipe.send() 在控制台打印数据，并使用 ExecuteAndSend 将任何结果返回给客户端。ExecuteAndSend 执行一个命令对象并将结果返回给调用环境。

您还可以利用 SendResultStart、SendResultRow 和 SendResultEnd 函数创建动态 SqlDataRecord 对象，如下所示：

SqlDataRecord rec = new SqlDataRecord("1","2","3");
pipe.SendResultsStart(rec);
pipe.SendResultsRow(rec);
pipe.SendResultsEnd(rec);

要运行代码，您首先需要通过右键单击项目并选择“部署”来部署应用程序。运行存储过程以获取结果。

用户定义函数

用户定义函数是类似于存储过程的代码块，但唯一的区别是它有一个返回参数，该参数将隐式地将一个对象返回到调用环境。看看下面的代码：

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlDouble xudf_GetSquare(SqlDouble number)
    {
        // Put your code here
        return Math.Pow(number.Value, 2);
    }
};

上面的函数接受 Double 参数，并将该数字的平方返回给服务器。我们使用了 SqlDouble 类将 Double 数据从托管环境发送出去。
运行代码

SELECT dbo.xudf_GetSquare(5)

用户定义聚合函数

聚合函数是对代表一组记录的多个记录进行操作的函数。我们可以处理一组行以使用聚合函数获得结果。看看下面的代码：

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]
public struct xudaf_AsciiSum
{
    private SqlInt32 asciiCount;

    /// <summary>
    /// Initialize will be called once.
    /// </summary>
    public void Init()
    {
        asciiCount = 0;
    }

    /// <summary>
    /// Write your main logic within this.
    /// </summary>
    /// <param name="Value">Should be the value coming from the Sql console</param>
    public void Accumulate(SqlString Value)
    {
        Char[] chr = Value.ToString().ToCharArray();
        for (int i = 0; i < chr.Length; i++)
        {
            asciiCount += Convert.ToInt32(chr[i]);
        }

    }

    /// <summary>
    /// Should call Accumulate. 
    /// </summary>
    /// <param name="Group"></param>
    public void Merge(xudaf_AsciiSum Group)
    {
        Accumulate(Group.Terminate());
    }
    /// <summary>
    /// Represents the Termination condition of the Aggregate function
    /// </summary>
    /// <returns>Return tyoe if the function</returns>
    public SqlString Terminate()
    {
        return asciiCount.ToString();
    }
}

在上面的代码中，我们创建了一个聚合函数，它处理传递的多条记录。它使用了四个接口：Init、Accumulate、Merge、Terminate。这些函数是多线程运行的。

1. 在代码中，当执行聚合函数时，Init 初始化您在函数中想要的所有变量。我使用 SqlInt32 来计算每条记录的字符 ASCII 值。
2. Accumulate 是您需要编写聚合逻辑的主要块。这里我们传递一个 SqlString 对象，它保存实际对象。Accumulate 函数对组中的每一行都会被调用。
3. Merge 代表一个代码块，它对每个组只调用一次。它用于分组调用 Accumulate 进行处理。
4. Terminate 用于将结果返回到调用环境。

运行上面的聚合函数：
select dbo.xudaf_AsciiSum(upass) from users group by userid，其中 Users 是一个表。

触发器

数据库触发器用于在执行某些数据库操作时自动运行代码块。我们可以使用 CLR 创建托管数据库对象。看看下面的代码：

public partial class Triggers
{
    // Enter existing table or view for the target and uncomment the attribute line
    [Microsoft.SqlServer.Server.SqlTrigger (
     Name="xut_UpdateDateTime", 
     Target="Users", 
     Event="FOR UPDATE")]
    public static void xut_UpdateDateTime()
    {
        SqlTriggerContext triggContext = SqlContext.TriggerContext;
        SqlParameter userID = new SqlParameter("@userid", System.Data.SqlDbType.NVarChar);

        if (triggContext.TriggerAction == TriggerAction.Update) 
            // Represents when to take what action. 
   //This is essential when you create triggers for more than one operation
        {
            using (SqlConnection con = new SqlConnection("context connection=true"))
            {
                con.Open();
                using (SqlCommand sqlComm = new SqlCommand())
                {
                    sqlComm.Connection = con;
                    SqlPipe sqlP = SqlContext.Pipe; 
// Represents the Pipe object to send data to the Sql server
                    sqlComm.CommandText = "SELECT UserId from INSERTED";
                    userID.Value = sqlComm.ExecuteScalar().ToString();
                    sqlComm.CommandText = "INSERT UpdateTrackBack(UpdateUId, UpdateTime) " _
    "VALUES(@userid, GetDate())";
                    sqlComm.Parameters.Add(userID);
                    sqlP.ExecuteAndSend(sqlComm); 
// Executes the Insert Statement and also writes {0} rows affected.
                    sqlP.Send("Trigger executed!");
                }

            }
        }
    }
}

上面的代码将在 "Users" 表上添加一个触发器，以便每当在 Users 表上执行 UPDATE 语句时，该代码块都会自动执行。

与普通 Triggers 类似，我们可以找到表中的临时表 INSERTED（用于 INSERT 和 UPDATE）或 DELETED（用于 DELETE）记录。

我们还可以使用 SqlPipe 对象以与在存储过程中类似的方式来操作调用环境的输出。

SqlTrigger 属性表示将应用触发器的配置部分。您可以使用三个参数：

• Name：表示触发器的名称。
• Target：表示要应用触发器的表对象。
• Event：逗号分隔的字符串，表示要执行触发器块的事件。
  示例：Event = "For Insert, Update, Delete"

在托管环境中，您还可以创建 DDL 语句（如 Create、Alter 或 Drop 语句）的触发器。

用户定义数据类型

我们还可以创建 SQL 数据库中的用户定义数据类型。这些数据类型可以在 TSQL 中用于存储数据。与 XML 数据类型以及 SQL Server 2008 中引入的所有新数据类型类似，您可以构建自己的数据类型来表示 SQL Server 内的业务逻辑数据。

让我们看看下面的代码：

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
public struct xut_GeoPoint : INullable
{
    private int _lat, _lon;
    private bool _isnull;
    public override string ToString()
    {
        return _lat.ToString() + "," + _lon.ToString();
    }

    public SqlInt32 Latitude
    {
        get { return this._lat; }
        set { this._lat = value.Value; }
    }
    public SqlInt32 Longitude
    {
        get { return this._lon; }
        set { this._lon = value.Value; }
    }
    public bool IsNull
    {
        get
        {
            if (this._lat == default(int) && this._lon == default(int))
            {
                this._isnull = true;
                return true;
            }
            else
            {
                this._isnull = false;
                return false;
            }
        }
    }

    public static xut_GeoPoint Null
    {
        get
        {
            xut_GeoPoint h = new xut_GeoPoint();
            h._isnull = true;
            return h;
        }
    }

    public static xut_GeoPoint Parse(SqlString s)
    {
        if (s.IsNull)
            return Null;
        xut_GeoPoint u = new xut_GeoPoint();
        string[] result = s.ToString().Split(',');
        u._lat = Convert.ToInt32(result[0]);
        u._lon = Convert.ToInt32(result[0]);
        return u;
    }

    // This is a place-holder method
    public SqlString GetLatLonData()
    {
        //Insert method code here
        return this._lat.ToString() + "," + this._lon.ToString();
    }

    // This is a place-holder static method
    public static xut_GeoPoint GetEmptyObject()
    {
        xut_GeoPoint point = new xut_GeoPoint();
        return point;
    }
}

在上面的代码中，我们实际上创建了一个可序列化的类，它具有一些函数，如 GetEmptyObject、GetLatLonData 等，并定义了一些属性，如 Latitude、Longitude、IsNull 等。其中每个字段都可以从 TSQL 环境访问。

DECLARE @x xut_GeoPoint
SET @x = '20,5'
set @x.Latitude = 5;
set @x.Longitude = 10;

print @x.ToString()

我们重写 ToString 函数来获取结果。我们可以使用 :: 运算符调用一个普通函数。

下载示例应用程序

• 下载 SqlServerCLR_CSharp - 26.53 KB
• 下载 SQLServerCLR_VB - 27.86 KB

参考文献

http://msdn.microsoft.com/en-us/library/5czye81z(VS.80).aspx
http://msdn.microsoft.com/en-us/library/938d9dz2(VS.80).aspx
http://msdn.microsoft.com/en-us/library/91e6taax(VS.80).aspx
http://msdn.microsoft.com/en-us/library/w2kae45k(VS.80).aspx
http://msdn.microsoft.com/en-us/library/a8s4s5dz(VS.80).aspx

结论

因此，CLR 存储过程是如今编写数据库对象最灵活的方式，我们可以轻松地使用它来创建自定义类。
由于我对 CLR 的东西还很陌生，请随时发表评论，也希望您喜欢我的作品。

您可以在此处查看文章的第一部分