Geeks With Blogs
Nat Luengnaruemitchai Geek Blog

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.

Posted on Monday, September 20, 2004 11:43 PM | Back to top


Comments on this post: Report progress from SQL Server

# re: Report progress from SQL Server
Requesting Gravatar...
Please Help!
I've tried this what you wrote there, and in my case the application is retrieving info at the end (the progress bar is updated at end of the stored procedure...)

What did I do wrong?
Could you give me a full example where this works?
Thanks
Frederic
Left by Frederic Zawadzki on Oct 25, 2004 10:28 AM

# re: Report progress from SQL Server
Requesting Gravatar...
1. Make sure that you use "WITH NOWAIT" statement after RAISERROR.
2. If you do so and it still doesn't work. Check your connection string. I have no clue but I would guess that TCP/IP connection is the best bet (specify "Network Library=DBMSSOCN;" in the connection string). It may not work if you connect using RPC.
3. Also make sure that you don't use ExecuteNonQuery() since ExecuteNonQuery may collect info and fire the event only when everything is done.
Left by Nat on Oct 25, 2004 11:07 PM

# re: Report progress from SQL Server
Requesting Gravatar...
Unfortunately! None o those solution....
here is my code:

On the form1 I have a button which is calling a Class:

private void btnOK_Click(object sender, System.EventArgs e)
{
string Conn = "Network Library=DBMSSOCN;Password=" + textBox3.Text + ";Persist Security Info=True;User ID=" + textBox2.Text + ";Initial Catalog=PanEuropean;Data Source=" + textBox1.Text;
UpdateData UD = new UpdateData();
UD.Load(Conn);


}


here is my class:

using System;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
namespace PanEuropean
{
/// <summary>
/// Summary description for UpdateData.
/// </summary>
public class UpdateData
{


public UpdateData()
{
//
// TODO: Add constructor logic here
//
}
public void Load(string e)
{
SqlConnection connection = new SqlConnection (e);

try
{

connection.Open();
connection.InfoMessage += new SqlInfoMessageEventHandler(ProgressStatus);

}

catch (Exception)
{
MessageBox.Show ("The connection was not possible! Please verify your connection parameters!");

Application.Exit();

}



SqlCommand command = new SqlCommand();
SqlTransaction transaction = connection.BeginTransaction();
command.Transaction=transaction;
command.Connection=connection;

try
{
command.CommandText="updateData";
command.CommandType=CommandType.StoredProcedure;
command.CommandTimeout=0;
command.ExecuteReader();
Application.DoEvents();
transaction.Commit();
connection.Close();
MessageBox.Show ("The Update is complete");


}

catch (Exception ex)
{
MessageBox.Show("Exception thrown when updating DB: ", ex.Message);
transaction.Rollback();
}

}
private void ProgressStatus(object sender,SqlInfoMessageEventArgs e)
{
if (e.Errors.Count>0)
{
string message = e.Errors[0].Message;
int state = e.Errors[0].State;
Form1 F1 = new Form1();
F1.label4.Text=message;
F1.progressBar1.PerformStep();
F1.progressBar1.Refresh();

}

}
}
}


Please could you have a look? Thanks
Frederic
Left by Frederic Zawadzki on Oct 26, 2004 8:32 AM

# re: Report progress from SQL Server
Requesting Gravatar...
Hmm... that might be because of the other issue.
1. ProgressStatus can be called from a thread which is not UI Thread
2. command.ExecuteReader(); will block UI Thread until the stored proc execution is done. So The UI won't be updated until then.
Left by Nat on Oct 26, 2004 11:36 AM

# re: Report progress from SQL Server
Requesting Gravatar...
try change ur method to be like this...

private void ProgressStatus(object sender,SqlInfoMessageEventArgs e)
{
if (e.Errors.Count>0)
{
string message = e.Errors[0].Message;
int state = e.Errors[0].State;
Form1 F1 = new Form1();
F1.label4.Text=message;
this.BeginInvoke(new MethodInvoker(F1.progressBar1.PerformStep());
this.BeginInvoke(new MethodInvoker(F1.progressBar1.Refresh());
Application.DoEvents();
}
Left by Nat on Oct 26, 2004 10:39 AM

# re: Report progress from SQL Server
Requesting Gravatar...
Hi,
Thanks for your answers...

Unfortunately, still does not work! the methode invoker asked me for "Method"....

don't know really what to do...
I even tried to use MDI..

Thanks for your help.
Frederic
Left by Frederic Zawadzki on Oct 29, 2004 6:33 AM

# re: Report progress from SQL Server
Requesting Gravatar...
Put the code that calls the stored procedure in a seperate thread and make sure not to use executenonquery even if you're not returning records.
Left by Greg on May 09, 2005 2:28 PM

# re: Report progress from SQL Server
Requesting Gravatar...
Try the following code to make it work:

sqlCommand = sqlConnection.CreateCommand();
sqlCommand.CommandText = "EXEC your_stproc";
sqlCommand.CommandTimeout = 0;
sqlDataReader = sqlCommand.ExecuteReader(Commandbehaviour.CloseConnection);

Good luck,

Ted.
Left by Ted on Oct 04, 2005 1:36 AM

# re: Report progress from SQL Server
Requesting Gravatar...
Anyone have any ideas how to get the Percent Complete of a Restore or backup operation?

I'm trying to make it so that I can get progress for TSQL commands for backup and restore just like Enterprise Manager does and I can't seem to get it to report the status until the end, using the above way, and NOWAIT doesn't seem to be valid on the Restore or Backup TSQL commands.

Thanks!
Left by James Hancock on Mar 19, 2006 8:58 PM

# re: Report progress from SQL Server
Requesting Gravatar...
Very good article. It works with SQLInfoMessageEventHandler, unfortunately it doesn't work perfectly with OleDbInfoMessageEventHandler as it seems you can get just the message and not the state (SQLState) for OleDb.
Left by Rosario on Sep 05, 2006 2:19 AM

# re: Report progress from SQL Server
Requesting Gravatar...
@James Hancock:

For management-type operations like backup/restore you want to use DMO (2000/native) or SMO (2005/managed).

In DMO you must create a COM object that exposes certain interfaces (PercentComplete, Complete, NextMedia) and AtlAdvise to a DMO restore object.

SMO is much nicer, you register EventHandlers with a Microsoft.SqlServer.Management.Smo.Restore object. See http://www.codeproject.com/useritems/BackupRestoreWithSmo.asp

Left by Aidan Ryan on May 02, 2007 7:32 AM

# re: Report progress from SQL Server
Requesting Gravatar...
Great work,

This was exactly what I needed, those pregnant pauses as I upload data are now gone and a progress bar and message system prevail.

Thank you!
Left by Paul on Dec 11, 2008 12:43 AM

Your comment:
 (will show your gravatar)


Copyright © Nat Luengnaruemitchai | Powered by: GeeksWithBlogs.net