Overview
Sometimes we need to execute some long-running SQL Server processes (Stored procedures or functions) and it’d be nice to get progress messages, and why not, a percent value in order to show a progress bar in our application.
In the past I’ve used different techniques to archive this, from SQL Assemblies for calling a SignalR server, to execute xp_cmdshell, and so many other esoteric ways.
None of that solved the problem in an elegant way, but well, it worked… more or less…
Changing the point of view
Today I noticed the SqlConnection class has an InfoMessage event, and it can be used for “Clients that want to process warnings or informational messages sent by the server” which is EXACTLY what I was looking for.
It looks promising but, how can I trigger an error from my SQL?
The answer is really E-A-S-Y :)
If you are a TSQL programmer you surely know RAISERROR, which generates an error message in a similar way the throw C# keyword does.
The trick here is its severity argument: It indicates the type of problem encountered by SQL Server, and values under 10 indicate that these errors are not severe. Just warnings or information that SQL server sends to anyone is listening.
And who’s listening? You can imagine…
Putting it all together (show me the code)
Let’s assume we’ve this long-running stored procedure in our database:
CREATE PROCEDURE [dbo].[TestLongRunningSP]
AS
BEGIN
RAISERROR('1) Starting', 0, 0) WITH NOWAIT;
WAITFOR DELAY '00:00:03'; --Wait for 3 seconds
RAISERROR('2) Fetching data', 0, 0) WITH NOWAIT;
WAITFOR DELAY '00:00:05'; --Wait for 5 seconds
RAISERROR('3) Some other actions', 0, 0) WITH NOWAIT;
WAITFOR DELAY '00:00:03'; --Wait for 3 seconds
RAISERROR('4) Calculating', 0, 0) WITH NOWAIT;
WAITFOR DELAY '00:00:02'; --Wait for 2 seconds
RAISERROR('5) Finishing', 0, 0) WITH NOWAIT;
END
Now let’s create some C# code:
First: A class to send the received messages from our repository to its consumer, which in this case the UI will be just a Windows Form (good old boys never die).
public class SqlMessageEventArgs {
public string Message { get; set; }
public int Progress { get; set; }
}
Notice the Progress property. We will use it in the last part, be patient ;)
Second: A repository class that open a connection, execute the stored procedure and send messages using a ProcessMessage event. We must explicitly enable the connection to receive these errors and raise handling the InfoMessage event:
public class SqlServerRepo
{
public event EventHandler 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: When using async pattern the received messages come from a different thread and cannot be used to update the UI directly. For this reason I always recommend send to the Repo the current synchronization context and use this Context to post to the main thread.
BOOM: Typical cross thread exception when updating UI from a different thread.
Third: Finally let’s create a simple Windows Form with just a button, a progress bar and a list box).
const string cs = "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 = cs,
SyncContext = SynchronizationContext.Current
};
repo.ProcessMessage += (ms, me) => {
listBoxLog.Items.Add($"{DateTime.Now} - {me.Message}");
};
await repo.GetDataAsync();
listBoxLog.Items.Add($"Finished Process!");
}
This code creates the repo, pass the connection string and the current synchronization context, handles the ProcessMessage event and invoke the GetDataAsync method.
Simply and elegant. No dependent assemblies or external resources.
One more thing
What about the progress bar? Also easy. Just use the state argument of the RAISERROR method on the SP like this:
RAISERROR('1) Starting', 0, 10) WITH NOWAIT;
WAITFOR DELAY '00:00:03'; --Wait for 3 seconds
RAISERROR('2) Fetching data', 0, 30) WITH NOWAIT;
WAITFOR DELAY '00:00:05'; --Wait for 5 seconds
RAISERROR('3) Some other actions', 0, 60) WITH NOWAIT;
WAITFOR DELAY '00:00:03'; --Wait for 3 seconds
RAISERROR('4) Calculating', 0, 70) WITH NOWAIT;
WAITFOR DELAY '00:00:02'; --Wait for 2 seconds
RAISERROR('5) Finishing', 0, 100) WITH NOWAIT;
Then update the GetDataAsync method:
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,
Progress = e.Errors[0]?.State ?? 0
}), null);
con.Open();
var sql = "EXEC dbo.TestLongRunningSP";
var commandDefinition = new CommandDefinition(sql);
await con.QueryAsync(commandDefinition);
con.Close();
}
}
And finally the code in the form:
repo.ProcessMessage += (ms, me) => {
listBoxLog.Items.Add($"{DateTime.Now} - {me.Message}");
progressBar.Value = me.Progress;
};
And that’s all!
Hope you enjoy it, and please, send me your thoughts about this solution ;)
Today I had to read some data from different OLAP cubes and show this information in our application. This could be accomplished using ADOMD.NET client library, which is the multidimensional equivalent to the ADO.NET libraries.
