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/