File Operations For MSSQL, A SQLCLR Assembly
http://nclsqlclrfile.codeplex.com/

Prepared by C. Elliott Whitlow II with great assistance from lizaguirre

Overall Notes:

While the datatypes for most input variables are nvarchar(4000) it is important to note that in reality the effective lengths are much smaller. Within SQL I would redommend casting any outputs to these sizes:

Input/Ouput Type

Recommended Data Type

File Path

varchar(128)

File Name

varchar(128)

File Path With File Name

varcahr(255)


Flag values while defined as tinyint are effective Boolean values of 0 (zero) for True or 1 (one) for False, in all cases a value other than 1 is considered False but for clarity please use 0 (zero) in your own code.

Stored Procedures

MSPCopyFile

Purpose:

Disk operations for copying files from one disk location to another, with optional overwrite if existing.

Parameters:

Name:

Data Type:

Direction:

@SourceFilePath

nvarchar(4000)

Input

@SourceFileName

nvarchar(4000)

Input

@DestinationFilePath

nvarchar(4000)

Input

@DestinationFileName

nvarchar(4000)

Input

@OverwriteExistingFileFlag

tinyint

Input

Usage:

EXECUTE dbo.MSPCopyFile 'C:\PathToSourceFile','FileName.Ext','C:\PathToDestination','NewFileName.ext',1

MSPDeleteFile

Purpose:

Given a path and filename, delete an existing file from disk

Parameters:

Name:

Data Type:

Direction:

@FilePath

nvarchar(4000)

Input

@FileName

nvarchar(4000)

Input

Usage:

EXECUTE dbo.MSPDeleteFile 'C:\PathToTheFile','Filename.Ext'

MSPDeleteFiles

Purpose:

Given a path and pattern to match against, delete all files within the Path matching the pattern.

Parameters:

Name:

Data Type:

Direction:

@FilePath

nvarchar(4000)

Input

@FilePattern

nvarchar(4000)

Input

Usage:

EXECUTE dbo.MSPDeleteFiles 'C:\PathToFiles','*.ext'

MSPGetFileSpecs

Purpose:

For a given file path and file name return the file specifications, this is similar to MFGetFileSpecs, except there is no result set and the output is conveyed through OUTPUT columns.

Parameters:

Name:

Data Type:

Direction:

@FilePath

nvarchar(4000)

Input

@FileName

nvarchar(4000)

Input

@FileSize

int

Output

@FileDt

datetime

Output

@FileExistFlag

tinyint

Output

Usage:

EXECUTE dbo.MSPGetFileSpecs 'C:\PathToFile','Filename.Ext',@FileSize OUTPUT,@FileDt OUTPUT,@FileExistFlag OUTPUT

MSPMakeDirectory

Purpose:

Given a string, will create a directory and multiple subdirectories in the string.

Parameters:

Name:

Data Type:

Direction:

@FullDirectoryPath

nvarchar(4000)

Input

Usage:

EXECUTE dbo.MSPMakeDirectory 'C:\BasePath\NewPath1\New Path 2'

Notes:

Assuming only C:\BasePath Exists it creates C:\BasePath\NewPath1 and C:\BasePath\NewPath1\New Path 2. This allows you to build a hierarchical structure by specifying the bottom node instead of having to build it up.

MSPMoveFile

Purpose:

Disk operations for movinging files from one disk location to another, with optional overwrite if existing.

Parameters:

Name:

Data Type:

Direction:

@SourceFilePath

nvarchar(4000)

Input

@SourceFileName

nvarchar(4000)

Input

@DestinationFilePath

nvarchar(4000)

Input

@DestinationFileName

nvarchar(4000)

Input

@OverwriteExistingFileFlag

tinyint

Input

Usage:

EXECUTE dbo.MSPMoveFile 'C:\PathToSourceFile','FileName.Ext','C:\PathToDestination','NewFileName.ext',1

MSPSaveFileImage

Purpose:

Given a varbinary(max) usually representing a complete file, write that data to disk.

Parameters:

Name:

Data Type:

Direction:

@FilePath

nvarchar(4000)

Input

@FileName

nvarchar(4000)

