Sometimes, your application might be very database centric. You might need to summarize/retrieve data from a big database. Therefore, the query might take a long time to execute such that user cannot bare to see a hang screen for several seconds or minutes without seeing any progress. To solve this problem, you might either create something like animated GIF i.e. hourglass icons or bouncing progress bar like FireFox installer or actually tell the user on the progress of query.
It's pretty simple to do that with ADO.NET/SQL Server. You can simply hook up an event to SqlInfoMessageEventHandler like this.....
{
SqlConnection sqlConnection = new SqlConnection(“................”);
sqlConnection.Open();
sqlConnection.InfoMessage += new SqlInfoMessageEventHandler(ProgressStatus);
.....
// Execute your long running operation
}
private void ProgressStatus(object sender, SqlInfoMessageEventArgs e)
{
if (e.Errors.Count>0) {
string message = e.Errors[0].Message;
int state = e.Errors[0].State;
// Set status of the progress bar
}
}
and in your sql statement/stored procedure if you have multiple statements to execute, you might choose to report the status among those statements i.e.
SELECT ............ FROM .......... INNER JOIN ............. INNER JOIN ............
RAISERROR('Message',10,25) WITH NOWAIT
SELECT ............ FROM .......... INNER JOIN ............. INNER JOIN ............
RAISERROR('Message',10,50) WITH NOWAIT
SELECT ............ FROM .......... INNER JOIN ............. INNER JOIN ............
RAISERROR('Message',10,75) WITH NOWAIT
SELECT ............ FROM .......... INNER JOIN ............. INNER JOIN ............
RAISERROR('Message',10,100) WITH NOWAIT
NOWAIT option will force the message to be delivered to the client immediately instead of waiting until everything is done. To use this option properly, you need to make sure that RPC is the way of communication... in which it should be default.
So whenever your stored procedure finishes running the first statement, RAISERROR will be called and causing ProgressStatus to be called and then you can update the status. Make sure that you run DataAdapter.Fill or SqlCommand.ExecuteReader in a separate thread which is not UI thread. Otherwise the progress bar may not be updated properly. If you are lazy, you can use Application.DoEvents to give some time to repaint the progress bar.