Hola, chicos,
¡Buen día!
En esta publicación, le mostraré cómo enumerar, leer, escribir, copiar, eliminar y mover archivos usando CLR (C#), que es una herramienta poderosa para aumentar la gama de funcionalidades de SQL Server. Como creo muchas rutinas de intercambio de archivos en mi trabajo, ya sea importando datos de una base de datos externa o exportando datos a archivos, decidí crear esta publicación para ayudar a las personas que tienen las mismas necesidades y que pueden resolver fácilmente estos problemas utilizando SQL Server, que es muy eficaz y sencillo de crear, implementar y mantener.
Antes de comenzar, me gustaría mencionar dos posts relacionados con este tema:
- Operaciones de archivos usando OLE Automation en SQL Server, donde hice una publicación similar a esta, donde usé OLE Automation, una característica realmente interesante de SQL Server, pero que tiene algunas limitaciones y riesgos para el servidor. Por estos motivos, CLR se considera el gran sustituto de las rutinas de automatización OLE.
- Introducción a SQL CLR (Common Language Runtime) en SQL Server es el post que hice hablando sobre el CLR, sus ventajas y desventajas, lo comparo con OLE Automation y explico como crear tus primeros SP’s y funciones en el CLR (C#)
- SQL Server: operaciones de archivos usando xp_cmdshell (cómo enumerar, leer, escribir, copiar, eliminar y mover archivos)
Cómo enumerar archivos en SQL Server
Para listar archivos, uso una función con valores de tabla, que es muy útil para listar archivos que ya están filtrados con WHERE, puedo ordenar los resultados, uso SELECT * INTO para guardar los resultados en una tabla. De todos modos, es una solución muy flexible y práctica:
Ejemplo de uso:
Código fuente:
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;
}
}
Cómo leer un archivo de texto línea por línea en SQL Server
Para la tarea de leer un archivo línea por línea, usaré nuevamente una función con valores de tabla del CLR, que me permitirá realizar una selección en la vista y devolver el contenido del archivo. Puedo exportar estos datos a una tabla, filtrar, ordenar, etc. todo con gran flexibilidad.
Ejemplo de uso:
Código fuente:
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;
}
}Cómo leer un archivo y devolverlo como una cadena en SQL Server
Para esta necesidad elegí crear una función escalar para un uso muy común a la hora de crear rutinas es la necesidad de importar un archivo y no devolver línea por línea, sino una cadena con todo el contenido del archivo. Lo uso mucho al importar archivos XML, por ejemplo.
Ejemplo de uso:
Código fuente:
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();
}
}
}
Cómo comprobar si existe un archivo o directorio en SQL Server
Funciones escalares con retorno booleano (BIT), le permiten verificar si un archivo o directorio existe en el sistema de archivos. Su código fuente es tan sencillo como su uso.
Ejemplo de uso:
Código fuente de fncFile_Exists:
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()));
}
};fncDiretorio_Existe código fuente:
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()));
}
};
Cómo exportar datos de una tabla de SQL Server a un archivo
Con este Procedimiento Almacenado, podemos exportar fácilmente datos desde una tabla o vista de SQL Server a un archivo de texto delimitado o no delimitado, donde cada registro será una línea del archivo creado.
Ejemplo de uso:
Código fuente:
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);
}
}
};
Cómo exportar una cadena de SQL Server a un archivo
Con este procedimiento almacenado, podemos exportar fácilmente datos de cadenas desde SQL Server y exportar esa cadena a un archivo de texto. No se forzará ningún salto de línea en el archivo, solo si la cadena escucha caracteres de salto de línea (avance de línea y/o retorno de carro).
El parámetro Ds_Codificacao le permite variar entre UTF-8, ISO-8859-1 y varios otros compatibles con .NET Framework. La lista completa de codificaciones se puede encontrar aquí: Método Encoding.GetEncodings
El parámetro Ds_Formato_Quebra_Linha le permite cambiar entre los formatos de salto de línea para cada sistema operativo, permitiendo utilizar valores de Windows, Unix y MAC.
El parámetro Fl_Append permite que si el archivo ya existe, se elimine y se sobrescriba (Fl_Append = 0) o se agregue el contenido al final del archivo (Fl_Append = 1)
Ejemplo de uso:
Código fuente:
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");
}
};
Cómo copiar archivos en SQL Server
Procedimiento que se puede utilizar para copiar un archivo de un directorio a otro
Ejemplo de uso:
Código fuente:
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);
}
}
};
Cómo mover archivos en SQL Server
Procedimiento que se puede utilizar para mover un archivo de un directorio a otro, manteniendo el mismo nombre de archivo
Ejemplo de uso:
Código fuente:
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);
}
}
};
Cómo cambiar el nombre de archivos en SQL Server
Procedimiento que se puede utilizar para cambiar el nombre de un archivo, permitiendo incluso moverlo a otro nombre
Ejemplo de uso:
Código fuente:
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);
}
}
};
Cómo eliminar archivos en SQL Server
Procedimiento que se puede utilizar para eliminar físicamente un archivo
Ejemplo de uso:
Código fuente:
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);
}
}
};
Cómo eliminar todos los archivos de un directorio en SQL Server
Procedimiento que se puede utilizar para eliminar todos los archivos de un directorio determinado
Ejemplo de uso:
Código fuente:
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);
}
}
};
Cómo eliminar un directorio en SQL Server
Procedimiento que se puede utilizar para eliminar un directorio específico en SQL Server. Si este directorio tiene archivos, se deben eliminar antes de eliminar el directorio.
Ejemplo de uso:
Código fuente:
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);
}
}
};
¡Y eso es todo, amigos!
Espero que te haya gustado el post y que te haya sido útil.
¡Abrazo!
Comentários (0)
Carregando comentários…