¡Hola, chicos!
Buen día.
En esta publicación, demostraré cómo exportar el resultado de una consulta a una cadena en formato HTML, para que pueda enviar el resultado de la consulta por correo electrónico de una manera visualmente agradable. Ya había hecho algo similar en el post. Cómo exportar datos de una tabla de SQL Server a HTML, pero en esta publicación traeré una solución aún más completa, personalizable y práctica, utilizando CLR.
Terminé eligiendo usar CLR para esta solución porque prefiero trabajar con una función en lugar de un procedimiento almacenado cuando necesito formatear y convertir cadenas, precisamente porque puedo usarlo en SELECCIONAR, ACTUALIZAR, etc. más fácilmente. Como necesito usar una consulta dinámica para esto, no es posible hacerlo con una función escalar en Transact-SQL, mientras que en una función escalar CLR esto es posible.
Requisitos previos para utilizar la función
Clase Utils.cs
En esta clase, agregaré algunas funciones genéricas, que uso en varias otras rutinas SP y CLR y, por lo tanto, se pueden reutilizar.
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;
}
}
}Clase Server.cs
En esta clase, uso configuraciones para almacenar cadenas de conexión y métodos generales para identificar el servidor al que estoy conectado actualmente usando 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;
}
}
}
}Cómo exportar el resultado de una consulta a HTML
Ahora que se han cumplido los requisitos previos, pasemos al código fuente de la clase principal, que es el motivo de esta publicación.
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;
}
}
Lo bueno de esta función es que el estilo del HTML generado se puede personalizar a través de una tabla en la base de datos, que es dbo.HTML_Layout_CSS. Puede crear varios formatos utilizando reglas CSS y utilizarlas en esta función para generar correos electrónicos personalizados. Si la tabla no tiene ningún registro, la función ya aplica un estilo predeterminado (pero al menos necesita crear esta tabla en la base de datos que usará su CLR).
A continuación se muestra el script de creación de tablas y algunos ejemplos de cómo personalizar los 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 uso de funciones
– Ds_Query: Consulta que se utilizará para consultar los resultados que se exportarán a HTML
– Ds_Titulo: Cadena que se utilizará como título de la tabla HTML generada. Si no desea usarlo, simplemente ingrese la cadena vacía
– estilo_fl: Número que indica el Id_Layout de la tabla dbo.HTML_Layout_CSS que se utilizará para formatear el HTML generado
– Fl_Html_Complete: Bandera booleana (0 o 1) que indica si se generará el HTML completo (html, cuerpo, etc.) o solo el HTML de la tabla.
Ejemplos de uso
Uso simple: simplemente generando el 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
Escribir HTML generado en el 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
* El código de stpWrite_File lo puedes encontrar en la publicación SQL Server: cómo enumerar, leer, escribir, copiar, eliminar y mover archivos con CLR (C#).
Escribir el HTML de dos consultas en el 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
Guardar en disco el HTML de tres consultas, sin título, usando otro estilo y enviarlo por correo electrónico
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'
Si no has configurado el Database Mail de tu instancia o necesitas ayuda para configurarlo, mira más accediendo al post SQL Server – Cómo activar y configurar el correo de base de datos para enviar y monitorear correos electrónicos a través de la base de datos (sp_send_dbmail).
Espero que os haya gustado este post y hasta el próximo.
Comentários (0)
Carregando comentários…