SQL Server - How to list, read, write, copy, delete and move files with CLR (C #)

Views: 3.813
Reading Time: 9 minutes

Hello people,
Good Morning!

In this post I will show you how to list, read, write, copy, delete and move files using CLR (C #), which is a powerful tool for increasing the range of features of SQL Server. Since I create many file exchange routines in my work, whether importing data from an external base or exporting data to files, I decided to create this post to help people who have the same needs and who can easily resolve these issues through SQL itself. Server, which is very performative and simple to create, deploy and maintain.

Before I begin, I would like to name two posts related to this subject:

Listing files

How to list files in SQL Server

To list files, I use a table-valued function, which is very useful for listing files already filtering with WHERE, I can sort the results, use SELECT * INTO to save the results in a table. Anyway, it's a solution. very flexible and practical:

Example of use:
SQL Server - How to list CLR files

Source code:

Reading files line by line

How to read a text file row by row in SQL Server

For the task of reading a file row by row, I will again use a table-valued function from CLR, which will allow me to select the view and return the contents of the file. I can export this data to a table, filter, sort, etc .. all very flexibly.

Example of use:
SQL Server - How to read line files by line with CLR

Source code:

Reading a file and returning as a string

How to read a file and return as a string in SQL Server

For this need I chose to create a scalar function for very common use when creating routines is the need to import a file and not return line by line, but a string with the entire contents of the file. I use this a lot when importing XML files, for example.

Example of use:
SQL Server - How to read files with CLR

Source code:

Checking if a file or directory exists

How to check if a file or directory exists in SQL Server

Scalar and boolean return (BIT) functions allow you to check whether a file or directory exists in the filesystem. Your source code is as simple as its use.

Example of use:
SQL Server - How to check if a file or directory exists with CLR

Fnc Source CodeFile:

Fnc Source CodeDirectory_Exists:

Exporting a SQL Server Query, Table, or View to File

How to export data from a SQL Server table to file

With this Stored Procedure, we can easily export data from a SQL Server table or view to a delimited text file or not, where each record will be a line from the created file.

Example of use:
SQL Server - How to export query table to text csv file with CLR

Source code:

Exporting a String to File in SQL Server

How to export a SQL Server string to file

With this Stored Procedure, we can easily export string data from SQL Server and export this string to a text file. No line wrapping will be forced on the file, only if in the string you hear the line feed and / or carriage return characters.

The Ds_Coding parameter lets you range from UTF-8, ISO-8859-1, and many others supported by the .NET Framework. The full list of encodings can be found here: Encoding.GetEncodings Method

The Ds_Formato_Quebra_Linha parameter lets you toggle between the line wrap formats of each operating system, and you can use Windows, Unix, and MAC values.

The Fl_Append parameter allows if the file already exists, it will be deleted and overwritten (Fl_Append = 0) or the contents added to the end of the file (Fl_Append = 1)

Example of use:
SQL Server - How to export variable string to text csv file with CLR

Source code:

Copying Files in SQL Server

How to copy files in SQL Server

Procedure that can be used to copy a file from one directory to another

Example of use:
SQL Server - How to copy a file with CLR

Source code:

Moving files in SQL Server

How to move files in SQL Server

Procedure that can be used to move a file from one directory to another, keeping the same filename

Example of use:
SQL Server - How to Move a File with CLR

Source code:

Renaming Files in SQL Server

How to rename files in SQL Server

Procedure that you can use to rename a file, even allowing it to be renamed

Example of use:
SQL Server - How to rename a file with CLR

Source code:

Deleting Files in SQL Server

How to delete files in SQL Server

Procedure that can be used to physically delete a file

Example of use:
SQL Server - How to delete a file with CLR

Source code:

Deleting All Files in a Directory on SQL Server

How to delete all files from a directory in SQL Server

Procedure that can be used to delete all files in a given directory.

Example of use:
SQL Server - How to delete all directory files with CLR

Source code:

Deleting a Directory in SQL Server

How to delete a directory in SQL Server

Procedure that can be used to delete a particular directory in SQL Server. If this directory has files, they must be deleted before deleting the directory.

Example of use:
SQL Server - How to delete directory with CLR

Source code:

And that's it folks!
I hope you enjoyed the post and that it is useful to you

Hug!