Retrieving events from SQL Server

Overview

When you’ve got long-running processes on SQL Server (stored procs, functions, whatever), it’s super handy to show progress updates in your app — maybe even a percentage to drive a progress bar.

Over the years I’ve tried all kinds of hacks to get this working: SQL assemblies that called into SignalR, using xp_cmdshell, and plenty of other questionable approaches. They worked… kinda… but nothing felt clean.

Rethinking the problem

Turns out, SqlConnection has an InfoMessage event. That event is for “warnings or informational messages sent by the server,” which is exactly what we need here.

The trick is figuring out how to send those messages from SQL Server without breaking things.

Enter RAISERROR. With severity < 10, SQL just treats it as informational (not a “real” error). Add WITH NOWAIT and you can push updates mid-execution.

So SQL shouts messages, and our C# code just has to listen.

Show me the code

Here’s a simple example: a fake “long-running” stored proc that simulates work with WAITFOR DELAY and fires off progress messages along the way.

SQL Script

CREATE PROCEDURE [dbo].[TestLongRunningSP]
AS
BEGIN
    RAISERROR('1) Starting', 0, 0) WITH NOWAIT;
    WAITFOR DELAY '00:00:03';

    RAISERROR('2) Fetching data', 0, 0) WITH NOWAIT;
    WAITFOR DELAY '00:00:05';

    RAISERROR('3) Some other actions', 0, 0) WITH NOWAIT;
    WAITFOR DELAY '00:00:03';

    RAISERROR('4) Calculating', 0, 0) WITH NOWAIT;
    WAITFOR DELAY '00:00:02';

    RAISERROR('5) Finishing', 0, 0) WITH NOWAIT;
END

Running that proc produces:

1) Starting
2) Fetching data
3) Some other actions
4) Calculating
5) Finishing

Now, let’s wire this up in C#.

C# Setup

First, a tiny event args class to wrap our messages:

public class SqlMessageEventArgs
{
    public string Message { get; set; }
    public int Progress { get; set; }
}

Next, the repo class that handles connection, execution, and pushing messages back to the UI:

public class SqlServerRepo
{
    public event EventHandler<SqlMessageEventArgs> ProcessMessage;
    public string ConnectionString { get; internal set; }
    public SynchronizationContext SyncContext { get; internal set; }

    public async Task GetDataAsync()
    {
        using (var con = new SqlConnection(ConnectionString))
        {
            con.FireInfoMessageEventOnUserErrors = true;
            con.InfoMessage += (s, e) =>
                SyncContext.Post(_ => ProcessMessage?.Invoke(
                    s, new SqlMessageEventArgs
                    {
                        Message = e.Message,
                    }), null);

            con.Open();
            var sql = "EXEC dbo.TestLongRunningSP";
            var commandDefinition = new CommandDefinition(sql);
            await con.ExecuteAsync(commandDefinition);
            con.Close();
        }
    }
}

Note the SyncContext. Without it, you’ll hit cross-thread exceptions when trying to update the UI. Always marshal back to the main thread.

Finally, the form code (button, progress bar, listbox):

const string connectionstring =
    "Data Source=YOUR_SERVER;" +
    "Initial Catalog=YOUR_DATABASE;" +
    "Integrated Security=True;";

private async void button1_Click(object sender, EventArgs e)
{
    var repo = new SqlServerRepo()
    {
        ConnectionString = connectionstring,
        SyncContext = SynchronizationContext.Current
    };
    repo.ProcessMessage += (ms, me) => {
        listBoxLog.Items.Add($"{DateTime.Now} - {me.Message}");
    };
    await repo.GetDataAsync();
    listBoxLog.Items.Add("Finished Process!");
}

That’s all it takes: no weird assemblies, no external services, just SQL and C#.

Adding a progress bar

You can also piggyback on RAISERROR’s state argument to carry progress info:

CREATE PROCEDURE [dbo].[TestLongRunningSP]
AS
BEGIN
    RAISERROR('1) Starting', 0, 10) WITH NOWAIT;
    WAITFOR DELAY '00:00:03';

    RAISERROR('2) Fetching data', 0, 30) WITH NOWAIT;
    WAITFOR DELAY '00:00:05';

    RAISERROR('3) Some other actions', 0, 60) WITH NOWAIT;
    WAITFOR DELAY '00:00:03';

    RAISERROR('4) Calculating', 0, 70) WITH NOWAIT;
    WAITFOR DELAY '00:00:02';

    RAISERROR('5) Finishing', 0, 100) WITH NOWAIT;
END

Update the repo to capture it:

con.InfoMessage += (s, e) =>
    SyncContext.Post(_ => ProcessMessage?.Invoke(
        s, new SqlMessageEventArgs
        {
            Message = e.Message,
            Progress = e.Errors[0]?.State ?? 0
        }), null);

And in the form:

repo.ProcessMessage += (ms, me) => {
    listBoxLog.Items.Add($"{DateTime.Now} - {me.Message}");
    progressBar.Value = me.Progress;
};

Done — a lightweight, elegant way to stream SQL progress straight into your UI.

---