Hello everybody!
Good day.
In this post I will demonstrate how to export the result of a query to a string in HTML format, so that you can email the query result in a way that is visually cool. I had already done something similar in the post How to export data from a SQL Server table to HTML, but in this post I will bring an even more complete, customizable and practical solution using the CLR.
I ended up choosing to use CLR for this solution because I prefer working with function over Stored Procedure when I need to format and convert strings, just because I can use SELECT, UPDATE, etc. more easily. As I need to use dynamic query for this, you can not do with a scalar-function in Transact-SQL, while in a Scalar-function CLR this is possible.
Prerequisites for using the function
Utils.cs Class
In this class, I will add some generic functions, which I use in several other SP's and CLR routines and, therefore, can be reused.
|
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; } } } |
Server.cs Class
In this class, I use configurations to store connection strings and general methods for identifying the server I'm currently connected to using the 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; } } } } |
How to export query result to HTML
Now that the prerequisites are met, let's go to the main class source code, which is the reason for this 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; } } |
The nice thing about this function is that the style of the generated HTML is customizable through a table in the database, which is dbo.HTML_Layout_CSS. You can create various formatting using CSS rules and use them in this function to generate custom emails. If the table has no records, the function already applies a default style (but you must at least create that table in the database your CLR will use).
Following is the table creation script and some examples of how to customize the styles:
|
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; }' |
Function Usage Parameters
- Ds_Query: Query that will be used to query the results that will be exported to HTML.
- Ds_Titulo: String that will be used as the title of the generated HTML table. If you don't want to use it, just enter the empty string
- Fl_Style: Number indicating the Id_Layout of the dbo.HTML_Layout_CSS table that will be used to format the generated HTML.
- Fl_Html_Completo: Boolean flag (0 or 1) that indicates whether to generate full HTML (html, body, etc.) or just table HTML
Examples of use
Simple use - just generating the 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 |
Writing the generated HTML to disk
|
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 |
* The code of stpWrite_File you can find in the post SQL Server - How to list, read, write, copy, delete and move files with CLR (C #).
Writing HTML of two queries to disk
|
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 |
Writing HTML of three queries to disk, untitled, using another style and emailing
|
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' |
If you haven't set up your instance's Database Mail or need help setting up, see more by visiting the post. SQL Server - How to enable and configure Database mail to send and monitor database emails (sp_send_dbmail).
I hope you enjoyed this post and see you next time.
Good night, Dirceu! how do I create this procedure “dbo.stpExporta_Tablea_HTML_Output”?
Do you have the project available for download Dirceu?
Hello Vinci_sp! I don't have the project because I bundle all my CLR codes into one project. When I get a break, I will compile 1 project for each post and make available the solution and the generated DLL.
Hug!
Good afternoon, Dirceu!
The fncExporta_Query_HTML function you created as?
Bruno, this function is created in visual studio, and used in sql server, in a feature called CLR.
To know more about this, read the Post https://www.dirceuresende.com/blog/introducao-sql-clr-common-language-runtime-sql-server/