Olá pessoal,
Bom dia!
Nesse post vou mostrar a vocês como listar, ler, escrever, copiar, excluir e mover arquivos utilizando o CLR (C#), que é uma poderosa ferramenta para aumentar a gama de funcionalidades do SQL Server. Como eu crio muitas rotinas de trocas de arquivos no meu trabalho, seja importando dados de uma base externa ou exportando os dados para arquivos, resolvi criar esse post para ajudar as pessoas que tem as mesmas necessidades e que podem resolver facilmente essas questões pelo próprio SQL Server, o que é muito performático e simples de criar, implementar e manter.
Antes de começar, gostaria de citar dois posts relacionados com esse assunto:
- Operações com arquivos utilizando OLE Automation no SQL Server, onde havia feito um post parecido com esse, onde eu utilizava OLE Automation, um recurso bem legal do SQL Server, mas que possui algumas limitações e riscos para o servidor. Por estes motivos, o CLR é apontado como o grande substituto para as rotinas OLE Automation
- Introdução ao SQL CLR (Common Language Runtime) no SQL Server é o post que eu fiz falando sobre o CLR, suas vantagens e desvantagens, comparei com o OLE Automation e explico como criar suas primeiras SP’s e funções no CLR (C#)
- SQL Server – Operações com arquivos utilizando xp_cmdshell (Como listar, ler, escrever, copiar, excluir e mover arquivos)
Como listar arquivos no SQL Server
Para listar arquivos, eu utilizo o uma table-valued function, que é muito útil para listar os arquivos já filtrando com o WHERE, posso ordenar os resultados, utilizar SELECT * INTO para salvar os resultados em uma tabela.. Enfim, é uma solução bem flexÃvel e prática:
Usage example:
Código-fonte:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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.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; } } |
Como ler um arquivo de texto linha a linha no SQL Server
Para a tarefa de ler um arquivo linha a linha, eu vou utilizar novamente um table-valued function do CLR, que vai me permitir realizar um select na view e retornar o conteúdo do arquivo. Posso exportar esses dados para uma tabela, filtrar, ordenar, etc.. tudo com muita flexibidade.
Exemplo de uso:
Código-fonte:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
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; } } |
Como ler um arquivo e retornar como uma string no SQL Server
Para esta necessidade optei por criar uma função scalar para uma utilização muito comum durante a criação de rotinas é a necessidade de importar um arquivo e não retornar linha a linha, mas sim uma string com o conteúdo todo do arquivo. Utilizo muito isso durante a importação de arquivos XML, por exemplo.
Usage example:
Código-fonte:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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(); } } } |
Como verificar se um arquivo ou diretório existe no SQL Server
Funções do tipo scalar e com retorno booleano (BIT), elas permitem verificar se um arquivo ou diretório existe no filesystem. Seu código-fonte é tão simples quanto sua utilização.
Usage example:
Código-fonte da fncArquivo_Existe:
|
1 2 3 4 5 6 7 8 9 10 11 |
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())); } }; |
Código-fonte da fncDiretorio_Existe:
|
1 2 3 4 5 6 7 8 9 10 11 |
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())); } }; |
Como exportar os dados de uma tabela do SQL Server para arquivo
Com essa Stored Procedure, podemos facilmente exportar os dados de uma tabela ou view do SQL Server para um arquivo de texto delimitado ou não, onde cada registro será uma linha do arquivo criado
Usage example:
Código-fonte:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 |
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); } } }; |
Como exportar uma string do SQL Server para arquivo
Com essa Stored Procedure, podemos facilmente exportar os dados de string do SQL Server e exportar essa string para um arquivo texto. Não será forçado nenhuma quebra de linha no arquivo, apenas se na string ouvir os caracteres de quebra de linha (line feed e/ou carriage return).
O parâmetro Ds_Codificacao permite variar entre UTF-8, ISO-8859-1 e vários outros suportados pelo .NET Framework. A lista completa das codificações pode ser encontrada aqui: Encoding.GetEncodings Method
O parâmetro Ds_Formato_Quebra_Linha permite alternar entre os formatos de quebra de linha de cada sistema operacional, sendo possÃvel utilizar os valores Windows, Unix e MAC.
O parâmetro Fl_Append permite que caso o arquivo já exista, ele seja apagado e sobrescrito (Fl_Append = 0) ou o conteúdo seja adicionado ao final do arquivo (Fl_Append = 1)
Usage example:
Código-fonte:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
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"); } }; |
Como copiar arquivos no SQL Server
Procedure que pode ser utilizada para copiar um arquivo de um diretório para outro
Usage example:
Código-fonte:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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); } } }; |
Como mover arquivos no SQL Server
Procedure que pode ser utilizada para mover um arquivo de um diretório para outro, mantendo o mesmo nome do arquivo
Usage example:
Código-fonte:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
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); } } }; |
Como renomear arquivos no SQL Server
Procedure que pode ser utilizada para renomear um arquivo, permitindo até movê-lo com outro nome
Usage example:
Código-fonte:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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); } } }; |
Como apagar arquivos no SQL Server
Procedure que pode ser utilizada para apagar um arquivo fÃsicamente
Usage example:
Código-fonte:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
using System; using System.IO; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void stpApaga_Arquivo(string caminho) { try { var Archive = new FileInfo(caminho); if (Archive.Exists) { Archive.Delete(); } else { throw new ApplicationException("O Arquivo especificado não existe."); } } catch (Exception e) { throw new ApplicationException("Erro : " + e.Message); } } }; |
Como apagar todos os arquivos de um diretório no SQL Server
Procedure que pode ser utilizada para apagar todos os arquivos de um determinado diretório
Usage example:
Código-fonte:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
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); } } }; |
Como apagar um diretório no SQL Server
Procedure que pode ser utilizada para apagar um determinado diretório no SQL Server. Caso esse diretório possua arquivos, eles devem ser apagados antes da exclusão do diretório.
Usage example:
Código-fonte:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
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!
Espero que tenham gostado do post e que ele lhe seja útil
Abraço!
Portuguese 
English
Muito bom.
Olá, Vinicius!
Muito obrigado pela visita!
Todas as funções eu mesmo que desenvolvi. Quanto as SP’s, a maioria é bem simples e você deve achar iguais ou bem parecidas na internet, até porque, não tem muito pra onde fugir, é C# básico.
A SP stpExporta_Query_Txt, que ficou muito boa por sinal, acredito que você mesmo que tenha criado, mas também existem várias formas de se fazer a mesma coisa se você pesquisar, tem até um código parecido com o dessa SP nesse link: http://stackoverflow.com/a/8580650
Qualquer dúvida, é só falar 🙂
Realmente! A stpExporta_Query_Txt é muito boa! Seu blog Dirceu se tornou referência para mim, visto que com C# temos muito mais liberdade para trabalhar!!
Obrigado por compartilhar seu conhecimento!
Abraços!
Rodrigo B Silveira
mt bom, queria saber quem criou essas funções ;x