--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