Sending iCalendar events via a .Net Assembly and SQLServer stored procedure

Posted on Tuesday, July 31st, 2012 at 5:01 pm in

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',
25
You might also be interested in

11 Comments

Add your comment

  1. karim - March 25, 2013 at 11:32 am

    Thank you for sharing.
    I am not able to create the stored procedure, and would appreciate your help.

    Thank you.

  2. Paul - May 9, 2013 at 9:35 am

    This looks like it might be exactly what I need to solve an issue that I’m having.
    I’m stalled at one of the stages though and I’m wondering if I might be missing something (this entire process is completely new to me).

    So, I built the .dll and registered it in SQL server by creating the “eventEmail” assembly, but when I try to create the stored procedure to call the assembly I get the following error message:

    What does ‘[eventEmail.EventNotification]’ and ‘[SendNotification]’ refer to in the query?

  3. Paul - May 9, 2013 at 9:38 am

    I completely messed up my last comment so please excuse the re-post :X

    This looks like it might be exactly what I need to solve an issue that I’m having.
    I’m stalled at one of the stages though and I’m wondering if I might be missing something (this entire process is completely new to me).

    So, I built the .dll and registered it in SQL server by creating the “eventEmail” assembly, but when I try to create the stored procedure to call the assembly I get the following error message:

    Msg 6505, Level 16, State 2, Procedure usp_mb_eventnotify, Line 6
    Could not find Type ‘eventEmail.EventNotification’ in assembly ‘iCalEmail’.

    What do ‘[eventEmail.EventNotification]‘ and ‘[SendNotification]‘ refer to in the query?
    Is there an obvious solution to this error?

    Cheers!

    • Matt Balent - May 16, 2013 at 8:18 am

      I had my Stored proc create incorrect. The external name should reference the [namespace].[namespace.class].[method name].

      I’ve corrected the code

      Sorry for the confusion.

      Matt

      • Paul - May 17, 2013 at 3:39 am

        Ah, I did spot one more thing when implementing this.

        The “method” property is set twice in calendarType –

        var calendarType = new ContentType(“text/calendar;method=REQUEST;charset=UTF-8”);
        calendarType.Parameters.Add(“method”, “REQUEST”);

        I just had to remove the second line there and everything worked great.

        Thanks for the response!

  4. marcosfreccia - March 12, 2014 at 9:44 pm

    Hello.

    I’m trying to create the procedure using this code.
    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 [eventEmail].[iCalEmail.Storedprocedures].[SendiCal];

    but then, I get this error

    Msg 6505, Level 16, State 2, Procedure usp_mb_eventnotify, Line 1
    Could not find Type ‘iCalEmail.Storedprocedures’ in assembly ‘icalemail’.

    do you have any idea in what it could be?

    • Matt Balent - March 13, 2014 at 7:58 am

      Did you create the .Net portion and register it in SQL?

      • marcosfreccia - March 13, 2014 at 3:50 pm

        Yes, querying sys.assemblies it shows the same created. Not sure if there is difference, but I compiled the code using .Net 3.5.

        Is this code executes to you properly?

        Thanks!!

        • Matt Balent - March 13, 2014 at 3:56 pm

          and the stored procedure references: [namespace].[namespace.class].[method name] from the .Net project?

          • marcosfreccia - March 13, 2014 at 4:41 pm

            What I did was, just get this script you provided saved in a .cs extension file and run this command on cmd “csc.exe /target:library /out:C:\temp\CLR\icalEmail.dll C:\temp\CLR\icalemail.cs”. So, I didn’t modified anything on it.

Leave a Reply

Top