Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Imports System.IO Partial Public Class UserDefinedFunctions <Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.Read)> _ Public Shared Function clr_fn_recentfile(ByVal Filepath As SqlString, _ ByVal Criteria As SqlString, _ ByVal ext As SqlString) As SqlString Dim strFile As String Dim maxDate As Date Dim fil As String = "" Dim fpath As String Dim extnsn As String extnsn = ext.ToString 'assign the input parameter of Filepath to a string variable to better work with the value fpath = Filepath.ToString Try 'Due to issues that arise if the "\" is left off of the path this appends 'the character to assure consistency of results If fpath.Substring(fpath.Length - 1, 1) <> "\" Then fpath = fpath.ToString + "\" End If 'Evaluate the second parameter in order to return the appropriate file name 'passing "created" will return the file with the most recent "Date Created" 'file attribute Select Case Criteria.ToString Case "created" 'Assure that the directory exists before searching the file objects If Directory.Exists(fpath) Then 'Use For Each to iterate through each file in the specified path For Each strFile In Directory.GetFiles(fpath) Dim fi As New FileInfo(strFile) 'If the maxDate variable has no value assigned to it then assign 'the files creationtime attribute and assign the filename to the 'fil variable If maxDate = Nothing Then If extnsn = "*" Then maxDate = fi.CreationTime fil = fi.FullName.ToString Else If fi.Extension.ToString = extnsn Then maxDate = fi.CreationTime fil = fi.FullName.ToString End If End If 'If the maxDate already has a value assigned then compare the 'new creationtime attribute to the maxDate variable and if 'this value is more recent assign the creationtime attribute 'to the maxdate variable and filename to the fil variable Else If extnsn = "*" Then If maxDate < fi.CreationTime Then maxDate = fi.CreationTime fil = Path.GetFileName(strFile) End If Else If fi.Extension.ToString = extnsn Then maxDate = fi.CreationTime fil = fi.FullName.ToString End If End If End If Next 'If the directory does not exists then return NULL Else Return "NULL" Exit Function End If 'If no file was found return NULL If fil <> Nothing Then Return fil Else Return "NULL" End If 'Evaluate the second parameter in order to return the appropriate file name 'passing "modified" will return the file with the most recent "Date Modified" 'file attribute Case "modified" 'Assure that the directory exists before searching the file objects If Directory.Exists(fpath) Then 'Use For Each to iterate through each file in the specified path For Each strFile In Directory.GetFiles(fpath) Dim fi As New FileInfo(strFile) 'If the maxDate variable has no value assigned to it then assign 'the files creationtime attribute and assign the filename to the 'fil variable If maxDate = Nothing Then If extnsn = "*" Then maxDate = fi.CreationTime fil = fi.FullName.ToString Else If fi.Extension.ToString = extnsn Then maxDate = fi.CreationTime fil = fi.FullName.ToString End If End If 'If the maxDate already has a value assigned then compare the 'new creationtime attribute to the maxDate variable and if 'this value is more recent assign the creationtime attribute 'to the maxdate variable and filename to the fil variable Else If extnsn = "*" Then If maxDate < fi.CreationTime Then maxDate = fi.CreationTime fil = Path.GetFileName(strFile) End If Else If fi.Extension.ToString = extnsn Then maxDate = fi.CreationTime fil = fi.FullName.ToString End If End If End If Next 'If the directory does not exists then return NULL Else Return "NULL" Exit Function End If 'If no file was found return NULL If fil <> Nothing Then Return fil Else Return "NULL" End If 'Evaluate the second parameter in order to return the appropriate file name 'passing "name" will return the file using the filename attribute End Select Catch ex As Exception End Try End Function |