In my ASP.NET web application I want to show to the end user the progress of the stored procedure.
The stored procedure computes a batch of calculations depending on how many the user added to the task basket.
It works like this:
- the user after having selected the tasks clicks the button on the page
- on server side tasks are added to a table in SQL Server
- then a stored procedure is executed in SQL Server which calculates tasks one by one
Something like this:
Protected Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
SaveTasks()
sqlWorker.SelectCommand = "EXECUTE dbo.spCalc " & hidBatchValue
sqlWorker.Select(DataSourceSelectArguments.Empty) 'sqldatasource
End Sub
The progress would be very easy to get from the server using another query.
The problem is that the page is waiting for the stored procedure to be ended.
I tried to use a timer on the update panel but it doesn't work until the stored procedure has completed.
I also tried async (marked the page async, made the button call also async), but it still waits for the stored procedure to complete.
Public Async Function GetDataSetAsync(sconn As String, ssql As String) As Task(Of Integer)
Dim newconnection As New SqlClient.SqlConnection(sconn)
Dim mycommand = New SqlClient.SqlCommand(ssql, newconnection)
mycommand.CommandType = CommandType.Text
Await newconnection.OpenAsync().ConfigureAwait(False)
Return Await mycommand.ExecuteNonQueryAsync().ConfigureAwait(False)
End Function
I know that I am using very simple measures for my web app so I would appreciate you advise on how to solve the problem.
2条答案
按热度按时间9ceoxa921#
OK, let's break this up a bit.
As you note, it's 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.
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 reasonably 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, we might as well get double duty out of that table! (By double duty, let's 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:
| ID | ProcNameRun | Description |
| ------------ | ------------ | ------------ |
| 1 | Proc1 | Give dog bowel of water |
| 2 | Proc2 | Put collar on dog |
| 3 | Proc3 | Open door to go outside |
| 4 | Proc4 | Take dog for walk |
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:
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:
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:
That loads up our grid, 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!).
So now the button click code (start the reactor).
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.
And now of course our routine to process (call) the one stored procedure.
(This routine is called by the client-side JavaScript click).
And that's about it.
It's 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 smaller here.
Most of that code is for UI stuff, and then included in that loop is the calling of the stored procedures.
For completeness, I used two of my helper routines. I'm sure everyone has such routines to save one wearing out keyboards for simple SQL statements, or calling some stored procedure.
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:
Edit: Have browser scroll current process row into view
Ok, we can add this markup. (a hidden control in which we set the current label in the row "client id".
So, add this to the markup (right after the gridview).
and then change the script to this:
And in code behind, then we add this:
im9ewurl2#
There's a ton of different ways you can approach this. One option would be to explore using threads - https://learn.microsoft.com/en-us/dotnet/standard/threading/using-threads-and-threading provides some documentation on this. Threads are not a simple tool, though, and it's very easy to mess your system up if you aren't careful with how you use them.
Another option is to use SQL Server Service Broker to make the requests process in an asynchronous manner on the server itself. I'll warn you that service broker is not a simple tool to use. It is, however, quite powerful. If you want to explore it, the MSDN page is here - https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-service-broker?view=sql-server-ver16 . I'd suggest familiarizing yourself with the basics, and if you have any specific questions, you can ask those after.
Yet another option would be to have a separate application handle processing of the stuff completely independently of your website.
The simplest option may just be to have SQL Server Agent run a job which checks the table periodically for data to process, and then runs the stored proc if it finds anything.