In my previous BLOG entry dated May 5th, I promised another sample that retrieves multiple files from a directory based on a specified name pattern. Here it is, this time in VB.NET.
This is a more traditional Table-Valued-Function in that it actually returns multiple rows, each with two columns (file-name and contents). Please refer to the earlier entry on the basic requirements of a TVF. Here, I will devote to describing the implementation of this particular TVF.
As we saw in the previous entry already, the key is to implement the IEnumerator interface with three members. The IEnumerator interface is implemented by the MultiFileLoader class. There is some initial work done during the construction of the MultiFileLoader class itself. The constructor retrieves the list of file-names of the specified pattern in the specified directory and loads it in the array FileNames. Note that the opening of the directory is done after impersonating the caller (and un-impersonating once we are done).
MoveNext - Advances the position in the array. Makes sure to close the currently opened filestream before returning.
Current - Opens the File as per the file-name in the current position in the FileNames array and returns the FileStream; using Filestream ensures streamed access without having to buffer the entire contents in memory; we also make sure to impersonate the caller before opening the file.
Reset - simply resets the array position to before the start.
Here is the VB.NET code:
Imports SystemImports System.DataImports System.Data.SqlImports System.Data.SqlTypesImports Microsoft.SqlServer.ServerImports System.IOImports System.CollectionsImports System.Security.Principal ' The record that holds each row returned by the TVFPartial Public Class FileRecord Public FName As String Public FContents As FileStream Sub New(ByVal fn As String, ByVal fc As FileStream) Me.FName = fn Me.FContents = fc End SubEnd Class 'The core of the TVF's implementation that implements IEnumeratorPartial Public Class MultiFileLoader Implements IEnumerator ' Array that holds the list of file names to be returned Private FileNames As String() Private CurrentFile As Integer = -1 Private CurrentFileStream As FileStream Private CallerIdentity As WindowsIdentity ' The constructor retrieves the list of file-names into Me.FileNames; it does the ' appropriate impersonation and un-impersonation after the directory has been read. Sub New(ByVal PathName As String, ByVal Pattern As String) Me.CallerIdentity = SqlContext.WindowsIdentity Dim OriginalContext As WindowsImpersonationContext = CallerIdentity.Impersonate() Try Me.FileNames = Directory.GetFiles(PathName, Pattern) Catch Me.FileNames = Nothing Finally If Not (OriginalContext Is Nothing) Then OriginalContext.Undo() End If End Try End Sub ' Retrieves the FileStream for the current file as pointed to by the CurrentFile array index. ' Constructs a FileRecord with the current file's name and the contents (as FileStream) and ' returns it. ' Impersonates caller before opening the file and unimpersonates after getting the filestream. Public ReadOnly Property Current() As Object _ Implements IEnumerator.Current Get Dim OriginalContext As WindowsImpersonationContext = CallerIdentity.Impersonate() Try Me.CurrentFileStream = New FileStream(FileNames(CurrentFile), FileMode.Open) Catch Me.CurrentFileStream = Nothing Finally If Not (OriginalContext Is Nothing) Then OriginalContext.Undo() End If End Try Return New FileRecord(FileNames(CurrentFile), CurrentFileStream) End Get End Property 'Advances the CurrentFile position in the FileNames array. 'Also makes sure to close the currently open FileStream before advancing to the next File. Public Function MoveNext() As Boolean _ Implements IEnumerator.MoveNext ' If there are no files, indicate end of result set If FileNames Is Nothing Then Return False End If If (CurrentFile < FileNames.Length - 1) Then CurrentFile = CurrentFile + 1 If Not (CurrentFileStream Is Nothing) Then CurrentFileStream.Close() End If Return True Else If Not (CurrentFileStream Is Nothing) Then CurrentFileStream.Close() Return False End If End If End Function Public Overridable Sub Reset() _ Implements IEnumerator.Reset CurrentFile = -1 End SubEnd Class Partial Public Class MultiFileAccess ' Root method that returns MultiFileLoader which does the bulk of the work. <Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName:="FillRow", TableDefinition:="FName nvarchar(300), FContents varbinary(max)")> _ Public Shared Function GetFilesInDirectory(ByVal PathName As String, ByVal Pattern As String) As IEnumerator Return New MultiFileLoader(PathName, Pattern) End Function ' The fill row method that cracks the FileRecord and returns the individual columns. Public Shared Sub FillRow(ByVal Obj As Object, ByRef FName As SqlString, ByRef FContents As SqlBytes) If Not (Obj Is Nothing) Then Dim fr As FileRecord fr = Obj FName = fr.FName FContents = New SqlBytes(fr.FContents) Else FName = SqlString.Null FContents = SqlBytes.Null End If End SubEnd Class
Imports SystemImports System.DataImports System.Data.SqlImports System.Data.SqlTypesImports Microsoft.SqlServer.ServerImports System.IOImports System.CollectionsImports System.Security.Principal
' The record that holds each row returned by the TVFPartial Public Class FileRecord Public FName As String Public FContents As FileStream
Sub New(ByVal fn As String, ByVal fc As FileStream) Me.FName = fn Me.FContents = fc End SubEnd Class
'The core of the TVF's implementation that implements IEnumeratorPartial Public Class MultiFileLoader Implements IEnumerator
' Array that holds the list of file names to be returned Private FileNames As String()
Private CurrentFile As Integer = -1 Private CurrentFileStream As FileStream Private CallerIdentity As WindowsIdentity
' The constructor retrieves the list of file-names into Me.FileNames; it does the ' appropriate impersonation and un-impersonation after the directory has been read. Sub New(ByVal PathName As String, ByVal Pattern As String)
Me.CallerIdentity = SqlContext.WindowsIdentity Dim OriginalContext As WindowsImpersonationContext = CallerIdentity.Impersonate()
Try Me.FileNames = Directory.GetFiles(PathName, Pattern) Catch Me.FileNames = Nothing Finally If Not (OriginalContext Is Nothing) Then OriginalContext.Undo() End If End Try End Sub
' Retrieves the FileStream for the current file as pointed to by the CurrentFile array index. ' Constructs a FileRecord with the current file's name and the contents (as FileStream) and ' returns it. ' Impersonates caller before opening the file and unimpersonates after getting the filestream. Public ReadOnly Property Current() As Object _ Implements IEnumerator.Current Get Dim OriginalContext As WindowsImpersonationContext = CallerIdentity.Impersonate()
Try Me.CurrentFileStream = New FileStream(FileNames(CurrentFile), FileMode.Open) Catch Me.CurrentFileStream = Nothing Finally If Not (OriginalContext Is Nothing) Then OriginalContext.Undo() End If End Try Return New FileRecord(FileNames(CurrentFile), CurrentFileStream) End Get End Property
'Advances the CurrentFile position in the FileNames array. 'Also makes sure to close the currently open FileStream before advancing to the next File. Public Function MoveNext() As Boolean _ Implements IEnumerator.MoveNext
' If there are no files, indicate end of result set If FileNames Is Nothing Then Return False End If If (CurrentFile < FileNames.Length - 1) Then CurrentFile = CurrentFile + 1 If Not (CurrentFileStream Is Nothing) Then CurrentFileStream.Close() End If Return True Else If Not (CurrentFileStream Is Nothing) Then CurrentFileStream.Close() Return False End If End If End Function
Public Overridable Sub Reset() _ Implements IEnumerator.Reset CurrentFile = -1 End SubEnd Class
Partial Public Class MultiFileAccess ' Root method that returns MultiFileLoader which does the bulk of the work. <Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName:="FillRow", TableDefinition:="FName nvarchar(300), FContents varbinary(max)")> _ Public Shared Function GetFilesInDirectory(ByVal PathName As String, ByVal Pattern As String) As IEnumerator Return New MultiFileLoader(PathName, Pattern) End Function
' The fill row method that cracks the FileRecord and returns the individual columns. Public Shared Sub FillRow(ByVal Obj As Object, ByRef FName As SqlString, ByRef FContents As SqlBytes) If Not (Obj Is Nothing) Then Dim fr As FileRecord fr = Obj FName = fr.FName FContents = New SqlBytes(fr.FContents) Else FName = SqlString.Null FContents = SqlBytes.Null End If End SubEnd Class
Assuming above code is compiled into an assembly called VBTVF.dll then the following is a T-SQL script to upload the assembly into SQL Server and register the TVF GetFilesInDirectory.
create assembly VBTVF from 'c:\Projects\TVF\VBTVF\bin\VBTVF.dll' with permission_set = external_access go create function GetFilesInDirectory(@PathName nvarchar(300), @Pattern nvarchar(300)) returns table(FileName nvarchar(300), FileContents varbinary(max)) as external name VBTVF.MultiFileAccess.GetFilesInDirectory go
create assembly VBTVF from 'c:\Projects\TVF\VBTVF\bin\VBTVF.dll' with permission_set = external_access go
create function GetFilesInDirectory(@PathName nvarchar(300), @Pattern nvarchar(300)) returns table(FileName nvarchar(300), FileContents varbinary(max)) as external name VBTVF.MultiFileAccess.GetFilesInDirectory go
Now you can test the TVF with queries like:
select FileName, datalength(FileContents), FileContents from GetFilesInDirectory('C:\Projects', '*.*') - Should Return empty result set. select FileName, datalength(FileContents), FileContents from GetFilesInDirectory('C:\NonExistentDirectory', '*.*')
select FileName, datalength(FileContents), FileContents from GetFilesInDirectory('C:\Projects', '*.*')
- Should Return empty result set. select FileName, datalength(FileContents), FileContents from GetFilesInDirectory('C:\NonExistentDirectory', '*.*')
Enough about TVFs. We will talk about something different next time.
- Balaji Rathakrishnan
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.