SQL Server 2005 调用vs.net 托管代码实例

在 SQL Server 2005 中，可以在 SQL Server 实例中使用以下方法创建数据库对象：基于 Microsoft .NET Framework 公共语言运行时 (CLR) 中创建的程序集使用编程方法创建。可以充分利用公共语言运行时所提供的丰富的编程模式的数据库对象包括聚合函数、函数、存储过程、触发器以及类型。

在 SQL Server 中创建 CLR 函数分为下列几个步骤：
使用 .NET Framework 支持的语言将函数定义为类的静态方法。然后，使用适当的语言编译器编译该类，在 .NET Framework 中生成程序集。
使用 CREATE ASSEMBLY 语句在 SQL Server 中注册程序集。
通过使用 CREATE FUNCTION 语句创建引用注册程序集的函数。

注意： 
默认情况下，关闭 SQL Server 执行 CLR 代码的功能。您可以创建、更改和删除引用托管代码模块的数据库对象，但是除非通过使用 sp_configure (Transact-SQL) 启用了 clr enabled 选项，否则这些引用将不会在 SQL Server 中执行。

访问外部资源

可以使用 CLR 函数访问外部资源，例如文件、网络资源、Web 服务及其他数据库（包括 SQL Server 远程实例）。这可以通过使用 .NET Framework 中的各种类（例如 System.IO、System.WebServices 及 System.Sql 等）来实现。至少应将包含此类函数的程序集配置为设置了 EXTERNAL_ACCESS 权限，才能实现此目的。可以使用 SQL 客户端托管提供程序访问 SQL Server 远程实例。但在 CLR 函数中不支持与发起服务器的环回连接。

----实例:

--1: 创建Vs.net 2005 建C#项目文件
--创建新类(EventLog.cs):
--代码:
using System;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data.SqlTypes;
using System.Diagnostics;

public class TabularEventLog
{
    [SqlFunction(FillRowMethodName = "FillRow")]
    public static IEnumerable InitMethod(String logname)
    {
        return new EventLog(logname, Environment.MachineName).Entries;
    }

    public static void FillRow(Object obj, out SqlDateTime timeWritten, out SqlChars message, out SqlChars category, out long instanceId)
    {
        EventLogEntry eventLogEntry = (EventLogEntry)obj;
        timeWritten = new SqlDateTime(eventLogEntry.TimeWritten);
        message = new SqlChars(eventLogEntry.Message);
        category = new SqlChars(eventLogEntry.Category);
        instanceId = eventLogEntry.InstanceId;
    }
}

--2: 创建创建包含类元数据和托管代码的托管应用程序模块，将其作为 SQL Server 实例中的对象。
CREATE ASSEMBLY tvfEventLog
FROM 'D:/IISWebRoot/SQLCLR/SQLFunction/SQLFunction/bin/Debug/SQLFunction.dll'
WITH  PERMISSION_SET = UNSAFE
GO
--3: 创建 CLR 函数。
CREATE FUNCTION ReadEventLog(@logname nvarchar(100))
RETURNS TABLE
(logTime datetime,Message nvarchar(4000),Category nvarchar(4000),InstanceId bigint)
AS
EXTERNAL NAME tvfEventLog.TabularEventLog.InitMethod
GO
--4: 测试
SELECT top 10 *
FROM dbo.ReadEventLog(N'Security') as T
go
--结果(自己查看)

--删除环境
Drop function ReadEventLog
Drop ASSEMBLY tvfEventLog

 

--注意事项(创建测试失败的情况)：
1: 启用数据库CLR支持
 --***********************************************
 EXEC sp_configure 'show advanced options','1';
 go
 reconfigure;
 go
 EXEC sp_configure 'clr enabled','1'
 go
 --***********************************************
2: 更改数据库信任模式(为正确添加到程序集)
TRUSTWORTHY { ON | OFF }
ON
使用模拟上下文的数据库模块（例如，用户定义函数或存储过程）可以访问数据库以外的资源。

OFF
模拟上下文中的数据库模块不能访问数据库以外的资源。
 --***********************************************
 Alter Database 数据库名称
 SET TRUSTWORTHY ON
 --***********************************************