Olá pessoal!
Bom dia.
Neste post vou demonstrar como exportar o resultado de uma query para uma string no formato HTML, de modo que você possa enviar o resultado da query por e-mail de uma forma que seja legal visualmente. Eu já havia feito algo parecido no post Como exportar dados de uma tabela do SQL Server para HTML, mas neste post vou trazer uma solução ainda mais completa, personalizável e prática, utilizando o CLR.
Eu acabei optando por utilizar o CLR para essa solução porque eu tenho preferência em trabalhar com função ao invés de Stored Procedure quando preciso fazer formatação e conversão de strings, justamente por poder utilizar em SELECT, UPDATE, etc com mais facilidade. Como preciso utilizar query dinâmica para isso, não dá pra fazer com uma scalar-function no Transact-SQL, enquanto numa CLR Scalar-function isso é possível.
Pré-requisitos para o uso da função
Classe Utils.cs
Nessa classe, vou adicionar algumas funções genéricas, que utilizo em várias outras SP’s e rotinas do CLR e portanto, podem ser reaproveitadas.
using System.Data;
using System.Data.SqlClient;
namespace Bibliotecas.Model
{
public static class Utils
{
public static bool QueryPerigosa(string dsQuery)
{
var query = dsQuery.ToUpper();
if (query.Contains("INSERT "))
return true;
if (query.Contains("INTO "))
return true;
if (query.Contains("DELETE "))
return true;
if (query.Contains("TRUNCATE "))
return true;
if (query.Contains("UPDATE "))
return true;
if (query.Contains("DROP "))
return true;
if (query.Contains("ALTER "))
return true;
if (query.Contains("CREATE "))
return true;
if (query.Contains("DBCC "))
return true;
if (query.Contains("EXEC "))
return true;
if (query.Contains("BACKUP "))
return true;
if (query.Contains("RESTORE "))
return true;
if (query.Contains("GRANT "))
return true;
if (query.Contains("REVOKE "))
return true;
if (query.Contains("DISABLE "))
return true;
if (query.Contains("sp_"))
return true;
return false;
}
public static DataTable ExecutaQueryRetornaDataTable(string dsServidor, string dsQuery)
{
using (var con = new SqlConnection(Servidor.Localhost.Replace("LOCALHOST", dsServidor)))
{
con.Open();
using (var cmd = new SqlCommand(dsQuery, con))
{
using (var sda = new SqlDataAdapter(cmd))
{
var dt = new DataTable();
sda.Fill(dt);
return dt;
}
}
}
}
public static string ExecutaQueryScalar(string dsServidor, string dsQuery)
{
string retorno;
using (var con = new SqlConnection(Servidor.Localhost.Replace("LOCALHOST", dsServidor)))
{
con.Open();
using (var cmd = new SqlCommand(dsQuery, con))
{
retorno = (cmd.ExecuteScalar() == null) ? "" : cmd.ExecuteScalar().ToString();
}
}
return retorno;
}
}
}Classe Servidor.cs
Nesta classe, utilizo configurações para armazenar as strings de conexão e métodos gerais para identificação do servidor em que estou atualmente conectado utilizando o CLR.
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 class Servidor
{
public static string Ds_Usuario => "usuario";
public static string Ds_Senha => "senha";
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;
}
}
}
}Como exportar o resultado de uma query para HTML
Agora que os pré-requisitos foram atendidos, vamos ao código-fonte da classe principal, que é o motivo desse post.
fncExporta_Query_HTML.cs
using System.Data;
using System.Data.SqlTypes;
using Bibliotecas.Model;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(
DataAccess = DataAccessKind.Read,
SystemDataAccess = SystemDataAccessKind.Read
)]
public static SqlString fncExporta_Query_HTML(SqlString Ds_Query, SqlString Ds_Titulo, SqlInt32 Fl_Estilo, SqlBoolean Fl_Html_Completo)
{
if (Ds_Query.IsNull)
return SqlString.Null;
var titulo = Ds_Titulo.IsNull ? "" : Ds_Titulo.Value;
if (Utils.QueryPerigosa(Ds_Query.Value))
return "Query perigosa";
var estilo = 1;
if (!Fl_Estilo.IsNull)
estilo = Fl_Estilo.Value;
var servidor = new ServidorAtual().NomeServidor;
using (var dados = Utils.ExecutaQueryRetornaDataTable(servidor, Ds_Query.Value))
{
var retorno = criaHtmlCabecalho(estilo, Fl_Html_Completo.Value);
retorno += @"
<table>";
if (titulo.Length > 0)
{
retorno += @"
<tbody>
<tr>
<th colspan='" + dados.Columns.Count + @"'>" + titulo + @"</th>
</tr>
<tr class='subtitulo'>";
for (var i = 0; i < dados.Columns.Count; i++)
{
retorno += @"
<td>" + dados.Columns[i].ColumnName + "</td>";
}
retorno += @"
</tr>";
}
else
{
retorno += @"
<thead>
<tr>";
for (var i = 0; i < dados.Columns.Count; i++)
{
retorno += @"
<th>" + dados.Columns[i].ColumnName + "</th>";
}
retorno += @"
</tr>
</thead>
<tbody>";
}
foreach (DataRow linha in dados.Rows)
{
retorno += @"
<tr>";
foreach (DataColumn coluna in dados.Columns)
{
retorno += @"
<td>" + linha[coluna.ColumnName] + "</td>";
}
retorno += @"
</tr>";
}
retorno += @"
</tbody>
</table>";
retorno += criaHtmlRodape(Fl_Html_Completo.Value);
return retorno;
}
}
private static string aplicaEstilo(int estilo)
{
var servidor = new ServidorAtual().NomeServidor;
var dsQuery = "SELECT Ds_CSS FROM dbo.HTML_Layout_CSS WHERE Id_Layout = " + estilo;
var html = Utils.ExecutaQueryScalar(servidor, dsQuery);
if (string.IsNullOrEmpty(html))
{
html = @"
table { padding:0; border-spacing: 0; border-collapse: collapse; }
thead { background: #00B050; border: 1px solid #ddd; }
th { padding: 10px; font-weight: bold; border: 1px solid #000; color: #fff; }
tr { padding: 0; }
td { padding: 5px; border: 1px solid #cacaca; margin:0; }";
}
return html;
}
private static string criaHtmlCabecalho(int estilo, bool Fl_Html_Completo)
{
var retorno = "";
if (Fl_Html_Completo)
{
retorno = @"<html>
<head>
<title>Titulo</title>";
}
retorno += @"
<style type='text/css'>";
retorno += aplicaEstilo(estilo);
retorno += @"
</style>";
if (Fl_Html_Completo)
{
retorno += @"
</head>
<body>";
}
return retorno;
}
private static string criaHtmlRodape(bool Fl_Html_Completo)
{
var retorno = "";
if (Fl_Html_Completo)
{
retorno += @"
</body>
</html>";
}
return retorno;
}
}
O legal dessa função, é que o estilo do HTML gerado é personalizável através de uma tabela no banco de dados, que é a dbo.HTML_Layout_CSS. Você pode criar várias formatações utilizando as regras CSS e utilizá-los nessa função para gerar e-mails personalizados. Caso a tabela não tenha nenhum registro, a função já aplica um estilo padrão (mas você precisa ao menos criar essa tabela no banco de dados que o seu CLR irá utilizar).
Segue script de criação da tabela e alguns exemplos de como personalizar os estilos:
CREATE TABLE dbo.HTML_Layout_CSS (
Id_Layout INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
Nm_Layout VARCHAR(100) NOT NULL UNIQUE,
Ds_CSS VARCHAR(MAX) NOT NULL
)
INSERT INTO dbo.HTML_Layout_CSS ( Nm_Layout, Ds_CSS )
SELECT 'Layout Fundo Preto Letra Branca', '
table { padding:0; border-spacing: 0; border-collapse: collapse; }
th { padding: 10px; font-weight: bold; border: 1px solid #cacaca; color: #fff; background: #000; }
tr { padding: 0; }
.subtitulo td { border: 1px solid #cacaca; color: #fff; background: #8c8989; }
td { padding: 5px; border: 1px solid #cacaca; margin:0; }'
INSERT INTO dbo.HTML_Layout_CSS ( Nm_Layout, Ds_CSS )
SELECT 'Layout Fundo Verde Letra Branca', '
table { padding:0; border-spacing: 0; border-collapse: collapse; }
th { padding: 10px; font-weight: bold; border: 1px solid #000; color: #fff; background: #67ca1c; }
tr { padding: 0; }
.subtitulo td { border: 1px solid #cacaca; color: #fff; background: #000000; }
td { padding: 5px; border: 1px solid #cacaca; margin:0; }'
Parâmetros de utilização da função
– Ds_Query: Query que será utilizada para consultar os resultados que serão exportados para HTML
– Ds_Titulo: String que será utilizada como título da tabela HTML gerada. Caso você não queira utilizá-lo, basta informar a string vazia
– Fl_Estilo: Número que indica o Id_Layout da tabela dbo.HTML_Layout_CSS que será utilizado para formatar o HTML gerado
– Fl_Html_Completo: Flag booleana (0 ou 1) que indica se será gerado o HTML completo (html, body, etc) ou apenas o HTML da tabela
Exemplos de uso
Uso simples – Apenas gerando o HTML
DECLARE
@HTML VARCHAR(MAX),
@Query VARCHAR(MAX) = 'SELECT * FROM msdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''VIEW'' ORDER BY TABLE_NAME'
SET @HTML = CLR.dbo.fncExporta_Query_HTML(@Query, 'Teste com Titulo', 1, 1)
PRINT @HTML
Gravando o HTML gerado no disco
DECLARE
@HTML VARCHAR(MAX),
@Query VARCHAR(MAX) = 'SELECT * FROM msdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''VIEW'' ORDER BY TABLE_NAME'
SET @HTML = CLR.dbo.fncExporta_Query_HTML(@Query, 'Teste com Titulo', 2, 1)
EXEC CLR.dbo.stpEscreve_Arquivo
@Ds_Texto = @HTML, -- nvarchar(max)
@Ds_Caminho = N'C:\Users\dirceu\Documents\Teste.html', -- nvarchar(max)
@Ds_Codificacao = N'UTF-8', -- nvarchar(max)
@Ds_Formato_Quebra_Linha = N'windows', -- nvarchar(max)
@Fl_Append = 0 -- bit
* O código da stpEscreve_Arquivo você pode encontrar no post SQL Server – Como listar, ler, escrever, copiar, excluir e mover arquivos com o CLR (C#).
Gravando o HTML de duas queries no disco
-- Executa a 1a query
DECLARE
@HTML VARCHAR(MAX),
@Query VARCHAR(MAX) = 'SELECT TOP 5 * FROM msdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''VIEW'' ORDER BY TABLE_NAME'
SET @HTML = CLR.dbo.fncExporta_Query_HTML(@Query, 'Views do MSDB', 2, 1)
-- Adiciona espaçamento
SET @HTML += '<br/><br/>'
-- Executa a 2a query
SET @Query = 'SELECT TOP 5 * FROM msdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'' ORDER BY TABLE_NAME'
SET @HTML += CLR.dbo.fncExporta_Query_HTML(@Query, 'Tabelas do MSDB', 2, 0) -- na 2a query não precisa do HTML completo
-- Grava a string HTML no disco
EXEC CLR.dbo.stpEscreve_Arquivo
@Ds_Texto = @HTML, -- nvarchar(max)
@Ds_Caminho = N'C:\Users\dirceu\Documents\Teste.html', -- nvarchar(max)
@Ds_Codificacao = N'UTF-8', -- nvarchar(max)
@Ds_Formato_Quebra_Linha = N'windows', -- nvarchar(max)
@Fl_Append = 0 -- bit
Gravando o HTML de três queries no disco, sem título, utilizando outro estilo e enviando por e-mail
DECLARE @Query VARCHAR(MAX)
-- Executa a 1a query
SET @Query = 'SELECT TOP 5 * FROM msdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''VIEW'' ORDER BY TABLE_NAME'
DECLARE @HTML1 VARCHAR(MAX) = CLR.dbo.fncExporta_Query_HTML(@Query, '', 1, 1)
-- Executa a 2a query
SET @Query = 'SELECT TOP 5 * FROM msdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'' ORDER BY TABLE_NAME'
DECLARE @HTML2 VARCHAR(MAX) = CLR.dbo.fncExporta_Query_HTML(@Query, '', 1, 0) -- na 2a query não precisa do HTML completo
-- Executa a 3a query
SET @Query = 'SELECT TOP 5 job_id, name, enabled, description, date_created FROM msdb.dbo.sysjobs ORDER BY name'
DECLARE @HTML3 VARCHAR(MAX) = CLR.dbo.fncExporta_Query_HTML(@Query, '', 1, 0)
-- Monta o HTML
DECLARE @HTML VARCHAR(MAX) = '
<strong>Views do MSDB</strong>' + @HTML1 + '
<br/><br/>
<strong>Tabelas do MSDB</strong>' + @HTML2 + '
<br/><br/>
<strong>Jobs do MSDB</strong>' + @HTML3
-- Grava a string HTML no disco
EXEC CLR.dbo.stpEscreve_Arquivo
@Ds_Texto = @HTML, -- nvarchar(max)
@Ds_Caminho = N'C:\Users\dirceu\Documents\Teste.html', -- nvarchar(max)
@Ds_Codificacao = N'UTF-8', -- nvarchar(max)
@Ds_Formato_Quebra_Linha = N'windows', -- nvarchar(max)
@Fl_Append = 0 -- bit
-- Envia o e-mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ProfileEnvioEmail', -- sysname
@recipients = '[email protected]', -- varchar(max)
@subject = N'Teste de e-mail', -- nvarchar(255)
@body = @HTML, -- nvarchar(max)
@body_format = 'HTML'
Caso você não tenha configurado o Database Mail da sua instância ou precise de ajuda para configurar, veja mais acessando o post SQL Server – Como ativar e configurar o Database mail para enviar e monitorar e-mails pelo banco de dados (sp_send_dbmail).
Espero que tenham gostado desse post e até o próximo.
Comentários (0)
Carregando comentários…