So after yesterday's post about getting my 4 line LCD display to start displaying something interesting.   I have just got a step closer to LCD Nirvana.    (Yes I am over egging it).

If you remember, I left it last time that I wanted a nice simple way for the LCD Windows service to be notified of events.  My starting event was my Caller ID service telling me someone was calling the house.   This service drops a row into SQL 2005.   Within SQL 2005 using a managed code trigger I fire emails alerts, to let anyone who's interested know who's calling.

We'll I found the magic command that lets SQL 2005, trust other assemblies so that in managed code stored procedures/triggers you can start to make full use of the .Net Framework.

Now before I go on, I should point out that only a handful of standard .Net assemblies  are trusted by SQL, and for good reason too.   SQL needs to handle multiple transactions in a dependable fashion,  relying on outside .Net code may fudge the whole reliability of SQL.   Anyway throwing caution to the wind, and recognising I'm not exactly building a mission critical system I registered the system.messaging namespace in my SQL database with -

CREATE ASSEMBLY Messaging FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\system.messaging.dll'
WITH PERMISSION_SET = UNSAFE

Now from Visual Studio,  I can now add the System.Messaging assembly to my managed code trigger.   Mind you when I add reference it shows up as Messaging, but that's beside the point, it all works -

I added the following code to my trigger -

        string msmq=@".\PRIVATE$\LCD";

        SqlContext.Pipe.Send("MSMQ Start "+msmq_q);

                   System.Messaging.MessageQueue mq = new System.Messaging.MessageQueue(msmq_q);
                   System.Messaging.Message mess = new System.Messaging.Message();
                   Encoding ASCII = Encoding.ASCII;
                   string msg = "FLASH|" + phonenumber + "|" + identifier + "|" + calltype + "|" + timestamp;
                   byte[] ourbuf = ASCII.GetBytes(msg);
                   mess.BodyStream = new MemoryStream(ourbuf);
                   mess.Label = "";
                   mq.Send(mess);
                   SqlContext.Pipe.Send("MSMQ End " + msmq_q);

This, puts all the call information onto a private message queue called LCD with all the details of the incoming call.

Finally I just hooked the Windows Service that controls the LCD screen to just listen for any incoming message queue messages on the specific LCD queue.

Anyway, hope this all makes sense.    Obviously this is early days for this project.   But I now have a sub-second display showing me exactly who's calling the house.   I usually get the alert even before the first ring.

Here's a screen shot of what the LCD screen displays -