Problem
While transferring data in and out of the file system, you need to perform file or directory operations like copy, move, or delete, with support for wildcards. As many SQL Server users are aware of, there is some support for file operations inside the Database Engine and Integration Services. In the first case, this is accomplished with calls to extended stored procedures, while in the second case this is accomplished by using the File System Task. In this tip we look at how we can extend these methods using SQL CLR functions.
Solution
To go past some of the limitations of the methods described above, we will examine the use of SQL Server CLR functions. .NET Base Class Library provides the System.IO namespace which contains classes with access to the Windows file system. Using these classes you can use .NET languages in addition to the T-SQL programming language to create database objects like stored procedures and functions to perform file operations such as copying, moving, renaming, and deleting a file. You can also perform directory functions such as creating, deleting, copying, moving, or renaming a directory.
At the time of this writing, there are 52 classes in the System.IO namespace, some of them listed below for the purpose of the tip’s context:
- Directory Class
- DirectoryInfo Class
- File Class
- FileInfo Class
- Path Class
We will use some of the methods provided by the File and FileInfo classes, as well as – for purpose of wildcard usage – by the Enumerable class in the System.Linq namespace.
In the tip presented here we will show the code necessary to develop SQL CLR C# functions to perform some of the basic file operations, like copy, delete, move, and replace. (To learn more about creating a CLR function, refer to this tip.)
For the purpose of wildcard file access we will show you how to delete one or more files in a directory that match / do not match a wildcard specification. Although pretty straightforward, the code samples below have comments to improve clarity:
using Microsoft.SqlServer.Server;
using System;
using System.Data.SqlTypes;
using System.IO;
using System.Linq;
public class IOTips
{
[SqlFunction]
public static SqlString FileCopy(
SqlString SourceFileName,
SqlString DestFileName,
SqlBoolean Overwrite)
{
try
{
// input parameters must not be NULL
if (!SourceFileName.IsNull &&
!DestFileName.IsNull &&
!Overwrite.IsNull)
{
// perform copy operation
File.Copy(SourceFileName.Value,
DestFileName.Value,
Overwrite.Value);
// return success message
return “Operation completed successfully.”;
}
else
{
// error if any input parameter is NULL
return “Error: NULL input parameter.”;
}
}
catch (Exception ex)
{
// return any unhandled error message
return ex.Message;
}
}
[SqlFunction]
public static SqlString FileDelete(
SqlString Path)
{
try
{
// input parameter must not be NULL
if (!Path.IsNull)
{
// perform delete operation
File.Delete(Path.Value);// return success message
return “Operation completed successfully.”;
}
else
{
// error if any input parameter is NULL
return “Error: NULL input parameter.”;
}
}
catch (Exception ex)
{
// return any unhandled error message
return ex.Message;
}
}
[SqlFunction]
public static SqlString FileDeleteMatch(
SqlString DirectoryPath,
SqlString SearchPattern,
SqlBoolean Subdirectories,
SqlBoolean Match)
{
try
{
// input parameters must not be NULL
if (!DirectoryPath.IsNull &&
!SearchPattern.IsNull &&
!Subdirectories.IsNull &&
!Match.IsNull)
{
// if Subdirectories parameter is true, search subdirectories
var DirectoryOption = Subdirectories.Value == true ? SearchOption.AllDirectories : SearchOption.TopDirectoryOnly;
if (!Match.Value)
{
// wildcard match found
foreach (string FileFound in
Directory.GetFiles(DirectoryPath.Value,
SearchPattern.Value,
DirectoryOption))
{
// perform delete operation
File.Delete(FileFound);
}
}
else
{
// wildcard match not found, use Except to get unmatched files
foreach (string FileFound in
Directory.GetFiles(DirectoryPath.Value,
“*”,
DirectoryOption).Except(
Directory.GetFiles(DirectoryPath.Value,
SearchPattern.Value,
DirectoryOption)))
{
// perform delete operation
File.Delete(FileFound);
}
}
// return success message
return “Operation completed successfully.”;
}
else
{
// error if any input parameter is NULL
return “Error: NULL input parameter.”;
}
}
catch (Exception ex)
{
// return any unhandled error message
return ex.Message;
}
}
[SqlFunction]
public static SqlString FileMove(
SqlString SourceFileName,
SqlString DestFileName)
{
try
{
// input parameters must not be NULL
if (!SourceFileName.IsNull &&
!DestFileName.IsNull)
{
// perform move operation
File.Move(SourceFileName.Value,
DestFileName.Value);
// return success message
return “Operation completed successfully.”;
}
else
{
// error if any input parameter is NULL
return “Error: NULL input parameter.”;
}
}
catch (Exception ex)
{
// return any unhandled error message
return ex.Message;
}
}
[SqlFunction]
public static SqlString FileReplace(
SqlString SourceFileName,
SqlString DestFileName,
SqlString BackupFileName,
SqlBoolean IgnoreMetadataErrors)
{
try
{
// input parameters must not be NULL
if (!SourceFileName.IsNull &&
!DestFileName.IsNull &&
!BackupFileName.IsNull &&
!IgnoreMetadataErrors.IsNull)
{
// perform replace operation
new FileInfo(SourceFileName.Value).Replace(DestFileName.Value,
BackupFileName.Value,
IgnoreMetadataErrors.Value);// return success message
return “Operation completed successfully.”;
}
else
{
// error if any input parameter is NULL
return “Error: NULL input parameter.”;
}
}
catch (Exception ex)
{
// return any unhandled error message
return ex.Message;
}
}
};
A sample SQL Server run is shown below:
————————————————————
—
–Examples are with arbitrary file names.
—
————————————————————
—
–Copy SourceFileName (‘C:\Directory2\File2.txt’)
–to DestFileName (‘C:\Directory1\File2.txt’)
–overwrite (1) DestFileName.
select dbo.FileCopy
(‘C:\Directory2\File2.txt’
, ‘C:\Directory1\File2.txt’
, 1)
—
————————————————————
—
–Delete file specified by full path
select dbo.FileDelete
(‘C:\Directory1\File2.txt’)
—
————————————————————
—
–Delete files in DirectoryPath,
–search for pattern SearchPattern in the file name,
–search subdirectories (1),
–delete files that do not match the pattern (0)
select dbo.FileDeleteMatch
(‘C:\Directory1’
, ‘*3*.txt’
, 1
, 0)
—
————————————————————
—
–Move file SourceFileName ‘C:\Directory2\File1.txt’,
–to file DestFileName ‘C:\Directory1\File32.txt’
select dbo.FileMove
(‘C:\Directory2\File1.txt’
, ‘C:\Directory1\File32.txt’)
—
————————————————————
—
–Replace file DestFileName ‘C:\Directory2\File3.txt’
–with file SourceFileName ‘C:\Directory2\File3.txt’
–create backup file of the DestFileName ‘C:\Directory2\File3.txt’
–to BackupFileName ‘C:\Directory2\File3 Backup.txt’
–ignore metadata errors (1)
select dbo.FileReplace
(‘C:\Directory2\File3.txt’
, ‘C:\Directory1\File3.txt’
, ‘C:\Directory2\File3 Backup.txt’
, 1)
—
————————————————————
The advantages of the functions created here are obvious as they can be used inline of SQL statements and with a whole spectrum of I/O processes within your SQL Server (including Express/Compact) environment.
Next Steps
- Compile, deploy, and use the file functions; enhance them to suit your needs.
- Based on the FileDeleteMatch function, create corresponding copy and move functions.
- Include using file I/O and CLR in your SQL Server development toolkit.
- Check other SQL CLR tips on this site.
Alex Tocitu is an engineer with MSEE in Automation and MBA in Finance. With over 27 years of software experience he has enjoyed 11 of them in Sillicon Valley. After a long time with Java and Sybase/Oracle, he switched to C# and SQL Server and never looked back. Alex is an avid chess player both live and online.