Yukon 中的 CLR Magic

引言

Microsoft .NET framework CLR 已与新版本的 SQL Server 集成。 T-SQL 是一种现有的数据访问操作语言，它仅适用于面向过程的语言。 它是在 10 多年前设计的，因此我们需要一种现代方法来创建数据对象，SQLServer 和 Common Language Runtime 的集成使开发人员能够使用面向对象的语言创建例程。 当我们在 SQLServer 中调用托管例程时，它会以进程内模式托管 CLR。 本文首先向您介绍这些新的 .NET CLR 功能，并向您展示一些示例（使用 VB.NET/C#），说明如何使用它们。

.NET 托管代码提供了四种类型的用户定义 SQL Server 例程

1. 用户定义的存储过程
2. 用户定义的触发器
3. 用户定义函数

所有 .NET 例程都可以在 SQL 语言的任何地方调用。

用户定义的存储过程

通常，存储过程将表格值和消息返回给客户端。 .NET 用户定义的存储过程例程可以使用 SQLPipe.Send 命令来做到这一点。 SQLPipe 类用于将结果发送给客户端，类似于 ASP.NET 中的 Response.Write 命令。 我们将在下面的示例中看到它是如何做的。

[VB.NET]

   Imports System.Data.Sqlserver 
   Imports System.Data.Sql 
     Public Class clsBookStoreProcedures 
     <SqlProcedure> 
        Public Shared Sub GetAuthorName() 
          Dim pipeSql As SqlPipe = Sqlcontext.GetPipe() 
          Dim cmdSql As Sqlcommand = Sqlcontext.GetCommand() 
          cmdSql.CommandText= _
             "Select AuthorName from Books where BookId   = @prmBookId" 
          Dim prmBookId As SqlParameter =     
          cmdSql.Parameters.Add("@prmBookId",SqlDBType.String) 
          prmBookId.Direction = ParameterDirection.Input 
          prmBookId.Value="BID001" 
          pipesql.Send(cmdSql.ExecuteScalar()) 
        End Function 
    End Class    

