SQL Server – CLR – List Files in a Folder

Background

This is something that I have wanted to address for so very long.

The inability to consume a huge batch of files into SQL Server made my need even more pressing than it ever was.

 

Googled

Googled for help and found Diana Moldovan already did the work.

That work is published on MSSQLTips.com.

And, it is available here:

Introduction to SQL Server CLR table valued functions
Link

Code

C#

folderList.cs


using System;
using System.IO;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
	
    private class FileProperties
    {
        public SqlString   fileName;
		public SqlString   fullName;
		public SqlString   extension;
		
        public SqlInt64    fileSize;
		
        public SqlDateTime creationTime;
        public SqlDateTime creationTimeUtc;
		
		public SqlDateTime lastWriteTime;
		public SqlDateTime lastWriteTimeUtc;
		
		//constructor
        public FileProperties
		(
		
			  SqlString fileName
			, SqlString fullName  
			, SqlString extension
			
			, SqlInt64  fileSize
			
			, SqlDateTime creationTime
			, SqlDateTime creationTimeUtc			
			
			, SqlDateTime lastWriteTime
			, SqlDateTime lastWriteTimeUtc						
			
		)
        {
            this.fileName = fileName;
			this.fullName = fullName;
			this.extension = extension;
			
            this.fileSize = fileSize;
			
            this.creationTime = creationTime;
            this.creationTimeUtc = creationTimeUtc;			
			
            this.lastWriteTime = lastWriteTime;
            this.lastWriteTimeUtc = lastWriteTimeUtc;						
			
        } //end constructor
		
    } // end class
	
	
    //The SqlFunction attribute tells Visual Studio to register this 
    //code as a user defined function
    [Microsoft.SqlServer.Server.SqlFunction
		(
			  // see implementation below	
			  FillRowMethodName 
				= "fillRow"
				
			// DDL for recordset that will be returned to caller	
			, TableDefinition
				= @"
				
						fileName 				nvarchar(500)
					  , fullName 				nvarchar(4000)
					  , extension 				nvarchar(30)
					  
					  , fileSize 				bigint
					 
					  , creationTime 			datetime
					  , creationTimeUtc 		datetime

					  , lastWriteTime 			datetime
					  , lastWriteTimeUtc 		datetime
					  
				   "	
					
		)
	]
    public static IEnumerable BuildFileNamesArray
	(
		  string targetDirectory
		, string searchPattern
	)
    {
		
		FileInfo[] objFiles = null;
			
		ArrayList objFilePropertiesCollection = null;
            
		DirectoryInfo objDirectoryInfo = null;
		
        try
        {
		
			//instanciate array	
            objFilePropertiesCollection = new ArrayList();

			// instanciate directory info - pass along target directory
			objDirectoryInfo = new DirectoryInfo(targetDirectory);
            
			//get files whose name match pattern
			objFiles = objDirectoryInfo.GetFiles(searchPattern);
			
            //for each file in collection, add to array
			foreach (FileInfo objFileInfo in objFiles)
            {
				
				FileProperties objFileProperties = null;
				
				objFileProperties
					= new FileProperties
						(
						
							  objFileInfo.Name
							, objFileInfo.FullName
							, objFileInfo.Extension
							
							, objFileInfo.Length
							
							, objFileInfo.CreationTime
							, objFileInfo.CreationTimeUtc	
							
							, objFileInfo.LastWriteTime
							, objFileInfo.LastWriteTimeUtc
							
						);
				
                //I'm adding to the colection the properties (FileProperties) 
                //of each file I've found  
                objFilePropertiesCollection.Add( objFileProperties	);
				
            } //for each
			
			//return array
            return objFilePropertiesCollection;
			
        }
        catch (Exception)
        {
			
            return null;
        }
		
    }
	
    
	//FillRow method. The method name has been specified above as 
    //a SqlFunction attribute property
    public static void fillRow
	(
		  object objFileProperty
		  
		, out SqlString fileName
		, out SqlString fullName
		, out SqlString extension
		
		, out SqlInt64 fileSize
		
		, out SqlDateTime creationTime
		, out SqlDateTime creationTimeUtc		
		
		, out SqlDateTime lastWriteTime
		, out SqlDateTime lastWriteTimeUtc				
	)
    {
        //I'm using here the FileProperties class defined above
        FileProperties fileProp = (FileProperties)objFileProperty;
		
        fileName = fileProp.fileName;
		fullName = fileProp.fullName;
		extension = fileProp.extension;
		
        fileSize = fileProp.fileSize;
		
        creationTime = fileProp.creationTime;
        creationTimeUtc = fileProp.creationTimeUtc;		
		
        lastWriteTime = fileProp.lastWriteTime;
        lastWriteTimeUtc = fileProp.lastWriteTimeUtc;				
		
    }
	
};

