ASP.NET : show progress of a stored procedure

Ok, lets break this up a bit.

As you fair note and state, its kind of easy to setup some table to "process" the routines. The challenge part is getting this information BACK to the client-side browser.

The progress would be very easy to get from the server using another query.

Hum, not really!

However, I would take your "table" idea, and run with that idea (that’s a good idea here).

However, while we can’t really show the progress for EACH stored procedure, we can certainly with ease show the progress of EACH stored procedure being called.

In other words, say the user picks 5 options (out of 10) to run?

Then we can certainly have code to call each stored procedure, and we can certainly update our progress as each stored procedure is done.

In other words, for ease of design, and ESPECIALLY our desired ability to update the client-side browser?

I suggest we move that table of "processing" to be done to the application side and NOT use SQL server for that task list.

I suppose it depends, and if the SQL stored procedures take a huge amount of time, then this approach I am suggesting here probably not the best. But, since you ALREADY asking this question, then I have to "reasonable" assume the time taken for each stored procedure call is not too large anyway, right?

And since we need/want the user to select the list of things to process? Then might as well get double duty out of that table! (By double duty, lets use that table for the list of things to do, but ALSO for user selection, and ALSO for the progress of how far we are along!! So, now we killing multiple birds with one stone so to speak.

So, our simple table:

Ok so now our grid.

And we don’t actually have to wrap all this in an update panel, but let’s do so.

So, our grid view:

        <asp:UpdatePanel ID="UpdatePanel1" runat="server">
            <ContentTemplate>

                <h3>Select Items to Process</h3>
                <asp:GridView ID="GridView1" runat="server"
                    DataKeyNames="ID"
                    AutoGenerateColumns="false"
                    CssClass="table" Width="40%">
                    <Columns>
                        <asp:BoundField DataField="ProcNameRun" HeaderText="Procedure" />
                        <asp:BoundField DataField="Description" HeaderText="Task Description" />
                        <asp:TemplateField HeaderText="Select to Process"
                            HeaderStyle-HorizontalAlign="Center"
                            ItemStyle-HorizontalAlign="Center">
                            <ItemTemplate>
                                <asp:CheckBox ID="chkSel" runat="server" />
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Status"
                            HeaderStyle-HorizontalAlign="Center"
                            ItemStyle-HorizontalAlign="Center">
                            <ItemTemplate>
                                <asp:Label ID="lblStatus" runat="server" Width="150px" Font-Bold="true">
                                </asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                </asp:GridView>
                <asp:Button ID="runOne" runat="server" Text="runone"
                    ClientIDMode="Static"
                    OnClick="runOne_Click"
                    Style="display: none" />

                <asp:HiddenField ID="ProcNum" runat="server" ClientIDMode="Static" />

                <div id="startarea" runat="server">
                    <div style="float: left">
                        <asp:Button ID="cmdStart" runat="server" Text="Start the Reactor!"
                            CssClass="btn btn-lg"
                            OnClick="cmdStart_Click" />
                    </div>
                    <div style="float: left; margin-left: 20px">
                        <asp:Label ID="lblbutMsg" runat="server" Text=""
                            Style="color: red; font: bold">
                        </asp:Label>
                    </div>
                </div>
                <div id="processarea" runat="server" style="clear: both; float: left; display: none">
                    <asp:Label ID="Label1" runat="server" Font-Size="Large" Text="Processing..."></asp:Label>
                    <img src="../Content/wait2.gif" style="width: 16px; height: 16px" />
                    <br />

                    <asp:Label ID="lblStep" runat="server" Text="Step"
                        Font-Size="Larger" Font-Bold="true"></asp:Label>
                </div>
                </div>
            </ContentTemplate>
        </asp:UpdatePanel>

Below the Grid, we have a button area (to start processing), and a simple label for a message (say when user does not select anything).

And right after that, we have a "processing" area that shows what step we are on, and a "spinner gif".

And right after the update panel we have a wee bit of JavaScript code.
(I also assumed jQuery here).

So, we have this:

    <script>
        var prm = Sys.WebForms.PageRequestManager.getInstance();
        prm.add_endRequest(myrunone);

        function myrunone() {
            // run one step of processing
            var MyStep = $('#ProcNum').val()

            if (MyStep >= 0) {
                $('#runOne').click()
            }
        }
    </script>

Now, all the above script does? Well, an update panel does not trigger "page ready client side, so the above does quite much the same thing, but in fact triggers our update panel to run again. (Any button clicked inside of an update panel is a post-back for that panel.

So, now the code:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If Not IsPostBack Then
        ProcNum.Value = "-1"
        LoadGrid()
    End If

End Sub


Sub LoadGrid()

    GridView1.DataSource =
        MyRst("SELECT * FROM tblProcess ORDER BY ID")
    GridView1.DataBind()

End Sub

Ok, that loads up our gird, and we now have this:

(And to be fair, one might hide/not show the actual SQL stored procedure that we going to call – I included that for this demo. (Probably a good idea to hide that one column, and doing so with a visible = false will NOT render that content client side (don’t want users changing the stored procedure name!!!).

Ok, so now the button click code (start the reactor).

Protected Sub cmdStart_Click(sender As Object, e As EventArgs)

    Dim sProcID As String = ""
    For Each gRow As GridViewRow In GridView1.Rows
        Dim ckBox As CheckBox = gRow.FindControl("chkSel")
        If ckBox.Checked Then
            If sProcID <> "" Then sProcID &= ","
            sProcID &= gRow.RowIndex
        End If
    Next

    ProcNum.Value = "-1"
    If sProcID = "" Then
        ' no process selected, give message
        lblbutMsg.Text = "No Process selected<br/>Please select at least one Process"
        Return
    Else
        lblbutMsg.Text = ""
    End If

    ' setup the process loop
    ProcNum.Value = "0"
    ViewState("ProcList") = Split(sProcID, ",")
    startarea.Style.Add("display", "none")      ' hide start button area
    processarea.Style.Add("display", "inline")  ' show procesisng area

    Call ProcessInfo()

End Sub

So, in above, we gather up the selected rows, create an array of row "id", and then save that into view state.

So, when you click start, the update panel updates, and then the client-side JavaScript code triggers the button click, and we now run code behind. (And the button not clicked from client side if process number is < 0 (i.e.: -1).

A routine to setup the information, highlight the one grid row.

Sub ProcessInfo()
    ' get the grid row
    Dim pList() As String = ViewState("ProcList")
    Dim gIndex As Integer = pList(ProcNum.Value)
    Dim gRow As GridViewRow = GridView1.Rows(gIndex)

    ' turn grid row to light blue
    gRow.Style.Add("background-color", "lightsteelblue")
    Dim lblStatus As Label = gRow.FindControl("lblStatus")
    lblStatus.Text = "Processing..."    ' update row status
    lblStep.Text = gRow.Cells(1).Text   ' update process area text

End Sub

And now of course our routine to process (call) the one stored procedure.

(This routine is called by the client-side JavaScript click).

Protected Sub runOne_Click(sender As Object, e As EventArgs)

    Dim pList() As String = ViewState("ProcList")
    Dim gIndex As Integer = pList(ProcNum.Value)
    Dim gRow As GridViewRow = GridView1.Rows(gIndex)
    Dim sProcTorun As String = gRow.Cells(0).Text

    Dim cmdSQL As New SqlCommand(sProcTorun)
    cmdSQL.CommandType = CommandType.StoredProcedure
    MyRstPE(cmdSQL)

    ' one SQL process done, 
    gRow.Style.Add("background-color", "white")
    Dim lblStatus As Label = gRow.FindControl("lblStatus")
    lblStatus.Text = "Complete!"
    Dim chkSel As CheckBox = gRow.FindControl("chkSel")
    chkSel.Checked = False
    ProcNum.Value += 1

    If ProcNum.Value < pList.Length Then
        ProcessInfo()   ' we have more processing to do, setup 
    Else
        ProcNum.Value = "-1"
        startarea.Style.Add("display", "inline")    ' show start button area
        processarea.Style.Add("display", "none")    ' hide procesisng area
    End If

End Sub

And that’s about it.

it not really a lot of code, and no worse than trying to setup a complex asynchronous system.

So, the end result is now this:

So, I can’t think of a code solution that would be "much less" here.

Most of that code is for UI stuff, and then included in that loop is the calling of the stored procedures.

And for FYI and completeness? I used 2 of my helper routines, but I sure everyone has such routines to save one wearing out keyboards for simple SQL statements, or calling some stored procedure.

Public Sub MyRstPE(cmdSQL As SqlCommand)
    Dim rstData As New DataTable
    Using conn As New SqlConnection(My.Settings.TEST5)
        Using (cmdSQL)
            cmdSQL.Connection = conn
            conn.Open()
            cmdSQL.ExecuteNonQuery()
        End Using
    End Using
End Sub

Public Function MyRst(strSQL As String) As DataTable
    Dim rstData As New DataTable
    Using conn As New SqlConnection(My.Settings.TEST5)
        Using cmdSQL As New SqlCommand(strSQL, conn)
            conn.Open()
            rstData.Load(cmdSQL.ExecuteReader)
            rstData.TableName = strSQL
        End Using
    End Using
    Return rstData
End Function

So, by "moving" each of the stored procedure calls to the code behind then we can rather easy track and show a progress of each routine. This approach does not require a timer system, no polling, nor any ajax calls.

So, each process will take as long as the stored procedure call.

My test/example stored proc was this:

CREATE PROCEDURE [dbo].[Proc1] 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    WAITFOR DELAY '00:00:03'
END