Hey guys,
Good afternoon!
Are you all right?
In this post I will demonstrate how to send emails through the database using the CLR (C#). In the post SQL Server – How to activate and configure Database mail to send and monitor emails through the database (sp_send_dbmail) I had already demonstrated how to send emails via SQL Server Database Mail, and this time I will show you how to do it directly via C#, without even needing to configure anything in SQL Server to send the email.
Simple email sending
In this first example, I will demonstrate the simplest way to send an email in HTML format, with attachments, through the CLR using the C# programming language. See how easy it is:
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 = "seuemail@yahoo.com.br";
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);
}
}
}
}
More complete email sending
In this second source code, I will demonstrate a more complete and organized way of creating a Stored Procedure for sending emails in the CLR. To do this, I will separate the authentication information into separate classes and add the following additional features:
- Validation of input parameters
- Email sending log recording for audit
- RETRY routine, to try to send the email up to 10 times in case of failure
- Try..Catch for error and exception handling
Prerequisites
Before demonstrating the source code for the Stored Procedure for sending emails, you will need to create the dependent classes below, in addition to the Return class, which I use to send error messages to the database. You can find the code for this class in the post SQL Server – How to send warnings and error messages to the database using the CLR (C#).
Server.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 => "seumail@yahoo.com.br";
public static int Nr_Timeout_SMTP => 60000;
public static string Ds_Usuario_SMTP => "seumail@yahoo.com.br";
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;
}
}
}
}
Stored Procedure stpEnvia_Email source code:
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);
}
}
Usage examples
See below some examples of how to use this Stored Procedure in your daily life.
Simple email sending:
EXEC CLR.dbo.stpEnvia_Email
@destinatarios = N'dirceu.resende@yahoo.com.br', -- nvarchar(max)
@assunto = N'Assunto', -- nvarchar(max)
@mensagem = N'Teste de Mensagem', -- nvarchar(max)
@arquivos = N'' -- nvarchar(max)
Sending email to multiple recipients:
EXEC CLR.dbo.stpEnvia_Email
@destinatarios = N'dirceu.resende@yahoo.com.br; dirceu@dirceuresende.com', -- nvarchar(max)
@assunto = N'Teste', -- nvarchar(max)
@mensagem = N'Teste de Mensagem', -- nvarchar(max)
@arquivos = N'' -- nvarchar(max)
Sending an email with an attachment:
EXEC CLR.dbo.stpEnvia_Email
@destinatarios = N'dirceu.resende@yahoo.com.br', -- nvarchar(max)
@assunto = N'Assunto', -- nvarchar(max)
@mensagem = N'Teste de Mensagem', -- nvarchar(max)
@arquivos = N'C:\Windows\iis.log' -- nvarchar(max)
Sending an email with multiple attachments:
EXEC CLR.dbo.stpEnvia_Email
@destinatarios = N'dirceu.resende@yahoo.com.br', -- 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)
I hope you enjoyed the post and see you next time.
Hug!
Comentários (0)
Carregando comentários…