In my application I am trying to report the progress to the user while executing a long running stored procedure. I found an example on the web to help me along, and I nearly have it working, but now I am stuck.
The stored procedure raises errors with the severity <10 to send as messages back to the application. My application gets the messages and displays them in the debug console in real time, so that much is working. At the moment I am just trying to get a
Label on my aspx page to update with the received messages, but I cannot seem to get that to work. Once the stored procedure is finished, the label updates with the last message to be received, but does nothing untill then....
I am not sure what I need to do to get my label to update with the message as they are received.
In my connection string I have Asynchronous Processing=true
..........
WHILE @@FETCH_STATUS = 0
BEGIN
IF OBJECT_ID('tempdb..#dq_businessRules') IS NOT NULL
BEGIN
DROP Procedure #dq_businessRules
END
set @procedure = 'create procedure #dq_businessRules (@output int OUTPUT) as
select @output = count(*) from('
set @procedure2 = ')b'
--set @query = @ruleSQl
set @query = REPLACE(@ruleSQl, '@periodid', '' + @vPeriodID + '')
exec (@procedure+@query+@procedure2)
exec #dq_businessRules @output = @result output
SET @NewNumber=CAST(@theCount2 AS VARCHAR(5))
RAISERROR('Completed Query %s of %d', 2, 2, @NewNumber, @theCount) WITH NOWAIT
insert into dq_app_results (ruleID, ruleVersion, executionID, results)
values(@ruleID, @ruleVersion, @executionID, @result
FETCH NEXT from ruleCursor
INTO @ruleID, @ruleVersion, @ruleSQl;
END
...........
Code Behind
protected void RunStoredProcedure()
{
_reset.Reset();
try
{
using (SqlConnection conn = new SqlConnection(ConnectionClass.GlobalVar))
{
using (SqlCommand cmd2 = new SqlCommand("dq_businessRules_execRules", conn))
{
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.CommandTimeout = 600;
cmd2.Parameters.Add(new SqlParameter("@vGroupID", groupID));
cmd2.Parameters.Add(new SqlParameter("@vPeriodID", periodID));
cmd2.Parameters.Add(new SqlParameter("@vSwitch", vSwitch));
cmd2.Connection.InfoMessage += ConnectionInfoMessage;
AsyncCallback result = ReaderCallback;
cmd2.Connection.Open();
cmd2.BeginExecuteReader(result, cmd2);
_reset.WaitOne();
}
}
}
catch (SqlException ex)
{
System.Diagnostics.Debug.Write("Problem with executing command! - [{0}]", ex.Message);
}
}
protected void ConnectionInfoMessage(object sender, SqlInfoMessageEventArgs e)
{
if (e.Errors.Count > 0)
{
// Check to make sure we are information only messages
Console.WriteLine("Recieved {0} messages", e.Errors.Count);
foreach (SqlError info in e.Errors)
{
if (info.Class > 9) // Severity
{
System.Diagnostics.Debug.Write(" ", info.Message);
}
else
{
System.Diagnostics.Debug.Write(" ", info.Message);
Label2.Text = info.Message;
}
}
}
else
{
System.Diagnostics.Debug.Write("Recieved Connection Info Message : {0}", e.Message);
}
}
private static void ReaderCallback(IAsyncResult result)
{
SqlCommand command = (SqlCommand)result.AsyncState;
try
{
if (command != null)
{
System.Diagnostics.Debug.Write("Waiting for completion of the Async call");
command.EndExecuteReader(result);
}
}
catch (SqlException ex)
{
System.Diagnostics.Debug.Write("Problem with executing command! - [{0}]", ex.Message);
}
finally
{
System.Diagnostics.Debug.Write("Completed call back so signal main thread to continue....");
_reset.Set();
}
}
Apologies in advance if this is not an appropriate forum. I was not sure which group to post this in.
What I'm feeling is that, there is nothing wrong with your code and the sample that you've used for that, but the only difference is that the sample (from codeproject) is built using a windows forms application (see the Console.WriteLine) and you're trying
to implement that in a web app.
The vary nature and significance of both types of applications is different. Win apps are statefull and Web apps are stateless. Implementing ajax like functionality will give you some edge by implementing asynchronous processes, but the only thing is that
the client and the server remain disconnected and talk only during the exchange of information (Request/Response) initiated from
client side only. This means, even if your stored procedure is returning the information to the calling code (your page code behind), the client doesn't get is until it requests a fresh copy of response again.
So, one quick (and probably ugly) way that comes to my mind is that, you'll have to implement some kind of logic using your client side code that keeps pinging the web server after a predefined time interval for fresh information, and the web server keeps
collecing the messages recieved from SQL Server and send them to the client when requested.
Another way is to use
http push, although its part of HTML5 standards but still sounds like a thing of tomorrow.
scheffetz
Member
12 Points
50 Posts
Report progress during long running stored procedure
May 02, 2012 08:33 AM|LINK
In my application I am trying to report the progress to the user while executing a long running stored procedure. I found an example on the web to help me along, and I nearly have it working, but now I am stuck.
The stored procedure raises errors with the severity <10 to send as messages back to the application. My application gets the messages and displays them in the debug console in real time, so that much is working. At the moment I am just trying to get a Label on my aspx page to update with the received messages, but I cannot seem to get that to work. Once the stored procedure is finished, the label updates with the last message to be received, but does nothing untill then....
I am not sure what I need to do to get my label to update with the message as they are received.
In my connection string I have Asynchronous Processing=true
This is the example I found:
http://www.codeproject.com/Articles/42266/ADO-NET-Asynchronous-SQL-Calls
ASPX page
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server"> <asp:ScriptManager ID="ScriptManager1" runat="server"> </asp:ScriptManager> <asp:UpdatePanel ID="UpdatePanel1" runat="server"> <ContentTemplate> <asp:Label ID="Label2" runat="server" Text=""></asp:Label> </ContentTemplate> </asp:UpdatePanel> ..........Stored Procedure
.......... WHILE @@FETCH_STATUS = 0 BEGIN IF OBJECT_ID('tempdb..#dq_businessRules') IS NOT NULL BEGIN DROP Procedure #dq_businessRules END set @procedure = 'create procedure #dq_businessRules (@output int OUTPUT) as select @output = count(*) from(' set @procedure2 = ')b' --set @query = @ruleSQl set @query = REPLACE(@ruleSQl, '@periodid', '' + @vPeriodID + '') exec (@procedure+@query+@procedure2) exec #dq_businessRules @output = @result output SET @NewNumber=CAST(@theCount2 AS VARCHAR(5)) RAISERROR('Completed Query %s of %d', 2, 2, @NewNumber, @theCount) WITH NOWAIT insert into dq_app_results (ruleID, ruleVersion, executionID, results) values(@ruleID, @ruleVersion, @executionID, @result FETCH NEXT from ruleCursor INTO @ruleID, @ruleVersion, @ruleSQl; END ...........Code Behind
protected void RunStoredProcedure() { _reset.Reset(); try { using (SqlConnection conn = new SqlConnection(ConnectionClass.GlobalVar)) { using (SqlCommand cmd2 = new SqlCommand("dq_businessRules_execRules", conn)) { cmd2.CommandType = CommandType.StoredProcedure; cmd2.CommandTimeout = 600; cmd2.Parameters.Add(new SqlParameter("@vGroupID", groupID)); cmd2.Parameters.Add(new SqlParameter("@vPeriodID", periodID)); cmd2.Parameters.Add(new SqlParameter("@vSwitch", vSwitch)); cmd2.Connection.InfoMessage += ConnectionInfoMessage; AsyncCallback result = ReaderCallback; cmd2.Connection.Open(); cmd2.BeginExecuteReader(result, cmd2); _reset.WaitOne(); } } } catch (SqlException ex) { System.Diagnostics.Debug.Write("Problem with executing command! - [{0}]", ex.Message); } } protected void ConnectionInfoMessage(object sender, SqlInfoMessageEventArgs e) { if (e.Errors.Count > 0) { // Check to make sure we are information only messages Console.WriteLine("Recieved {0} messages", e.Errors.Count); foreach (SqlError info in e.Errors) { if (info.Class > 9) // Severity { System.Diagnostics.Debug.Write(" ", info.Message); } else { System.Diagnostics.Debug.Write(" ", info.Message); Label2.Text = info.Message; } } } else { System.Diagnostics.Debug.Write("Recieved Connection Info Message : {0}", e.Message); } } private static void ReaderCallback(IAsyncResult result) { SqlCommand command = (SqlCommand)result.AsyncState; try { if (command != null) { System.Diagnostics.Debug.Write("Waiting for completion of the Async call"); command.EndExecuteReader(result); } } catch (SqlException ex) { System.Diagnostics.Debug.Write("Problem with executing command! - [{0}]", ex.Message); } finally { System.Diagnostics.Debug.Write("Completed call back so signal main thread to continue...."); _reset.Set(); } }Apologies in advance if this is not an appropriate forum. I was not sure which group to post this in.
rio.jones
Member
246 Points
53 Posts
Re: Report progress during long running stored procedure
May 02, 2012 09:31 AM|LINK
check this link this may help you out
http://codingrecipes.com/ajax-beginner-ajax-tutorial-display-a-progress-bar-or-a-loading-message
dhimant
Star
8170 Points
1152 Posts
Re: Report progress during long running stored procedure
May 02, 2012 09:56 AM|LINK
What I'm feeling is that, there is nothing wrong with your code and the sample that you've used for that, but the only difference is that the sample (from codeproject) is built using a windows forms application (see the Console.WriteLine) and you're trying to implement that in a web app.
The vary nature and significance of both types of applications is different. Win apps are statefull and Web apps are stateless. Implementing ajax like functionality will give you some edge by implementing asynchronous processes, but the only thing is that the client and the server remain disconnected and talk only during the exchange of information (Request/Response) initiated from client side only. This means, even if your stored procedure is returning the information to the calling code (your page code behind), the client doesn't get is until it requests a fresh copy of response again.
So, one quick (and probably ugly) way that comes to my mind is that, you'll have to implement some kind of logic using your client side code that keeps pinging the web server after a predefined time interval for fresh information, and the web server keeps collecing the messages recieved from SQL Server and send them to the client when requested.
Another way is to use http push, although its part of HTML5 standards but still sounds like a thing of tomorrow.
Dhimant Trivedi
"When the going gets tough, tough gets going."
"Mark as Answer" the post(s) which helped you solve the problem
scheffetz
Member
12 Points
50 Posts
Re: Report progress during long running stored procedure
May 02, 2012 01:51 PM|LINK
Still stuck on this issue...
Would a Client Callback seem like a good way to approach the problem?
Maybe have it on a timer of some type...
meowthecat
Member
2 Points
2 Posts
Re: Report progress during long running stored procedure
Feb 15, 2013 01:47 AM|LINK
Did you ever firgure this out?