SqlClr：创建一个简单的表值函数

1. 创建项目：

2. 添加函数代码：

using System;using System.Data.Sql;using Microsoft.SqlServer.Server;using System.Collections;using System.Data.SqlTypes;using System.Diagnostics;public class TabularEventLog{[SqlFunction(TableDefinition = @"logTime datetime,Message nvarchar(4000),Category nvarchar(4000),InstanceId bigint",Name = "ReadEventLog", 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;}}

3. 脚本：

USE MASTERGOsp_configure 'show advanced options',1;GORECONFIGURE;GOsp_configure 'clr enabled', 1;GORECONFIGURE;GO–表值函数放在 db_study 库上USE db_studyGO–删除函数IF OBJECT_ID('[dbo].[ReadEventLog]') IS NOT NULLDROP FUNCTION [dbo].ReadEventLogGO–删除程序集IF EXISTS(SELECT * FROM SYS.ASSEMBLIES WHERE NAME='tvfEventLog') DROP ASSEMBLY tvfEventLogGO–创建程序集, 设置为实际路径，，注意应设置为: UNSAFECREATE ASSEMBLY tvfEventLog FROM'D:\Project\StudySimple\SqlServerProject1\bin\Debug\SqlServerProject1.dll' WITH PERMISSION_SET = UNSAFEGO–创建表值函数CREATE FUNCTION dbo.ReadEventLog(@logname nvarchar(100))RETURNS TABLE (logTime DATETIME,Message nvarchar(4000),Category nvarchar(4000),InstanceId BIGINT)ASEXTERNAL NAME tvfEventLog.TabularEventLog.InitMethod GO–查询SELECT TOP 10 T.logTime, T.Message, T.InstanceIdFROM dbo.ReadEventLog(N'Security') as TORDER BY logTime DESC

参考：?mfr=true

此页面做法有问题：https://msdn.microsoft.com/zh-cn/library/ms131103(v=sql.120).aspx

开始的时侯，我们就知道，总会有终结。

相关文章：

你感兴趣的文章：

标签云：