通过CLR同步SQL Server和Sharepoint List数据（二）

写在前面

    本系列文章一共分为四部分：

        1. CLR概述。

        2. 在Visual Studio中进行CLR集成编程并部署到SQL Server，包括存储过程、触发器、自定义函数、自定义类型和聚合。

        3. CLR集成编程的调试和所遇到的问题。

        4. 利用CLR同步SQL Server表和Sharepoint List（来源于实际项目应用）。

    本系列文章建立在以下软件环境的基础上：

• Windows Server 2003 Enterprise Edition Service Pack 2
• Microsoft Visual Studio Team System 2008
• Microsoft SQL Server 2008
• Microsoft Office Sharepoint Server 2007

准备工作

    默认情况下SQL Server对CLR的允许状态是关闭的，我们需要在查询分析器中打开它，将CLR设置为允许，这样该服务器将对所有数据库实例开放CLR执行条件。可以按照下面的步骤进行：

Exec sp_configure 'clr enabled'

        该系统存储过程用于查看当前CLR的状态，返回的结果中如果config_value字段的值为0则表示禁止CLR在该服务器上运行，值为1则为允许。增加第二个参数到该存储过程，然后再执行。

Exec sp_configure 'clr enabled', 1--1 enabled, 0 disabled

        查询分析器的消息窗口中给出提示：Configuration option 'clr enabled' changed from 0 to 1. Run the RECONFIGURE statement to install. 按提示运行reconfigure命令进行安装。

reconfigure with override

        现在SQL Server服务器已经允许执行CLR托管代码了，接下来我们只需要在Visual Studio中编写代码，将生成的dll部署到SQL Server中即可。按下图，创建SQL Server Project并设置好数据库连接，然后就可以编写用户自定义类型、存储过程、用户自定义函数、聚合和触发器了。

开始

一个简单的自定义函数

    现在我们已经可以在Visual Studio中开始CLR项目了，在Solution Explorer中右键单击项目文件，点击Add，在其中选择你所要添加的类型。我们从最简单的类型开始，选中User-Defined Function…，取名为DateTimePre.cs，该自定义函数主要实现在用户给定的字符串数据前加上系统当前时间前缀，编写代码如下。

 using System;
 using System.Data;
 using System.Data.SqlClient;
 using System.Data.SqlTypes;
 using Microsoft.SqlServer.Server;
 
 public partial class UserDefinedFunctions
 {
     [Microsoft.SqlServer.Server.SqlFunction]
     public static SqlString DateTimePre(string input, string format)
     {
         string sRst = string.Format("{0}:{1}", DateTime.Now.ToString(format), input);
         return new SqlString(sRst);
     }
 };

        代码很简单，就是在用户给定的文本前加上当前时间前缀，第二个参数用于指定时间显示的样式。先说明一下代码的结构。SQLCLR中定义的类（用户自定义的类除外）都以partial关键字开头，表示它是一个分部类，这个我们不需要去改它，默认的类名可以修改，同一个类中可以有多个自定义的类型（自定义函数、触发器、存储过程等），但都必须用特征属性显示指明类型，如SqlFunction、SqlProcedure、SqlTrigger、SqlUserDefinedType等，这些类型必须定义为public类型的而且必须为static，以向SQL Server完全公开，有些类型必须要有返回值，如自定义函数，如果不需要类型返回值，则可以定义为存储过程类型，这个后面再举例。

    编译代码，部署到SQL Server服务器，这个服务器的地址取决于你在Visual Studio中所指定的数据库服务器，就是我们在创建项目前所指定的那个数据库服务器。在Solution Explorer中右键单击项目文件，点击Deploy，Visual Studio会自动为你进行程序集的部署。因为我们的这个示例很简单，也没有涉及到访问外部资源的代码，所以不用考虑程序集的访问级别和安全性，让Visual Studio按照默认的情况自动进行就可以了。我会在后面专门介绍如何设置程序集的访问级别以及其中遇到的问题。

    部署成功后转到SQL Server Management Studio，打开你所连接的数据库，依次选择Programmability—Functions—Scalar-valued Functions，在下面可以找到我们刚创建的这个类型，表示一切顺利！

    接下来我们在查询分析器中执行它，看一下执行结果，OK。一个简单的自定义函数就完成了，因为有Visual Studio的帮助，使得SQLCLR的编写变得非常简单，否则，你需要在命令行方式下手动进行部署，这个过程比较复杂，也不是本文的重点。