properties\AssemblyInfo.cs


using System.Reflection;
using System.Runtime.CompilerServices;
using System.Runtime.InteropServices;

// General Information about an assembly is controlled through the following 
// set of attributes. Change these attribute values to modify the information
// associated with an assembly.
[assembly: AssemblyTitle("folderList")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("")]
[assembly: AssemblyProduct("folderList")]
[assembly: AssemblyCopyright("Copyright © Microsoft 2017")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]

// Setting ComVisible to false makes the types in this assembly not visible 
// to COM components. If you need to access a type in this assembly from 
// COM, set the ComVisible attribute to true on that type.
[assembly: ComVisible(false)]

// The following GUID is for the ID of the typelib if this project is exposed to COM
[assembly: Guid("2957bccd-03ed-4ff3-868d-8a07226516d7")]

// Version information for an assembly consists of the following four values:
//
// Major Version
// Minor Version 
// Build Number
// Revision
//
// You can specify all the values or you can default the Build and Revision Numbers 
// by using the '*' as shown below:
// [assembly: AssemblyVersion("1.0.*")]
[assembly: AssemblyVersion("1.0.0.0")]
[assembly: AssemblyFileVersion("1.0.0.0")]

Project File

folderList.csproj


<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="14.0" DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <Import Project="$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props" Condition="Exists('$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props')" />
  <PropertyGroup>
    <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
    <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
    <ProjectGuid>{2957BCCD-03ED-4FF3-868D-8A07226516D7}</ProjectGuid>
    <OutputType>library</OutputType>
    <AppDesignerFolder>Properties</AppDesignerFolder>
    <RootNamespace>folderList</RootNamespace>
    <AssemblyName>folderList</AssemblyName>
    <TargetFrameworkVersion>v4.5.2</TargetFrameworkVersion>
    <FileAlignment>512</FileAlignment>
    <AutoGenerateBindingRedirects>true</AutoGenerateBindingRedirects>
  </PropertyGroup>
  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' ">
    <PlatformTarget>AnyCPU</PlatformTarget>
    <DebugSymbols>true</DebugSymbols>
    <DebugType>full</DebugType>
    <Optimize>false</Optimize>
    <OutputPath>bin\Debug\</OutputPath>
    <DefineConstants>DEBUG;TRACE</DefineConstants>
    <ErrorReport>prompt</ErrorReport>
    <WarningLevel>4</WarningLevel>
  </PropertyGroup>
  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' ">
    <PlatformTarget>AnyCPU</PlatformTarget>
    <DebugType>pdbonly</DebugType>
    <Optimize>true</Optimize>
    <OutputPath>bin\Release\</OutputPath>
    <DefineConstants>TRACE</DefineConstants>
    <ErrorReport>prompt</ErrorReport>
    <WarningLevel>4</WarningLevel>
  </PropertyGroup>
  <ItemGroup>
	
	<Reference Include="System" />
	<Reference Include="System.Configuration" />
    <Reference Include="System.Core" />
    <Reference Include="System.Xml.Linq" />
    <Reference Include="System.Data.DataSetExtensions" />
    <Reference Include="Microsoft.CSharp" />
    <Reference Include="System.Data" />
    <Reference Include="System.Net.Http" />
    <Reference Include="System.Xml" />
  
  </ItemGroup>
  
  <ItemGroup>
    <Compile Include="folderList.cs" />
    <Compile Include="Properties\AssemblyInfo.cs" />
  </ItemGroup>
  
  <ItemGroup>
    <None Include="packages.config" />
  </ItemGroup>
  
  <Import Project="$(MSBuildToolsPath)\Microsoft.CSharp.targets" />
  <!-- 
  
	  To modify your build process, add your task inside one of the targets below and uncomment it. 
      Other similar extension points exist, see Microsoft.Common.targets.
	
	  <Target Name="BeforeBuild">
	  </Target>
	  <Target Name="AfterBuild">
	  </Target>
	  
  -->
