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

go4321

　　写在前面
　　本系列文章一共分为四部分：
　　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
  

　　
　　前言
　　CLR（Common Language Runtime）公共语言运行时是Microsoft在.NET出来之后创造出来的一个概念，它是.NET架构中重要的组成部分，为所有.NET Framework代码提供执行环境。在CLR中运行的代码称为托管代码。CLR提供执行程序所需的各种函数和服务，包括实时（JIT）编译、分配和管理内存、强制类型安全、异常处理、线程管理和安全性等。相信读者已经在任何一本介绍.NET的书中对它进行了了解，并且深知CLR的工作原理。本文要介绍的不仅仅是.NET架构中的CLR，更多的则是有关CLR的集成编程。事实上，Microsoft在公共语言运行时（CLR）集成编程上已经做了很多准备了，以至于用户现在可以用.NET的任何一种语言将自己编写好的功能安插在微软的任何一款产品上（可能有些夸张了），例如SQL Server、Office产品等，本文正是针对CLR在SQL Server上的应用进行介绍。有关CLR在其它产品上的应用，我将在其它系列文章中再做介绍（如VSTO等）。
　　从SQL Server 2005开始，Microsoft就已经在其中集成了公共语言运行时（CLR）组件，只不过当时的应用可能还不太广泛（也许我当时还并没有怎么听说），使用的用户不多。但是，这也就意味着用户已经可以使用.NET的任何一种语言（如VB.NET和C#.NET等）来为数据库编写存储过程、触发器、用户定义类型、用户定义函数、用户定义聚合和流式表函数等等数据库对象了。这个消息听起来着实让人很兴奋，这让那些许多年来都十分保守的DBA、DEV（数据库开发人员）们有了更多的选择，同时也让许多单纯的SDE（软件开发人员）可以尝试数据库开发，从而让程序和后台的数据库结合得更加紧密，开发人员之间的协作更加顺畅。
　　
　　公共语言运行时（CLR）集成概述
　　Microsoft SQL Server已经具备了与.NET Framework的公共语言运行时（CLR）组件集成的功能。CLR为托管代码提供服务，例如跨语言集成、代码访问安全性、对象生存期管理以及调试和分析支持，它具有以下一些特点：

• 更好的编程模型。.NET Framework语言在许多方面都比 Transact-SQL丰富，它为SQL Server开发人员提供了以前没有的构造和功能。开发人员还可以利用 .NET Framework 库的功能，它提供了大量可用于快速有效地解决编程问题的类。
  
• 改进了安全和安全性。托管代码在数据库引擎承载的公共语言运行时环境中运行。SQL Server利用这一特点为在 SQL Server 早期版本中提供的扩展存储过程提供更安全更可靠的替代方法。
  
• 能够定义数据类型和聚合函数。 用户定义类型和用户定义聚合是两个新的托管数据库对象，这两个对象扩展了SQL Server的存储和查询功能。
  
• 通过标准化环境简化了开发。数据库开发集成到将来版本的Microsoft Visual Studio .NET开发环境中。开发人员在开发和调试数据库对象和脚本时所使用的工具与他们编写中间层或客户端层的 .NET Framework组件和服务时所使用的工具相同。
  
• 具备改善性能和可扩展性的潜力。在多数情况下，.NET Framework语言编译和执行模型通过Transact-SQL提高性能。
  

　　托管代码使用代码访问安全性（CAS）来使程序集无法执行某些操作。SQL Server使用CAS来帮助保护托管代码，并阻止对操作系统或数据库服务器的侵害。
　　CLR集成的优点
　　Transact-SQL是为了在数据库中直接进行数据访问和操纵而专门设计的。虽然Transact-SQL在数据访问和管理方面表现很好，但它不是完整的编程语言。例如，Transact-SQL不支持数组、集合、for-each循环、移位或类。虽然可以在Transact-SQL中模拟某些这样的构造，但托管代码已经集成了对这些构造的支持。根据具体情况，这些功能足以为在托管代码中实现某些数据库功能提供充分理由。
　　选择Transact-SQL还是托管代码
　　如果代码主要执行没有或只有很少过程逻辑的数据访问，请使用Transact-SQL。如果要编写有复杂逻辑并且CPU占用量大的函数和过程，或者想使用.NET Framework的BCL，则使用托管代码。
　　选择在服务器中执行还是在客户端中执行
　　影响使用Transact-SQL还是托管代码的另一个因素是您想将代码驻留在服务器计算机上，还是在客户端计算机上。Transact-SQL和托管代码都可以在服务器上运行。这种方式可以将代码和数据靠近放在一起，并允许您利用服务器的强大处理能力。另一方面，您可能希望避免将处理器占用量大的任务放在数据库服务器上。目前大多数客户端计算机都有非常强大的功能，因此您可能希望通过将尽可能多的代码放在客户端上，来利用这种处理能力。托管代码可以在客户端计算机上运行，而Transact-SQL不能。
　　选择扩展存储过程还是托管代码
　　可以生成扩展存储过程来执行使用Transact-SQL存储过程无法实现的功能。但是，扩展存储过程可能有损于SQL Server进程的完整性，而经过验证确定为类型安全的托管代码则不会。进一步来说，在CLR的托管代码与SQL Server之间更深入地集成了内存管理、线程及纤程的调度以及同步服务。如果所编写的存储过程需要执行在Transact-SQL中不可能完成的任务，则CLR集成有比扩展存储过程更安全的方式来实现它。
　　
　　公共语言运行时（CLR）集成性能
　　现在开发人员可以自由选择Transact-SQL或者CLR进行SQL Server数据库开发，但是两者在性能方面各有优缺点，针对于不同类型的运算和数据库对象，下表给出了两者之间的区别。