Select dbo.DateTimePre('Begin', '')

流式表值函数（TVF）的实现

    前面我在介绍CLR集成性能的时候提到了流式表值函数（TVF），它返回IEnumerable接口的托管函数，通过特征属性指定返回结果的样式和定义方法，将结果以数据表的形式在SQL Server的查询分析器中输出。它的性能将优于在SQL Server查询分析器中使用扩展存储过程的性能。

    在刚才创建的class文件中再写一个方法，用于实现流式表值函数。示例给出的是在用户指定的位置搜索出用户指定类型的所有文件，然后以规定的表格样式将结果在SQL Server的查询分析器中输出。

 [SqlFunction(FillRowMethodName = "BuildRow",
      TableDefinition = "Name nvarchar(32), Length bigint, Modified DateTime")]
     public static IEnumerable FileListCs(string directoryName, string pattern)
     {
         FileInfo[] files;
         //模拟当前SQL安全上下文
         WindowsImpersonationContext contect = SqlContext.WindowsIdentity.Impersonate();
         try
         {
             DirectoryInfo di = new DirectoryInfo(directoryName);
             files = di.GetFiles(pattern);
         }
         finally
         {
             if (contect != null)
             {
                 contect.Undo();
             }
         }
         return files;
     }
 
     private static void BuildRow(object Obj,
           ref SqlString fileName,
           ref SqlInt64 fileLength,
           ref SqlDateTime fileModified)
     {
         if (Obj != null)
         {
             FileInfo file = (FileInfo)Obj;
             fileName = file.Name;
             fileLength = file.Length;
             fileModified = file.LastWriteTime;
         }
         else
         {
             fileName = SqlString.Null;
             fileLength = SqlInt64.Null;
             fileModified = SqlDateTime.Null;
         }
     }

     对代码的说明。特征属性中的FillRowMethodName用来告诉该函数，输出的结果需要用它指定的函数进行格式化，而格式化的样式（即Table的定义）由TableDefinition的值指定。这也就是说，我们需要自己编写FillRowMethodName所指定的函数，即代码中的BuildRow方法。该方法有一个输入型参数Obj，用于接收流式表值函数的返回值，另外几个ref型的参数必须与TableDefinition所规定的内容一致，并且必须是ref类型的参数。经过这样的规定，流式表值函数就会按照我们定义好的表结构在SQL Server的查询分析器中输出结果。WindowsImpersonationContext对象用于在SQL Server中模拟当前安全上下文，以SQL Server进程的身份执行程序代码，在访问外部资源时（尤其是网络资源），如果当前SQL Server没有访问权限，则依附于它的CLR程序集的访问也会失败。注意在对CLR进行安全上下文模拟之后必须进行Undo操作！

    有一个前提条件需要说明一下。前面我在CLR集成安全性中提到，当CLR的程序集需要访问外部资源时（例如我们这里所访问的磁盘文件），需要设置程序集的访问级别为External，而且需要将宿主数据库的权限级别设置为EXTERNAL_ACCESS，否则SQL Server的查询分析器会提示错误。

Use Test
Go
Alter Database Test Set trustworthy On
Go

     编译、部署，转到SQL Server查询分析器中，在Programmability—Functions—Table valued Functions下可以看到我们刚创建的流式表值函数。执行它！

Select * From Test.dbo.FileListCs('D:\TreeView', '*.*')

    出现了错误！原来我们在执行流式表值函数的特征属性时少了一个DataAccess，下面补上。再运行，函数给出了正确的结果。

Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "FileListCs":
System.InvalidOperationException: Data access is not allowed in this context.  Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.
System.InvalidOperationException:
   at System.Data.SqlServer.Internal.ClrLevelContext.CheckSqlAccessReturnCode(SqlAccessApiReturnCode eRc)
   at System.Data.SqlServer.Internal.ClrLevelContext.GetCurrentContext(SmiEventSink sink, Boolean throwIfNotASqlClrThread, Boolean fAllowImpersonation)
   at Microsoft.SqlServer.Server.InProcLink.GetCurrentContext(SmiEventSink eventSink)
   at Microsoft.SqlServer.Server.SmiContextFactory.GetCurrentContext()
   at Microsoft.SqlServer.Server.SqlContext.get_CurrentContext()
   at Microsoft.SqlServer.Server.SqlContext.get_WindowsIdentity()
   at UserDefinedFunctions.FileListCs(String directoryName, String pattern)
