Recurring Tasks Inside ASP.NET Applications Using HangFire

15. June 2014 22:46

ASP.NET ASP.NET MVC C# SQL Server Web 

This is open-source at its best. Running background task to work in context with ASP.NET was and is still a big deal for few developers. I user QueueUserWorkItem to schedule emails when a new comment is added on my blog. This makes sure that the UI is responsive and the user can close or navigate to other post. I have been working on enterprise applications for many years now and most of the long running tasks are running in the background i.e. windows services.

HangFire is not limited to ASP.NET applications, you can even use it in your console applications.

HangFire is an open-source project which allows us to run recurring tasks within the ASP.NET application. No need of scheduling tasks and windows services. Everything will be within the ASP.NET application. When a new comment is added on my blog, an email is sent to my inbox as a notification to moderate it. In a normal scenario it will take a bit more than a normal time to add a comment because an email is also being sent to my inbox. To overcome this problem, I queued the mail process in the background like so:

bool commentSave = _db.AddComment(comment);
if (commentSave)
{
    System.Threading.ThreadPool.QueueUserWorkItem(s=> BlogEmail.SendEmail(comment));
    return Json(new { message = "Thanks for your comment. The comment is now awaiting moderation" });
}
else
    return Json(new { message = "There is an error while saving comment. Please try again later" });

As soon as a comment is added, user will be prompted that comment is added in the DB but the process of sending the email is scheduled in the background. But this approach has a drawback. What if the email sending is failed? As the admin of my blog, will I be able to see the status of the process? HangFire resolves all these questions and it comes with an awesome HangFire monitor which displays the status of all the background tasks in real-time. I will discuss about the HangFire monitor later in this post, but first let's get started with HangFire.

Installing HangFire

HangFire is available on NuGet. Firing the below command will automatically add the references in your project and takes care of all the configuration.

Install-Package HangFire
Attempting to resolve dependency 'HangFire.SqlServer (= 0.9.1)'.
Attempting to resolve dependency 'HangFire.Core (= 0.9.1)'.
Attempting to resolve dependency 'Common.Logging (= 2.1.2)'.
Attempting to resolve dependency 'Newtonsoft.Json (= 5.0.0)'.
Attempting to resolve dependency 'ncrontab (= 1.0.0)'.
Attempting to resolve dependency 'Dapper (= 1.13)'.
Attempting to resolve dependency 'HangFire.Web (= 0.9.1)'.
Attempting to resolve dependency 'CronExpressionDescriptor (= 1.10.1)'.
Attempting to resolve dependency 'WebActivatorEx (= 2.0.1)'.
Attempting to resolve dependency 'Microsoft.Web.Infrastructure (= 1.0.0.0)'.
Installing 'Common.Logging 2.1.2'.
Successfully installed 'Common.Logging 2.1.2'.
Installing 'ncrontab 1.0.0'.
Successfully installed 'ncrontab 1.0.0'.
Installing 'HangFire.Core 0.9.1'.
Successfully installed 'HangFire.Core 0.9.1'.
Installing 'Dapper 1.13'.
Successfully installed 'Dapper 1.13'.
Installing 'HangFire.SqlServer 0.9.1'.
Successfully installed 'HangFire.SqlServer 0.9.1'.
Installing 'CronExpressionDescriptor 1.10.1'.
Successfully installed 'CronExpressionDescriptor 1.10.1'.
Installing 'WebActivatorEx 2.0.1'.
Successfully installed 'WebActivatorEx 2.0.1'.
Installing 'HangFire.Web 0.9.1'.
Successfully installed 'HangFire.Web 0.9.1'.
Installing 'HangFire 0.9.1'.
Successfully installed 'HangFire 0.9.1'.
Adding 'Common.Logging 2.1.2' to HangfireDemo.
Successfully added 'Common.Logging 2.1.2' to HangfireDemo.
Adding 'ncrontab 1.0.0' to HangfireDemo.
Successfully added 'ncrontab 1.0.0' to HangfireDemo.
Adding 'HangFire.Core 0.9.1' to HangfireDemo.
Successfully added 'HangFire.Core 0.9.1' to HangfireDemo.
Adding 'Dapper 1.13' to HangfireDemo.
Successfully added 'Dapper 1.13' to HangfireDemo.
Adding 'HangFire.SqlServer 0.9.1' to HangfireDemo.
Successfully added 'HangFire.SqlServer 0.9.1' to HangfireDemo.
Adding 'CronExpressionDescriptor 1.10.1' to HangfireDemo.
Successfully added 'CronExpressionDescriptor 1.10.1' to HangfireDemo.
Adding 'WebActivatorEx 2.0.1' to HangfireDemo.
Successfully added 'WebActivatorEx 2.0.1' to HangfireDemo.
Adding 'HangFire.Web 0.9.1' to HangfireDemo.
Successfully added 'HangFire.Web 0.9.1' to HangfireDemo.
Adding 'HangFire 0.9.1' to HangfireDemo.
Successfully added 'HangFire 0.9.1' to HangfireDemo.

