When executing some long-running server side processes on a SQL Server (Stored procedures or functions) would 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?
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 😄
Show me the code
Let’s imagine we’ve this long-running stored procedure in our database. We’re using WAITFOR DELAY for simulating intensive processing.
CREATEPROCEDURE[dbo].[TestLongRunningSP]ASBEGINRAISERROR('1) Starting',0,0)WITHNOWAIT;WAITFORDELAY'00:00:03';--Wait for 3 seconds
RAISERROR('2) Fetching data',0,0)WITHNOWAIT;WAITFORDELAY'00:00:05';--Wait for 5 seconds
RAISERROR('3) Some other actions',0,0)WITHNOWAIT;WAITFORDELAY'00:00:03';--Wait for 3 seconds
RAISERROR('4) Calculating',0,0)WITHNOWAIT;WAITFORDELAY'00:00:02';--Wait for 2 seconds
Thanks to RAISEERROR when executing this stored procedure we will show this output.
Notice the Progress property. We will use it later in the last part of the post, 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:
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.
Third: Finally let’s create a simple Windows Form with just a button, a progress bar and a list box).
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? Just use the state argument of the RAISERROR method on the SP like this:
CREATEPROCEDURE[dbo].[TestLongRunningSP]ASBEGINRAISERROR('1) Starting',0,10)WITHNOWAIT;WAITFORDELAY'00:00:03';--Wait for 3 seconds
RAISERROR('2) Fetching data',0,30)WITHNOWAIT;WAITFORDELAY'00:00:05';--Wait for 5 seconds
RAISERROR('3) Some other actions',0,60)WITHNOWAIT;WAITFORDELAY'00:00:03';--Wait for 3 seconds
RAISERROR('4) Calculating',0,70)WITHNOWAIT;WAITFORDELAY'00:00:02';--Wait for 2 seconds