start an sql job from application and run the job in bacground

Question

Hi Team,

I want to start a stored procedure or a job from an asp.net MVC application. This job or stored procedure should continuously run even if the session got expired or the user logout and closes the browser. Also want to show the status of the job when the user login again. Please suggest a best method to implement this functionality.

Prasad Raveendran · Answer

To achieve the functionality of running a background job (stored procedure) that continues to run even after the user logs out or closes the browser, and then showing the status of the job when the user logs in again, you can follow these steps: Background Job Processing: You can use a background job processing framework like Hangfire, Quartz.NET, or Azure Functions to execute your stored procedure as a background job. These frameworks provide mechanisms to enqueue and process jobs independently of the user's session. Persistent Storage for Job Status: You will need a database to store the status and progress of the background job. Create a table to store job-related information, such as job ID, status, progress, and any other relevant details. Logout Handling: When a user logs out or closes the browser, the session is terminated, but the background job should continue running. You can achieve this by decoupling the background job's lifecycle from the user's session. Job Status Display: To show the status of the job when the user logs in again, you can create a page or dashboard that queries the database for the job status based on the user's ID. Display the status and any relevant progress information. Here's a high-level implementation guide: 1. Set Up Background Job Processing: Choose a background job processing framework and integrate it into your ASP.NET MVC application. Configure it to enqueue and process the stored procedure job. 2. Design the Database: Create a database table to store job-related information. This table should include columns like JobId , UserId , Status , Progress , StartTime , EndTime , etc. 3. Enqueue Job on User Action: When the user initiates the job, enqueue a new job in the background job processing framework. Make sure to store the job's ID and related information in the database. 4. Independent Job Processing: The background job processing framework will execute the stored procedure independently of user sessions. It will keep processing even if the user logs out or closes the browser. 5. Update Job Status: As the job progresses, update the job's status and progress in the database. This could be done in the job itself at various stages. 6. User Login and Status Display: When the user logs in again, retrieve the job status and progress from the database based on the user's ID. Display this information on a user dashboard or relevant page. Remember that the specifics of implementation may vary based on the chosen background job processing framework and your application's architecture. Be sure to consult the documentation of the framework you choose for detailed implementation steps. By decoupling the background job from the user's session and leveraging a persistent storage solution, you can achieve the desired functionality of running a job independently and displaying its status across user sessions.