.

[SqlFunction(DataAccess = DataAccessKind.Read,
        FillRowMethodName = "BuildRow",
        TableDefinition = "Name nvarchar(32), Length bigint, Modified DateTime")]

存储过程的实现

    在Visual Studio中重新创建一个类型为Stored Procedure的class，编写代码如下。

 [Microsoft.SqlServer.Server.SqlProcedure]
     public static void GetData(string tbName)
     {
         using (SqlConnection cn = new SqlConnection("context connection=true"))
         {
             using (SqlCommand cmd = cn.CreateCommand())
             {
                 cmd.CommandText = string.Format("Select * from {0}", tbName);
                 cn.Open();
                 SqlContext.Pipe.Send(cmd.ExecuteReader());
             }
         }
     }

     按照我在前面CLR集成安全性中介绍的，CLR在EXTERNAL_ACCESS访问模式下默认以SQL Server当前的服务账户运行，所以我们可以利用当前上下文来获取数据库连接字符串，并进行相关数据处理（如输出Message，访问站点等）。SqlContext.Pipe.Send方法用于在当前上下文中输出结果到SQL Server查询分析器的Results窗口中，它可以接收SqlDataReader、SqlDataRecord和string类型的参数，同时也可以SqlContext.Pipe.ExecuteAndSend(SqlCommand)这样来用。

    编译、部署，转到SQL Server查询分析器中，刚刚编写的存储过程出现在Programmability—Stored Procedure下，直接调用该存储过程，得到结果。

触发器的实现

    触发器的实现比较简单，主要还是方法前的特征属性需要描述清楚，这里给出一个示例，当对表Area进行更新操作的时候会在表Region中更新相应的值，相关截图和代码如下。

表Area（修改前）		表Region（修改前）

 [Microsoft.SqlServer.Server.SqlTrigger(Name = "Triggers", Target = "Area", Event = "FOR UPDATE")]
     public static void TriggersTest()
     {
         string comText = @" Declare @oldTitle varchar(50)
                             Declare @newTitle varchar(50)
                                                    
                             Select @oldTitle = Title From Deleted
                             Select @newTitle = Title From Inserted
 
                             Update Region Set Area = @newTitle Where Area = @oldTitle";
 
         using (SqlConnection cn = new SqlConnection())
         {
             cn.ConnectionString = "context connection=true";
             cn.Open();
             using (SqlCommand cmd = cn.CreateCommand())
             {
                 cmd.CommandText = comText;
                 SqlContext.Pipe.ExecuteAndSend(cmd);
             }
         }
     }

         编译并部署，触发器出现在相关表下面的Triggers目录下，并且该触发器的图标上有一个小锁，表示该触发器是由CLR生成的，并且不能被修改。现在update表Area的一条数据，会发现Region表中相关的记录也发生了变化，表示触发器已经生效了。

Update Area Set Title = 'APAC' Where Title = 'APAC1'

表Area（修改后）		表Region（修改后）

