Longing to know

Earlier today, I needed to be able to retrieve progress indicators from a long-running stored procedure. The procedure itself executed many different queries, so the idea of sending messages to the client between those queries seemed like a good place to start.

Initially, I had no idea how to achieve this. After a bit of research, though, I found that the .NET SqlConnection object has an event object called “InfoMessage” that you can tie into. Within EntityFramework, we have to cast our DbConnection to SqlConnection to access this handler. Once we have a reference to the event, we can attach our own event handler. The basic premise looks like this:

var dbConext = new DbContext("connection");
var sqlConn = dbConext.Database.Connection as SqlConnection;
if (sqlConn != null)
{
    sqlConn.FireInfoMessageEventOnUserErrors = true;
    sqlConn.InfoMessage += delegate (object sender, SqlInfoMessageEventArgs e)
    {
        Console.WriteLine(e.Message);
    };
}

dbContext.Database.ExecuteSqlCommand("EXEC [dbo].[spMessageTest]");

Within the stored procedure (T-SQL based), we can use the RAISERROR method to get messages back. If the severity of the message is 10 or less, an actual error is not thrown – and it becomes a handy messaging system. As a simple test, I have a stored procedure that raises messages with pauses between each message like this:

CREATE PROC [dbo].[spMessageTest]
AS
BEGIN
	RAISERROR('Message1', 0, 10) WITH NOWAIT;
	WAITFOR DELAY '00:00:02';

	RAISERROR('Message2', 0, 10) WITH NOWAIT;
	WAITFOR DELAY '00:00:02';

	RAISERROR('Message3', 0, 10) WITH NOWAIT;
	WAITFOR DELAY '00:00:02';
	
	RETURN 0;
END
GO

Although you can see there is some EF specific code here, this should work with any SqlConnection object.

Since I’m using a repository pattern, I wanted something a little more reusable. I already have “execute stored proc” methods within my repository, so I really just needed a way to pass a delegate/predicate to these methods and call it appropriately.

There are a few alternatives. We can use delegate, Func<obj,obj>, Action<obj>, or Predicate<obj>. All of these types are inherently similar. For example, Func<T, bool> is simply a Predicate. Action<T> is just like Func<T, obj> except there is no return type. After looking at these cases, Action<T> seemed the most appropriate. T can be our “progress” indicator.

Within the repository, then, we can define a simple method that will handle this similarly to the above code. The difference will be that the Action callback will be executed. A method to handle this would look like this:

/// <summary>
/// Attach a handler for the InfoMessage event if the underlying connection is a SqlConnection
/// </summary>
/// <param name="callback"></param>
private void AttachInfoMessageDelegate(Action<ProgressMessage> callback = null)
{
    var sqlConn = _dbContext.Database.Connection as SqlConnection;
    if (sqlConn != null)
    {
        if (callback == null)
        {
            callback = (ProgressMessage progress) => { Console.WriteLine(progress.Message); };
        }

        sqlConn.FireInfoMessageEventOnUserErrors = true;
        sqlConn.InfoMessage += delegate (object sender, SqlInfoMessageEventArgs e)
        {
            callback(new ProgressMessage() { Message = e.Message });
        };
    }
}

I allow for not passing in a callback so that in my repository, which calls this method whenever a stored proc is executed, doesn’t have to perform a null check. Once all of this code is in place, I call my repository’s “execute sproc” method, and pass in a callback Action, I get all of the info messages returned to my method. Within the context of the application where I’m using this, it flows through an actor pattern back to a SignalR receiver. The messages then drive a progress bar / indicator.

Overall, this became a nice, serviceable solution without the need for multiple DB connections, logging to a table, or other methodologies.