Skip to content
AtlanticBT Monogram Atlanticbt.com
Menu
October 5, 2010

How to send an iCal to Gmail with C# and SQL Server 2005

capabilities covered
Amazon Web Services

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("[email protected]");
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:[email protected]" +

"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

CLR Stored Procedure Calling External Web Service – SQL Server 2005 Tutorials

capabilities covered
Amazon Web Services

The Atlantic BT Manifesto

The Ultimate Guide To Planning A Complex Web Project

Partner With Us

Ready for modern web technology and a sharp, user-friendly design? We want to give you exactly that. Contact us to get started.

Contact

Insights

Atlantic BT's Insights

We’re sharing the latest concepts in tech, design, and software development. Learn more about our findings.

Questions & Answers

Are there differences in application architecture that are important for the cloud?
It is important to build applications and workloads specifically for the cloud. You will want to carefully consider what services the cloud provider of your choice has to offer and how your application leverages those services.
Learn More
Are there any drawbacks to cloud hosting?
Yes, there will always be some risks associated with any hosting option. You are relying on the resiliency and engineering of infrastructure that has scaled at an astounding rate.
Learn More
What’s the benefit of hosting in the cloud vs. traditional options?
Reasons not to host in the cloud are few and far between. If you don't host in the cloud, you will spend more in both CapEx and OpEx to manage your applications or websites in a traditional environment.
Learn More
How can I improve the performance of my application?
There are several primary reasons that applications perform poorly, and in some cases it’s a combination of several. 1) Data latency: If your application is making calls to a data source (whether it’s an API or a direct call) and there is latency at the data provider, your application performance will suffer.
Learn More
Should I move my application to the cloud?
The answer is ‘probably yes’. There aren’t many reasons for an application to be hosted elsewhere, aside from occasional compliance standards, or requirements to integrate with local services that would require large amounts of data to move from on-premise to cloud.
Learn More
Where should my application be hosted?
There are many different options for hosting, but most applications would do well with one of the cloud providers -- Amazon Web Services, Google Cloud Platform, Microsoft Azure.
Learn More