</Project>

Command Files

buildFL.cmd


set "_appFolder=C:\Program Files (x86)\MSBuild\14.0\Bin\"

"%_appFolder%\msbuild" folderList.csproj /t:Rebuild 

Compile

Task

Compile the file and if everything goes well, you will get the resultant files in the bin\Debug folder.

BTW, to compile access command line and invoke buildFL.com

 

Deploy

Let us deploy to SQL Server.

prepareDB.cmd

Outline

  1. Enable CLR at the Instance Level
  2. On the Database that you will be using the function, set TRUSTWORTHY on

Code


use [tempdb]
go

use [DBLab]
go

declare @sql varchar(600)
declare @FORMAT_SQL_SETDATABASETRUSTWORTHYON varchar(800)
declare @database sysname

set @FORMAT_SQL_SETDATABASETRUSTWORTHYON
		= ' ALTER DATABASE [%s] set TRUSTWORTHY on;'

if exists
(
	select *
	from   sys.configurations tblSC
	where  tblSC.[name] = 'clr enabled'
	and    tblSC.[value] = 0
)
begin

	print 'Enabling CLR'

	exec sp_configure 'clr enabled', 1

	reconfigure with override

	print 'CLR Enabled'

end


/*
	On current database, set trustworthy on
*/
set @database = db_name()

exec master.dbo.xp_sprintf
	  @sql output
	, @FORMAT_SQL_SETDATABASETRUSTWORTHYON
	, @database


if exists
(
	select *
	from   sys.databases tblSD
	where  tblSD.[name] = db_name()
	and    tblSD.is_trustworthy_on = 0
)
begin

	print 'Issued ' + @sql

	exec(@sql)

end
go


 

Teardown existing function & Dll

Outline

  1. Drop Table Value Function if it exists
  2. Drop Assembly/Dll if already registred

Code



if object_id('[dbo].[ITVF_getFiles]') is not null
begin

	drop FUNCTION [dbo].[ITVF_getFiles]

end
go


if exists 
(
	select * 
	from   sys.assemblies
	where  [name] = 'folderList'
)
begin

	DROP ASSEMBLY [folderList]

end

 

Create Assembly & Create Function

Outline

  • Create Assembly
  • Create Function

Code



CREATE ASSEMBLY [folderList]
AUTHORIZATION [dbo]
FROM 'C:\Personal\dadeniji\Blog\Microsoft\SQLServer\CLR\folderListing\folderList\bin\Debug\folderList.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS

GO

CREATE FUNCTION [dbo].[ITVF_getFiles]
(
	  @targetDirectory   nvarchar(255)
	, @searchPattern	 nvarchar(255)
)
RETURNS TABLE
(
	  [fileName]		 nvarchar(500)
    , fullName			 nvarchar(4000)
	, [extension]		 nvarchar(60)

	, fileSize			 bigint
	, creationTime		 datetime
	, creationTimeUtc	 datetime
	, lastWriteTime		 datetime
	, lastWriteTimeUtc   datetime
)
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [folderList].[UserDefinedFunctions].[BuildFileNamesArray]
GO


Test

Let us test via SQL.

Test Sample – 001

Code


declare @targetDirectory   nvarchar(255)
declare @searchPattern	   nvarchar(255)

set @targetDirectory = 'C:\Microsoft\SQLServer\ExtendedEvents\LockTimeOut'
set @searchPattern = '*.xel'

select *
from   [dbo].[ITVF_getFiles]
		(
				@targetDirectory
			, @searchPattern
		)


Output

 

Dedicated

Dedicated to Diana Moldovan

 

References

  1. Blog
    • Diana Moldovan
      • Introduction to SQL Server CLR table valued functions
        Link
      • Converting UTC to local time
        Link
  2. Reference – MSFT
    • Docs / S​QL / Relational databases / C​LR integration / Database objects / User defined functions (​UD​F)
      • CLR Table value Function
        Link
  3. .Net File
      • How to: Get Information About Files, Folders, and Drives (C# Programming Guide)
        Link
      • FileInfo Class
        Link
  4. Q/A
    • Stack Overflow
      • Creating a CLR Table Valued Function
        Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s