Thursday, 16 May 2013

Send emails with SQL Server using Html files

4 comments

Sending emails from MS SQL Server using the Database Mail is easy. Once the Database Mail is active and configured (see this blog for quick configuration) the emails can be send using the SQL script below:
USE msdb
GO
EXEC sp_send_dbmail @profile_name='EmailProfile',
@recipients='test@example.com',
@subject='Test message',
@body='This is a test message.'
In cases where there is a need to send emails in html format - creating the content inside of SQL script is not so convenient.
With the help of OPENROWSET function we can read the content of html file, which contains the properly formatted and tested html.

For example below is the html file that we can easily open in the browser and validate it displays correctly the way we want. It contains the html and body tags which wraps the content that will be inserted into the email’s body.

  
       

Test Email

This is a test message
Below is the SQL script which will send the emails by reading the designated html file. The script will remove the leading and ending html and body tags and assign the html content to the email’s body.
DECLARE @SmtpProfile VARCHAR( 50) = 'EmailProfile ' ;
DECLARE @html_file NVARCHAR( 100) = 'c:\test-email.html' ;
DECLARE @Subject NVARCHAR( 255) = 'Test Message' ;
DECLARE @Recipients VARCHAR( 255) = 'test@example.com' ;

DECLARE @Body NVARCHAR( MAX);
DECLARE @ParmDefinition nvarchar( MAX) = N'@Body NVARCHAR(MAX) OUTPUT' ;
DECLARE @sql NVARCHAR( MAX) = 'SET @Body = (SELECT REPLACE(REPLACE(f.BulkColumn, '''', ''''), '''', '''') FROM OPENROWSET ( BULK ''' + @html_file + ''', SINGLE_NCLOB ) f)';

EXEC sp_executesql @sql, @ParmDefinition, @Body OUTPUT;

EXEC msdb ..sp_send_dbmail @profile_name=@SmtpProfile,
                           @recipients=@Recipients,                                            
                           @subject=@Subject,
                           @body_format= 'HTML',
                           @body=@Body;

Please note that the OPENROWSET function in the example above takes SINGLE_NCLOB flag, which reads a file as nvarchar for Unicode support. For varchar use SINGLE_CLOB.

Pretty straightforward, isn’t it.
Read More...

Tuesday, 14 May 2013

Create Google Charts in Asp.Net MVC by generating JSON data on the SQL SERVER

3 comments

The purpose of this article is to share the implementation of Google Charts with data retrieved from SQL Server whereas JSON is created on the SQL Server.

Google chart tools provide a rapid way of creating charts from some data for the web developers. All charts are populated with data stored in a DataTable object. The DataTable object can be filled with data using JSON format.
There are several different ways of creating and populating a DataTable and they all have some advantages and disadvantages (more about it here).
The application I needed to add Google Charts to be Asp.Net MVC using nHibernate with MS SQL Server.
The idea is to create all required data on the SQL Server using stored procedures to gain performance benefits, minimize the efforts of creating JSON for each chart and realize the DRY principal.
Once the required SQL stored procedure is created we are going to convert the result table data into JSON and pass it into our DataTable constructor.

Let’s say we need to create the bar chart showing the number of items by location.
Our stored procedure will be like the one below, where we have to create the string of columns, populate the temp table “#chart_data” with data and call another stored procedure to convert the content of the temp table into JSON.
create procedure [dbo].[GetItemsByLocationAsJson]
as
begin
set nocount on;
/* Set your columns data here */
/* The result string should look like the one below */
/* "cols": [{"id":"","label":"Topping","type":"string"},{"id":"","label":"Slices","type":"number"}] */
declare @columns_list varchar(max) = '"cols":[' +
'{"id":"Alocation","label":"Location","type":"string"}' +
',{"id":"ItemCount","label":"Items Count","type":"number"}' +
']';

select * into #chart_data from ( 
 select 'Location 1' as Alocation, 5 as ItemCount
 union all
 select 'Location 2' as Alocation, 10 as ItemCount
 union all
 select 'Location 3' as Alocation, 15 as ItemCount
 union all
 select 'Location 4' as Alocation, 20 as ItemCount
 ) as t

exec dbo.ConvertTableToGoogleChartJson @columns_list;

drop table #chart_data;  
 
end
If we run this stored procedure we’ll get the following result:
{
  "cols":[{"id":"Alocation","label":"Location","type":"string"},{"id":"ItemCount","label":"Items Count","type":"number"}],
  "rows":[{"c":[{"v":"Location 1"},{"v":5}]} ,{"c":[{"v":"Location 2"},{"v":10}]} ,{"c":[{"v":"Location 3"},{"v":15}]} ,{"c":[{"v":"Location 4"},{"v":20}]}]
}
JSON data are generated by sorting the column names in ascending order.
Note that the order of columns and rows should be in sync, e.g. the generated JSON data in rows should related to the correct columns.
The order between columns and rows can be achieved by giving such names so they will be sorted correctly when populating the #chart_data table. In our example we name the location field as “Alocation” instead of “Location” to make it first. Thus we can ensure the data for “Alocation” and “ItemCount” will be generated in correct order.

Below is the code for the ConvertTableToGoogleChartJson stored procedure.
This generic procedure generates the appropriate JSON based on the data in the #chart_data table and pre-append the column list passed as a parameter. Most of the code based on the answer from StackOverflow (thanks to pyrospade) and use metaprogramming to boost the performance.
create procedure [dbo].[ConvertTableToGoogleChartJson] 
 @columns_list varchar(max)
as
begin
set nocount on;

    declare @columns table (
    id int identity primary key,
    name sysname,
    datatype sysname,
    is_number bit,
    is_date bit);

    insert into @columns(name, datatype, is_number, is_date)
 select columns.name, types.name,
     case when number_types.name is not NULL
    then 1 else 0
     end as is_number,
     case when date_types.name is not NULL
    then 1 else 0
     end as is_date
 from tempdb.sys.columns
 join tempdb.sys.types
  on (columns.system_type_id = types.system_type_id)
 left join (values ('int'), ('real'), ('numeric'),
       ('decimal'), ('bigint'), ('tinyint')) as number_types(name)
  on (types.name = number_types.name)
 left join (values ('date'), ('datetime'), ('datetime2'),
       ('smalldatetime'), ('time'), ('datetimeoffset')) as date_types(name)
  on (types.name = date_types.name)
 where object_id = OBJECT_ID('tempdb..#chart_data') and types.name != 'sysname';

 declare @row_list varchar(max) = STUFF((select '+'',''+''{"v":''+' + 
  case when is_number = 1 then 'COALESCE(LTRIM(' + QUOTENAME(name) + '),''null'')'
    when is_date = 1 then 'COALESCE(QUOTENAME(LTRIM(convert(varchar(max),' + QUOTENAME(name) + ', 126)),''"''),''null'')'
    else 'COALESCE(QUOTENAME(' + QUOTENAME(name) + ',''"''),''null'')'
    end + '+''}'''
  from @columns order by name  for xml path('')), 1, 5, '');

     create table #json_result (id int identity primary key, line varchar(max));

     declare @sql varchar(max) = REPLACE('insert into #json_result '  + 'select '',{"c":[''+{f}+'']}''' + 'from #chart_data', '{f}', @row_list);

     exec(@sql);

     update #json_result set line = STUFF(line, 1, 1, '') where id = 1;

     declare @row_json Varchar(max) = (select line + '' as 'data()' from #json_result for xml path(''));

     select ('{' + @columns_list + ',"rows":[' + @row_json + ']}') as Result;

     drop table #json_result;
end
The point here is that the ConvertTableToGoogleChartJson stored procedure can be reused for the other calls to speed up the development of google charts.

Here is the sample of the controller calling our stored procedure and returning the JSON result:
public ActionResult GetItemsByLocationData ()
{
    var jsonText = this.dbService.GetItemsByLocationData();
    return this.Content(jsonText, "application/json");
}
Our view can have the jQuery ajax call to pass the JSON into the DataTable constructor:
function drawPieChart() {            
    var jsonData = $.ajax({
        url: 'MyController/GetItemsByLocationData',
        dataType: 'json',
        async: false
        }).responseText;
            
    var data = new google.visualization.DataTable(jsonData);
            
    // Set chart options      
    var options = {title: 'Items by Location'};
    // Instantiate and draw our chart, passing in some options.      
    var chart = new google.visualization.PieChart(document.getElementById('pie_chart_div'));            
    chart.draw(data, options);
}

That’s it. Happy coding!
Read More...