SQL Server - How to List, Read, Write, Copy, Delete and Move Files with CLR (C #)

Views: 6.761 views

In this article

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:

File Operations Using OLE Automation in SQL Server, where I had made a similar post, where I used OLE Automation, a very cool feature of SQL Server, but that has some limitations and risks to the server. For these reasons, the CLR is touted as the major replacement for OLE Automation routines.
Introduction to SQL Common Language Runtime (CLR) in SQL Server is the post I made talking about the CLR, its advantages and disadvantages, compared with OLE Automation and explain how to create your first SP's and functions in the CLR (C #)
SQL Server - File Operations Using xp_cmdshell (Listing, Reading, Writing, Copying, Deleting, and Moving Files)

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:

Source code:

using System.IO;
using System.Collections;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{

    private class FileProperties
    {

        public SqlInt32 NrLinha;
        public SqlString Tipo;
        public SqlString FileName;
        public SqlString FileNameWithoutExtension;
        public SqlString DirectoryName;
        public SqlString Extension;
        public SqlString FullName;
        public SqlInt64 FileSize;
        public SqlBoolean IsReadOnly;
        public SqlDateTime CreationTime;
        public SqlDateTime LastAccessTime;
        public SqlDateTime LastWriteTime;


        public FileProperties(SqlInt32 nrLinha, SqlString tipo, SqlString fileName, SqlString fileNameWithoutExtension, SqlString directoryName, SqlString extension, SqlString fullName, SqlInt64 fileSize, SqlBoolean isReadOnly, SqlDateTime creationTime, SqlDateTime lastAccessTime, SqlDateTime lastWriteTime)
        {

            NrLinha = nrLinha;
            Tipo = tipo;
            FileNameWithoutExtension = fileNameWithoutExtension;
            FileName = fileName;
            DirectoryName = directoryName;
            Extension = extension;
            FullName = fullName;
            FileSize = fileSize;
            IsReadOnly = isReadOnly;
            CreationTime = creationTime;
            LastAccessTime = lastAccessTime;
            LastWriteTime = lastWriteTime;

        }
    }

    [Microsoft.SqlServer.Server.SqlFunction(
        FillRowMethodName = "listarArquivos",
        TableDefinition = "Nr_Linha int, Fl_Tipo nvarchar(50), Nm_Arquivo nvarchar(500), Nm_Arquivo_Sem_Extensao nvarchar(500), Nm_Diretorio nvarchar(500), " +
                          "Nm_Extensao nvarchar(20), Nm_Completo nvarchar(500), Qt_Tamanho bigint, Fl_Somente_Leitura bit, Dt_Criacao datetime, " +
                          "Dt_Ultimo_Acesso datetime, Dt_Modificacao datetime"
    )]
    public static IEnumerable fncArquivo_Listar(string Ds_Diretorio, string Ds_Filtro)
    {

        var FilePropertiesCollection = new ArrayList();
        var dirInfo = new DirectoryInfo(Ds_Diretorio);
        var files = dirInfo.GetFiles(Ds_Filtro);
        var directories = dirInfo.GetDirectories(Ds_Filtro);
        var contador = 1;



        foreach (var fileInfo in directories)
        {

            FilePropertiesCollection.Add(new FileProperties(
                contador,
                "Diretorio",
                fileInfo.Name,
                fileInfo.Name,
                fileInfo.Name,
                "",
                fileInfo.FullName + "\\",
                0,
                false,
                fileInfo.CreationTime,
                fileInfo.LastAccessTime,
                fileInfo.LastWriteTime
            ));

            contador++;

        }

        foreach (var fileInfo in files)
        {

            FilePropertiesCollection.Add(new FileProperties(
                contador,
                "Arquivo",
                fileInfo.Name,
                (fileInfo.Extension.Length > 0) ? fileInfo.Name.Replace(fileInfo.Extension, "") : "",
                fileInfo.DirectoryName,
                fileInfo.Extension.ToLower(),
                fileInfo.FullName,
                fileInfo.Length,
                fileInfo.IsReadOnly,
                fileInfo.CreationTime,
                fileInfo.LastAccessTime,
                fileInfo.LastWriteTime
            ));

            contador++;

        }

        return FilePropertiesCollection;

    }

    protected static void listarArquivos(object objFileProperties, out SqlInt32 nrLinha, out SqlString tipo, out SqlString fileName, out SqlString fileNameWithoutExtension, out SqlString directoryName, out SqlString extension, out SqlString fullName, out SqlInt64 fileSize, out SqlBoolean isReadOnly, out SqlDateTime creationTime, out SqlDateTime lastAccessTime, out SqlDateTime lastWriteTime)
    {

        var fileProperties = (FileProperties) objFileProperties;

        nrLinha = fileProperties.NrLinha;
        tipo = fileProperties.Tipo;
        fileName = fileProperties.FileName;
        fileNameWithoutExtension = fileProperties.FileNameWithoutExtension;
        directoryName = fileProperties.DirectoryName;
        extension = fileProperties.Extension;
        fullName = fileProperties.FullName;
        fileSize = fileProperties.FileSize;
        isReadOnly = fileProperties.IsReadOnly;
        creationTime = fileProperties.CreationTime;
        lastAccessTime = fileProperties.LastAccessTime;
        lastWriteTime = fileProperties.LastWriteTime;

    }

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

using System.IO;

using System.Collections;

using System.Data.SqlTypes;

public partial class UserDefinedFunctions

{

private class FileProperties

{

public SqlInt32 NrLinha;

public SqlString Tipo;

public SqlString FileName;

public SqlString FileNameWithoutExtension;

public SqlString DirectoryName;

public SqlString Extension;

public SqlString FullName;

public SqlInt64 FileSize;

public SqlBoolean IsReadOnly;

public SqlDateTime CreationTime;

public SqlDateTime LastAccessTime;

public SqlDateTime LastWriteTime;

public FileProperties(SqlInt32 nrLinha, SqlString tipo, SqlString fileName, SqlString fileNameWithoutExtension, SqlString directoryName, SqlString extension, SqlString fullName, SqlInt64 fileSize, SqlBoolean isReadOnly, SqlDateTime creationTime, SqlDateTime lastAccessTime, SqlDateTime lastWriteTime)

{

NrLinha = nrLinha;

Tipo = tipo;

FileNameWithoutExtension = fileNameWithoutExtension;

FileName = fileName;

DirectoryName = directoryName;

Extension = extension;

FullName = fullName;

FileSize = fileSize;

IsReadOnly = isReadOnly;

CreationTime = creationTime;

LastAccessTime = lastAccessTime;

LastWriteTime = lastWriteTime;

}

[Microsoft.SqlServer.Server.SqlFunction(

FillRowMethodName = "listarArquivos",

TableDefinition = "Nr_Linha int, Fl_Tipo nvarchar(50), Nm_Arquivo nvarchar(500), Nm_Arquivo_Sem_Extensao nvarchar(500), Nm_Diretorio nvarchar(500), " +

"Nm_Extensao nvarchar(20), Nm_Completo nvarchar(500), Qt_Tamanho bigint, Fl_Somente_Leitura bit, Dt_Criacao datetime, " +

"Dt_Ultimo_Acesso datetime, Dt_Modificacao datetime"

)]

public static IEnumerable fncArquivo_Listar(string Ds_Diretorio, string Ds_Filtro)

{

var FilePropertiesCollection = new ArrayList();

var dirInfo = new DirectoryInfo(Ds_Diretorio);

var files = dirInfo.GetFiles(Ds_Filtro);

var directories = dirInfo.GetDirectories(Ds_Filtro);

var contador = 1;

foreach (var fileInfo in directories)

{

FilePropertiesCollection.Add(new FileProperties(

contador,

"Diretorio",

fileInfo.Name,

"",

fileInfo.FullName + "\\",

false,

fileInfo.CreationTime,

fileInfo.LastAccessTime,

fileInfo.LastWriteTime

));

contador++;

}

foreach (var fileInfo in files)

{

FilePropertiesCollection.Add(new FileProperties(

contador,

"Arquivo",

fileInfo.Name,

(fileInfo.Extension.Length > 0) ? fileInfo.Name.Replace(fileInfo.Extension, "") : "",

fileInfo.DirectoryName,

fileInfo.Extension.ToLower(),

fileInfo.FullName,

fileInfo.Length,

fileInfo.IsReadOnly,

fileInfo.CreationTime,

fileInfo.LastAccessTime,

fileInfo.LastWriteTime

));

contador++;

}

return FilePropertiesCollection;

}

protected static void listarArquivos(object objFileProperties, out SqlInt32 nrLinha, out SqlString tipo, out SqlString fileName, out SqlString fileNameWithoutExtension, out SqlString directoryName, out SqlString extension, out SqlString fullName, out SqlInt64 fileSize, out SqlBoolean isReadOnly, out SqlDateTime creationTime, out SqlDateTime lastAccessTime, out SqlDateTime lastWriteTime)

{

var fileProperties = (FileProperties) objFileProperties;

nrLinha = fileProperties.NrLinha;

tipo = fileProperties.Tipo;

fileName = fileProperties.FileName;

fileNameWithoutExtension = fileProperties.FileNameWithoutExtension;

directoryName = fileProperties.DirectoryName;

extension = fileProperties.Extension;

fullName = fileProperties.FullName;

fileSize = fileProperties.FileSize;

isReadOnly = fileProperties.IsReadOnly;

creationTime = fileProperties.CreationTime;

lastAccessTime = fileProperties.LastAccessTime;

lastWriteTime = fileProperties.LastWriteTime;

}

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:

Source code:

using System.IO;
using System.Collections;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{

    private class ArquivoLer
    {

        public SqlInt32 Nr_Linha;
        public SqlString Ds_Texto;
        
        public ArquivoLer(SqlInt32 nrLinha, SqlString dsTexto)
        {

            Nr_Linha = nrLinha;
            Ds_Texto = dsTexto;
            
        }

    }

    [Microsoft.SqlServer.Server.SqlFunction(
            FillRowMethodName = "FillRow_Arquivo_Ler",
            TableDefinition = "Nr_Linha INT, Ds_Texto NVARCHAR(MAX)"
        )]
    public static IEnumerable fncArquivo_Ler(string Ds_Caminho)
    {

        var ArquivoLerCollection = new ArrayList();

        if (string.IsNullOrEmpty(Ds_Caminho))
            return ArquivoLerCollection;

        var contador = 1;

        using (var sr = new StreamReader(Ds_Caminho))
        {

            while (sr.Peek() >= 0)
            {

                ArquivoLerCollection.Add(new ArquivoLer(
                    contador,
                    sr.ReadLine()
                ));

                contador++;

            }

            sr.Close();

        }

        return ArquivoLerCollection;

    }

    protected static void FillRow_Arquivo_Ler(object objArquivoLer, out SqlInt32 nrLinha, out SqlString dsTexto)
    {

        var ArquivoLer = (ArquivoLer) objArquivoLer;

        nrLinha = ArquivoLer.Nr_Linha;
        dsTexto = ArquivoLer.Ds_Texto;

    }

}

using System.IO;

using System.Collections;

using System.Data.SqlTypes;

public partial class UserDefinedFunctions

{

private class ArquivoLer

{

public SqlInt32 Nr_Linha;

public SqlString Ds_Texto;

public ArquivoLer(SqlInt32 nrLinha, SqlString dsTexto)

{

Nr_Linha = nrLinha;

Ds_Texto = dsTexto;

}

[Microsoft.SqlServer.Server.SqlFunction(

FillRowMethodName = "FillRow_Arquivo_Ler",

TableDefinition = "Nr_Linha INT, Ds_Texto NVARCHAR(MAX)"

)]

public static IEnumerable fncArquivo_Ler(string Ds_Caminho)

{

var ArquivoLerCollection = new ArrayList();

if (string.IsNullOrEmpty(Ds_Caminho))

return ArquivoLerCollection;

var contador = 1;

using (var sr = new StreamReader(Ds_Caminho))

{

while (sr.Peek() >= 0)

{

ArquivoLerCollection.Add(new ArquivoLer(

contador,

sr.ReadLine()

));

contador++;

}

sr.Close();

}

return ArquivoLerCollection;

}

protected static void FillRow_Arquivo_Ler(object objArquivoLer, out SqlInt32 nrLinha, out SqlString dsTexto)

{

var ArquivoLer = (ArquivoLer) objArquivoLer;

nrLinha = ArquivoLer.Nr_Linha;

dsTexto = ArquivoLer.Ds_Texto;

}

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:

Source code:

using System.Data.SqlTypes;
using System.IO;

public partial class UserDefinedFunctions
{
    
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString fncArquivo_Ler_Retorna_String(SqlString Ds_Caminho)
    {

        if (Ds_Caminho.IsNull)
            return SqlString.Null;


        if (!File.Exists(Ds_Caminho.Value))
            return SqlString.Null;


        using (var sr = new StreamReader(Ds_Caminho.Value))
        {
            return sr.ReadToEnd();
        }

    }
}

using System.Data.SqlTypes;

using System.IO;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlString fncArquivo_Ler_Retorna_String(SqlString Ds_Caminho)

{

if (Ds_Caminho.IsNull)

return SqlString.Null;

if (!File.Exists(Ds_Caminho.Value))

return SqlString.Null;

using (var sr = new StreamReader(Ds_Caminho.Value))

{

return sr.ReadToEnd();

}

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:

Fnc Source CodeFile:

using System.Data.SqlTypes;
using System.IO;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean fncArquivo_Existe(SqlString Ds_Arquivo)
    {
        return (File.Exists(Ds_Arquivo.ToString()));
    }
};

using System.Data.SqlTypes;

using System.IO;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlBoolean fncArquivo_Existe(SqlString Ds_Arquivo)

{

return (File.Exists(Ds_Arquivo.ToString()));

}

};

Fnc Source CodeDirectory_Exists:

using System.Data.SqlTypes;
using System.IO;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean fncDiretorio_Existe(SqlString Ds_Diretorio)
    {
        return (Directory.Exists(Ds_Diretorio.ToString()));
    }
};

using System.Data.SqlTypes;

using System.IO;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlBoolean fncDiretorio_Existe(SqlString Ds_Diretorio)

{

return (Directory.Exists(Ds_Diretorio.ToString()));

}

};

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:

Source code:

using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Globalization;
using System.Text;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void stpExporta_Query_Txt(string query, string separador, string caminho, int Fl_Coluna)
    {

        var fileStream = new FileStream(caminho, FileMode.Create);
        var sw = new StreamWriter(fileStream, Encoding.Default);

        try
        {
            using (var conn = new SqlConnection("context connection=true"))
            {

                var getOutput = new SqlCommand
                {
                    CommandText = query,
                    CommandType = CommandType.Text,
                    CommandTimeout = 120,
                    Connection = conn
                };

                
                conn.Open();

                var exportData = getOutput.ExecuteReader();

                if (Fl_Coluna == 1)
                {
                    for (var i = 0; i < exportData.FieldCount; i++)
                    {
                        sw.Write(exportData.GetName(i));
                        if (i < exportData.FieldCount - 1)
                            sw.Write(separador);
                    }
                    sw.WriteLine();
                }

                if (string.IsNullOrEmpty(separador))
                {
                    while (exportData.Read())
                    {
                        for (var i = 0; i < exportData.FieldCount; i++)
                        {
                            sw.Write(Convert.ToString(exportData.GetValue(i), CultureInfo.GetCultureInfo("pt-BR")));
                            if (i < exportData.FieldCount - 1)
                                sw.Write(separador);
                        }
                        sw.WriteLine();
                    }
                }
                else
                {
                    var separadorTroca = new string(' ', separador.Length);

                    while (exportData.Read())
                    {
                        for (var i = 0; i < exportData.FieldCount; i++)
                        {
                            sw.Write(Convert.ToString(exportData.GetValue(i), CultureInfo.GetCultureInfo("pt-BR")).Replace(separador, separadorTroca));
                            if (i < exportData.FieldCount - 1)
                                sw.Write(separador);
                        }
                        sw.WriteLine();
                    }
                }


                conn.Close();
                sw.Close();
                conn.Dispose();
                getOutput.Dispose();

            }

        }
        catch (Exception e)
        {
            sw.Close();
            throw new ApplicationException(e.Message);
        }
    }
};

using System;

using System.Data;

using System.Data.SqlClient;

using System.IO;

using System.Globalization;

using System.Text;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void stpExporta_Query_Txt(string query, string separador, string caminho, int Fl_Coluna)

{

var fileStream = new FileStream(caminho, FileMode.Create);

var sw = new StreamWriter(fileStream, Encoding.Default);

try

{

using (var conn = new SqlConnection("context connection=true"))

{

var getOutput = new SqlCommand

{

CommandText = query,

CommandType = CommandType.Text,

CommandTimeout = 120,

Connection = conn

};

conn.Open();

var exportData = getOutput.ExecuteReader();

if (Fl_Coluna == 1)

{

for (var i = 0; i < exportData.FieldCount; i++)

{

sw.Write(exportData.GetName(i));

if (i < exportData.FieldCount - 1)

sw.Write(separador);

}

sw.WriteLine();

}

if (string.IsNullOrEmpty(separador))

{

while (exportData.Read())

{

for (var i = 0; i < exportData.FieldCount; i++)

{

sw.Write(Convert.ToString(exportData.GetValue(i), CultureInfo.GetCultureInfo("pt-BR")));

if (i < exportData.FieldCount - 1)

sw.Write(separador);

}

sw.WriteLine();

}

else

{

var separadorTroca = new string(' ', separador.Length);

while (exportData.Read())

{

for (var i = 0; i < exportData.FieldCount; i++)

{

sw.Write(Convert.ToString(exportData.GetValue(i), CultureInfo.GetCultureInfo("pt-BR")).Replace(separador, separadorTroca));

if (i < exportData.FieldCount - 1)

sw.Write(separador);

}

sw.WriteLine();

}

conn.Close();

sw.Close();

conn.Dispose();

getOutput.Dispose();

}

catch (Exception e)

{

sw.Close();

throw new ApplicationException(e.Message);

}

};

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:

Source code:

using System;
using System.Data.SqlTypes;
using System.IO;
using System.Text;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void stpEscreve_Arquivo(SqlString Ds_Texto, SqlString Ds_Caminho, SqlString Ds_Codificacao, SqlString Ds_Formato_Quebra_Linha, SqlBoolean Fl_Append)
    {


        if (!Ds_Texto.IsNull && !Ds_Caminho.IsNull && !Fl_Append.IsNull)
        {

            try
            {

                var dir = Path.GetDirectoryName(Ds_Caminho.Value);

                if (!Directory.Exists(dir))
                    Directory.CreateDirectory(dir);

            }
            catch (Exception e)
            {
                throw new ApplicationException(e.Message);
            }


            var encoding = (Ds_Codificacao.IsNull) ? "UTF-8" : Ds_Codificacao.Value;
            if (Ds_Codificacao.Value.Trim() == "")
                encoding = "UTF-8";

            var sb = new StringBuilder(Ds_Texto.Value);

            var fileStream = new FileStream(Ds_Caminho.Value, ((Fl_Append) ? FileMode.Append : FileMode.Create));
            var sw = new StreamWriter(fileStream, Encoding.GetEncoding(encoding));

            switch (Ds_Formato_Quebra_Linha.Value.ToLower())
            {
                case "unix":
                    sw.NewLine = "\n";
                    sb.Replace("\r", "");
                    break;
                case "mac":
                    sw.NewLine = "\r";
                    sb.Replace("\n", "");
                    break;
                default:
                    sw.NewLine = "\r\n";
                    break;
            }


            try
            {

                var texto = sb.ToString();

                sw.Write(texto);
                sw.Close();

            }
            catch (Exception e)
            {
                sw.Close();
                throw new ApplicationException(e.Message);
            }

        }
        else
            throw new ApplicationException("Os parâmetros de input estão vazios");

    }
};

using System;

using System.Data.SqlTypes;

using System.IO;

using System.Text;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void stpEscreve_Arquivo(SqlString Ds_Texto, SqlString Ds_Caminho, SqlString Ds_Codificacao, SqlString Ds_Formato_Quebra_Linha, SqlBoolean Fl_Append)

{

if (!Ds_Texto.IsNull && !Ds_Caminho.IsNull && !Fl_Append.IsNull)

{

try

{

var dir = Path.GetDirectoryName(Ds_Caminho.Value);

if (!Directory.Exists(dir))

Directory.CreateDirectory(dir);

}

catch (Exception e)

{

throw new ApplicationException(e.Message);

}

var encoding = (Ds_Codificacao.IsNull) ? "UTF-8" : Ds_Codificacao.Value;

if (Ds_Codificacao.Value.Trim() == "")

encoding = "UTF-8";

var sb = new StringBuilder(Ds_Texto.Value);

var fileStream = new FileStream(Ds_Caminho.Value, ((Fl_Append) ? FileMode.Append : FileMode.Create));

var sw = new StreamWriter(fileStream, Encoding.GetEncoding(encoding));

switch (Ds_Formato_Quebra_Linha.Value.ToLower())

{

case "unix":

sw.NewLine = "\n";

sb.Replace("\r", "");

break;

case "mac":

sw.NewLine = "\r";

sb.Replace("\n", "");

break;

default:

sw.NewLine = "\r\n";

break;

}

try

{

var texto = sb.ToString();

sw.Write(texto);

sw.Close();

}

catch (Exception e)

{

sw.Close();

throw new ApplicationException(e.Message);

}

else

throw new ApplicationException("Os parâmetros de input estão vazios");

}

};

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:

Source code:

using System;
using System.IO;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void stpCopia_Arquivo(string origem, string destino, bool sobrescrever)
    {
        try
        {
            File.Copy(@origem, @destino, sobrescrever);
        }
        catch (Exception e)
        {
            throw new ApplicationException("Erro : " + e.Message);
        }
    }
};

using System;

using System.IO;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void stpCopia_Arquivo(string origem, string destino, bool sobrescrever)

{

try

{

File.Copy(@origem, @destino, sobrescrever);

}

catch (Exception e)

{

throw new ApplicationException("Erro : " + e.Message);

}

};

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:

Source code:

using System;
using System.Data.SqlTypes;
using System.IO;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void stpMove_Arquivo(SqlString Arquivo_Origem, SqlString Pasta_Destino, SqlBoolean Fl_Sobrescrever)
    {

        if (Arquivo_Origem.IsNull)
            throw new ApplicationException("Favor informar o arquivo de origem");

        if (Pasta_Destino.IsNull)
            throw new ApplicationException("Favor informar a pasta de destino");
   

        try
        {

            var _pasta = new DirectoryInfo(Pasta_Destino.Value);
            var _arquivo = new FileInfo(Arquivo_Origem.Value);
            var _aquivoNovo = new FileInfo(_pasta.FullName + "\\" +  _arquivo.Name);

            if (!_pasta.Exists)
                throw new ApplicationException("A pasta de destino " + _pasta.FullName + " não existe.");

            if (!_arquivo.Exists)
                throw new ApplicationException("O arquivo de origem " + _arquivo.FullName + " não existe.");

            if (_aquivoNovo.FullName == _arquivo.FullName)
                throw new ApplicationException("O caminho de origem e destino não podem ser iguais.");

            if (Fl_Sobrescrever)
                if (_aquivoNovo.Exists)
                    _aquivoNovo.Delete();

            _arquivo.MoveTo(_aquivoNovo.FullName);

        }
        catch (Exception e)
        {
            throw new ApplicationException("Erro : " + e.Message);
        }
    }
};

using System;

using System.Data.SqlTypes;

using System.IO;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void stpMove_Arquivo(SqlString Arquivo_Origem, SqlString Pasta_Destino, SqlBoolean Fl_Sobrescrever)

{

if (Arquivo_Origem.IsNull)

throw new ApplicationException("Favor informar o arquivo de origem");

if (Pasta_Destino.IsNull)

throw new ApplicationException("Favor informar a pasta de destino");

try

{

var _pasta = new DirectoryInfo(Pasta_Destino.Value);

var _arquivo = new FileInfo(Arquivo_Origem.Value);

var _aquivoNovo = new FileInfo(_pasta.FullName + "\\" + _arquivo.Name);

if (!_pasta.Exists)

throw new ApplicationException("A pasta de destino " + _pasta.FullName + " não existe.");

if (!_arquivo.Exists)

throw new ApplicationException("O arquivo de origem " + _arquivo.FullName + " não existe.");

if (_aquivoNovo.FullName == _arquivo.FullName)

throw new ApplicationException("O caminho de origem e destino não podem ser iguais.");

if (Fl_Sobrescrever)

if (_aquivoNovo.Exists)

_aquivoNovo.Delete();

_arquivo.MoveTo(_aquivoNovo.FullName);

}

catch (Exception e)

{

throw new ApplicationException("Erro : " + e.Message);

}

};

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:

Source code:

using System;
using System.Data.SqlTypes;
using System.IO;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void stpRenomeia_Arquivo(SqlString Caminho_Origem, SqlString Caminho_Destino, SqlBoolean Fl_Sobrescrever)
    {
        try
        {
            if (Fl_Sobrescrever.Value)
                if (File.Exists(Caminho_Destino.Value))
                        File.Delete(Caminho_Destino.Value);
            
            File.Move(Caminho_Origem.Value, Caminho_Destino.Value);
        }
        catch (Exception e)
        {
            throw new ApplicationException("Erro : " + e.Message);
        }
    }
};

using System;

using System.Data.SqlTypes;

using System.IO;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void stpRenomeia_Arquivo(SqlString Caminho_Origem, SqlString Caminho_Destino, SqlBoolean Fl_Sobrescrever)

{

try

{

if (Fl_Sobrescrever.Value)

if (File.Exists(Caminho_Destino.Value))

File.Delete(Caminho_Destino.Value);

File.Move(Caminho_Origem.Value, Caminho_Destino.Value);

}

catch (Exception e)

{

throw new ApplicationException("Erro : " + e.Message);

}

};

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:

Source code:

using System;
using System.IO;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void stpApaga_Arquivo(string caminho)
    {
        try
        {

            var Arquivo = new FileInfo(caminho);

            if (Arquivo.Exists)
            {
                Arquivo.Delete();
            }
            else
            {
                throw new ApplicationException("O Arquivo especificado não existe.");
            } 
        }
        catch (Exception e)
        {
            throw new ApplicationException("Erro : " + e.Message);
        }
    }
};

