(Not a) tutorial: Creating my first DNN scheduled task

In this post, I’ll describe my (successful) attempt to create a basic email queue functionality using a custom DNN scheduled task.

 

Why not a tutorial? Because my intention is to show you what I have done, without telling you that this is the right (or the only) way to do it, or demonstrating this as a techically complete and correct solution. This work is far from finished – it just covers basic functionality and is not usable out of the box. But if you’re struggling with DNN scheduled tasks, reading below may provide you with a way to get started.

 

I found great help in this excellent post here, which you should read for more information on creating your own scheduled tasks in DNN. Actually, this article here is a simple application of the tutorial I found in that post. Read it, it’ll be extremely helpful.

 

Let’s get to the point.

 

Purpose:

 

I had the need to notify custom groups of DNN registered users upon submission of some forms created either with the XMOD Pro or the Datasprings Dynamic Forms module. Although both modules support sending emails upon submission, they are restricted to sending emails to specific people, lists, or roles.

 

I needed something more complex, such as a database query based on several tables, in order to determine who would get the notification. Both modules allow issuing queries to the database upon submission, so it seemed right to create my own email queue, consisting of a database table (the queue) and a scheduled task to send the mails.

What’s more, a queue can help where there is a bunch of emails to be sent – let’s say you need to send 1k emails upon the next form submission – you don’t have to send them all at once. You just send a dozen each time the scheduled task is ran, until the queue is empty.

 

Database objects

 

So – what did I need first? Of course, a table to hold my queue.

 

CREATE TABLE [dbo].[DotSee_MailQueue]( 
    [MailID] [int] IDENTITY(1,1) NOT NULL, 
    [FromUser] [nvarchar](500) NOT NULL, 
    [ToUser] [nvarchar](500) NOT NULL, 
    [Subject] [nvarchar](500) NOT NULL, 
    [BodyHtml] [nvarchar](max) NULL, 
CONSTRAINT [PK_DotSee_MailQueue] PRIMARY KEY CLUSTERED 
( 
    [MailID] ASC 
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY] 
) ON [PRIMARY] 

GO

Yes, it’s a silly table but enough for my basic functionality: From, To, Subject, Body and an id. All the necessary information to send an email.

Then I needed a stored procedure to retrieve information from that table:

CREATE PROCEDURE [dbo].[DotSee_GetMailQueueItems] 
AS 
BEGIN 
SET NOCOUNT ON; 

Select top 10 [MailID],[FromUser],[ToUser],[Subject],[BodyHtml] FROM [dbo].[DotSee_MailQueue] 

END 

GO

Note: I prefix my tables with “DotSee_” since this is the name I have selected for my custom work (namespaces included, as you’ll see below). Don’t give it serious attention, it’s just my convention.

Setting up a project for my scheduled task

There are more than one ways to begin creating a scheduled job for DotNetNuke using Visual Studio, but there is a common denominator: Your job MUST be a DLL file in DotNetNuke’s bin directory. So you can go either with a Web Application Project or with a Class Library. I chose Class Library. Here’s what I did:

1. Created a new Class Library project.


2. Added a reference to DotNetNuke.dll from an existing DNN installation (and set Copy Local to false)


3. Added a reference to Microsoft.ApplicationBlocks.Data from the same DNN installation (in order to be able to use DNN’s own DAL for interacting with the database)


4. Set the build path INSIDE DNN’s bin folder to save me time copying and recopying the dll.


5. Went to the project’s properties and deleted the contents of the root namespace field. Yes, it should be empty for your dll to work. Don’t miss that.

Regarding (4), I did this work on a virtual machine, where I had a DNN installation and my class library project on the same computer. I suggest you follow some similar approach, since it is very easy to test and debug (you can debug only by attaching to the w3wp.exe process, but it’s better than nothing).

Writing the code

Two more things you must know: Your custom class must inherit DotNetNuke.Services.Scheduling.SchedulerClient and the only method that is called by DNN’s scheduler is DoWork().

So here’s my custom scheduler class:

Imports DotNetNuke.Services.Scheduling 
Imports DotNetNuke.Services.Exceptions 

Imports System 
Imports System.Web 
Imports System.IO 

