SQL Server - How to Email a Query Result in HTML Format Using CLR (C #)

Views: 2.265
Reading Time: 7 minutes

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 various other SP's and CLR routines and therefore can be reused.

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.

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

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:

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 HTML

Writing the generated HTML to disk

* 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

Writing HTML of three queries to disk, untitled, using another style and emailing

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.