Report progress during long running stored procedure : The Official Microsoft ASP.NET Forums

Reply

scheffetz

Member

12 Points

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.

Reply

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

Reply

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.

Thanks,
Dhimant Trivedi
"When the going gets tough, tough gets going."

"Mark as Answer" the post(s) which helped you solve the problem