Input

@FileBytes

varbinary(max)

Input

Usage:

DECLARE @FileImage varbinary(max)

SET @FileImage = ??????????? -- How this gets populated is up to you

EXEC dbo.MSPSaveFileImage 'C:\PathToNewFile','NewFileName.Ext',@FileImage


 

Scalar Functions

MFBreakFileNameWithPathForFileName

Purpose:

Given a full file path with a filename, return just the Filename

Input Parameters:

Name:

Data Type:

@FileNameWithPath

nvarchar(4000)

Return Parameter:

What is returned:

Data Type:

A Filename

nvarchar(4000)

Usage:

SELECT dbo.MFBreakFileNameWithPathForFileName('C:\Path\Path2\FileName.Ext') – returns “FileName.Ext”

MFBreakFileNameWithPathForFilePath

Purpose:

Given a full file path with a filename, return just the Path without the filename.

Input Parameters:

Name:

Data Type:

@FileNameWithPath

nvarchar(4000)

Return Parameter:

What is returned:

Data Type:

A Path

nvarchar(4000)

Usage:

SELECT dbo.MFBreakFileNameWithPathForFilePath('C:\Path\Path2\FileName.Ext') – returns “C:\Path\Path2\”

MFFileExistsCheck

Purpose:

Given a path and filename return whether the file exists

Input Parameters:

Name:

Data Type:

@FilePath

nvarchar(4000)

@FileName

nvarchar(4000)

Return Parameter:

What is returned:

Data Type:

An effective boolean, 0 (zero)/1

tinyint

Usage:

SELECT dbo.MFFileExistsCheck('C:\Path\','Filename.Ext') --returns 0 or 1 for false/true

 

MFFolderExistsCheck

Purpose:

For a given path, check if the path exists /is valid

Input Parameters:

Name:

Data Type:

@FullDirectoryPath

nvarchar(4000)

Return Parameter:

What is returned:

Data Type:

An effective boolean, 0 (zero)/1

tinyint

Usage:

SELECT dbo.MFFolderExistsCheck('C:\PathToCheck\') --returns 0 or 1 for false/true

MFGetFileImage

Purpose:

Given a path and filename, return the varbinary of the file.

Input Parameters:

Name:

Data Type:

@FilePath

nvarchar(4000)

@FileName

nvarchar(4000)

Return Parameter:

What is returned:

Data Type:

A binary image of the file

varbinary(max)

Usage:

DECLARE @FileImage varbinary(max)

SELECT @FileImage = dbo.MFGetFileImage('C:\PathTofile\','Filename.Ext' )


 

Table Valued Functions

MFBreakFileNameWithPath

Purpose:

Given a string containing both path and filename, returns a two column results table with Filename and File Path as seperate columns

Input Parameters:

Name:

Data Type:

@FileNameWithPath

nvarchar(4000)

Return Table Definition:

Field Name:

Data Type:

FilePath

nvarchar(128)

FileName

nvarchar(128)

Usage:

SELECT * FROM dbo.MFBreakFileNameWithPath('C:\PathToFile\FileName.txt')

MFGetDirectoryList

Purpose:

Given a string containing a path returns a three column results table with path,name and fullfilename and path

Input Parameters:

Name:

Data Type:

@Directory

nvarchar(4000)

Return Table Definition:

Field Name:

Data Type:

FilePath

nvarchar(128)

FileName

nvarchar(128)

FileNameWithPath

nvarchar(255)

Usage:

SELECT * FROM dbo.MFGetDirectoryList('C:\Data\')

MFGetFileSpecs

Purpose:

Given a filename, return a three column results set with size, last modified date and whether the file exists

Input Parameters:

Name:

Data Type:

@FilePath

nvarchar(4000)

@FileName

nvarchar(4000)

Return Table Definition:

Field Name:

Data Type:

FileSize

int

FileDt

datetime

FileExistFlag

tinyint

Usage:

SELECT * FROM dbo.MFGetFileSpecs( 'C:\PathToFile\','FileName.Ext')

 

Last edited Oct 13, 2011 at 7:04 PM by novaconceptsltd, version 2

Comments

No comments yet.