Dot Net Tips and Tricks

I’ve recently have been working on a custom delivery protocol for notification services. I needed a way for sql server 2005 to call a remoted service when a trigger fired, to try and avoid polling the database for changes or using a SQLDependency that would need to be wired back up after each firing.

The delivery protocol takes 2 parameters from the instance config file: Service Contract Type (IServiceContract) and MethodName (The method to call).

On intialization I load the contract assembly in and reflect on the type to get the MethodInfo for later usage. It currently does not support overloaded methods and will only grab the first method match.

if (channelArgs.Count == 2)
{
string service = channelArgs["ServiceType"];
methodName = channelArgs["MethodName"];
Type t = TypeLoader.LoadType(service);

//I’m using windows security on my remoted object hosted in IIS, so setting the current principal is necessary.
Thread.CurrentPrincipal = new WindowsPrincipal(WindowsIdentity.GetCurrent());
MethodInfo[] myMethodInfos = t.GetMethods();
foreach (MethodInfo Mi in myMethodInfos)
{
if (Mi.Name == methodName)
{
myMethodInfo = Mi;
//Store the parameters for later usage.
info = myMethodInfo.GetParameters();
break;
}
}
}
else
{
throw new ArgumentException(
“Inadequate number of arguments supplied.”);
}

When a delivery notification comes in, I take the list of ProtocolFields and match them to the method parameters and invoke the method.

bool successfulDelivery = false;
Exception failureException = null;

try
{
foreach (NotificationHeaders header in headersList)
{
object[] param = null;
// Set the method parameters.
if (null != header.ProtocolFields)
{
param = new object[header.ProtocolFields.Count];
int idx = 0;
foreach (string protocolField in header.ProtocolFields.Values)
{
Type tp = info[idx].ParameterType;
object o = Convert.ChangeType(protocolField, tp);
param[idx] = o;
idx++;
}
}

object obj = ServiceFactory.CreateInstance(t, service);
myMethodInfo.Invoke(obj, param);
}
}
catch (Exception ex)
{
failureException = ex;
// Handle any exceptions here; for instance,
// write exception information to the event log.
}
finally
{
SendStatus(headersList, successfulDelivery,
body, failureException);
}

You will need to make sure RemotingServices.Configure has been called before the method is invoked. Also the ServiceFactory is a helper to extract the particulars of creating the object away from any client code to allow for future support of Windows Communication Foundation (WCF) or any other communication protocol. SendStatus returns the message status to Notification Services.

There is certainly some cleanup that needs to occur but the jist is there. This allows Sql Server 2005 to call back to any service when an event occurs in the database. Next I’ll post the application config files to setup the service.