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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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
|
1 2 3 4 5 6 7 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
-- 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
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 @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.
Boa noite, Dirceu! como eu faço pra criar essa procedure “dbo.stpExporta_Tabela_HTML_Output”?
Você tem o projeto disponível para download Dirceu?
Olá Vinci_sp! Não tenho o projeto, porque eu agrupo todos os meus códigos CLR em um projeto só. Quando eu conseguir um tempo, eu vou compilar 1 projeto para cada post e disponibilizar a solução e a DLL gerada.
Abraço!
Boa tarde, Dirceu!
A função fncExporta_Query_HTML, você criou como?
Bruno, essa função é criada no visual studio, e utilizada no sql server, num recurso chamado CLR.
Para saber mais sobre isso, dê uma lida no Post https://www.dirceuresende.com/blog/introducao-sql-clr-common-language-runtime-sql-server/