We had the need to forward meeting notices to mobile devices. Since we use Goldmine as our CRM all entries are simply database entries which are rendered by the application.
The solution was to create a trigger that sends out an email after insert. We explored DBMail, but you don’ really have much control over the email header which is the key to creating an iCal entry from scratch.
Enter CLR Stored Procedures! I have known that these existed for some time, but never really had a use for them and mostly it was because I always thought embedding business logic in your stored procedures was a bad idea, but in this case it seemed like an easy enough approach…but we got it working, but it of course was not as easy as it sounds.
Show us the code you say? Well all right then…
1.) Create a class library project with a class named Stored Procedures, and pay attention to your namespace:
namespace AtlanticBT.Utilities
{
public partial class StoredProcedures
{
[SqlProcedure]
public static void SendiCal(DateTime startDateTime, DateTime endDateTime, string emailSubject,
string userEmailAddress, string companyName, DateTime createdOn, string notes, string lanId )
{
string iCal = CreateiCalFormat(startDateTime, endDateTime, emailSubject, userEmailAddress,
companyName, createdOn, lanId, notes);
var calendarType = new ContentType("text/calendar");
calendarType.Parameters.Add("method", "request");
AlternateView caledarView = AlternateView.CreateAlternateViewFromString(iCal, calendarType);
caledarView.TransferEncoding = TransferEncoding.SevenBit;
var client = new SmtpClient("mail.myspamomaticsmtpserver.com", 25);
var mailMesage = new MailMessage();
mailMesage.From = new MailAddress("goldmineforward@atlanticbt.com");
mailMesage.To.Add(new MailAddress(userEmailAddress));
mailMesage.Subject = emailSubject;
mailMesage.AlternateViews.Add(caledarView);
client.Send(mailMesage);
}Here is the private method to create the iCal entry:
private static string _dateFormat = "yyyyMMddTHHmmssZ";
private static string CreateiCalFormat(DateTime startDateTime, DateTime endDateTime,
string emailSubject, string userEmailAddress, string companyName, DateTime createdOn, string lanId,
string notes)
{
string iCal =
"BEGIN:VCALENDAR" +
"nPRODID:-//FrontRange Solutions//GoldMine 8.0//EN" +
"nVERSION:2.0" +
"nMETHOD:REQUEST" +
"nBEGIN:VEVENT" +
"nORGANIZER:MAILTO:goldmine@abt.com" +
"nATTENDEE;CN='" + lanId + "'" +
";ROLE=REQ-PARTICIPANT;RSVP=TRUE:" + userEmailAddress +
"nDTSTART:" + startDateTime.ToUniversalTime().ToString(_dateFormat) +
"nDTEND:" + endDateTime.ToUniversalTime().ToString(_dateFormat) +
"nTRANSP:OPAQUE" +
"nSEQUENCE:0" +
"nUID:" + Guid.NewGuid() + "@ATLANTICBT.COM" +
"nDTSTAMP:" + createdOn.ToUniversalTime().ToString(_dateFormat) +
"nDESCRIPTION:" + notes +
"nSUMMARY:" + companyName + " " + emailSubject +
"nPRIORITY:5" +
"nCLASS:PUBLIC" +
"nX-FRS-EXT-BUILDNO;X-FRS-SEND=SEND:8.03.80716" +
"nX-FRS-EXT-OPLINK;X-FRS-SEND=SEND:205A5936304D412A315F4B3026512E" +
"nX-FRS-EXT-RECTYPE;X-FRS-SEND=SEND:A" +
"nBEGIN:VALARM" +
"nTRIGGER:PT10M" +
"nACTION:DISPLAY" +
"nDESCRIPTION:Reminder" +
"nEND:VALARM" +
"nEND:VEVENT" +
"nEND:VCALENDAR";
return iCal;
}2.) Now remember this stored procedure is being called from a trigger in our our GoldMine store, but it can be called like any stored procedure. Deploy the assembly that is created in Step 1 to your SQL Server.
3.) Configure the stored procedure on your SQL Server. Here is the T-SQL:
sp_configure 'clr enabled', 1 GO RECONFIGURE GO
ALTER DATABASE Goldmine SET TRUSTWORTHY ON
CREATE ASSEMBLY GoldmineiCalForward FROM 'C:mypathtotheassemblyAtlanticBT.Utilities.dll' WITH PERMISSION_SET = UNSAFE; GO
CREATE PROCEDURE SendiCal(@startDate DateTime, @endDate DateTime, @emailSubject nvarchar(255), @userEmailAddress nvarchar(255), @companyName nvarchar(255), @createdOn DateTime, @notes nvarchar(max), @landId nvarchar(255))AS EXTERNAL NAME GoldmineiCalForward.[AtlanticBT.Utilities.StoredProcedures].SendiCal GO
4. Then create the Goldmine trigger upon every calendar create to look up your user’s email account that accepts iCal entries (Gmail for Android phones is working cool for us).
5. Shazam!
Thanks to these other bloggers whom I used while trying to figure out how to configure this thing:
Writing CLR Stored Procedures
