CLR Trigger – Microsoft SQL database

In my last post (Realtime database applications with Node.JS and HTML5 WebSockets) I talked about using the CLR-trigger in combination with the Microsoft SQL database to get realtime results. Ever since I have had multiple requests about publishing the C# script, so I decided to do so! I edited out some implementation details, but I’m sure that won’t be a problem. If any more explanation is necessary, please contact me! 🙂

 

using System;
using System.IO;
using System.Threading;
using System.Net.Sockets;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using TheBigB.JsonSerializerLite;
using System.Collections.Generic;
using Microsoft.SqlServer.Server;

namespace HG.CLRSolution
{

	public class LPMTrigger
	{
		private const string SERVER_IP = "<your ip here>";
		private const int SERVER_PORT = 9227;

		// This value should be high, as it also serves as a connection retry value
		// when under heavy load.
		private const int SERVER_CONNECT_TIMEOUT = 5000;

		private static TcpClient client = new TcpClient();
		private static StreamWriter sOut;
		private static SqlConnection dbConnection = new SqlConnection("context connection = true");

		// Status variable to indicate if a thread is already trying to connect to the Node server
		private static bool connecting = false;

		// Mutex reference
		private static object mutexObj = new object();

		public static void DataInserted()
		{
			// If there is no connection and creating a connection fails, exit
			if (!connect())
				return;

			try
			{
				// Only respond to inserts
				if (SqlContext.TriggerContext.TriggerAction != TriggerAction.Insert)
					return;

				// Open database connection if not open yet
				if(dbConnection.State == System.Data.ConnectionState.Closed)
					dbConnection.Open();

				// Query last inserted items
				SqlCommand command = dbConnection.CreateCommand();
				command.CommandText = @"<query here>";
				SqlDataReader reader = command.ExecuteReader();

				// Loop through results and store the JSON encoded version of the results in a list
				List<string> measurements = new List<string>(20);
				while (reader.Read())
				{
					// Do something with the read data.
				}
				reader.Close();
				dbConnection.Close();

				// Mutex to prevent thread from writing through each other
				lock (mutexObj)
				{
					// Write each inserted measurement to the Node server
					foreach(string measurement in measurements)
						sOut.WriteLine(measurement);
				}
			}
			catch (Exception e)
			{
				logWrite(e.Message);
				logWrite(e.StackTrace);
			}
		}

		/// <summary>
		/// Tries to open a connection to the server with the pre-configured
		/// time-out. Returns a boolean describing if the connection succeeded or not.
		/// </summary>
		/// <returns>Success?</returns>
		private static bool connect()
		{
			// If another instance of this method is busy connecting, abort
			if (connecting)
				return false;

			// Check whether we are already connected
			if (client.Connected)
				return true;

			// Mutex to prevent multiple connection instantiations on the same socket
			lock (mutexObj)
			{
				// Notify that we are busy connecting 
				// (this effectively means that all calls to DataInserted are ignored during this period)
				connecting = true;

				IAsyncResult asyncResult;
				WaitHandle waitHandle = null;
				try
				{
					// Start connecting asynchronously
					asyncResult = client.BeginConnect(SERVER_IP, SERVER_PORT, null, null);
					waitHandle = asyncResult.AsyncWaitHandle;

					// Wait for connection or abort after the timespan of SERVER_CONNECT_TIMEOUT has elapsed
					if (!asyncResult.AsyncWaitHandle.WaitOne(SERVER_CONNECT_TIMEOUT, false))
					{
						// On timeout, close connection and return negative
						client.Close();
						return false;
					}

					// Implicit else, notify callbacks
					client.EndConnect(asyncResult);

					// Open an output stream
					sOut = new StreamWriter(client.GetStream());
					sOut.AutoFlush = true;

					return true;
				}
				catch (Exception e)
				{
					logWrite(e.Message);
					logWrite(e.StackTrace);
					return false;
				}
				finally
				{
					// On exit disable 'connecting' flag
					connecting = false;
					if (waitHandle != null)
						waitHandle.Close();
				}
			}
		}

		/// <summary>
		/// Writes the given message to a logfile.
		/// NOTE: the directory 'inmotio' must exist on the root drive C:\ and
		/// must accessible to the MSSQLServer users.
		/// </summary>
		/// <param name="message">Message to write to log</param>
		/// <param name="file">File to write to</param>
		private static void logWrite(string message, string file = "trigger.log")
		{
			file = @"C:\" + file;
			StreamWriter writer = File.AppendText(file);
			writer.WriteLine(DateTime.Now.ToString("u") + "\t" + message);
			writer.Flush();
			writer.Close();
		}
	}
}

Leave a Reply