用户自定义类型的实现

    用户自定义类型理解起来可能稍微有点复杂，在实际应用当中可能也很少用到，我在这里直接引用了MSDN上的一个例子，了解一下。实际上，当我们在Visual Studio上创建一个UserDefinedType时，IDE已经为我们做了很多事情了，剩下的工作只需要以填空的方式完善代码即可，这样说来，实际上也不是那么复杂啊，至少Visual Studio在为我们搭建好的代码结构中已经有了不少注释，我们应该知道怎么去做。

    这个示例中定义了一个UserDefinedType类型，用户使用的时候可以给定一个字符串值，然后通过内部的转换显示出相应的信息。如给定1:2，则表示right top，给定-1:3，则表示left top等，内部的转换规则需要自己去实现，当然，你也可以实现更加复杂的结构。

  1 [Serializable]
  2 [Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
  3 public struct UserDefinedType : INullable
  4 {
  5     public override string ToString()
  6     {
  7         if (this.IsNull)
  8         {
  9             return "NULL";
         }
         else
         {
             return this.m_x + ":" + this.m_y;
         }
     }
 
     public bool IsNull
     {
         get { return this.m_Null; }
         set { this.m_Null = value; }
     }
 
     public static UserDefinedType Null
     {
         get
         {
             UserDefinedType h = new UserDefinedType();
             h.m_Null = true;
             return h;
         }
     }
 
     public static UserDefinedType Parse(SqlString s)
     {
         if (s.IsNull)
             return Null;
 
         string str = Convert.ToString(s);
         string[] xy = str.Split(':');
 
         UserDefinedType u = new UserDefinedType();
         u.X = Convert.ToInt32(xy[0]);
         u.Y = Convert.ToInt32(xy[1]);
 
         return u;
     }
 
     public SqlString Quadrant()
     {
         if (m_x == 0 && m_y == 0)
         {
             return "centered";
         }
 
         SqlString stringReturn = "";
 
         if (m_x == 0)
         {
             stringReturn = "center";
         }
         else if (m_x > 0)
         {
             stringReturn = "right";
         }
         else if (m_x < 0)
         {
             stringReturn = "left";
         }
 
         if (m_y == 0)
         {
             stringReturn = stringReturn + " center";
         }
         else if (m_y > 0)
         {
             stringReturn = stringReturn + " top";
         }
         else if (m_y < 0)
         {
             stringReturn = stringReturn + " bottom";
         }
 
         return stringReturn;
     }
 
     // This is a place-holder field member
     public int X 
     {
         get { return this.m_x; }
         set { this.m_x = value; }
     }
 
     public int Y
     {
         get { return this.m_y; }
         set { this.m_y = value; }
     }
 
     // Private member
     private int m_x;
     private int m_y;
     private bool m_Null;
 }

    编译部署，在SQL Server的查询分析器中打开Programmability—Types—User-Defined Types，可以看到刚创建的类型，执行后可以看到结果。

CREATE TABLE test_table (column1 UserDefinedType)
go

INSERT INTO test_table (column1) VALUES ('1:2')
INSERT INTO test_table (column1) VALUES ('-2:3')
INSERT INTO test_table (column1) VALUES ('-3:-4')

Select column1.Quadrant() From test_table

聚合的实现

    与自定义类型类似，在Visual Studio中创建聚合（Aggreagte），IDE同样已经为我们做好了准备。这里我也给出一个MSDN上的例子（不过对于嵌套循环稍做了修改），用来在聚合中计算字符串数据类型中的原音字符的个数。

 [Serializable]
 [Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]
 public struct Aggregate
 {
     public void Init()
     {
         countOfVowels = 0;
     }
 
     public void Accumulate(SqlString value)
     {
         // list of vowels to look for
         List<string> vowels = new List<string>();
         vowels.Add("a");
         vowels.Add("e");
         vowels.Add("i");
         vowels.Add("o");
         vowels.Add("u");
 
         // for each character in the given parameter
         for (int i = 0; i < value.ToString().Length; i++)
         {
             if (vowels.Contains(value.Value.Substring(i, 1).ToLower()))
             {
                 // it is a vowel, increment the count
                 countOfVowels += 1;
             }
         }
     }
 
     public void Merge(Aggregate value)
     {
         Accumulate(value.Terminate());
     }
 
     public SqlString Terminate()
     {
         return countOfVowels.ToString();
     }
 
     // This is a place-holder member field
     private SqlInt32 countOfVowels;
 }

    编译部署，在SQL Server的查询分析器中打开Programmability—Functions—Aggreate Functions，可以看到刚创建的聚合函数，下面是执行后的结果。

结语

    CLR创建SQL Server对象应该还不止上面提到的这几种类型，但常用的基本都在这里了，尤其是存储过程、函数和触发器。利用C#来编写这些类型，灵活性更大，可操控性也更强了。下一篇我将会介绍如何在Visual Studio中进行CLR调试，对程序集的分发和手动部署，以及常见问题的解决办法。

示例代码下载

1

2

3

4