Sending iCalendar events via a .Net Assembly and SQLServer stored procedure
As I’ve mentioned in earlier posts, the iCalendar format is a standard way to send event information to a variety of email/scheduling systems (Outlook, Google, etc.). Here is a way to set it up so that you can simply call a stored procedure to send the event. This assumes you have SQL Server set up with access to Exchange.
In C# (I’m using Visual Studio 2008) create a class as indicated.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Net.Mail;
using System.Net.Mime;
namespace icalEmail
{
public class StoredProcedures
{
[SqlProcedure]
public static void SendiCal(DateTime startDateTime, DateTime endDateTime, string emailSubject,
string userEmailAddress, string cnAttendee, string methodType, DateTime createdOn, string notes, string UID, string location, string seqNo, string smtpserver, int smtpport)
{
// create the iCal string in the proper format
string iCal = CreateiCalFormat(startDateTime, endDateTime, emailSubject, userEmailAddress,
cnAttendee, methodType, createdOn, UID, notes, location, seqNo);
// create the MIME protocol Content-Type
var calendarType = new ContentType("text/calendar;method=REQUEST; charset=UTF-8");
calendarType.Parameters.Add("method", "REQUEST");
AlternateView caledarView = AlternateView.CreateAlternateViewFromString(iCal, calendarType);
caledarView.TransferEncoding = TransferEncoding.SevenBit;
// connect to smtpserver
var client = new SmtpClient(smtpserver, smtpport);
// create the email
var mailMesage = new MailMessage();
mailMesage.From = new MailAddress("donotreply@email.com");
mailMesage.To.Add(new MailAddress(userEmailAddress));
mailMesage.Subject = emailSubject;
mailMesage.Body = iCal;
mailMesage.AlternateViews.Add(caledarView);
client.Send(mailMesage);
}
private static string _dateFormat = "yyyyMMddTHHmmssZ";
private static string CreateiCalFormat(DateTime startDateTime, DateTime endDateTime,
string emailSubject, string userEmailAddress, string cnAttendee, string methodType, DateTime createdOn, string UID,
string notes, string location, string seqNo)
{
string iCal =
"BEGIN:VCALENDAR" +
"\nPRODID:-//SampleApp//AoT//EN" +
"\nVERSION:2.0" +
/// REQUEST for add or update, CANCEL for cancellation
"\nMETHOD:" + methodType +
"\nBEGIN:VEVENT" +
"\nORGANIZER:CN='Matt'" +
//"\nORGANIZER:MAILTO:matt.balent@anvil-of-time.com" +
"\nATTENDEE;CN='" + cnAttendee + "'" +
";ROLE=REQ-PARTICIPANT;RSVP=TRUE;CN=" + userEmailAddress +
"\nDTSTART:" + startDateTime.ToUniversalTime().ToString(_dateFormat) +
"\nDTEND:" + endDateTime.ToUniversalTime().ToString(_dateFormat) +
"\nSTATUS:CONFIRMED" +
"\nTRANSP:OPAQUE" +
/// 0 for add, 1 for update or cancellation
"\nSEQUENCE:" + seqNo +
"\nUID:" + Guid.NewGuid() +
/// resend original UID for updates and cancellations
//"\nUID:" + UID +
"\nDTSTAMP:" + createdOn.ToUniversalTime().ToString(_dateFormat) +
"\nLAST-MODIFIED:" + createdOn.ToUniversalTime().ToString(_dateFormat) +
"\nLOCATION:" + location +
"\nDESCRIPTION:" + notes +
"\nSUMMARY:" + emailSubject +
"\nPRIORITY:5" +
"\nCLASS:PUBLIC" +
"\nEND:VEVENT" +
"\nEND:VCALENDAR";
return iCal;
}
}
}
This would be compiled into a .dll which needs to be registered in SQL Server. This assembly is to be marked as UNSAFE so someone with sysadmin priviges needs to register it.
CREATE ASSEMBLY [eventEmail] AUTHORIZATION [dbo] FROM 'C:\temp\icalemail.dll' WITH PERMISSION_SET = UNSAFE
There is an interesting discussion of ‘UNSAFE’ assemblies on Stackoverflow.
Now create the stored procedure which will call the assembly.
create procedure dbo.usp_mb_eventnotify(@startDateTime datetime , @endDateTime datetime , @emailSubject nvarchar(80) , @emailSummary nvarchar(1000) , @location nvarchar(255) , @attendeeName nvarchar(100) , @attendeeEmail nvarchar(255) , @organizerName nvarchar(100) , @organizerEmail nvarchar(255) , @UID nvarchar(255) , @status nvarchar(12) , @requestType nvarchar(12) , @smtpserver nvarchar (255) , @smtpport bigint) as external name [iCalEmail].[iCalEmail.Storedprocedures].[SendiCal];
Examples of use:
Create the event
exec dbo.usp_mb_eventnotify '2012-08-08 13:00:00', '2012-08-08 15:00:00', 'BigProj 2', 'New schedule of Event sent by Matt', 'CONF01', 'Balent, Matt', 'matt.balent@anvil-of-time.com', 'AoT', 'donotreply@email.com', 'AOT372711c', 'CONFIRMED', 'REQUEST', '1234.na.corp.aot.com', 25
Update the event
exec dbo.usp_mb_eventnotify '2012-08-08 10:00:00', '2012-08-08 12:00:00', 'BigProj 2', 'New schedule of Event sent by Matt', 'CONF01', 'Balent, Matt', 'matt.balent@anvil-of-time.com', 'AoT', 'donotreply@email.com', 'AOT372711c', 'CONFIRMED', 'REQUEST', '1234.na.corp.aot.com', 25
Cancel the event
exec dbo.usp_mb_eventnotify '2012-08-08 10:00:00', '2012-08-08 12:00:00', 'BigProj 2', 'New schedule of Event sent by Matt', 'CONF01', 'Balent, Matt', 'matt.balent@anvil-of-time.com', 'AoT', 'donotreply@email.com', 'AOT372711c', 'CANCELLED', 'CANCEL', '1234.na.corp.aot.com', 25You might also be interested in