I am using HangFire with ASP.NET MVC application. Here are the few things that you need to configure before you dive in. When installing HangFire via NuGet, it adds HangFireConfig.cs under App_Start folder. HangFire supports Redis, SQL Server, SQL Azure or MSMQ. I am using SQL Server in this demo. The reason we require this storage because it is being used by the HangFire monitor to display the real-time data of the jobs. To configure HangFire to use SQL Server, open HangFireConfig.cs file and change the connection string as per your SQL Server installation.

JobStorage.Current = new SqlServerStorage(
    @"Server=GHOST\SERVER; Database=Jobs;user id=sa; password=pass#w0rd1;");

When the application first starts, all required database objects are created. 

You can find the scripts inside the downloaded package HangFire.SqlServer.0.9.1\Tools\install.sql. The jobs and monitor will be using this database to show me the real-time status of the jobs running in the background. To view the HangFire monitor, simply navigate to http://<sitename>/hangfire.axd. As it is a handler, you can see it in your web.config file. Let's see it in action:

The navigation pane on the right, lets you see the jobs and their status. It let's you even see the queues which are currently running. 

Scheduling the Jobs

Scheduling jobs using HangFire is easier then I thought it would be. Talking about the same example from my blog which sends email in my inbox when a new comment is added. If I want to schedule the mail send process as a background job I can do it easily using the BackgroundJob class.

bool commentSave = _db.AddComment(comment);
if (commentSave)
{
    BackgroundJob.Enqueue(() => BlogEmail.SendEmail(comment));
    return Json(new { message = "Thanks for your comment. The comment is now awaiting moderation" });
}
else
    return Json(new { message = "There is an error while saving comment. Please try again later" });

As I require it to run only once I just have queue it using the BackgroundJob.Enqueue() method. I can also delay the execution of the job using the Schedule method of the BackgroundJob class.

bool commentSave = _db.AddComment(comment);
if (commentSave)
{
    BackgroundJob.Schedule(() => BlogEmail.SendEmail(comment), TimeSpan.FromMinutes(60));
    return Json(new { message = "Thanks for your comment. The comment is now awaiting moderation" });
}
else
    return Json(new { message = "There is an error while saving comment. Please try again later" });

What if the email sending is failed? The SendMail method throws an exception that the mail sending is failed. HangFire will handle this by default and it will retry automatically 3 more times after a consecutive delay after each retry. But if I want to retry it more than 3 times then I can make use of the AutomaticRetry attribute and pass the number of retries I want, something like this:

[AutomaticRetry(Attempts = 5)]
public bool SendEmail(Comment comment)
{
    //Email code
}

Let's say if I do have another method that I want to run every minute (it's an overkill for my blog) then I will make use of RecurringJob class.

RecurringJob.AddOrUpdate(() => Storage.PunchIt(), Cron.Minutely);

Cron enum allows me to schedule a job daily, weekly, monthly, yearly, hourly and minutely. Now as my job is schedule in the background, time to take a look at HangFire monitor.

