通过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和Sharepoint List的数据同步，这是一个实际项目的应用。
客户的Sharepoint站点上拥有四个List，相互之间组成了一个具有层次结构的Organization数据，该数据最终来源于由另外一个系统所使用的数据库中的一个表。在项目的前期，开发人员手动从数据库中将数据导出来，然后导入到Sharepoint站点已创建好的List中；项目的后期，源数据库中的数据发生了变化，于是开发人员需要手动检查这些数据的变化，并同步到Sharepoint List中。因为数据量比较大，这项工作很耗费人力和精力，于是，我们打算在SQL Server中编写一个存储过程，并添加到SQL Server的计划任务中，让SQL Server在一个周期中自动去同步这些数据。因为涉及到Sharepoint站点和List的访问以及权限的控制，我们想到了在SQL Server中使用CLR方法来编写存储过程并进行部署。以下是这四个List的结构和数据源的截图。
Area List
两个text类型的字段，Title和Area_Code，Title是Area的名称，Area_Code是Area的地区编码。
Region List
Title：text类型，Region的名称。
Area：Loopup类型，指向Area的ID。
Region_Code：text类型，Region的编码。
Country List
Title：text类型，Country的名称。
Region：Lookup类型，指向Region的ID。
Country_Code：text类型，Country的编码。
CompanyCode List
CompanyName：text类型，Company的名称。
CompanyCode：Company的编码。
Country：Lookup类型，指向Country的ID。
数据库中的源表
将层次关系组合在一个表中，Area_Code和Area_Name对应Area List，Region_Code和RegionName对应Region List，SubRegion_Code和SubRegion_Name对应Country List，Company_Code和Company_Name对应CompanyCode List。
准备工作
我们事先需要在一个测试环境中进行编码测试，然后再部署到生产环境上。先在测试服务器上新建一个空白Sharepoint站点，然后按照上面的描述创建四个List，再从客户的服务器上将数据导入到测试用的数据库中。创建List的时候注意字段的名称，重新修改名称会导致List字段的内部名称和外部名称不一致，这在后面编写同步程序的时候需要注意一下。
打开Visual Studio，创建一个SQL Server Project工程，取名为SqlClrSharePointSynchronizer，接下来就开始我们的同步程序编码啦。
开始
程序主要完成两部分的工作，先连接到数据源所在的数据库，取到数据，然后连接到要进行数据同步的Sharepoint站点，最后调用WSS提供的方法将数据写到对应的List中。考虑到所编写的程序是需要部署到宿主数据库中才能执行的，首先要考虑的问题是采用什么方式执行程序。因为程序所完成的功能相对比较独立，不依赖于数据库中的其它对象（如表、视图等），比较理想的方式就是存储过程和自定义函数，而自定义函数又分为Scale函数和TVF函数两种（在前面的文章中已经介绍过），Scale函数适用于对数据的处理，类似于一个数据处理工具函数，用在这里显然不合适，而TVF函数需要指定一个合适的输出表结构，况且我们的程序只是完成一个数据同步过程，输出的信息可能简单到只有成功或失败这样简单的文字，所以也不需要TVF函数。另外，自定义函数是必须要有返回值的，这也可能不是我们所需要的。那么存储过程是最合适的选择了，于是我们先建好一个名为Synchronizer的类和一个public static方法SyncHierarchy，来表示一个数据库的存储过程。
接下来的工作是要把基础工作做好，一个DBUtil类，用来处理数据库连接和获取数据，这个在大多数三层结构的ASP.NET程序开发中都有，没什么好说的，只是我在这里不需要它完成更多的功能，只需要执行SQL语句并返回一个填充好数据的DataSet即可，其余的方法都被删掉了。读者在附件中可以看到这个简单的类，这里我就不再重复贴出代码了。
重要的是需要在工程中使用WSS提供的方法。我们在工程中引用Sharepoint站点提供的Web服务，它的地址是http://SiteName/_vti_bin/lists.asmx，其中SiteName就是Sharepoint站点的地址。将这个Web引用取名为Lists，修改代理类如下：
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Server;
using System.Security.Principal;
using System.Net;
namespace SqlClrSharePointSynchronizer.Lists
{
public partial class Lists
{
public WindowsImpersonationContext impersonationContext;
public Lists(string siteUrl, bool impersonateCaller, IWebProxy proxy)
: this()
{
if (siteUrl.EndsWith(@"/"))
{
siteUrl = siteUrl + "_vti_bin/Lists.asmx";
}
else
{
siteUrl = siteUrl + "/_vti_bin/Lists.asmx";
}
if (impersonateCaller)
{
this.impersonationContext = SqlContext.WindowsIdentity.Impersonate();
}
this.Url = siteUrl;
this.Credentials = CredentialCache.DefaultCredentials;
//this.Credentials = new NetworkCredential("uid", "pwd", "domain");
if (proxy != null)
{
this.Proxy = proxy;
}
}
protected override void Dispose(bool disposing)
{
if (disposing && impersonationContext != null)
{
impersonationContext.Undo();
}
base.Dispose(disposing);
}
}
}
主要功能是提升程序访问Sharepoint List时的权限，该权限利用SqlContext对象通过SQL Server宿主程序的当前登录帐户去模拟，记住权限提升之后必须执行Undo操作，这个是对应的！
核心部分的代码在SPListUtil类中完成，主要实现调用Web Service和执行更新SPList的方法。主要使用到了两个Web Service方法，Lists.GetListItems()和Lists.UpdateListItems()。有一个需要注意的地方，SPList中有Lookup类型的字段，这就需要我们在更新数据的时候将这种字段的值写成1;#Title1;#2;#Title2的形式，这个需要自己在程序中去处理。还有就是当一次性更新的数据量比较大时，执行Lists.UpdateListItems()方法时所使用的CAML会被自动截断，这个我已经在《使用WSS的Lists.UpdateListItems()方法之被截断的CAML 》一文中给出了解决办法。
具体的代码读者可以看我在本文后面给出的附件。我通过四条SQL语句从数据源表中读取数据并填充到DataSet中，然后顺序将这四部分数据进行转换并更新到对应的SPList中，同时输出Lists.UpdateListItems()方法的返回信息，该信息是一段XML文本，包含了执行CAML更新数据时的详细情况，在FormatResult()方法中进行格式化，将返回信息格式化为我们希望的样子。下面是我在程序中使用的用于取数据的SQL语句。
string SELECT_REGION = "Select Distinct REGION_NAME, AREA_CODE, REGION_CODE From {0}dbo.SECCompanyHierarchy Where REGION_NAME <> ''";
string SELECT_COUNTRY = "Select Distinct SUBREGION_NAME, REGION_CODE, SUBREGION_CODE From {0}dbo.SECCompanyHierarchy Where SUBREGION_NAME <> ''";
string SELECT_COMPANYCODE = "Select COMPANY_CODE, COMPANY_NAME, SUBREGION_CODE From {0}dbo.SECCompanyHierarchy Where COMPANY_CODE <> '' And COMPANY_NAME <> ''";
因为考虑到宿主数据库中可能会使用LinkedServers方式连接客户数据库服务器，所以使用了这种方式的SQL语句。其它的地方应该都很好理解，读者看源代码就OK了。
最后说一下部署。按照我在前面几篇文章中介绍的部署方式就可以完成dll在宿主数据库服务器上的部署，Visual Studio中直接在项目文件上点击右键，点Deploy就可以自动部署，一般情况下自动部署不会有什么问题，但是我们总会遇到将编译好的dll部署到正式的服务器上的问题，读者可以看看我在第三篇文章中给出的那个手动部署的SQL脚本的例子。部署成功后，在SQL Server查询分析器中执行，下面是执行过程的截图。
存储过程的第一个参数给定了数据同步的目的地址，第二个参数是LinkedServers的名称，如果数据库就在本地的宿主服务器上，则不需要指定第二个参数。同时Messages窗口中也给出了经过格式化后的执行结果。
结语
通过四篇文章介绍了SQL CLR编程的背景、示例、调试和部署，以及实际应用的例子，希望对正在使用或将来会使用SQL CLR的朋友有所帮助，同时也为自己今后使用SQL CLR编程开发做一个记录。
| 1 | 2 | 3 | 4 |