using System;

using System.IO;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void stpApaga_Arquivo(string caminho)

{

try

{

var Arquivo = new FileInfo(caminho);

if (Arquivo.Exists)

{

Arquivo.Delete();

}

else

{

throw new ApplicationException("O Arquivo especificado não existe.");

}

catch (Exception e)

{

throw new ApplicationException("Erro : " + e.Message);

}

};

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:

Source code:

using System;
using System.IO;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void stpDiretorio_Apagar_Arquivos(string caminho, string filtro)
    {
    
        try
        {
            if (!Directory.Exists(caminho))
            {
                throw new ApplicationException("Caminho especificado ('" + caminho + "') não existe ou inacessivel.");
                return;
            }
        }
        catch
        {
            throw new ApplicationException("Erro ao converter caminho especificado ('" + caminho + "').");
            return;
        }


        try
        {

            var diretorio = new DirectoryInfo(caminho);

            foreach (var arquivo in diretorio.GetFiles(filtro))
            {
                arquivo.Delete();
            }
        }
        catch (Exception e)
        {
            throw new ApplicationException("Erro : " + e.Message);
        }
    }

};

using System;

using System.IO;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void stpDiretorio_Apagar_Arquivos(string caminho, string filtro)

{

try

{

if (!Directory.Exists(caminho))

{

throw new ApplicationException("Caminho especificado ('" + caminho + "') não existe ou inacessivel.");

return;

}

catch

{

throw new ApplicationException("Erro ao converter caminho especificado ('" + caminho + "').");

return;

}

try

{

var diretorio = new DirectoryInfo(caminho);

foreach (var arquivo in diretorio.GetFiles(filtro))

{

arquivo.Delete();

}

catch (Exception e)

{

throw new ApplicationException("Erro : " + e.Message);

}

};

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:

Source code:

using System;
using System.IO;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void stpApaga_Diretorio(string caminho)
    {

        try
        {

            var Diretorio = new DirectoryInfo(caminho);

            if (Diretorio.Exists)
            {
                if (Diretorio.GetFiles().Length > 0 || Diretorio.GetDirectories().Length > 0)
                {
                    throw new ApplicationException("Não é possível apagar um diretório que possua arquivos.");
                }
                else
                {
                    Diretorio.Delete();
                }
            }
            else
            {
                throw new ApplicationException("O Diretório especificado não existe.");
            } 
        }
        catch (Exception e)
        {
            throw new ApplicationException("Erro : " + e.Message);
        }
    }
};

using System;

using System.IO;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void stpApaga_Diretorio(string caminho)

{

try

{

var Diretorio = new DirectoryInfo(caminho);

if (Diretorio.Exists)

{

if (Diretorio.GetFiles().Length > 0 || Diretorio.GetDirectories().Length > 0)

{

throw new ApplicationException("Não é possível apagar um diretório que possua arquivos.");

}

else

{

Diretorio.Delete();

}

else

{

throw new ApplicationException("O Diretório especificado não existe.");

}

catch (Exception e)

{

throw new ApplicationException("Erro : " + e.Message);

}

};

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

Hug!

Diego said:

30 November 2019 to 16: 23

Very good.

Dirceu Resende said:

28 May 2016 to 00: 10

Hello Vinicius!
Thank you for your visit!

All the functions I developed myself. As for SP's, most are very simple and you should find them the same or very similar on the internet, not least because, there is not much to escape from, it is basic C #.

The stpExporta_Query_Txt SP, which was very good by the way, I believe you created it yourself, but there are also several ways to do the same thing if you search, there is even code similar to this SP in this link: http://stackoverflow.com/a/8580650

Any questions, just talk 🙂

- Rodrigo Berthelsen da Silveira said:
  
  26 March 2017 to 09: 11
  
  Really! StpExporta_Query_Txt is very good! Your blog Dirceu has become a reference for me, since with C # we have much more freedom to work !!
  
  Thanks for sharing your knowledge!
  
  Hugs!
  
  Rodrigo B Silveira
  
Vinicius Watson said:

27 May 2016 to 12: 43

mt well, I was wondering who created these roles;

Other Languages

Subscribe to a blog by email

Blog Views

Power Alerts

Azure Banking Admin Course

SQL Server Security Course

My Certifications

Posts Archive

Categories

Recent Posts

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

How to list files in SQL Server

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

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

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

How to export data from a SQL Server table to file

How to export a SQL Server string to file

How to copy files in SQL Server

How to move files in SQL Server

How to rename files in SQL Server

How to delete files in SQL Server

How to delete all files from a directory in SQL Server

How to delete a directory in SQL Server

You may also like ...

Comments

Leave a Comment Cancel reply

How to list files in SQL Server

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

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

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

How to export data from a SQL Server table to file

How to export a SQL Server string to file

How to copy files in SQL Server

How to move files in SQL Server

How to rename files in SQL Server

How to delete files in SQL Server

How to delete all files from a directory in SQL Server

How to delete a directory in SQL Server

You may also like ...

Generating Object Reverse Engineering (DDL Backup) in Oracle Database 11g

Generating AWR Reports via Script in Oracle Database 11g

Microsoft MCSE Certification Exam 70-774 (Perform Cloud Data Science with Azure Machine Learning) for free (beta) through 31 / 03 / 2017

Comments

Leave a Comment Cancel reply