Display of Command execution.

  • Thread starter Thread starter news.microsoft.com
  • Start date Start date
N

news.microsoft.com

Hi,
I have some queries with a command time out of some 10 minutes. This is
taking solong that users offen think the app is hanging. To prevent them of
complaining i want to display an progress indicator. Is there anyone who can
tell me on how to deal with this in asp.net and ado.net. Is there a way or
event wich is fired by the ado.net CommandExecuter????
Thanks for Help.
Btw: I Use C# but VB samples arer also welcom.

Regards,
Rene
 
But what is essential way to inform client application what is progress of
specific job (stored proc)? Is any common api to do it?
I made think about it some day and I supposed that possible method is to
make additional table that will contain "messages" from sp. And application
will check the table on other thread (and other db connection) and if need
progress bar will be updated.

Regards,
Grzegorz
 
You can use the InfoMessage Event which sends back info about errors witha
severity of 13 or less (I may be off by a number or two on the severity
level but it's in the documentation on .InfoMessage). You can send back
Print statements for instance and catch those.
 
U¿ytkownik "W.G. Ryan eMVP said:
You can use the InfoMessage Event which sends back info about errors witha
severity of 13 or less (I may be off by a number or two on the severity
level but it's in the documentation on .InfoMessage). You can send back
Print statements for instance and catch those.
Click to expand...

Well, it looks fine, but I have made some tests and it does not work.
My stored procedure:

Create Procedure MyVeryLongStoredProc
As
RaisError('start first step stored proc', 10, 1) --must be below 11
Waitfor Delay '00:00:01'
RaisError('first step was done', 1, 1)

Print 'second step' --the same efect as RaisError
Waitfor Delay '00:00:05'
Print 'second step was done'

Print 'final step'
Waitfor Delay '00:00:05'
Print 'final step of sp was done'
Go

And then in c#:

using System;
using System.Data.SqlClient;

namespace TestInfoMessageEvent
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
TestInfoMessageEv t = new TestInfoMessageEv();
t.Start();
System.Console.ReadLine();
}
}
public class TestInfoMessageEv
{
SqlConnection mySqlC =
new SqlConnection("Persist Security Info=False;" +
"Integrated Security=SSPI;" +
"server=(local);Database=Testy;");
SqlCommand myC;
public TestInfoMessageEv()
{
mySqlC.InfoMessage +=
new SqlInfoMessageEventHandler(mySqlC_InfoMessage);
}
public void Start()
{
mySqlC.Open();

myC = new SqlCommand("MyVeryLongStoredProc", mySqlC);
myC.ExecuteNonQuery();

mySqlC.Close();
}

private void mySqlC_InfoMessage(object sender,
SqlInfoMessageEventArgs e)
{
System.Console.WriteLine(e.Message.ToString());
}
}
}

All messages are displayed in the same time. I suppose that I should use
additional thread, but I some experimented without positive results:

public void Start()
{
System.Threading.ThreadStart ts = new ThreadStart(ExecMyLongProc);
System.Threading.Thread t = new Thread(ts);
t.Start();
}
private void ExecMyLongProc()
{
mySqlC.Open();

myC = new SqlCommand("MyVeryLongStoredProc", mySqlC);
myC.ExecuteNonQuery();

mySqlC.Close();
}

Could you get me an example?

Regards,
Grzegorz
 
Back
Top