类型	Transact_SQL	CLR
用户定义函数	执行数据访问时更有效。	适用于过程代码、计算和字符串操作以及需要大量计算和不执行数据访问的部分。
用户定义聚合	非基于游标的本机内置聚合函数的性能高于CLR方式。	性能高于基于游标的聚合，但执行速度较慢。
流式表值函数（TVF函数）	—	托管TVF的性能优于可比扩展存储过程实现的性能，它返回IEnumerable接口的托管函数。
数组与游标	游标的性能低于CLR中的数组	当Transact-SQL游标必须遍历更容易表示为数组的数据时，使用托管代码可以显著提高性能。
字符串数据	char或varchar数据类型。	托管函数中可以是SqlString或 SqlChars类型。
扩展存储过程	无法查看或控制扩展存储过程的资源使用情况。	可以使用托管代码对给定的线程进行检测。

　　
　　公共语言运行时（CLR）集成安全性
　　与.NET Framework公共语言运行时 (CLR) 集成的SQL Server的安全模式用于管理和保护SQL Server内运行的不同类型CLR对象和非CLR对象之间的访问。这些对象可能由Transact-SQL语句或服务器上运行的其他CLR对象调用。对象之间的调用称为链接。对这些对象执行的安全检查类型取决于相关的链接类型。CLR集成安全模式可实现以下目的：

• 默认情况下，在SQL Server中运行托管用户代码不应当损害SQL Server的完整性和稳定性。如果执行有可能损害 SQL Server可靠性的操作，则应当受到适当的高级权限的保护。
  
• 托管用户代码不应当获得对数据库中用户数据或其他用户代码的未经授权访问。用户定义代码应当在调用该代码的用户会话的安全上下文中运行，且拥有该安全上下文的正确特权。
  
• 应当有控制来限制用户代码不得访问服务器以外的任何资源，而只能用于本地数据访问和计算。
  
• 用户定义代码不应能通过在SQL Server进程中运行而获得对系统资源的未经授权访问。
  

　　SQL Server已经集成了SQL Server基于用户的安全模式和CLR基于代码访问的安全模式。SQL Server主机策略级别授予程序集的代码访问安全性权限集由创建该程序集时指定的权限集决定。有三个权限集：SAFE、EXTERNAL_ACCESS和UNSAFE。

SAFE	最具限制性的权限集，只允许内部计算和本地数据访问，无法访问外部系统资源，如文件、网络、环境变量或注册表。并且只能使用上下文连接字符串指定数据库连接，即context connection=true或context connection=yes。
EXTERNAL_ACCESS	与SAFE具有相同的权限，但允许访问外部系统资源。
UNSAFE	允许程序集不受限制地访问SQL Server内部和外部的资源，此时程序集被授予FullTrust。

　　一般情况下，在不使用SQL Server外部系统资源时建议采用SAFE方式的程序集，如果在程序集中需要访问外部系统资源，推荐使用EXTERNAL_ACCESS，而不是UNSAFE，后者将允许程序集中的代码对SQL Server进程空间进行非法操作，可能会损害SQL Server的健壮性和可靠性。EXTERNAL_ACCESS程序集默认情况下将以SQL Server的当前服务账户运行，它可以显示模拟调用方的Windows身份验证安全上下文，这也就是我在后面使用SQL CLR连接Sharepoint List时为什么要将程序集设置为EXTERNAL_ACCESS！
　　
　　结语
　　总之，公共语言运行时（CLR）为使用C# Custom程序集连接SQL Server和外部资源（诸如Sharepoint List、网络资源、文件系统等），以及SQL Server本身数据运算提供了良好的基础和更好的便利性。在下一篇文章中我将介绍如何在SQL Server 2008中开启CLR并编写CLR使之在SQL Server中成功运行，当中可能会遇到很多小的问题，到时我会一一给出解决办法。

1

2

3

4