[C#]

    using System.Data.Sqlserver; 
    using System.Data.Sql; 
     public Class clsBookStoreProcedure 
      { 
       [SqlProcedure] 
      public static void GetAuthorName() {
         SqlPipe  pipeSql = Sqlcontext.GetPipe(); 
         Sqlcommand  cmdSql = Sqlcontext.GetCommand(); 
         cmdSql.CommandText=
           "Select AuthorName from Books where BookId   = @prmBookId"; 
         SqlParameter prmBookId  =     
         cmdSql.Parameters.Add("@prmBookId",SqlDBType.String); 
         prmBookId.Direction = ParameterDirection.Input; 
         prmBookId.Value="BID001"; 
         pipeSql.Send(cmdSql.ExecuteScalar()); 
       } 
     }
 

如何注册

CREATE ASSEMBLY ProcBookstore FROM file://Server/bin/ BookStoreProcedure.dll 
CREATE PROCEDURE GetAuthorByID(@prmID as Varchar) 
    AS EXTERNAL NAME BookStoreProcedure: clsBookStoreProcedure: GetAuthorName 

如何运行

EXEC ProcBookstore()

输出

Tamil selvan Subramanian

用户定义的触发器

创建基于 .NET 的用户定义的触发器是即将推出的 CLR 集成 SQLServer Yukon 启用的另一项新功能。 在下例中，我们将从 Books 表中获取 "INSERTED" 表值

[VB.NET]

   Imports System.Data.Sqlserver 
   Imports System.Data.Sql 
     Public Class clsBookStoreTriggers 
     Public Shared Sub GetInsertedAuthor()   
       Dim cntSql As SqlContext = SqlContext.GetTriggerContext() 
       Dim pipeSql As SqlPipe = Sqlcontext.GetPipe() 
       Dim cmdSql As Sqlcommand = Sqlcontext.GetCommand()   
         If cntSql.TriggerAction = System.Data.Sql.TriggerAction.Insert Then 
            cmdSql.CommandText="Select * FROM INSERTED"    
            pipesql.Execute(cmdSql)   
         End If 
     End Function 
    End Class      

[C#]

  using System.Data.Sqlserver; 
   using System.Data.Sql; 
          public Class clsBookStoreTriggers 
         { 
           public static void GetInsertedAuthor()   
             { 
              SqlContext cntSql = SqlContext.GetTriggerContext(); 
              SqlPipe pipeSql = Sqlcontext.GetPipe(); 
              Sqlcommand cmdSql = Sqlcontext.GetCommand();  
               if (cntSql.TriggerAction == 
                   System.Data.Sql.TriggerAction.Insert)  
                 { 
                  cmdSql.CommandText="Select * FROM INSERTED";    
                  pipesql.Execute(cmdSql);  
                 } 
         } 
       } 
 

如何注册

CREATE ASSEMBLY funBooks FROM "file://Server/bin/funBooks.dll"   
CREATE TRIGGER bookTrigger ON BOOKS  
FOR INSERT AS  
EXTERNAL NAME BookStore:clsBookStoreTriggers:GetInsertedAuthor 
 

用户定义函数

此函数返回单个 SQLType 值，例如integer、string、bit。 例如，如果下面的函数返回 Book ID 对应的书名。 SQLContext是一个类，它提供当前执行环境的数据库连接和事务。 Yukon 支持两种类型的用户定义函数

• 标量值用户定义函数 - 仅返回一个值
• 表值用户定义函数 - 返回多个值

.NET CLR 使用户能够创建聚合函数。 让我们看看如何在 .NET 中创建标量值用户定义函数

[VB.NET]

   Imports System.Data.Sqlserver 
    Imports System.Data.Sql 
      Public Class BookStore 
       <SqlFunction(DataAccess=DataAccessKind.Read)> 
       Public Shared Function GetAuthorName() as String 
       Dim cmdSql As Sqlcommand = Sqlcontext.GetCommand() 
       cmdSql.CommandText= _
           "Select AuthorName from Books where BookId = @prmBookId" 
       Dim prmBookId As SqlParameter =     
                      cmdSql.Parameters.Add("@prmBookId",SqlDBType.String) 
       prmBookId.Direction = ParameterDirection.Input 
       prmBookId.Value="BID001" 
       Return Ctype(cmdSql.ExecuteScalar(),String) 
     End Function 
    End Class    

[C#]

using System.Data.Sqlserver; 
    using System.Data.Sql; 
       public Class BookStore 
       { 
      [SqlFunction(DataAccess=DataAccessKind.Read)] 
      public static string GetAuthorName() {
        Sqlcommand  cmdSql = Sqlcontext.GetCommand(); 
        cmdSql.CommandText=
           "Select AuthorName from Books where BookId   = @prmBookId"; 
        SqlParameter prmBookId  =     
                      cmdSql.Parameters.Add("@prmBookId",SqlDBType.String); 
        prmBookId.Direction = ParameterDirection.Input; 
        prmBookId.Value="BID001"; 
        return (string)cmdSql.ExecuteScalar(); 
      } 
    } 
  

在上面的代码中，SqlFunction自定义属性将信息发送到托管代码，该函数是用于读取、插入、更新还是删除。 在我们的示例中，我们只是从数据库中读取 AuthorName。 因此仅包含DataAccessKind.Read参数。 此属性位于System.Data.Sql命名空间中。 GetCommand函数根据 SQLContext 类中的当前上下文返回 Command 类型。 最后 4 行代码应该让所有 .NET 程序员都熟悉

如何注册

 CREATE ASSEMBLY funBooks FROM "file://Server/bin/funBooks.dll" 
 CREATE FUNCTION GetAuthName() RETURNS String 
 AS EXTERNAL NAME funBooks:BookStore:: GetAuthorName 

如何运行

   Select dbo.GetAuthName() 

输出

Tamilselvan Subramanian

关注点

所有程序集注册过程均由 Whidbey IDE 使用 SQL 项目的“构建”菜单处理。

结论

在本文中，我们已经看到了 .NET CLR 支持的主要例程。 它允许创建用户定义的数据类型和安全机制，以保护用户免受未经授权的访问。 我无法在本文中讨论所有功能。 因此，即将发布的文章肯定会帮助您完成剩余的议题。
请发表您的评论