Use MVC controller code or SQL Agent scheduled job?

Question

I have a basic asp.net core web app connected to a database for an "dynamic employee schedule". The main page displays who is available next, who is already scheduled or if they are off work. Each database record has a "Status" field containing values such as, "Off Shift", "Scheduled", or "Available"
I want to update this field automatically based on other static records such as Shift Start/End Time, Booked Schedule, etc.
Is it better to write the logic code into the View Controller for checking and setting the status every time the page is loaded, or create a SQL Agent scheduled job to update the database every 15 or 30 minutes? Or other better way that I am unaware of?

Thanks!

Answer 1

Hi @Chris Allen ,

Is it better to write the logic code into the View Controller for checking and setting the status every time the page is loaded, or create a SQL Agent scheduled job to update the database every 15 or 30 minutes? Or other better way that I am unaware of?

Both the above methods have advantages and disadvantages:

Write the logic code in the View Controller: By using this method, we could get the updated status in time, but if the logic code spends too much time, it will cause the lower performance.

Create a SQL Agent scheduled job: For the MVC controller, there is no need to change status, just access the table could get the updated status. But since the SQL job is running every 15 or 30 minutes, it might cause the status not updated in time.

So, you could consider creating a SQL Stored Procedure, in the Stored Procedure, you could query the table with join clause and the status logic code, and then get the latest status. In the MVC controller, you can call this stored procedure.

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best Regards,
Dillion

Answer 2

Is it better to write the logic code into the View Controller for checking and setting the status every time the page is loaded

You should keep any database code out of the controller.

The controller is for flow control, as discussed in Understanding Models topic in the link.

https://learn.microsoft.com/en-us/aspnet/mvc/overview/older-versions-1/overview/understanding-models-views-and-controllers-cs

At best for the MVC solution, the code you're talking about should be executed by a domain model object in the Models folder called upon by the controller.

https://deviq.com/terms/kinds-of-models

You could also use the SQLCLR stored procedure, which could be execued by MS SQL Server Service Broker in an async manner that is callable by an ASP.NET solution such as MVC.

https://www.c-sharpcorner.com/UploadFile/84c85b/using-clr-with-sql-server-2012/

Answer 3

Just to close the loop on this, here is what I ended up with. Don't judge me on the WorkingDays part, I know there's a better solution but haven't had time to get to it yet :)

SQL Stored Procedure

CREATE PROCEDURE [dbo].[UpdateEmployeeStatus]  
	-- Add the parameters for the stored procedure here  
	@currentdatetime datetime,  
	@currenttime AS time,  
	@workdayquery char(5)  
  
AS  
BEGIN  
	-- SET NOCOUNT ON added to prevent extra result sets from  
	-- interfering with SELECT statements.  
	SET NOCOUNT ON;  
  
    -- Insert statements for procedure here  
	UPDATE dbo.EmployeeModel  
	SET Status =  
		CASE  WHEN (ExtendedOOF = 'false') THEN  
			CASE  
				WHEN (@currenttime NOT BETWEEN StartShiftTime AND EndShiftTime OR WorkingDays NOT LIKE @workdayquery) THEN 'Off Shift'  
				WHEN (ScheduledTimeEndStamp > @currentdatetime) THEN 'Booked'  
				ELSE 'Available'  
			END  
			ELSE 'Out of Office'  
		END  
END  

Code in ViewController

        public async Task<IActionResult> Index()  
        {  
                // Collect current Date, Time and Day for Employee status update  
            string currentdatetime = DateTime.Now.ToString(); // For comparing Scheduled Time [ScheduledTimeEndStamp]  
            string currenttime = DateTime.Now.ToString("T").Substring(0, 8); // For comparing shift times [ShiftStartTime & ShiftEndTime]  
            string workdayquery = "%" + DateTime.Now.ToString("D").Substring(0, 3) + "%"; // For comparing working day of the week [WorkingDays]  
                // Update Employee Status in the database before displaying the data  
            await _context.Database.ExecuteSqlInterpolatedAsync(sql: $"UpdateEmployeeStatus {currentdatetime}, {currenttime}, {workdayquery}");  
  
        //blah blah get list  
  
            return View(await employee.ToListAsync());  
        }