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:
Exemplo de utilização:
Código-fonte:
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:
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.
Exemplo de utilização:
Código-fonte:
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.
Exemplo de utilização:
Código-fonte da fncArquivo_Existe:
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:
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
Exemplo de utilização:
Código-fonte:
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)
Exemplo de utilização:
Código-fonte:
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
Exemplo de utilização:
Código-fonte:
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
Exemplo de utilização:
Código-fonte:
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
Exemplo de utilização:
Código-fonte:
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
Exemplo de utilização:
Código-fonte:
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);
}
}
};
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
Exemplo de utilização:
Código-fonte:
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.
Exemplo de utilização:
Código-fonte:
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);
}
}
};
E é isso aí, pessoal!
Espero que tenham gostado do post e que ele lhe seja útil
Abraço!
Comentários (0)
Carregando comentários…