Olá pessoal,
Boa tarde!
Tudo bem com vocês ?
Neste post vou demonstrar como enviar e-mails pelo banco de dados utilizando o CLR (C#). No post SQL Server – Como ativar e configurar o Database mail para enviar e monitorar e-mails pelo banco de dados (sp_send_dbmail) eu já havia demonstrado como enviar os e-mails pelo Database Mail do SQL Server, e desta vez vou mostrar como fazer isso diretamente pelo C#, sem precisar nem configurar nada no SQL Server para fazer o envio do e-mail.
Envio de e-mail simples
Neste primeiro exemplo, vou demonstrar a forma mais simples de se enviar um e-mail no formato HTML, com anexos, pelo CLR utilizando a linguagem de programação C#. Vejam como é fácil:
using System.Data.SqlTypes;
using System.Net;
using System.Net.Mail;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void stpEnvia_Email(SqlString destinatarios, SqlString assunto, SqlString mensagem, SqlString arquivos)
{
const string smtpEndereco = "smtp.mail.yahoo.com";
const int smtpPorta = 587;
const int smtpTimeout = 60000; // 60 segundos
const bool smtpUsaCredenciaisPadrao = false;
const bool smtpUsaSsl = true;
const string smtpUsuario = "[email protected]";
const string smtpSenha = "senha";
using (var clienteSmtp = new SmtpClient(smtpEndereco, smtpPorta) {DeliveryMethod = SmtpDeliveryMethod.Network, Timeout = smtpTimeout, UseDefaultCredentials = smtpUsaCredenciaisPadrao, EnableSsl = smtpUsaSsl })
{
if (!string.IsNullOrEmpty(smtpUsuario))
clienteSmtp.Credentials = new NetworkCredential(smtpUsuario, smtpSenha);
using (var eMail = new MailMessage())
{
var emailOrigem = new MailAddress(smtpUsuario);
eMail.From = emailOrigem;
foreach (var destinatario in destinatarios.Value.Split(';'))
{
if (!string.IsNullOrEmpty(destinatario))
eMail.To.Add(destinatario);
}
foreach (var arquivo in arquivos.Value.Split(';'))
{
if (!string.IsNullOrEmpty(arquivo))
eMail.Attachments.Add(new Attachment(arquivo));
}
eMail.Subject = assunto.Value;
eMail.IsBodyHtml = true;
eMail.Body = (string.IsNullOrEmpty(mensagem.Value)) ? "" : mensagem.Value;
clienteSmtp.Send(eMail);
}
}
}
}
Envio de e-mail mais completo
Já neste segundo código-fonte, vou demonstrar uma forma mais completa e organizada de se criar uma Stored Procedure de envio de e-mail no CLR. Para isso, vou separar as informações de autenticação em classes separadas e adicionar os seguintes recursos adicionais:
- Validação de parâmetros de entrada (input)
- Gravação de log de envio de e-mail para auditoria
- Rotina de RETRY, para tentar enviar o e-mail até 10x em caso de falha
- Try..Catch para tratamento de erros e exceções
Pré-Requisitos
Antes de demonstrar o código-fonte da Stored Procedure de envio de e-mail, você irá precisar criar as classes dependentes abaixo, além da classe Retorno, que utilizo para enviar mensagens de erro para o banco de dados. O código dessa classe você entra no post SQL Server – Como enviar avisos e mensagens de erro para o banco pelo CLR (C#).
Servidor.cs
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
namespace Bibliotecas.Model
{
public class ServidorAtual
{
public string NomeServidor { get; set; }
public ServidorAtual()
{
try
{
using (var conn = new SqlConnection(Servidor.Context))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT @@SERVERNAME AS InstanceName";
NomeServidor = (string) cmd.ExecuteScalar();
}
var partes = NomeServidor.Split('\\');
if (partes.Length <= 1) return;
if (string.Equals(partes[0], partes[1], StringComparison.CurrentCultureIgnoreCase))
NomeServidor = partes[0];
}
}
catch (Exception ex)
{
throw ex;
}
}
public static string[] UsuarioAtual()
{
try
{
using (var conn = new SqlConnection(Servidor.Context))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT suser_name() AS usuario, host_name() AS maquina, APP_NAME() AS programa";
using (var dr = cmd.ExecuteReader())
{
dr.Read();
var item = new string[3];
item.SetValue(dr["usuario"].ToString(), 0);
item.SetValue(dr["maquina"].ToString(), 1);
item.SetValue(dr["programa"].ToString(), 2);
return item;
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
}
public static class Servidor
{
public static string Ds_Servidor_SMTP => "smtp.mail.yahoo.com";
public static int Nr_Porta_SMTP => 587;
public static bool Fl_Conexao_SSL => true;
public static bool Fl_Credencial_Padrao_SMTP => false;
public static string Ds_Remetente_SMTP => "[email protected]";
public static int Nr_Timeout_SMTP => 60000;
public static string Ds_Usuario_SMTP => "[email protected]";
public static string Ds_Senha_SMTP => "senha";
public static string Ds_Usuario => "Usuario_CLR_Banco";
public static string Ds_Senha => "Senha_CLR_Banco";
public static string Context => "context connection=true";
public static string Localhost => "data source=LOCALHOST;initial catalog=CLR;Application Name=SQLCLR;persist security info=False;Enlist=False;packet size=4096;user id='" + Ds_Usuario + "';password='" + Ds_Senha + "'";
public static string getLocalhost()
{
var servidorAtual = new ServidorAtual().NomeServidor;
return "data source=" + servidorAtual + ";initial catalog=CLR;Application Name=SQLCLR;persist security info=False;Enlist=False;packet size=4096;user id='" + Ds_Usuario + "';password='" + Ds_Senha + "'";
}
public static List<string> Servidores
{
get
{
var servidores = new List<string>
{
Localhost
};
return servidores;
}
}
}
}
Utils.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
namespace Bibliotecas.Model
{
public static class Utils
{
public static void verificaParametroVazio(object input, string nomeInput, bool permiteStringVazia = false)
{
var msgNull = $"O valor do parâmetro '@{nomeInput}' não pode ser NULL";
var msgVazio = $"O valor do parâmetro '@{nomeInput}' não pode ser uma string vazia";
if (input is SqlString)
{
var parametro = (SqlString) input;
if (parametro.IsNull)
Retorno.Erro(msgNull);
if (!permiteStringVazia && parametro.Value.Trim().Length == 0)
Retorno.Erro(msgVazio);
}
else if (input is SqlChars)
{
var parametro = (SqlChars) input;
if (parametro.IsNull)
Retorno.Erro(msgNull);
if (!permiteStringVazia && parametro.Value.Length == 0)
Retorno.Erro(msgVazio);
}
else if (input is SqlInt16)
{
var parametro = (SqlInt16) input;
if (parametro.IsNull)
Retorno.Erro(msgNull);
}
else if (input is SqlInt32)
{
var parametro = (SqlInt32) input;
if (parametro.IsNull)
Retorno.Erro(msgNull);
}
else if (input is SqlInt64)
{
var parametro = (SqlInt64) input;
if (parametro.IsNull)
Retorno.Erro(msgNull);
}
else if (input is SqlBoolean)
{
var parametro = (SqlBoolean) input;
if (parametro.IsNull)
Retorno.Erro(msgNull);
}
else if (input is SqlByte)
{
var parametro = (SqlByte)input;
if (parametro.IsNull)
Retorno.Erro(msgNull);
}
else if (input is SqlBinary)
{
var parametro = (SqlBinary) input;
if (parametro.IsNull)
Retorno.Erro(msgNull);
}
else if (input is SqlDateTime)
{
var parametro = (SqlDateTime) input;
if (parametro.IsNull)
Retorno.Erro(msgNull);
}
else if (input is SqlDecimal)
{
var parametro = (SqlDecimal) input;
if (parametro.IsNull)
Retorno.Erro(msgNull);
}
else if (input is SqlDouble)
{
var parametro = (SqlDouble)input;
if (parametro.IsNull)
Retorno.Erro(msgNull);
}
else if (input is SqlGuid)
{
var parametro = (SqlGuid) input;
if (parametro.IsNull)
Retorno.Erro(msgNull);
if (!permiteStringVazia && parametro.Value.ToString().Length == 0)
Retorno.Erro(msgVazio);
}
else if (input is SqlXml)
{
var parametro = (SqlXml) input;
if (parametro.IsNull)
Retorno.Erro(msgNull);
if (!permiteStringVazia && parametro.Value.Length == 0)
Retorno.Erro(msgVazio);
}
else if (input is SqlMoney)
{
var parametro = (SqlMoney) input;
if (parametro.IsNull)
Retorno.Erro(msgNull);
}
else if (input is SqlSingle)
{
var parametro = (SqlSingle) input;
if (parametro.IsNull)
Retorno.Erro(msgNull);
}
}
public static bool gravaLogEmail(SqlString destinatarios, SqlString assunto, SqlString mensagem, SqlString arquivos)
{
/*
CREATE TABLE CLR.dbo.Log_Email (
Id_Log BIGINT IDENTITY(1, 1) NOT NULL,
Dt_Log DATETIME DEFAULT GETDATE(),
Ds_Destinatario VARCHAR(MAX) NOT NULL,
Ds_Assunto VARCHAR(MAX) NULL,
Ds_Mensagem VARCHAR(MAX) NULL,
Ds_Arquivos VARCHAR(MAX) NULL,
Ds_Usuario VARCHAR(100) NULL
)
*/
try
{
using (var conexao = new SqlConnection(Servidor.getLocalhost()))
{
conexao.Open();
using (var comando = new SqlCommand("INSERT INTO dbo.Log_Email (Ds_Destinatario, Ds_Assunto, Ds_Mensagem, Ds_Arquivos, Ds_Usuario) VALUES (@Ds_Destinatario, @Ds_Assunto, @Ds_Mensagem, @Ds_Arquivos, @Ds_Usuario)", conexao))
{
var dadosUsuario = ServidorAtual.UsuarioAtual();
comando.Parameters.Add(new SqlParameter("@Ds_Destinatario", SqlDbType.VarChar, -1)).Value = destinatarios.Value;
comando.Parameters.Add(new SqlParameter("@Ds_Assunto", SqlDbType.VarChar, -1)).Value = assunto.Value;
comando.Parameters.Add(new SqlParameter("@Ds_Mensagem", SqlDbType.VarChar, -1)).Value = mensagem.Value;
comando.Parameters.Add(new SqlParameter("@Ds_Arquivos", SqlDbType.VarChar, -1)).Value = arquivos.Value;
comando.Parameters.Add(new SqlParameter("@Ds_Usuario", SqlDbType.VarChar, -1)).Value = dadosUsuario[0];
comando.ExecuteNonQuery();
return true;
}
}
}
catch (Exception e)
{
return false;
}
}
}
}
Código-fonte da Stored Procedure stpEnvia_Email:
using System.Data.SqlTypes;
using System.Linq;
using System.Net;
using System.Net.Mail;
using System.Threading;
using Bibliotecas.Model;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void stpEnvia_Email(SqlString destinatarios, SqlString assunto, SqlString mensagem, SqlString arquivos)
{
Utils.verificaParametroVazio(destinatarios, nameof(destinatarios));
Utils.verificaParametroVazio(assunto, nameof(assunto));
Utils.verificaParametroVazio(mensagem, nameof(mensagem));
Utils.verificaParametroVazio(arquivos, nameof(arquivos), true); // Aceita string vazia
using (var clienteSmtp = new SmtpClient(Servidor.Ds_Servidor_SMTP, Servidor.Nr_Porta_SMTP) {DeliveryMethod = SmtpDeliveryMethod.Network, Timeout = Servidor.Nr_Timeout_SMTP, UseDefaultCredentials = Servidor.Fl_Credencial_Padrao_SMTP, EnableSsl = Servidor.Fl_Conexao_SSL })
{
if (!string.IsNullOrEmpty(Servidor.Ds_Usuario_SMTP))
clienteSmtp.Credentials = new NetworkCredential(Servidor.Ds_Usuario_SMTP, Servidor.Ds_Senha_SMTP);
using (var eMail = new MailMessage())
{
const int tentativas = 10;
var sucesso = false;
for (var i = 1; i <= tentativas; i++)
{
try
{
var emailOrigem = new MailAddress(Servidor.Ds_Remetente_SMTP);
eMail.From = emailOrigem;
foreach (var destinatario in destinatarios.Value.Split(';').Where(destinatario => !string.IsNullOrEmpty(destinatario)))
eMail.To.Add(destinatario);
foreach (var arquivo in arquivos.Value.Split(';').Where(arquivo => !string.IsNullOrEmpty(arquivo)))
eMail.Attachments.Add(new Attachment(arquivo));
eMail.Subject = assunto.Value;
eMail.IsBodyHtml = true;
eMail.Body = (string.IsNullOrEmpty(mensagem.Value)) ? "" : mensagem.Value;
clienteSmtp.Send(eMail);
sucesso = true;
}
catch (SmtpException e)
{
if (e.StatusCode == SmtpStatusCode.ServiceClosingTransmissionChannel || e.StatusCode == SmtpStatusCode.TransactionFailed || e.StatusCode == SmtpStatusCode.GeneralFailure || e.StatusCode == SmtpStatusCode.ServiceNotAvailable)
{
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\nStatus Code:{e.StatusCode}\nInner Exception: {e.InnerException}");
}
if (sucesso)
break;
Thread.Sleep(10000);
}
}
}
Utils.gravaLogEmail(destinatarios, assunto, mensagem, arquivos);
}
}
Exemplos de uso
Veja abaixo, alguns exemplos de como utilizar essa Stored Procedure no seu dia a dia.
Envio de e-mail simples:
EXEC CLR.dbo.stpEnvia_Email
@destinatarios = N'[email protected]', -- nvarchar(max)
@assunto = N'Assunto', -- nvarchar(max)
@mensagem = N'Teste de Mensagem', -- nvarchar(max)
@arquivos = N'' -- nvarchar(max)
Envio de e-mail para múltiplos destinatários:
EXEC CLR.dbo.stpEnvia_Email
@destinatarios = N'[email protected]; [email protected]', -- nvarchar(max)
@assunto = N'Teste', -- nvarchar(max)
@mensagem = N'Teste de Mensagem', -- nvarchar(max)
@arquivos = N'' -- nvarchar(max)
Envio de e-mail com anexo:
EXEC CLR.dbo.stpEnvia_Email
@destinatarios = N'[email protected]', -- nvarchar(max)
@assunto = N'Assunto', -- nvarchar(max)
@mensagem = N'Teste de Mensagem', -- nvarchar(max)
@arquivos = N'C:\Windows\iis.log' -- nvarchar(max)
Envio de e-mail com múltiplos anexos:
EXEC CLR.dbo.stpEnvia_Email
@destinatarios = N'[email protected]', -- nvarchar(max)
@assunto = N'Assunto', -- nvarchar(max)
@mensagem = N'Teste de Mensagem', -- nvarchar(max)
@arquivos = N'C:\Windows\iis.log; C:\Windows\config.log; C:\Teste.txt' -- nvarchar(max)
Espero que tenham gostado do post e até a próxima.
Abraço!
Comentários (0)
Carregando comentários…