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:
- Created a new Class Library project.
- Added a reference to DotNetNuke.dll from an existing DNN installation (and set Copy Local to false)
- 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)
- Set the build path INSIDE DNN’s bin folder to save me time copying and recopying the dll.
- 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:
- Host-Schedule and click “Add Item to Schedule”.
- Set friendly name to whatever you want
- 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.
- Check the “schedule enabled” checkbox
- 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...
Posts
