通过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的地区编码。

3-25-2009 5-32-18 PM

Region List

    Title:text类型,Region的名称。

    Area:Loopup类型,指向Area的ID。

    Region_Code:text类型,Region的编码。

3-25-2009 5-33-29 PM

 

 Country List

    Title:text类型,Country的名称。

    Region:Lookup类型,指向Region的ID。

    Country_Code:text类型,Country的编码。

3-25-2009 5-34-19 PM

 

CompanyCode List

    CompanyName:text类型,Company的名称。

    CompanyCode:Company的编码。

    Country:Lookup类型,指向Country的ID。

3-25-2009 5-35-10 PM

 

数据库中的源表

    将层次关系组合在一个表中,Area_Code和Area_Name对应Area List,Region_Code和RegionName对应Region List,SubRegion_Code和SubRegion_Name对应Country List,Company_Code和Company_Name对应CompanyCode List。

3-25-2009 5-38-51 PM

 

 

准备工作

    我们事先需要在一个测试环境中进行编码测试,然后再部署到生产环境上。先在测试服务器上新建一个空白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;
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_AREA = "Select Distinct AREA_NAME, AREA_CODE From {0}dbo.SECCompanyHierarchy Where AREA_NAME <> ''";
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查询分析器中执行,下面是执行过程的截图。 

 
3-25-2009 5-28-54 PM

 

    存储过程的第一个参数给定了数据同步的目的地址,第二个参数是LinkedServers的名称,如果数据库就在本地的宿主服务器上,则不需要指定第二个参数。同时Messages窗口中也给出了经过格式化后的执行结果。

代码示例下载

 

结语

    通过四篇文章介绍了SQL CLR编程的背景、示例、调试和部署,以及实际应用的例子,希望对正在使用或将来会使用SQL CLR的朋友有所帮助,同时也为自己今后使用SQL CLR编程开发做一个记录。

1 2 3 4
posted @ 2009-03-26 15:33 Jaxu 阅读(...) 评论(...) 编辑 收藏