Namespace DotSee.DnnScheduledMailNotifier 

    Public Class Mailer 
        Inherits SchedulerClient 

        ' requires a special constructor which 
        ' accepts a ScheduleHistoryItem 
        Public Sub New( _ 
            ByVal objScheduleHistoryItem _ 
            As ScheduleHistoryItem) 

            MyBase.New() 
            Me.ScheduleHistoryItem = objScheduleHistoryItem 

        End Sub 

        Public Overrides Sub DoWork() 
            Try 
                ' perform some tasks 
                MailWorker.DoWork() 

                ' report success to the scheduler framework 
                ScheduleHistoryItem.Succeeded = True 

            Catch ex As Exception 

                ScheduleHistoryItem.Succeeded = False 
                ScheduleHistoryItem.AddLogNote _ 
                    ("EXCEPTION" + ex.ToString()) 
                Errored(ex) 
                Exceptions.LogException(ex) 

            End Try 
        End Sub 
    End Class 

End Namespace 

Apart from the MailWorker.DoWork() line (code coming right below), the class can be used as a template for writing your own scheduled tasks. (You will use your own namespace, of course). I put my specific code in a different class so that it would be easy to make the distinction between code needed for my specific need and the “template” code that every DNN scheduled task needs.

Here’s my MailWorker class (DotNetNuke.dll referenced was version 5.2.1, GetHostSettingsDictionary may not work with 4.x):

Imports DotNetNuke.Services.Mail
Imports DotNetNuke
Imports DotNetNuke.Data
Imports DotNetNuke.Entities.Host


Namespace DotSee.DnnScheduledMailNotifier


    Public Class MailWorker

        Private Sub New()

        End Sub

        Public Shared Sub DoWork()

            Dim d As Dictionary(Of String, String)
            d = Host.GetHostSettingsDictionary()

            Dim dnp As DataProvider
            dnp = DataProvider.Instance
            Dim dr As IDataReader

            Dim ids As New List(Of Int32)

            dr = dnp.ExecuteReader( _
                "DotSee_GetMailQueueItems", Nothing)

            If Not dr.Read() Then Return

            While dr.Read

                Mail.SendMail( _
                    dr("fromuser").ToString _
                    , dr("touser").ToString _
                    , "" _
                    , dr("subject").ToString _
                    , dr("bodyhtml").ToString _
                    , "" _
                    , "" _
                    , d("SMTPServer") _
                    , d("SMTPAuthentication") _
                    , d("SMTPUsername") _
                    , d("SMTPPassword") _
                    )

                ids.Add(CType(dr("mailid"), Int32))

            End While

            For Each id In ids
                dnp.ExecuteSQL( _
                    String.Format( _
                        "DELETE FROM DotSee_MailQueue WHERE mailid={0}" _
                        , id.ToString))
            Next

        End Sub

    End Class

End Namespace

What it actually does (in a brutal way, I should add) is check the table, get 10 items at a time (via the DotSEe_GetMailQueueItems stored procedure defined earlier), send an email for each of the items and then delete the corresponding records from the table. (Ok, I know the way it does it sucks a bit, but It’s only quick and dirty functionality, remember).

So when I compile this beauty, my DotSee.DNNScheduledMailNotifier.dll goes inside DNN’s bin folder, and now what I have to do is tell DNN that this is a scheduled task and that the scheduler should include it. Here’s how:

1. Host-Schedule and click “Add Item to Schedule”.


2. Set friendly name to whatever you want


3. Full class name and assembly should be your assembly name and your class name (the class containing the DoWork method) combined, as in AssemblyName.Classname. My Assembly’s name is DotSee.DnnScheduledMailNotifier and my class name is Mailer, so the full name is DotSee.DnnScheduledMailNotifier.Mailer.


4. Check the “schedule enabled” checkbox


5. Time lapse MUST have some value or else the task will not run at all.

That’s it! The task is ready to run. I tested it by manually inserting records into the DotSee_MailQueue table and it worked well, even with hundreds of records, sending 10 emails every 5 seconds.

So now I have my basic system ready. When I need to send emails to groups of people not defined by roles or other DNN constructs, I just have to write a good SQL query that fills the table with recipients that correspond to the criteria I need every time and presto!

Of course, there’s plenty of room for configuration / parameterization (for example, the number of emails sent each time or the type of email – html or plain text, or even a check for whether delivery was successful). But I guess you got the idea.

Thank you for your time.

Read more...