ADO.NET 3.5 Cookbook, 2nd Edition

12.3. Creating a Table-Valued Function

Problem

You need to create a CLR table-valued function.

Solution

This solution creates, registers, and executes a table-valued function that returns a table containing the Name, Length, and ModifiedDate for each file in a specified directory. Follow these steps:

1. Create a new SQL Server project in Visual Studio and name it ClrTableValuedFunction.

2. Create a user-defined function item in the project. Name the item ReadDirectoryFileInfo.cs.

3. The C# code in ReadDirectoryFileInfo.cs in the project ClrTableValuedFunction is shown in Example 12-6.

Example 12-6. File: ReadDirectoryFileInfo.cs

using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Collections; using System.IO; public partial class UserDefinedFunctions { [SqlFunction(FillRowMethodName = "FillRow", TableDefinition = "FileName nvarchar(256), Size int, DateModified datetime")] public static IEnumerator ReadDirectoryFileInfo(string path) { return new DirectoryLoader(path); } private static void FillRow(object obj, out SqlString fileName, out SqlInt64 fileLength, out SqlDateTime dateModified) { if (obj != null) { DirectoryEntry de = (DirectoryEntry)obj; fileName = de._fileName; fileLength = de._fileLength; dateModified = de._fileDateModified; } else { fileName = SqlString.Null; fileLength = SqlInt64.Null; dateModified = SqlDateTime.Null; } } } public class DirectoryLoader : IEnumerator { // array that stores the directory ...