I have no idea why my Recurring Jobs screen is showing Next and Last execution time as 44 years ago. But you can see the Succeeded Jobs with a minute interval (#5 and #4). HangFire uses persistent storage and therefore you can trigger the job at your will or remove it when you feel like it. That means you configure the job in the code and manage it from the HangFire monitor.

What else you can do with HangFire

I just showed you how easy it can be scheduling jobs using HangFire. But there are more advanced topics which you should be looking into for more complex implementation. HangFire supports logging, dependency injection using Ninject, multiple queue processing and more.

References

Currently rated 3.3 by 6 people

Using SQL CLR Stored Procedure To Track IP Address

24. April 2010 00:24

C# SQL Server T-SQL 

Recently I was asked by one of my friend how to call a web service directly from a stored procedure. He needs to track IP addresses using a stored procedures so, I suggested him two ways to call a web service using the SQL Procedure.

  • Using SP_OACREATE with MSXML
  • Create an assembly using SQL CLR using C#.

I personally tried using the first way to call a web service but was not successful. Then I learnt about SQL CLR programming with Visual Studio and C# and get it done in a first go. As a good friend I create the whole project for my friend. And if your thinking that this is going to be a difficult, this is not so.

To start creating a project, open Visual Studio and create a new project.

Before the project gets loaded completely, you will be asked to add database reference. Here select the database you want to build the assembly for. If you don’t want to add a database reference then just simply cancel. But if you choose the reference to add at this point then this would really be of help as this will allow you to deploy your assembly directly to the SQL Server database but if you have not then you need to write some SQL scripts.

NOTE: If you have added a database reference then it doesn’t mean that your assembly is build specifically for that database you can still deploy your assembly in other database also.

As I am creating a stored procedure, I then have to add a New Item to my project. Right-click project choose Add>Stored Procedure.  Here give a name of your choice to stored procedure. In my case it is IPInfo.

Change the static method here to accept a parameter. Our parameter will be an IP address, which I have set as of string type.

public static void GetIPInfo(string IP)
{  
//Do something here
}

Now the question here is from where we can the information of the IP Addresses? I do some search and found a website http://www.geobytes.com. It offers some rich information of the IP Address you want to track, so out of five sources I choose to go with geobytes.

Lets take a quick look at the code. This is not a web service as you can see from the code below, I am just making a http request and then get the response in an XML form so I can easily traverse the nodes and read/save the information I received.

string XMLResponse;
IPAddress = IP;
byte[] XMLResults;
string str = "http://www.geobytes.com/IpLocator.htm?GetLocation&template=xml.txt"
+ "&IpAddress=" + IP;
XMLResults = new WebClient().DownloadData(str.ToString());
XMLResponse = Encoding.UTF8.GetString(XMLResults);

Note the fourth line where I have built the URL. The parameter Template should always have a value to xml.txt. If you change the name, it won’t throw any error but the data received cannot be read and saved in a database table. Convert the response to string format and then read the nodes one by one using the below code.

try
        {
            XmlDocument document = new XmlDocument();
            document.LoadXml(XMLResponse);
            XmlNode documentElement = document.DocumentElement;
            if (documentElement.HasChildNodes)
            {
                for (int i = 0; i < documentElement.ChildNodes.Count; i++)
                {
                    if (documentElement.ChildNodes[i].NodeType == XmlNodeType.Element)
                    {
                        switch (documentElement.ChildNodes[i].Name)
                        {
                            case "locationcode":
                                LocationCode = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "fips":
                                FIPS104 = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "iso2":
                                ISO2 = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "iso3":
                                ISO3 = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "ison":
                                ISON = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "internet":
                                Internet = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "countryid":
                                CountryID = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "country":
                                Country = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "regionid":
                                RegionID = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "region":
                                Region = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "code":
                                RegionCode = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "adm":
                                Admn1Code = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "cityid":
                                CityID = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "city":
                                City = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "latitude":
                                Latitude = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "longitude":
                                Longitude = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "timezone":
                                TimeZone = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "certainty":
                                Certainty = documentElement.ChildNodes[i].InnerText;
                                break;
                        }
                    }
                }
            }
        }
        catch (Exception) { }

I traversed through each node and save the response data in a variable I have already declared in my program using a switch case. Once all the response has been assigned to the variable save the response to the database.

try
        {
            using (SqlConnection con = new SqlConnection("context connection=true"))
            {
                string strSQL = "INSERT INTO IPTrack values ('" + IPAddress + "','" + LocationCode + "','" + FIPS104 + "','" + ISO2 + "','" + ISO3 + "','" + ISON + "','" + Internet + "','" + CountryID + "','" + Country +
                    "','" + RegionID + "','" + Region + "','" + RegionCode + "','" + Admn1Code + "','" + CityID + "','" + City + "','" + Latitude + "','" + Longitude + "','" + TimeZone + "','" + Certainty + "')";
                SqlCommand cmd = new SqlCommand(strSQL, con);
                //SqlContext.Pipe.ExecuteAndSend(cmd);
                con.Open();
                SqlContext.Pipe.ExecuteAndSend(cmd);
                //cmd.ExecuteNonQuery();
            }
        }
        catch (Exception) { }
    }

