问题
我们有时会遇到关于CLR函数或存储过程的需求。例如我们可能需要调用一个存储过程来获得某一特定文件夹下的文件列表。我们怎样使用CLR将文件列表作为一个标准结果集(例如行和列)返回呢?
专家解答
SQL Server 2005和之后的版本集成了CLR(通用语言运行时)，它使得我们可以使用Microsoft .NET代码编写存储过程、触发器、用户友好的函数、用户友好的聚合和用户友好的类型;例如Visual Basic .NET或C#。被增强的各种T-SQL CREATE命令使得我们可以定义一个数据库对象(例如一个存储过程)并将我们的.NET代码与它连接起来。当你执行这个存储过程的时候，.NET代码就被执行了。
当编写一个存储过程或函数时，返回数据所用的最常见方法是结果集。在这篇技巧中，我们将执行一个存储过程来获得某个文件夹的文件列表并将这个列表作为一个具有行和一列的标准结果集返回。我们将执行下面的步骤：
- 激活CLR。
- 获得C#代码示例。
- 部署CLR存储过程。
激活CLR
默认情况下，在SQL Server 2005中CLR没有激活。你可以通过执行外围应用配置器(Surface Area Configuration)工具或sp_configure来激活CLR。在外围应用配置器中点开层级找到CLR Integration，然后点击复选框来激活它，如下图所示：
要使用sp_configure来激活CLR，执行下面的脚本：
| sp_configure 'clr enabled', 1 GO reconfigure GO |
代码示例
下面的C#函数将获得某文件夹的文件列表并将这个列表作为一个结果集返回。这个函数的参数如下所示：
path——获取文件所在的文件夹;例如C:TEMP。
pattern——通配符，如*.*(所有文件)，*.dat，等等。
recursive——是否包含在子文件夹下的文件;1代表true，否则为0。
| public static void GetListOfFiles( SqlString path, SqlString pattern, SqlBoolean recursive) { SqlPipe pipe = SqlContext.Pipe; SqlMetaData[] cols = new SqlMetaData[1]; cols[0] = new SqlMetaData( "FILE_NAME", SqlDbType.NVarChar, 1024); SearchOption searchOption; if (recursive == true) searchOption = SearchOption.AllDirectories; else searchOption = SearchOption.TopDirectoryOnly; string dir = path.ToString(); if (Directory.Exists(dir) == false) { pipe.Send("Directory does not exist"); return; } string[] files = Directory.GetFiles( dir, pattern.ToString(), searchOption); if (files.Length > 0) { SqlDataRecord rec = new SqlDataRecord(cols); pipe.SendResultsStart(rec); foreach (string file in files) { rec.SetSqlString(0, new SqlString(file)); pipe.SendResultsRow(rec); } pipe.SendResultsEnd(); } else { pipe.Send("No files"); } } |
Directory是.NET框架中的一个类。它是用来检查这个路径是否存在并获得文件列表。在上面的代码中从CLR集成的观点来看，重点是：
- SqlPipe对象是用来将结果发送给调用者的。SqlContext对象是自动可用的，并提供SqlPipe对象。
- SqlMetaData类是用来指定结果集中的一个字段的。我们指定字段名称、类型和大小。我们在这个例子中只返回一个字段，但是你可以返回多个字段。
- SqlDataRecord类是用来形成结果集中的一个单独记录的。它被初始化为SqlMetaData对象的数组(例如这些字段)。SetSqlString方法是被调用来基于序列号给每个字段分配值的(例如在SqlMetaData对象数组中的索引)。
- SqlPipe的SendResultsRow方法通过SqlDataRecord对象发回一条记录给调用者。
- SqlPipe的SendResultsEnd方法是用来表示结果集结束的。
- SqlPipe的Send方法是用来发回一条信息给调用者的。
部署CLR存储过程
上面的示例代码需要进行编译才能通过存储过程来调用。在命令行中执行下面的命令来编译这些代码并创建类库DLL MSSQLTipsCLRLib.dll：
| CSC /target:library StoredProcedures.cs /out:MSSQLTipsCLRLib.dll |
假设你使用的是Microsoft .NET框架的第二版本，那么你可以在C:WINDOWSMicrosoft.NETFrameworkv2.0.50727目录下看到CSC.EXE。
执行下面的T-SQL脚本来创建这个存储过程：
| ALTER DATABASE mssqltips SET TRUSTWORTHY ON GO USE mssqltips GO CREATE ASSEMBLY MSSQLTipsCLRLib FROM 'C:mssqltipsMSSQLTipsCLRLib.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS GO CREATE PROCEDURE dbo.GetListOfFiles @path NVARCHAR(256) , @pattern NVARCHAR(64) , @recursive BIT AS EXTERNAL NAME MSSQLTipsCLRLib.StoredProcedures.GetListOfFiles |
在编译了这个代码到类库(.dll)之后，CREATE ASSEMBLY命令就被执行了，它将这个DLL与ASSEMBLY数据库对象关联起来。FROM条件从句必须指向DLL的实际路径。PERMISSION_SET必须设置为EXTERNAL_ACCESS，因为.NET代码将访问SQL Server 之外的文件系统。TRUSTWORTHY选项被设置为on，以允许外部访问。最后，CREATE PROCEDURE命令的EXTERNAME NAME将这个集合、类和函数与这个存储过程名称关联起来。
要执行这个存储过程，执行下面的脚本：
| EXEC dbo.GetListOfFiles 'C:mssqltips', '*.*', 0 |
你将看到类似于下面的输出，这取决于你选择的文件夹的内容;例如，一个只有一列、每一行是一个文件的结果集：
图2
接下来的步骤
- 在这里下载示例脚本并执行从一个CLR存储过程返回结果集。
- 记住，当在Microsoft .NET框架中有满足你需求的函数时，使用SQL Server的CLR可能是个很好的解决方法。
- Visual Studio 2005有一个SQL Server项目模板，它可以简化CLR集成代码的开发和部署。
评论加载中…
