Extending File System Operations in SQL Server Using CLR


By:   |   Updated: 2011-02-14   |   Comments (8)   |   Related: More > Common Runtime Language


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.





get scripts

next tip button



About the author
MSSQLTips author Alex Tocitu Alex Tocitu is an engineer with an MSEE in Automation and MBA in Finance.

View all my tips


Article Last Updated: 2011-02-14

Comments For This Article




Saturday, April 30, 2011 - 8:24:49 PM - Jeff Moden Back To Top (13727)

Exactly the points I believe are missing from this article.  Whether it be with xp_CmdShell or with CLRs, permissions must always be one of the primary concerns.  Long gone are the days when the logins and users for an application should have the likes "SA" or "dbo" privs. Sure, it's likely beyond the scope to cover certificates and the like in such an article, but such security should at least be mentioned.

Thanks for the thoughtful response, Greg.


Saturday, April 30, 2011 - 7:24:10 AM - Greg Robidoux Back To Top (13724)

No there is not but this could be easily handled within the code to prevent this. 

This would be the same issue if someone had access to xp_cmshelll to issue that command. By using the CLR you could reduce the risk to only allow certain statements to be executed.  Also, just becuase this function exists the DBA still needs to make sure they protect who has rights to run this.  If someone has sysadmin rights they could easily turn on xp_cmdshell and do the same thing, so part of this starts at a higher level to make sure your systems are secure and only authorized people have access to these commands / functions.


Friday, April 29, 2011 - 8:41:23 AM - Jeff Moden Back To Top (13718)

Is there anything to prevent the following if an outstide attack occurs or if an insider decides to be nasty because (s)he just got laid off?

select dbo.FileDeleteMatch
('C:\Directory1'
, '*.*'
, 1
, 0)


Friday, February 18, 2011 - 10:08:22 AM - Alex Tocitu Back To Top (12973)

------------------------------------------------------------

-- HOW TO TEST THE CODE

------------------------------------------------------------

--

-- Save text in article code window to

-- C:\MSSQLTips\IOTips.cs

-- Run the line below in a DOS command window

--

----------------------------------------------------------------------------------------------------------------------

-- C:\Windows\Microsoft.NET\Framework\v3.5\csc.exe /target:library /out:C:\MSSQLTips\IOTips.dll C:\MSSQLTips\IOTips.cs

----------------------------------------------------------------------------------------------------------------------

--

-- Code gets compiled to IOTips.dll

--

-- Create your test database

--

CREATE DATABASE DB_TIPS

GO

--

-- Configure your database

--

USE DB_TIPS

GO

--

sp_configure 'clr enabled', 1

GO

--

ALTER DATABASE DB_TIPS SET TRUSTWORTHY ON

GO

--

-- Register the assembly

-- 

CREATE ASSEMBLY IOTips

FROM 'C:\MSSQLTips\IOTips.dll'

WITH PERMISSION_SET = EXTERNAL_ACCESS

--

-- Create functions from the assembly

--

CREATE FUNCTION [dbo].[FileCopy](

@SourceFileName [nvarchar](4000),

@DestFileName [nvarchar](4000),

@Overwrite [bit])

RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER

AS 

EXTERNAL NAME [IOTips].[IOTips].[FileCopy]

GO

--

CREATE FUNCTION [dbo].[FileDelete](@Path [nvarchar](4000))

RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER

AS 

EXTERNAL NAME [IOTips].[IOTips].[FileDelete]

GO

--

CREATE FUNCTION [dbo].[FileDeleteMatch](

@DirectoryPath [nvarchar](4000),

@SearchPattern [nvarchar](4000),

@Subdirectories [bit], @Match [bit])

RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER

AS 

EXTERNAL NAME [IOTips].[IOTips].[FileDeleteMatch]

GO

--

CREATE FUNCTION [dbo].[FileMove](

@SourceFileName [nvarchar](4000),

@DestFileName [nvarchar](4000))

RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER

AS 

EXTERNAL NAME [IOTips].[IOTips].[FileMove]

GO

--

CREATE FUNCTION [dbo].[FileReplace](

@SourceFileName [nvarchar](4000),

@DestFileName [nvarchar](4000),

@BackupFileName [nvarchar](4000),

@IgnoreMetadataErrors [bit])

RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER

AS 

EXTERNAL NAME [IOTips].[IOTips].[FileReplace]

GO

--

-- YOU ARE NOW ROCKIN'

--


Thursday, February 17, 2011 - 6:54:18 PM - biank Back To Top (12966)

well, can you also post a script to create a function...

Thanks and sorry for asking too much.


Thursday, February 17, 2011 - 11:07:29 AM - Alex Tocitu Back To Top (12958)

Alex,

The code has been updated to reflect your corrections.

Thanks to the Helpdesk at MSSQLTips.com


Thursday, February 17, 2011 - 8:51:47 AM - Alex Tocitu Back To Top (12957)

Alex,

Thanks for catching the errors.

I wish we'll get better at copy / paste.

Good point too, on setting up the database to run the functions;

in the interest of keeping things short and sweet, I assume people

already can figure that. 


Thursday, February 17, 2011 - 1:49:26 AM - Alex Lam Back To Top (12952)

A very good and practical article. To get the code working, I needed to

1) Changed !Overwrite.Value" to "!Overwrite.IsNull"
2) Changed "SqlBoolean Directories" to "SqlBoolean Subdirectories"
3) Changed "Subirectories.Value" to "Subdirectories.Value"

My SQL Server Service account is a standard user, not local administrator, I needed to resolve the following security issues as well:

- set the database trustworthy ON
- set file level security
- create assembly WITH PERMISSION_SET = EXTERNAL_ACCESS

 



download














get free sql tips
agree to terms