SQLServer使用CCLR开发用户自定义表值函数.docx
《SQLServer使用CCLR开发用户自定义表值函数.docx》由会员分享，可在线阅读，更多相关《SQLServer使用CCLR开发用户自定义表值函数.docx（15页珍藏版）》请在冰豆网上搜索。
SQLServer使用CCLR开发用户自定义表值函数
SQLServer–使用C#CLR开发用户自定义表值函数
MicrosoftSQLServer2005的一项新增功能是其与Microsoft.NETFramework公共语言运行库（CLR）的集成。
这使得人们能够将.NETFramework类和函数纳入Transact-SQL语句和查询。
CLR集成的机制有多种：
•CLR用户定义函数（包括表值函数）。
•CLR用户定义类型。
•CLR存储过程。
•CLR触发器。
本白皮书说明如何使用CLR表值函数根据包括数据库在内的各种源创建报表数据，从而创建可靠的ReportingServices报表。
扩展ReportingServices
使用.NETFramework扩展ReportingServices并集成CLR功能的方法有多种，其中包括以下方法：
•传递扩展—传递报表来响应事件
•呈现扩展—除ReportingServices支持的之外，还能以其他格式显示报表
•安全扩展—提供您自己的身份验证和授权机制用于查看和管理报表
•数据处理扩展—通过开发能够处理来自ReportingServices不支持的数据源的数据
•自定义报表项—是自定义的服务器控件，可嵌入报表中，以提供除内置控件以外的其他功能
本白皮书阐述如何实现表值函数来处理数据，使之成为使用数据处理扩展的替代方法。
有关扩展ReportingServices的详细信息，请参阅SQLServer2005联机丛书中的ReportingServices扩展。
使用表值函数
表值函数用于通过编程方式在运行时创建表。
它们创建的表可象任何其他数据库表一样用于Transact-SQL查询语句。
在SQLServer2000中引入表值函数时，只能使用Transact-SQL创建它们。
以下是在Transact-SQL中实现的表值函数的示例。
CREATEfunctionEmployeeNames（）
returns@employeeNamestable（idint,namenvarchar（20）,）
asbegin
INSERT@employeeNamesvalues（1,'Ryan'）;
INSERT@employeeNamesvalues（2,'John'）;
INSERT@employeeNamesvalues（3,'Bob'）;
return
end
然后，就可以从select语句中引用该函数，就象它是表一样：
SELECTnamefromEmployeeNames（）whereid=1。
查询返回以下值：
name
--------------------
Ryan
尽管这很有用，但还是受到Transact-SQL语言的限制，因为该语言主要应用于关系数据。
如果您尝试离开其适用范围，那么Transact-SQL会变得多少有点不太灵活。
在SQLServer2005中，您现在可以使用自己喜欢的.NETFramework语言来创建表值函数，这就可能出现一些令人惊叹的事情。
现在，程序员能够将他们所需的任何信息提取到关系数据库表中。
例如，以下代码是在MicrosoftVisualC#中实现的SQLServer2005表值函数，它返回根据系统事件日志创建的表：
usingSystem;
using;
using;
usingSystem.Collections;
using;
usingSystem.Diagnostics;
publicclassTabularEventLog
{
[SqlFunction（TableDefinition="logTimedatetime,Message"+
"nvarchar（4000）,Categorynvarchar（4000）,InstanceIdbigint",
Name="ReadEventLog",FillRowMethodName="FillRow"）]
publicstaticIEnumerableInitMethod（Stringlogname）
{
returnnewEventLog（logname,Environment.MachineName）.Entries;
}
publicstaticvoidFillRow（Objectobj,outSqlDateTimetimeWritten,
outSqlCharsmessage,outSqlCharscategory,
outlonginstanceId）
{
EventLogEntryeventLogEntry=（EventLogEntry）obj;
timeWritten=newSqlDateTime（eventLogEntry.TimeWritten）;
message=newSqlChars（eventLogEntry.Message）;
category=newSqlChars（eventLogEntry.Category）;
instanceId=eventLogEntry.InstanceId;
}
}
该表值函数是作为TabularEventLog类的两个静态方法实现的。
第一个方法（InitMethod）赋予SqlFunction属性，用于将它指定为该表值函数的入口点。
此方法必须返回IEnumerable或IEnumerator对象。
该对象包含将用于填充返回表的数据。
执行该函数时，SQLServer将循环访问IEnumerator对象中的每个对象，并使用它来填充数据行。
为此，它要将该对象传递到该类中的第二个方法FillRow。
此方法会将该对象转换成返回表中的某一行。
此方法在SqlFunction属性的FillRowMethodName参数中指定。
其他元数据在SqlFunction属性的参数中定义。
在前一示例中，列名和类型以及返回表的名称都是在此属性中定义的。
将此函数部署到某SQLServer实例后，就可以运行以下查询来查看应用程序日志中的最后10项。
SELECTTOP10T.logTime,T.Message,T.InstanceId
FROMdbo.ReadEventLog（N'Application'）asT
结果如图1所示：
图1.查询结果
将表值函数用作数据处理扩展的替代方法
ReportingServices数据处理扩展功能可用于通过实现一组ADO.NET接口来对数据源建模。
这在概念上类似于如何才能将表值函数用于ReportingServices。
表值函数明显优于数据处理扩展。
优点:
首先，表值函数比数据处理扩展要容易实现的多。
只需创建两种方法即可实现表值函数。
而数据处理扩展则必须实现许多接口。
同样，部署模型也更简单明了。
MicrosoftVisualStudio2005可自动将.NETFramework表值函数部署到SQLServer，之后，该函数即立即变得可从ReportingServices中使用。
为了部署某数据处理扩展，必须将程序集复制到客户端和报表服务器，并在这两处编辑XML配置文件。
表值函数的另一个重要优点在于它可以是与之连接的数据库中某一联接的组成部分。
这意味着SQLServer中的关系数据在被放入报表之前，可以与该函数中定义的自定义数据混合在一起并筛选出来。
这对于数据处理扩展则是不可能的，因为ReportingServices不支持数据源之间的联接查询。
缺点:
数据处理扩展较之表值函数要强大和灵活得多。
表值函数只能对单个数据库表建模，而数据处理扩展可以对整个数据库的等效项建模。
同样，数据处理扩展可以充当完全自定义的数据源，而且它可以有自己的查询语言和连接语法。
对于不同类型的数据，使用SQL作为查询语言并非总是理想的选择。
例如，ReportingServices包括XML数据的数据处理扩展，它使用类似于Xpath的查询语言。
当开发人员希望完全控制数据访问代码路径时，数据扩展很有用。
将表值函数与SQLServerReportingServices一起使用
您必须先完成三件事，然后才能将表值函数用于ReportingServices。
首先，必须配置SQLServer，使之允许CLR集成。
其次，必须在VisualStudio中开发表值函数。
最后，必须将该函数部署到某SQLServer实例中。
要让SQLServer允许CLR集成，必须使用SQLServer外围应用配置器工具或运行查询来设置一个标志。
配置SQLServer，使之允许CLR集成:
1.单击“开始”按钮，依次指向“所有程序”、MicrosoftSQLServer2005和“配置工具”，然后单击“外围应用配置器”。
2.在SQLServer2005外围应用配置器工具中，单击“功能的外围应用配置器”。
3.选择您的服务器实例，展开“数据库引擎”选项，然后单击“CLR集成”。
4.选择“启用CLR集成”。
此外，您可以在SQLServer中运行以下查询（此查询需要ALTERSETTINGS权限）:
USEmaster
sp_configure'showadvancedoptions',1;
GO
RECONFIGURE;
GO
sp_configure'clrenabled',1;
GO
RECONFIGURE;
GO
开发表值函数:
若要开发表值函数，请在VisualStudio中创建一个新的SQLServer项目。
若要创建SQLServer项目，请打开“新建项目”对话框，展开VisualC#，然后选择“数据库”。
此时，系统会提示您输入数据库连接信息。
有关详细信息，请参阅SQLServer2005联机丛书中的如何：
创建SQLServer项目。
在您建立了数据库连接后，就可以编写表值函数了。
在项目中创建一个空白.cs文件，文件名为EventLog.cs，然后将示例函数从前一部分复制并粘贴到该文件中。
部署表值函数:
若要部署，您必须向SQLServer实例注册该函数和包含它的程序集。
这一操作可通过Transact-SQL命令完成。
以下脚本会注册tvfEventLogs程序集和ReadEventLog函数：
CREATEASSEMBLYtvfEventLog
FROM'D:
\assemblies\tvfEventLog\tvfeventlog.dll'
WITHPERMISSION_SET=SAFE
GO
CREATEFUNCTIONReadEventLog（@lognamenvarchar（100））
RETURNSTABLE（logTimedatetime,Messagenvarchar（4000）,
Categorynvarchar（4000）,InstanceIdbigint）
ASEXTERNALNAMEGO
此外，您还可以在解决方案资源管理器中右键单击相应的项目，并选择“部署”选项，直接从VisualStudio中部署您的程序集。
VisualStudio会使用SqlFunction属性来自动确定函数签名和其他必需的元数据。
部署权限:
SQLServer使用权限集在其宿主环境中安全地运行代码。
当您在VisualStudio中创建数据库项目时，默认的权限集是SAFE。
这是唯一允许您在不进行其他配置的情况下直接从VisualStudio中部署的权限集。
若要向您的程序集赋予SAFE以外的权限集，必须给您的程序集一个强名称，并执行其他配置步骤，然后才将该程序集部署到数据库。
向SQLServer注册程序集时，有三种可能的权限集：
SAFE、EXTERNALACCESS和UNSAFE。
•SAFE只允许内部计算和从该程序集中的代码进行本地数据访问。
•EXTERNALACCESS允许访问外部系统资源，如文件、网络资源和注册表。
•UNSAFE允许该程序集中的代码不受限制地运行。
若要使用SAFE以外的权限集部署您的程序集，必须遵循一些其他步骤。
首先，必须从您要向SQLServer注册的程序集创建非对称密钥。
其次，使用该密钥来创建登录信息。
最后，必须向该登录信息授予相应的权限。
以下Transact-SQL语句使用上述步骤将UNSAFE权限集授予根据前一部分的tvfEventLogs示例创建的程序集。
USEmaster
GO
CREATEASYMMETRICKEYEventLogKeyFROMEXECUTABLEFILE=
'D:
\assemblies\tvfEventLog\tvfeventlog.dll'
CREATELOGINEventLogLoginFROMASYMMETRICKEYEventLogKey
GRANTUNSAFEASSEMBLYTOEventLogLogin
GO
这一操作只需进行一次，不是每次部署该程序集时都要进行。
有关使用不同的权限集和向SQLServer注册程序集的详细信息，请参阅CLR集成代码访问安全性和创建程序集，二者都包含在SQLServer2005联机丛书中。
事件日志报表:
在您部署了该表值函数后，该计算机的系统事件日志的虚拟表会添加到数据库中。
由于SQLServer将该函数视为表，所以它可以无缝地用在ReportingServices中。
在您部署了该程序集后，请使用VisualStudio来创建新的ReportingServices项目。
（如果您不熟悉如何使用ReportingServices报表设计器来创建报表，请参阅SQLServer2005联机丛书中的ReportingServices教程。
）使用连接到安装了该函数的同一数据库的SQLServer数据源创建报表。
接着，创建使用以下查询的数据集：
SELECTTOP10T.logTime,T.Message,T.InstanceId
FROMdbo.ReadEventLog（N'Security'）asT
WHERET.Category=N'Logon/Logoff'
定义了数据集后，将表数据区域添加到报表布局，并将数据集中的各个字段添加到表详细信息行。
最后，运行该报表。
它将显示本地计算机的Logon/Logoff类别中的最后10个安全事件。
图2显示了该报表的一个示例。
图2.报表示例
此简单示例可加以扩展，在其中包括其他计算机管理和监视任务。
例如，可以创建一个表值函数来分析MicrosoftInternet信息服务（IIS）日志。
然后，可以使用ReportingServices来创建Web流量监视应用程序。
Web服务和表值函数
表值函数的一个有趣功能是从Web服务中提取数据的能力。
这可用于创建非常规的报表。
我将说明如何在表值函数中使用MicrosoftMapPointWeb服务，并将它与AdventureWorks数据库中的数据联接，从而将空间数据显示在地图上和显示在报表中。
使用MapPointWeb服务:
您必须先从Microsoft获得免费的开发人员帐户，然后才能开始按照MapPointWeb服务进行开发。
您可以访问MapPointWeb服务客户服务网站，从中获取一个帐户。
开始根据该Web服务进行开发之前，可以从一个好地方获取信息，即MapPointWeb服务SDK入门。
您需要使用VisualStudio将Web引用添加到您的项目中，该引用指向MapPoint暂存服务器上的.wsdl文件。
有关为MapPointWeb服务配置Web引用的详细信息，请参阅访问MapPointWeb服务SOAPAPI。
MapPointWeb服务提供四种服务，每种服务都有其自己的SOAP端点：
•公共服务提供可供其他服务使用的功能。
此服务用于检索元数据和用于实用功能。
•查找服务可用于搜索位置，查找某一地址的经度和纬度（“地理编码”），以及查找某位置附近的兴趣点。
•路线服务指示一个位置到另一个位置的行车路线。
•呈现服务可用于利用位置和路线信息创建地图图像。
MapPointWeb服务表值函数:
最后，我希望我的表值函数使用MapPointWeb服务来执行以下任务：
•使用查找服务来查找AdventureWorks自行车店的经度和纬度。
•使用查找服务来查找距此经度和纬度最近的五台自动柜员机（ATM）。
•使用呈现服务来查找从该商店到ATM的路线。
•使用呈现服务将此路线显示在地图上。
首先，我必须定义一个称为GetProximity的表值函数。
以下Transact-SQL代码显示我的表值函数的签名：
CREATEFUNCTIONGetProximity（@citynvarchar（200）,@statenvarchar
（2）,
@countint,@entityTypeNamenvarchar（200））
RETURNSTABLE
（HitNamenvarchar（200）,HitAddressnvarchar（200）,MapImage
varbinary（max））
GetProximity使用城市名和两位数的州代码来代表初始位置。
它返回实体数，和要搜索的实体类型名。
它会搜索n个最近的实体，其中，n由计算参数指定，实体类型由entityTypeName参数指定。
它会返回一个表和一幅地图（二进制文件图像），表中包含名称列、地址列，地图中包含每个实体的路线。
C#方法签名如下所示：
publicstaticIEnumerable
InitMap（stringcity,stringstate,intcount,
stringentityTypeName）
publicstaticvoidFillRow（Objectobj,
outSqlCharsname,outSqlChars
address,outSqlBinarymap）
请注意，nvarcharTransact-SQL数据类型映射到SqlChars.NETFramework数据类型，而varbinaryTransact-SQL数据类型映射到SqlBinary.NETFramework数据类型。
有关数据类型之间的映射的完整列表，请参阅命名空间的文档。
在InitMap方法中，我将城市和州转换成经度和纬度。
接着，我找到了与此坐标接近的所有实体。
最后，我找到了初始位置和找到的实体之间的行车路线。
返回值是封装有行车路线的一组Route对象。
publicstaticIEnumerable
InitMap（stringcity,stringstate,
intcount,stringentityTypeName）
{
FindServiceSoapfind=newFindServiceSoap（）;
find.PreAuthenticate=true;
find.Credentials=newNetworkCredential（username,passwd）;
//对初始城市和州进行地理编码（Geocode）
FindAddressSpecificationfindSpec
=newFindAddressSpecification（）;
AddressfindAddr=newAddress（）;
findAddr.CountryRegion="US";
findAddr.Subdivision=state;
findAddr.PrimaryCity=city;
findSpec.InputAddress=findAddr;
findSpec.DataSourceName="MapPoint.NA";
findSpec.Options=newFindOptions（）;
=0.45;
FindResultsresults=find.FindAddress（findSpec）;
if（results.NumberFound>0）
{
//如果城市和州已经存在，则获取经度和纬度
LocationstartLocation=results.Results[0].FoundLocation;
LatLongstartPoint=startLocation.LatLong;
//查找附近的实体
FindNearbySpecificationfindNearby=new
FindNearbySpecification（）;
FindFilterfilter=newFindFilter（）;
filter.EntityTypeName=entityTypeName;
findNearby.Filter=filter;
FindOptionsoptions=newFindOptions（）;
options.Range=newFindRange（）;
//设置计数限制
=count;
findNearby.Options=options;
findNearby.DataSourceName="NavTech.NA";
findNearby.LatLong=startPoint;
findNearby.Distance=10.0;
results=find.FindNearby（findNearby）;
Route[]routes=newRoute[;
RouteServiceSoaprouteService=newRouteServiceSoap（）;
routeService.PreAuthenticate=true;
routeService.Credentials=newNetworkCredential（username,passwd）;
RouteSpecificationspec=newRouteSpecifi
升级会员 