Build you project and you will see a DLL file in your debug/release folder. But this will not work. You will still have to set some permission level to assembly and to SQL Server database.

Set Assembly Permissions

Before you build the final version of the assembly go the project properties and set the permissions as shown below.

Under Build select option for Generate Serialization Assembly to On.

Under Database select Permission Level to Unsafe

SQL Server Permissions

If you find problems while deploying your assembly, then make sure you have CLR enabled and set the Trustworthy property for the database to ON.

You can find all the scripts in the attached solution below.

Deploying your assembly

To deploy your assembly use the below SQL command:

CREATE ASSEMBLY IPTracker from 'C:\IPInfo.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

To create a stored procedure whcih in trun calls the SQL method from the deployed assembly:

CREATE PROC TrackIP(@IP as nvarchar(50))AS 
-- [Assembly Name].[Class Name].[CLR function Name]
EXTERNAL NAME IPTracker.StoredProcedures.GetIPInfo 
GO

And execute the procedure to get the IP information

Download: IPInfo.zip (19.46 kb)

Currently rated 5.0 by 1 person

Passing Parameters and Loading Crystal Report Programmatically

1. March 2010 11:55

C# SQL Server 

Reporting is an important part for every application. Crystal reports are widely used and also available in Visual Studio for reporting purposes. I personally never like designing application interfaces and working on web designs, it needs good designing skills which I lack and so I never put my hands on designing anything, but sometimes we have to. In crystal reports when you create a report using wizard or just adding a blank report to the project and then fetching data using code (after designing), the report works fine on the machine where you have designed and develop it and the problem occurs when you have to deploy or distribute your application with reports on multiple machines or clients. Of course, the connection string or you can say in simple words the server name, database name, user name and password is different than the name you used in your connection string while you test and make your application ready to be distributed. Therefore, to overcome this problem I wrote a class which will help me to achieve this in one line and also keep my code neat and clean. So this simple class will let you set the connection for your report dataset and let your reports work properly without any problems.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;


namespace ReportExportDemo
{
    class Reports
    {
        static TableLogOnInfo crTableLogonInfo;
        static ConnectionInfo crConnectionInfo;
        static Tables crTables;
        static Database crDatabase;

        public static void ReportLogin(ReportDocument crDoc, string Server, string Database, string UserID, string Password)
        {
            crConnectionInfo = new ConnectionInfo();
            crConnectionInfo.ServerName = Server;
            crConnectionInfo.DatabaseName = Database;
            crConnectionInfo.UserID = UserID;
            crConnectionInfo.Password = Password;
            crDatabase = crDoc.Database;
            crTables = crDatabase.Tables;
            foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in crTables)
            {
                crTableLogonInfo = crTable.LogOnInfo;
                crTableLogonInfo.ConnectionInfo = crConnectionInfo;
                crTable.ApplyLogOnInfo(crTableLogonInfo);
            }
        }

        public static void ReportLogin(ReportDocument crDoc, string Server, string Database)
        {
            crConnectionInfo = new ConnectionInfo();
            crConnectionInfo.ServerName = Server;
            crConnectionInfo.DatabaseName = Database;
            crConnectionInfo.IntegratedSecurity = true;
            crDatabase = crDoc.Database;
            crTables = crDatabase.Tables;
            foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in crTables)
            {
                crTableLogonInfo = crTable.LogOnInfo;
                crTableLogonInfo.ConnectionInfo = crConnectionInfo;
                crTable.ApplyLogOnInfo(crTableLogonInfo);
            }
        }
    }
}

This class contains ReportLogIn method which is overloaded which lets you choose the type of datasourde you want to connect to. The first method in the class requires SQL Server authentication to log-on to the server and the second method is used when the server is configured on windows authentication, similar to Integrated Security = true.

Sample usage of the Reports class is as follows. I have used AdventureWorks database for this example. If you dont have adventure works database then you can download the sample database from Microsoft's website or from codeplex or you can create your own report and check it with this example.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;


namespace ReportExportDemo
{
    public partial class frm_main : Form
    {
        public frm_main()
        {
            InitializeComponent();
        }

