The building of the following CLR function will get the following error /* Error: Unsupported type. */ in the automatically generated SQL. Which type caused the problem? The MSDN document https://msdn.microsoft.com/en-us/library/ms131103.aspx was used as an example to create the Clr function.
--------------------------------------------------------------------------------
-- This code was generated by a tool.
--
-- Changes to this file may cause incorrect behavior and will be lost if
-- the code is regenerated.
--------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[InitMethod] (@path [nvarchar](MAX), @pattern [nvarchar](MAX), @recursive [bit])
RETURNS /* Error: Unsupported type. */
AS EXTERNAL NAME [ListFiles].[FileList].[InitMethod];
The following is the C# code.
public class FileInf
{
public string Name { get; set; }
public DateTime LastWriteTime { get; set; }
public long Length { get; set; }
public bool IsFile { get; set; }
};
[SqlFunction(FillRowMethodName = "FillRow")]
public static IEnumerable InitMethod(string path, string pattern, bool recursive)
{
var dir = new DirectoryInfo(path);
var files = dir.GetFiles(pattern, recursive ? SearchOption.AllDirectories : SearchOption.TopDirectoryOnly);
foreach (var f in files)
{
yield return new FileInf
{
Name = f.Name,
LastWriteTime = f.LastWriteTime,
Length = f.Length,
IsFile = (f.Attributes & FileAttributes.Directory) == FileAttributes.Directory ? false : true
};
}
}
public static void FillRow(Object obj,
out SqlChars name,
out SqlDateTime lastWriteTime,
out SqlInt64 Length,
out SqlBoolean isFile)
{
var fileInfo = (FileInf)obj;
name = new SqlChars(fileInfo.Name);
lastWriteTime = new SqlDateTime(fileInfo.LastWriteTime);
Length = new SqlInt64(fileInfo.Length);
isFile = new SqlBoolean(fileInfo.IsFile);
}
