question

ChrisAllen-3099 avatar image
0 Votes"
ChrisAllen-3099 asked ChrisAllen-3099 commented

Use MVC controller code or SQL Agent scheduled job?

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!

sql-server-transact-sqldotnet-aspnet-core-mvc
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZhiLv-MSFT avatar image
0 Votes"
ZhiLv-MSFT answered ChrisAllen-3099 commented

Hi @ChrisAllen-3099,

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you, I'll will research this.

0 Votes 0 ·
ChrisAllen-3099 avatar image
0 Votes"
ChrisAllen-3099 answered ChrisAllen-3099 commented

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());
         }



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the github link. It really helps to see examples of these concepts put into practice.

0 Votes 0 ·
DuaneArnold-0443 avatar image
0 Votes"
DuaneArnold-0443 answered ChrisAllen-3099 commented

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://docs.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/



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks again for the quick response! I am obviously still learning but just had a feeling there must be a better solution to this. I'll research the information you provided.

0 Votes 0 ·