        private void btn_prvrpt_Click(object sender, EventArgs e)
        {
            int Cust_Id = Convert.ToInt32(txt_customerid.Text);
            //string Cust_Id = txt_customerid.Text.Trim();

            //Initialize report document object and load the report in the report document object
            ReportDocument crReportDocument = new ReportDocument();
            crReportDocument.Load(Application.StartupPath+"\\Reports\\AdventureCustReport.rpt");
            
            //login to the server to get details from the server and populate to the report           
            Reports.ReportLogin(crReportDocument, "MX\\SERVER", "AdventureWorks", "sa", "pass#w0rd1");
            
            //Pass parameter to the report object
            crReportDocument.SetParameterValue("id", Cust_Id);

            //To create PDF from the crystal report
            crReportDocument.ExportToDisk(ExportFormatType.PortableDocFormat, "CustomerReport" + Cust_Id + ".pdf");
            
            //Preview the generated PDF
            System.Diagnostics.Process.Start(Application.StartupPath + "\\CustomerReport" + Cust_Id + ".pdf");
        }
    }
}

Note the namespaces I have used in the above code and also how I have used the Reports class ReportLogin method. For more clarification of the code download the sample application with the report below.

One important thing that you have to keep in mind while designing report is to set the parameter. After you have set the parameter and you use your report with the above code, the report will then populate the whole lot of records in the table trrespective of the parameter you pass to the report. So to avoid this and to view only the record of your choice you needs to set the formula in the crystal report. the formula goes like this:

Sample Syntax:

{<Table's Column Name>}={?<Parameter Name>}

In my report this formula looks something like this:

{CustomerID}={?ID}

Download: ReportExportDemo.zip (55.76 kb)

Currently rated 3.5 by 2 people

SQL Server performance and NOCOUNT

25. February 2010 20:44

SQL Server T-SQL 

Performance of SQL server matters while working with large enterprise applications and where performance really bothers the business workflow. When NOCOUNT is ON, the count of rows by the execution of the query (T-SQL statements) is not returned. You must have noticed that when you perform operations by executing INSERT, UPDATE, DELETE or SELECT statements, the server returns the number of rows. The count of rows is necessary while debugging your queries, when you are done with debugging you can turn NOCOUNT ON. To view the number of rows affected use the command SET NOCOUNT OFF. You can try writing a normal select query with NOCOUNT as OFF, when NOCOUNT is OFF you can see the number of rows affected, but when you set NOCOUNT ON, you will not see any message related to the count of rows which are affected when you execute select statement or T-SQL statement.

 No Rating

Using OpenXML to insert data in SQL Server

19. February 2010 22:44

SQL Server T-SQL 

Microsoft SQL Server provides the mechanism to save XML data to the SQL table using OpenXML. We can use a stored procedure and pass a XML text as a string. It is useful when we have a large data in XML format and we want to save the whole data in different tables.

So first start declaring the variables. The first declare the document variable and a XML document variable as Varchar (8000) or Varchar(MAX) - It's a same thing

DECLARE @idoc int
DECLARE @doc varchar(8000)

Now we can set the value to the @doc variable, the @doc variable will accept the XML file as a parameter. We can also have a complete XML data in the parameter only if the XML data is less.

OpenXML takes 3 parameters:

  • The handler which we have declare at the begining will holds the XML document in the memory.
  • XPath variable to access the various elements of the XML document.
  • The last parameter here '2' allows us to access XPath as elements.

Here I have read a XML file in the document variable and store in the table. Here is the full SQL query to achieve this. You can also convert the below code to stored procedure and pass XML file or string as a parameter

DECLARE @idoc int
DECLARE @doc varchar(8000)
SET @doc ='<ROOT><student>
<id>1</id>
<name>Prashant</name>
<age>32</age>
</student>
<student>
<id>2</id>
<name>Swami</name>
<age>42</age>
</student>
<student>
<id>3</id>
<name>Ash</name>
<age>23</age>
</student>
<student>
<id>4</id>
<name>Kris</name>
<age>12</age>
</student>
<student>
<id>5</id>
<name>Derek</name>
<age>75</age>
</student>
</ROOT>'

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
Insert into Students SELECT id, name, age from 
OPENXML (@idoc, '/ROOT/student',2)
WITH (id  int, name varchar(50), age int)
Currently rated 4.0 by 4 people

«12»