Display of Command execution.

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
 
G

Grzegorz Danowski

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
 
W

W.G. Ryan eMVP

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.
 
G

Grzegorz Danowski

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.

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top