Warning: preg_match(): Unknown modifier 'b' in /mnt/datadisk/www/src/Controllers/PostController.php on line 206 SQL Server - How to integrate with FTP and list, upload, and download files using CLR (C#) — Dirceu ResendeSkip to content
In this post, I will talk again about the CLR (C#), demonstrating another use of this powerful SQL Server feature, which is the integration between the database and FTP servers, whether to download (download) or upload (upload) files between a local folder or on your network and the FTP server.
This feature is especially useful when you need integrations with other companies, where you need to export data and make it available on another company's external FTP or import files from an FTP and import them to your network or database.
In all routines, I placed a “retry” of up to 10 attempts in cases of connection failure or timeout, so that your routine does not fail due to any network instability (this has already helped me A LOT).
View more details, usage examples, and source code
This FTPControle class is the base class of this post, as all Procedures and Functions in this post require this class to function. The idea is to place all the FTP control logic in this base class, and the procedures and functions only use the methods of this class.
Although the code is quite large, it is not a complex class, and can be easily understood.
using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.Globalization;
using System.Net;
using System.IO;
namespace Bibliotecas.Model
{
public class FTPControle
{
public string Host { get; set; }
public string Login { get; set; }
public string Senha { get; set; }
private FtpWebRequest _ftp;
public FTPControle(string host, string login, string senha)
{
Host = host;
Login = login;
Senha = senha;
}
public void ApagarArquivos(string pastaFtp, List<string> arquivos)
{
foreach (var arquivo in arquivos)
{
var arquivoFtp = Host + pastaFtp + arquivo;
_ftp = (FtpWebRequest)WebRequest.Create(arquivoFtp);
IniciarMetodo();
_ftp.Method = WebRequestMethods.Ftp.DeleteFile;
_ftp.GetResponse();
}
}
public void ApagarArquivo(string pastaFtp, string arquivo)
{
var arquivoFtp = Host + pastaFtp + arquivo;
_ftp = (FtpWebRequest)WebRequest.Create(arquivoFtp);
IniciarMetodo();
_ftp.Method = WebRequestMethods.Ftp.DeleteFile;
_ftp.GetResponse();
}
public List<string> ListarArquivos(string pastaFtp, string filtro)
{
var _pastaFtp = Host + pastaFtp;
_ftp = (FtpWebRequest)WebRequest.Create(_pastaFtp);
var arquivos = new List<string>();
IniciarMetodo();
_ftp.Method = WebRequestMethods.Ftp.ListDirectory;
using (var response = (FtpWebResponse) _ftp.GetResponse())
{
using (var responseStream = response.GetResponseStream())
{
if (responseStream != null)
{
using (var r = new StreamReader(responseStream))
{
var s = r.ReadLine();
while (s != null)
{
arquivos.Add(s);
s = r.ReadLine();
}
}
}
}
}
return arquivos.FindAll(
nome => nome.Contains(filtro.Replace("*", ""))
);
}
public List<string> ListarArquivosCompleto(string pastaFtp, string filtro)
{
var _pastaFtp = Host + pastaFtp;
_ftp = (FtpWebRequest) WebRequest.Create(_pastaFtp);
var arquivos = new List<string>();
IniciarMetodo();
_ftp.Method = WebRequestMethods.Ftp.ListDirectoryDetails;
using (var response = (FtpWebResponse) _ftp.GetResponse())
{
using (var responseStream = response.GetResponseStream())
{
if (responseStream != null)
{
using (var r = new StreamReader(responseStream))
{
var s = r.ReadLine();
while (s != null)
{
arquivos.Add(s);
s = r.ReadLine();
}
}
}
}
}
return arquivos.FindAll(
nome => nome.Contains(filtro.Replace("*", ""))
);
}
private static string GetOctalPermission(string Ds_Permissao)
{
switch (Ds_Permissao)
{
case "---":
return "0";
case "--x":
return "1";
case "-w-":
return "2";
case "-wx":
return "3";
case "r--":
return "4";
case "r-x":
return "5";
case "rw-":
return "6";
case "rwx":
return "7";
default:
return "";
}
}
private static int GetMonth(string Ds_Mes)
{
switch (Ds_Mes.ToLower())
{
case "jan":
return 1;
case "feb":
return 2;
case "mar":
return 3;
case "apr":
return 4;
case "may":
return 5;
case "jun":
return 6;
case "jul":
return 7;
case "aug":
return 8;
case "sep":
return 9;
case "oct":
return 10;
case "nov":
return 11;
case "dec":
return 12;
default:
return 0;
}
}
private static string GetMonthName(string Ds_Mes)
{
switch (Ds_Mes)
{
case "1":
return "jan";
case "2":
return "feb";
case "3":
return "mar";
case "4":
return "apr";
case "5":
return "may";
case "6":
return "jun";
case "7":
return "jul";
case "8":
return "aug";
case "9":
return "sep";
case "10":
return "oct";
case "11":
return "nov";
case "12":
return "dez";
default:
return "";
}
}
public FTPArquivoListar ListarArquivosCompleto_Parse(string arquivo, string Ds_Pasta_FTP, int Nr_Contador)
{
try
{
var dir = (arquivo.Substring(0, 1) == "d") ? "d" : "f";
var owner = "";
var group = "";
var ownerSec = "";
var groupSec = "";
var everyoneSec = "";
var size = 0;
var month = "";
var year = "";
var day = "";
var filename = "";
SqlDateTime data;
if (arquivo.Substring(15, 2).ToUpper() == "AM" || arquivo.Substring(15, 2).ToUpper() == "PM")
{
dir = arquivo.Contains("<DIR>") ? "d" : "f";
int.TryParse(arquivo.Substring(29, 9).Trim(), out size);
month = arquivo.Substring(0, 2);
day = arquivo.Substring(3, 2);
year = arquivo.Substring(6, 2);
year = (int.Parse(year) > DateTime.Now.Year ? "19" : "20") + year;
var hora = int.Parse(arquivo.Substring(10, 2)) + (arquivo.Substring(15, 2).ToUpper() == "PM" && int.Parse(arquivo.Substring(10, 2)) < 12 ? 12 : 0);
var minuto = int.Parse(arquivo.Substring(13, 2));
data = new DateTime(int.Parse(year), int.Parse(month), int.Parse(day), hora, minuto, 0);
filename = arquivo.Substring(39, arquivo.Length - 39).Trim();
}
else
{
var palavras = arquivo.Split(' ');
var contador = 1;
ownerSec = arquivo.Substring(1, 3).Trim();
groupSec = arquivo.Substring(4, 3).Trim();
everyoneSec = arquivo.Substring(7, 3).Trim();
foreach (var palavra in palavras)
{
if (contador == 3)
{
owner = palavra.Trim();
}
else if (contador == 4)
{
group = palavra.Trim();
}
else if (contador == 5)
{
int.TryParse(palavra.Trim(), out size);
}
else if (contador == 6)
{
month = palavra.ToLower().Trim();
}
else if (contador == 7)
{
day = palavra.Trim();
}
else if (contador == 8)
{
year = palavra.Trim();
}
else
{
if (contador >= 9)
{
filename += palavra;
}
}
if (!string.IsNullOrEmpty(palavra) || contador >= 9)
contador++;
}
if (year.IndexOf(":", StringComparison.Ordinal) >= 0)
{
var mes = GetMonth(month);
var time = year.Split(':');
data = mes > DateTime.Now.Month ? new SqlDateTime(DateTime.Now.Year - 1, mes, Convert.ToInt32(day), Convert.ToInt32(time[0]), Convert.ToInt32(time[1]), 0) : new SqlDateTime(DateTime.Now.Year, mes, Convert.ToInt32(day), Convert.ToInt32(time[0]), Convert.ToInt32(time[1]), 0);
}
else
{
var dataOrigem = DateTime.ParseExact(year + "-" + month + "-" + day, "yyyy-MMM-d", new CultureInfo("en-US"));
data = Convert.ToDateTime(dataOrigem.ToString("dd/MM/yyyy", new CultureInfo("pt-BR")));
}
}
if (filename == "." || filename == "..")
{
return new FTPArquivoListar(
0,
"Diretorio",
Ds_Pasta_FTP + "/",
filename, //arquivo,
size,
SqlDateTime.Null,
ownerSec + groupSec + everyoneSec,
"0" + GetOctalPermission(ownerSec) + GetOctalPermission(groupSec) + GetOctalPermission(everyoneSec),
owner,
group
);
}
return new FTPArquivoListar(
Nr_Contador,
(dir == "d" ? "Diretorio" : "Arquivo"),
Ds_Pasta_FTP + "/",
filename, //arquivo,
size,
data,
ownerSec + groupSec + everyoneSec,
"0" + GetOctalPermission(ownerSec) + GetOctalPermission(groupSec) + GetOctalPermission(everyoneSec),
owner,
group
);
}
catch (Exception e)
{
return new FTPArquivoListar(
0,
"Erro",
arquivo,
e.Message, //arquivo,
0,
SqlDateTime.Null,
"",
"000",
"",
""
);
}
}
public int BaixarArquivos(string pastaFtp, List<string> arquivos, string pastaLocal)
{
var buffer = new byte[2048];
foreach (var arquivo in arquivos)
{
var arquivoFtp = Host + pastaFtp + arquivo;
var arquivoLocal = pastaLocal + arquivo;
_ftp = (FtpWebRequest)WebRequest.Create(arquivoFtp);
IniciarMetodo();
_ftp.Method = WebRequestMethods.Ftp.DownloadFile;
using (var response = (FtpWebResponse)_ftp.GetResponse())
{
using (var responseStream = response.GetResponseStream())
{
using (var fs = new FileStream(arquivoLocal, FileMode.Create))
{
if (responseStream != null)
{
var read = responseStream.Read(buffer, 0, buffer.Length); ;
while (read != 0)
{
fs.Write(buffer, 0, read);
read = responseStream.Read(buffer, 0, buffer.Length);
}
}
fs.Flush();
}
}
}
}
return arquivos.Count;
}
public void BaixarArquivo(string pastaFtp, string arquivo, string arquivoLocal)
{
var buffer = new byte[2048];
var arquivoFtp = Host + pastaFtp + arquivo;
var _arquivoLocal = arquivoLocal;
_ftp = (FtpWebRequest) WebRequest.Create(arquivoFtp);
IniciarMetodo();
_ftp.Method = WebRequestMethods.Ftp.DownloadFile;
using (var response = (FtpWebResponse) _ftp.GetResponse())
{
using (var responseStream = response.GetResponseStream())
{
using (var fs = new FileStream(_arquivoLocal, FileMode.Create))
{
if (responseStream != null)
{
var read = responseStream.Read(buffer, 0, buffer.Length); ;
while (read != 0)
{
fs.Write(buffer, 0, read);
read = responseStream.Read(buffer, 0, buffer.Length);
}
}
fs.Flush();
}
}
}
}
public int SubirArquivos(List<string> arquivos, string pastaFtp)
{
foreach (var arquivo in arquivos)
{
string nomeArquivo;
if (arquivo.LastIndexOf(@"/", StringComparison.Ordinal) > 0)
nomeArquivo = arquivo.Substring(arquivo.LastIndexOf(@"/", StringComparison.Ordinal),arquivo.Length - arquivo.LastIndexOf(@"/", StringComparison.Ordinal));
else
if (arquivo.LastIndexOf(@"\", StringComparison.Ordinal) > 0)
nomeArquivo = arquivo.Substring(arquivo.LastIndexOf(@"\", StringComparison.Ordinal), arquivo.Length - arquivo.LastIndexOf(@"\", StringComparison.Ordinal));
else
return -1;
var arquivoFtp = Host + pastaFtp + nomeArquivo;
var arquivoLocal = arquivo;
_ftp = (FtpWebRequest)WebRequest.Create(arquivoFtp);
IniciarMetodo();
_ftp.Method = WebRequestMethods.Ftp.UploadFile;
_ftp.Timeout = 600000;
byte[] buffer;
using (var stream = File.OpenRead(arquivoLocal))
{
buffer = new byte[stream.Length];
stream.Read(buffer, 0, buffer.Length);
}
using (var reqStream = _ftp.GetRequestStream())
{
reqStream.Write(buffer, 0, buffer.Length);
}
}
return arquivos.Count;
}
public void SubirArquivo(string arquivoLocal, string pastaFtp, string nomeArquivoRemoto)
{
var arquivoFtp = Host + pastaFtp + nomeArquivoRemoto;
var _arquivoLocal = arquivoLocal;
_ftp = (FtpWebRequest)WebRequest.Create(arquivoFtp);
IniciarMetodo();
_ftp.Method = WebRequestMethods.Ftp.UploadFile;
_ftp.Timeout = 600000;
byte[] buffer;
using (var stream = File.OpenRead(_arquivoLocal))
{
buffer = new byte[stream.Length];
stream.Read(buffer, 0, buffer.Length);
}
using (var reqStream = _ftp.GetRequestStream())
{
reqStream.Write(buffer, 0, buffer.Length);
}
}
public FtpWebResponse CriaDiretorio(string pastaFtp)
{
var arquivoFtp = Host + "/" + pastaFtp;
_ftp = (FtpWebRequest) WebRequest.Create(arquivoFtp);
IniciarMetodo();
_ftp.Method = WebRequestMethods.Ftp.MakeDirectory;
using (var response = (FtpWebResponse) _ftp.GetResponse())
{
return response;
}
}
public FtpWebResponse ApagaArquivo(string pastaFtp, string arquivoRemoto)
{
var arquivoFtp = Host + "/" + pastaFtp + "/" + arquivoRemoto;
_ftp = (FtpWebRequest) WebRequest.Create(arquivoFtp);
IniciarMetodo();
_ftp.Method = WebRequestMethods.Ftp.DeleteFile;
using (var response = (FtpWebResponse) _ftp.GetResponse())
{
return response;
}
}
public FtpWebResponse ApagaDiretorio(string pastaFtp, bool apagaArquivos)
{
pastaFtp = ("/" + pastaFtp + "/").Replace("//", "/");
if (apagaArquivos)
{
var filesList = ListarArquivos(pastaFtp, "*.*");
foreach (var file in filesList)
{
ApagaArquivo(pastaFtp, file);
}
}
var arquivoFtp = Host + pastaFtp;
_ftp = (FtpWebRequest)WebRequest.Create(arquivoFtp);
IniciarMetodo();
_ftp.Method = WebRequestMethods.Ftp.RemoveDirectory;
using (var response = (FtpWebResponse) _ftp.GetResponse())
{
return response;
}
}
private void IniciarMetodo()
{
//_ftp.Proxy = new WebProxy(Servidor.Ds_Proxy_Url, Servidor.Ds_Proxy_Porta) { Credentials = new NetworkCredential(Servidor.Ds_Proxy_Usuario, Servidor.Ds_Proxy_Senha, Servidor.Ds_Proxy_Dominio) };
_ftp.Proxy = null;
_ftp.Credentials = new NetworkCredential(Login, Senha);
_ftp.KeepAlive = false;
_ftp.UseBinary = true;
_ftp.UsePassive = true;
}
}
public class FTPArquivoListar
{
public SqlInt32 Nr_Linha;
public SqlString Nm_Arquivo;
public SqlString Nm_Diretorio;
public SqlString Fl_Tipo;
public SqlInt64 Qt_Tamanho;
public SqlDateTime Dt_Criacao;
public SqlString Ds_Permissao;
public SqlString Ds_Permissao_Octal;
public SqlString Ds_Proprietario;
public SqlString Ds_Grupo;
public FTPArquivoListar(SqlInt32 nrLinha, SqlString nmArquivo, SqlString nmDiretorio, SqlString flTipo, SqlInt64 qtTamanho, SqlDateTime dtCriacao, SqlString dsPermissao, SqlString dsPermissaoOctal, SqlString dsProprietario, SqlString dsGrupo)
{
Nr_Linha = nrLinha;
Nm_Arquivo = nmArquivo;
Nm_Diretorio = nmDiretorio;
Fl_Tipo = flTipo;
Qt_Tamanho = qtTamanho;
Dt_Criacao = dtCriacao;
Ds_Permissao = dsPermissao;
Ds_Permissao_Octal = dsPermissaoOctal;
Ds_Proprietario = dsProprietario;
Ds_Grupo = dsGrupo;
}
}
}
How to list files from an FTP using SQL Server
View more details, usage examples, and source code
Using the stored procedure below, it is now possible to list all files and directories present on an FTP server. This information will be very useful to identify whether a file was actually sent to the server or even to list the files that exist in a directory to download them later.
The filter parameter allows you to enter the name of the file you want to filter or use a Wildcard to return only the files that meet the specified criteria (Ex: “*” returns all files, “*.txt” returns all files ending with .txt, etc..)
using System.Data;
using System.Data.SqlTypes;
using System.Net;
using System.Threading;
using Microsoft.SqlServer.Server;
using Bibliotecas.Model;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void stpFTP_Arquivo_Listar(SqlString host, SqlString pastaFtp, SqlString filtro, SqlString login, SqlString senha)
{
const int tentativas = 10;
var sucesso = false;
for (var i = 1; i <= tentativas; i++)
{
try
{
var ftp = new FTPControle(host.Value, login.Value, senha.Value);
var arquivos = ftp.ListarArquivos(pastaFtp.Value, filtro.Value);
var pipe = SqlContext.Pipe;
var cols = new SqlMetaData[1];
cols[0] = new SqlMetaData("Nm_Arquivo", SqlDbType.NVarChar, 1024);
var rec = new SqlDataRecord(cols);
if (pipe == null) return;
pipe.SendResultsStart(rec);
foreach (var file in arquivos)
{
rec.SetSqlString(0, new SqlString(file));
pipe.SendResultsRow(rec);
}
pipe.SendResultsEnd();
sucesso = true;
}
catch (WebException e)
{
if (e.Status == WebExceptionStatus.Timeout || e.Status == WebExceptionStatus.ConnectFailure || e.Status == WebExceptionStatus.SendFailure || e.Status == WebExceptionStatus.ReceiveFailure || e.Status == WebExceptionStatus.PipelineFailure || e.Status == WebExceptionStatus.ConnectionClosed)
{
if (i < tentativas)
Retorno.Mensagem($"Erro na tentativa {i}: {e.Message}");
else
Retorno.Erro($"Erro : {e.Message}\n\nInner Exception: {e.InnerException}");
}
else
Retorno.Erro($"Erro : {e.Message}\n\nInner Exception: {e.InnerException}");
}
if (sucesso)
break;
Thread.Sleep(10000);
}
}
};
Function to list files from an FTP using SQL Server
View more details, usage examples, and source code
Although we have already demonstrated how to list files, this table-valued function allows you to identify a series of additional information about the files and directories on the FTP server, such as creation date, size, permissions, etc.
Furthermore, it can be easily used to filter with WHERE, use JOINS, ORDER BY, SELECT * INTO #Table, etc., giving much more flexibility in its use compared to the previous Stored Procedure.
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.Net;
using System.Threading;
using Bibliotecas.Model;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(
FillRowMethodName = "FillRow_FTP_Arquivos_Listar",
TableDefinition = "Nr_Linha INT, Fl_Tipo NVARCHAR(20), Nm_Diretorio NVARCHAR(500), Nm_Arquivo NVARCHAR(500), " +
"Qt_Tamanho BIGINT, Dt_Criacao DATETIME, Ds_Permissao NVARCHAR(20), Ds_Permissao_Octal NVARCHAR(4), " +
"Ds_Proprietario NVARCHAR(20), Ds_Grupo NVARCHAR(20)"
)]
public static IEnumerable fncFTP_Arquivos_Listar(string Ds_Hostname, string Ds_Usuario, string DsSenha, string Ds_Pasta_FTP, string Ds_Filtro)
{
var ftpArquivoListarCollection = new ArrayList();
if (string.IsNullOrEmpty(Ds_Hostname))
return ftpArquivoListarCollection;
var ftp = new FTPControle(Ds_Hostname, Ds_Usuario, DsSenha);
var arquivos = new List<string>();
const int tentativas = 10;
var sucesso = false;
for (var i = 1; i <= tentativas; i++)
{
try
{
arquivos = ftp.ListarArquivosCompleto(Ds_Pasta_FTP, Ds_Filtro);
sucesso = true;
}
catch (WebException e)
{
if (e.Status == WebExceptionStatus.Timeout || e.Status == WebExceptionStatus.ConnectFailure || e.Status == WebExceptionStatus.SendFailure || e.Status == WebExceptionStatus.ReceiveFailure || e.Status == WebExceptionStatus.PipelineFailure || e.Status == WebExceptionStatus.ConnectionClosed)
{
if (i < tentativas)
Retorno.Mensagem($"Erro na tentativa {i}: {e.Message}");
else
Retorno.Erro($"Erro : {e.Message}\n\nInner Exception: {e.InnerException}");
}
else
Retorno.Erro($"Erro : {e.Message}\n\nInner Exception: {e.InnerException}");
}
if (sucesso)
break;
Thread.Sleep(10000);
}
var contador = 1;
foreach (var arquivo in arquivos)
{
if (arquivo.Substring(0, 1) != "d") continue;
var retorno = ftp.ListarArquivosCompleto_Parse(arquivo, Ds_Pasta_FTP, contador);
if (retorno.Nr_Linha.Value == 0) continue;
ftpArquivoListarCollection.Add(retorno);
contador++;
}
foreach (var arquivo in arquivos)
{
if (arquivo.Substring(0, 1) == "d") continue;
var retorno = ftp.ListarArquivosCompleto_Parse(arquivo, Ds_Pasta_FTP, contador);
ftpArquivoListarCollection.Add(retorno);
contador++;
}
return ftpArquivoListarCollection;
}
protected static void FillRow_FTP_Arquivos_Listar(object objFTPArquivoListar, out SqlInt32 nrLinha, out SqlString nmArquivo, out SqlString nmDiretorio, out SqlString flTipo, out SqlInt64 qtTamanho, out SqlDateTime dtCriacao, out SqlString dsPermissao, out SqlString dsPermissaoOctal, out SqlString dsProprietario, out SqlString dsGrupo)
{
var ftpArquivoListar = (FTPArquivoListar) objFTPArquivoListar;
nrLinha = ftpArquivoListar.Nr_Linha;
nmArquivo = ftpArquivoListar.Nm_Arquivo;
nmDiretorio = ftpArquivoListar.Nm_Diretorio;
flTipo = ftpArquivoListar.Fl_Tipo;
qtTamanho = ftpArquivoListar.Qt_Tamanho;
dtCriacao = ftpArquivoListar.Dt_Criacao;
dsPermissao = ftpArquivoListar.Ds_Permissao;
dsPermissaoOctal = ftpArquivoListar.Ds_Permissao_Octal;
dsProprietario = ftpArquivoListar.Ds_Proprietario;
dsGrupo = ftpArquivoListar.Ds_Grupo;
}
}
How to download a file from an FTP server using SQL Server
View more details, usage examples, and source code
Using this Stored Procedure, you can easily download a specific file or multiple files using regular expressions, as I will demonstrate below.
An especially useful parameter is @apagarRemoto, which when the value “1” is entered, downloads the files that meet the filter criteria entered and then deletes these files from the FTP server.
Usage examples:
Downloading the "about.php" file
Downloading the “about.php” file
Downloading multiple files using LIKE
Downloading multiple files using Wildcard filters
Final result with files downloaded from FTP
Final result with files downloaded from FTP
Source code:
using System.Data.SqlTypes;
using System.IO;
using System.Net;
using System.Threading;
using Bibliotecas.Model;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void stpFTP_Arquivo_Download(SqlString host, SqlString pastaFtp, SqlString filtro, SqlString login, SqlString senha, SqlString pastaLocal, SqlBoolean apagarRemoto)
{
#region tratamento de entradas
try
{
if (!Directory.Exists(pastaLocal.Value))
{
Retorno.Erro("Pasta local especificada não existe ou inacessivel.");
return;
}
}
catch
{
Retorno.Erro("Erro ao converter pasta local especificada.");
return;
}
#endregion
const int tentativas = 10;
var sucesso = false;
for (var i = 1; i <= tentativas; i++)
{
try
{
var ftp = new FTPControle(host.Value, login.Value, senha.Value);
var arquivos = ftp.ListarArquivos(pastaFtp.Value, filtro.Value);
var qntArquivos = ftp.BaixarArquivos(pastaFtp.Value, arquivos, pastaLocal.Value);
if (apagarRemoto.Value)
{
ftp.ApagarArquivos(pastaFtp.Value, arquivos);
}
Retorno.Mensagem("Total de: (" + qntArquivos + ") arquivos foram baixados para: " + pastaLocal.Value);
sucesso = true;
}
catch (WebException e)
{
if (e.Status == WebExceptionStatus.Timeout || e.Status == WebExceptionStatus.ConnectFailure || e.Status == WebExceptionStatus.SendFailure || e.Status == WebExceptionStatus.ReceiveFailure || e.Status == WebExceptionStatus.PipelineFailure || e.Status == WebExceptionStatus.ConnectionClosed)
{
if (i < tentativas)
Retorno.Mensagem($"Erro na tentativa {i}: {e.Message}");
else
Retorno.Erro($"Erro : {e.Message}\n\nInner Exception: {e.InnerException}");
}
else
Retorno.Erro($"Erro : {e.Message}\n\nInner Exception: {e.InnerException}");
}
if (sucesso)
break;
Thread.Sleep(10000);
}
}
};
How to send a file to an FTP server using SQL Server
View more details, usage examples, and source code
Using this Stored Procedure, you can upload files from your local server or files that are on network paths to an FTP server.
Again, SP allows you to specify the file name in the @filtro parameter to send a specific file, or use wildcards to perform filters on file names and send multiple files in a single execution of the Stored Procedure.
Usage example:
Uploading all files with ".txt" extension to FTP server
Uploading all files with “.txt” extension to FTP server
Source code:
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.IO;
using System.Net;
using System.Threading;
using Bibliotecas.Model;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void stpFTP_Arquivo_Upload(SqlString host, SqlString pastaFtp, SqlString login, SqlString senha, SqlString pastaLocal, SqlString filtro)
{
#region tratamento de entradas
try
{
if (!Directory.Exists(pastaLocal.Value))
{
Retorno.Erro("Pasta local especificada não existe ou inacessivel.");
return;
}
}
catch
{
Retorno.Erro("Erro ao converter pasta local especificada.");
return;
}
List<string> listaArquivos;
try
{
var arquivos = Directory.GetFiles(pastaLocal.Value, filtro.Value);
listaArquivos = new List<string>(arquivos);
if (listaArquivos.Count == 0)
{
Retorno.Mensagem("(0) arquivos atendem o requisito de filtragem na pasta especificada.");
return;
}
}
catch
{
Retorno.Erro("Erro ao listar arquivos da pasta especificada.");
return;
}
#endregion
const int tentativas = 10;
var sucesso = false;
for (var i = 1; i <= tentativas; i++)
{
try
{
var ftp = new FTPControle(host.Value, login.Value, senha.Value);
var qntArquivos = ftp.SubirArquivos(listaArquivos, pastaFtp.Value);
if (qntArquivos != -1)
{
Retorno.Mensagem("Total de: (" + qntArquivos + ") arquivos foram enviados para: " + pastaFtp.Value);
sucesso = true;
}
else
Retorno.Erro("Erro : Diretório do arquivo local inválido!");
}
catch (WebException e)
{
if (e.Status == WebExceptionStatus.Timeout || e.Status == WebExceptionStatus.ConnectFailure || e.Status == WebExceptionStatus.SendFailure || e.Status == WebExceptionStatus.ReceiveFailure || e.Status == WebExceptionStatus.PipelineFailure || e.Status == WebExceptionStatus.ConnectionClosed)
{
if (i < tentativas)
Retorno.Mensagem($"Erro na tentativa {i}: {e.Message}");
else
Retorno.Erro($"Erro : {e.Message}\n\nInner Exception: {e.InnerException}");
}
else
Retorno.Erro($"Erro : {e.Message}\n\nInner Exception: {e.InnerException}");
}
if (sucesso)
break;
Thread.Sleep(10000);
}
}
}
How to create a directory on an FTP server using SQL Server
View more details, usage examples, and source code
This Stored Procedure is intended to allow the creation of directories on an FTP server. This is very important, because if you need to upload files to a new directory, entering that directory when uploading, without having created it first, will cause an error message.
Using the functions to list files and directories, it is possible to identify whether the desired directory exists or not, and if it does not, create it using this SP.
using System.Data.SqlTypes;
using Bibliotecas.Model;
using System.Net;
using System.Threading;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void stpFTP_Cria_Diretorio(SqlString host, SqlString login, SqlString senha, SqlString pastaFtp)
{
const int tentativas = 10;
var sucesso = false;
for (var i = 1; i <= tentativas; i++)
{
try
{
var ftp = new FTPControle(host.Value, login.Value, senha.Value);
using (var response = ftp.CriaDiretorio(pastaFtp.Value))
{
if (response.StatusCode == FtpStatusCode.PathnameCreated)
{
Retorno.Mensagem("Diretório " + pastaFtp.Value + " criado com sucesso");
sucesso = true;
}
else
{
Retorno.Erro(response.StatusDescription);
}
}
}
catch (WebException e)
{
if (e.Status == WebExceptionStatus.Timeout || e.Status == WebExceptionStatus.ConnectFailure || e.Status == WebExceptionStatus.SendFailure || e.Status == WebExceptionStatus.ReceiveFailure || e.Status == WebExceptionStatus.PipelineFailure || e.Status == WebExceptionStatus.ConnectionClosed)
{
if (i < tentativas)
Retorno.Mensagem($"Erro na tentativa {i}: {e.Message}");
else
Retorno.Erro($"Erro : {e.Message}\n\nInner Exception: {e.InnerException}");
}
else
Retorno.Erro($"Erro : {e.Message}\n\nInner Exception: {e.InnerException}");
}
if (sucesso)
break;
Thread.Sleep(10000);
}
}
};
How to delete a file from an FTP server using SQL Server
View more details, usage examples, and source code
Stored Procedure that allows you to delete one or more files from an FTP directory. Unlike previous SP’s, it is not possible to use a filter to delete multiple files. The @arquivoRemoto parameter must receive the name of the file that will be deleted. If you want to delete several files, use a WHILE to iterate between the files and call SP several times.
This is not due to technical impossibility, I just found it more “safe” not to allow deleting multiple files using a filter, but nothing that prevents you from changing this Procedure to allow this or using the Stored Procedure stpFTP_Apaga_Diretorio, which is just below.
using System.Data.SqlTypes;
using Bibliotecas.Model;
using System.Net;
using System.Threading;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void stpFTP_Apaga_Arquivo(SqlString host, SqlString login, SqlString senha, SqlString pastaFtp, SqlString arquivoRemoto)
{
const int tentativas = 10;
var sucesso = false;
for (var i = 1; i <= tentativas; i++)
{
try
{
var ftp = new FTPControle(host.Value, login.Value, senha.Value);
using (var response = ftp.ApagaArquivo(pastaFtp.Value, arquivoRemoto.Value))
{
if (response.StatusCode == FtpStatusCode.CommandOK || response.StatusCode == FtpStatusCode.FileActionOK)
{
Retorno.Mensagem("Arquivo " + pastaFtp.Value + "/" + arquivoRemoto.Value + " apagado com sucesso");
sucesso = true;
}
else
{
Retorno.Erro(response.StatusDescription);
}
}
}
catch (WebException e)
{
if (e.Status == WebExceptionStatus.Timeout || e.Status == WebExceptionStatus.ConnectFailure || e.Status == WebExceptionStatus.SendFailure || e.Status == WebExceptionStatus.ReceiveFailure || e.Status == WebExceptionStatus.PipelineFailure || e.Status == WebExceptionStatus.ConnectionClosed)
{
if (i < tentativas)
Retorno.Mensagem($"Erro na tentativa {i}: {e.Message}");
else
Retorno.Erro($"Erro : {e.Message}\n\nInner Exception: {e.InnerException}");
}
else
Retorno.Erro($"Erro : {e.Message}\n\nInner Exception: {e.InnerException}");
}
if (sucesso)
break;
Thread.Sleep(10000);
}
}
};
How to delete a directory from an FTP server using SQL Server
View more details, usage examples, and source code
Using this Stored Procedure, you can delete directories from your FTP server. One detail about this SP is the @apagaArquivos parameter, which if you enter the value 1, will delete all files in that directory and then try to delete the directory.
Keep in mind that if you enter the value of the @apagarArquivos parameter = 0 and there are files in the directory you are trying to delete, SP will return an error message informing you that there are files in the directory.
using System.Data.SqlTypes;
using Bibliotecas.Model;
using System.Net;
using System.Threading;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void stpFTP_Apaga_Diretorio(SqlString host, SqlString login, SqlString senha, SqlString pastaFtp, SqlBoolean apagaArquivos)
{
const int tentativas = 10;
var sucesso = false;
for (var i = 1; i <= tentativas; i++)
{
try
{
var ftp = new FTPControle(host.Value, login.Value, senha.Value);
using (var response = ftp.ApagaDiretorio(pastaFtp.Value, apagaArquivos.Value))
{
if (response.StatusCode == FtpStatusCode.FileActionOK)
{
Retorno.Mensagem("Diretório " + pastaFtp.Value + " apagado com sucesso");
sucesso = true;
}
else
{
Retorno.Erro(response.StatusDescription);
}
}
}
catch (WebException e)
{
if (e.Status == WebExceptionStatus.Timeout || e.Status == WebExceptionStatus.ConnectFailure || e.Status == WebExceptionStatus.SendFailure || e.Status == WebExceptionStatus.ReceiveFailure || e.Status == WebExceptionStatus.PipelineFailure || e.Status == WebExceptionStatus.ConnectionClosed)
{
if (i < tentativas)
Retorno.Mensagem($"Erro na tentativa {i}: {e.Message}");
else
Retorno.Erro($"Erro : {e.Message}\n\nInner Exception: {e.InnerException}");
}
else
Retorno.Erro($"Erro : {e.Message}\n\nInner Exception: {e.InnerException}");
}
if (sucesso)
break;
Thread.Sleep(10000);
}
}
};
That's it, folks!
I hope you enjoyed this post and see you later!
sql server clr c# csharp download download read read send upload upload files files list list integrate integrate integration integration server ftp server
sql server clr c# csharp download download read read send upload upload files files list list integrate integrate integration integration server ftp server
Dirceu Resende
Database & BI Architect · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.
Comentários (0)
Carregando comentários…