Connect to Database

Scheduler ASP.NET MVC
#1

I am trying to use a sql server table on my timeline scheduler to display employee names on the left hand side and then another table for events associated with each employee. How do I load in the data to accomplish this?

This is how I would like it to look, only use data from my database.

Sch
Sch1688×389 28.3 KB

Here is my code:
Calendar Controller

  using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Web;
  using System.Web.Mvc;


 using DHTMLX.Scheduler;
 using DHTMLX.Common;
using DHTMLX.Scheduler.Data;
using DHTMLX.Scheduler.Controls;

using LotusWorksHolidayTracker.Models;


namespace LotusWorksHolidayTracker.Controllers
{
 public class CalendarController : Controller
{
    public ActionResult Index()
    {
        var sched = new DHXScheduler(this);
        sched.Config.readonly_form = true;
        sched.Views.Clear();
        sched.InitialDate = new DateTime(2019, 1, 1);


        var unit = new UnitsView("timeline", "key");
        sched.InitialView = unit.Name;

        sched.LoadData = true;
        var dbcontext = new LotusworksHTEntities();
        

        var timeline = new TimelineView("timeline", "Employee Name"); // initializes the view
        timeline.FolderEventsAvailable = false;
        timeline.RenderMode = TimelineView.RenderModes.Tree;


        var empList = new List<object>();
        foreach (var employee in dbcontext.Employees)
        {
            empList.Add(new { key = employee.FirstName, label = employee.EmployeeID });
        }
        ViewBag.employees = empList;





        var rooms = new List<object>(){
        new { key = "1", label = "Joe Bloggs"},
        new { key = "2", label = "Joe Bloggs"},
        new { key = "3", label = "Joe Bloggs"},
        new { key = "4", label = "Joe Bloggs"},
        new { key = "5", label = "Joe Bloggs"},
        new { key = "6", label = "Joe Bloggs"},
        new { key = "7", label = "Joe Bloggs"},
        new { key = "8", label = "Joe Bloggs"},
        new { key = "9", label = "Joe Bloggs"},
        new { key = "10", label = "Joe Bloggs"},
        new { key = "11", label = "Joe Bloggs"},
        new { key = "12", label = "Joe Bloggs"},
        new { key = "13", label = "Joe Bloggs"},
        new { key = "14", label = "Joe Bloggs"},
        new { key = "15", label = "Joe Bloggs"},
        
};
        timeline.AddOptions(rooms);


       
       


    
        timeline.FitEvents = false;
        timeline.SectionAutoheight = false;
        timeline.Dy = 25; 
        timeline.X_Unit = TimelineView.XScaleUnits.Day;
        timeline.X_Date = "%j";
        timeline.X_Step = 1;
        timeline.X_Size = 31;
        sched.Views.Add(timeline);
  

        


       sched.TimeSpans.Add(new DHXMarkTime() {
        Day = DayOfWeek.Saturday,
        CssClass = "green_section", 
        SpanType = DHXMarkTime.Type.Default
        });

        sched.TimeSpans.Add(new DHXMarkTime()
        {
            Day = DayOfWeek.Sunday,
            CssClass = "green_section",
            SpanType = DHXMarkTime.Type.Default
        }); 





return View(sched);
#2

Hi,

please try the following code:

        timeline.RenderMode = TimelineView.RenderModes.Tree;


        var empList = new List<object>();
        foreach (var employee in dbcontext.Employees)
        {
            empList.Add(new { key = employee.EmployeeID, label = employee.FirstName });
        }

        timeline.AddOptions(empList);

instead of this:

        timeline.RenderMode = TimelineView.RenderModes.Tree;


        var empList = new List<object>();
        foreach (var employee in dbcontext.Employees)
        {
            empList.Add(new { key = employee.FirstName, label = employee.EmployeeID });
        }
        ViewBag.employees = empList;
	...
        timeline.AddOptions(rooms);

Changed lines are these:

empList.Add(new { key = employee.EmployeeID, label = employee.FirstName });
  • employee id is supposed to go into the key property and the readable name into the label

and this:

timeline.AddOptions(empList);

after you added records into empList variable you can add it into timeline using timeline.AddOptions method.

In your initial code, you’ve put some hardcoded collection into the timeline, I think you want to put the collection from the database instead.
This should add the list of employees into the timeline

Also, regarding this line:

 var timeline = new TimelineView("timeline", "Employee Name");

The second parameter of TimelineView constructor should point to the property of your Event/Task entity which holds the relation between tasks and employees, e.g. Task.EmployeeID - so the timeline know which property of the event object to use in order to place events in the appropriate rows.

So if the property is named Task.EmployeeID constructor should look like this:

 var timeline = new TimelineView("timeline", "EmployeeID");

Regards,
Alex

#3

Hi I’ve changed the code to the following and still no employees are being loaded in??? (I’ll also add in the hardcoded collection (which is commented out) I used it as an example for how I eventually want the scheduler to look!

Here’s the updated code for the Calendar method.

 public ActionResult Index()
    {
        var sched = new DHXScheduler(this);
        sched.DataAction = "Events"; //Specifying a different action, might just use default data one.
        sched.LoadData = true;
        sched.Config.readonly_form = true;
        sched.Views.Clear();
        sched.InitialDate = new DateTime(2019, 1, 1);


        var unit = new UnitsView("timeline", "key");
        sched.InitialView = unit.Name;

        sched.EnableDynamicLoading(SchedulerDataLoader.DynamicalLoadingMode.Day);
        
        


        var dbcontext = new LotusworksHTEntities();
       
        

        var timeline = new TimelineView("timeline", "EmployeeID"); // initializes the view
        timeline.FolderEventsAvailable = false;
        
        timeline.RenderMode = TimelineView.RenderModes.Tree;

        var empList = new List<object>();
        foreach (var employee in dbcontext.Employees)
        {
            empList.Add(new { key = employee.EmployeeID, label = employee.FirstName });
        }

        timeline.AddOption(empList);




       /* var rooms = new List<object>(){
        new { key = "1", label = "Joe Bloggs"},
        new { key = "2", label = "Joe Bloggs"},
        new { key = "3", label = "Joe Bloggs"},
        new { key = "4", label = "Joe Bloggs"},
        new { key = "5", label = "Joe Bloggs"},
        new { key = "6", label = "Joe Bloggs"},
        new { key = "7", label = "Joe Bloggs"},
        new { key = "8", label = "Joe Bloggs"},
        new { key = "9", label = "Joe Bloggs"},
        new { key = "10", label = "Joe Bloggs"},
        new { key = "11", label = "Joe Bloggs"},
        new { key = "12", label = "Joe Bloggs"},
        new { key = "13", label = "Joe Bloggs"},
        new { key = "14", label = "Joe Bloggs"},
        new { key = "15", label = "Joe Bloggs"},
        
};
        timeline.AddOptions(rooms); */


       
       


    
        timeline.FitEvents = false;
        timeline.SectionAutoheight = false;
        timeline.Dy = 25; 
        timeline.X_Unit = TimelineView.XScaleUnits.Day;
        timeline.X_Date = "%j";
        timeline.X_Step = 1;
        timeline.X_Size = 31;
        sched.Views.Add(timeline);
  

        


       sched.TimeSpans.Add(new DHXMarkTime() {
        Day = DayOfWeek.Saturday,
        CssClass = "green_section", 
        SpanType = DHXMarkTime.Type.Default
        });

        sched.TimeSpans.Add(new DHXMarkTime()
        {
            Day = DayOfWeek.Sunday,
            CssClass = "green_section",
            SpanType = DHXMarkTime.Type.Default
        }); 





return View(sched);
    }

Do I need to add a .net connector and if so how? Thanks for the help!

#4

Also, Two more questions,
if I want to add in sections for the tree for employees in different departments, do I add it in here with this code?
If I add a new employee to the database will the timeline update adding the employee to the left hand side too?

Again Thank you,

Conor

#5

Hi Conor,

Hi I’ve changed the code to the following and still no employees are being loaded in???

Please show me how the timeline looks now after you’ve changed the code. Are the timeline sections(rows) being loaded and displayed as you want them?
Also, please show the definitions of your model classes (Employee and Event)

If you can attach a complete project with test data - it would also help.

You can also check this article https://docs.dhtmlx.com/scheduler/troubleshooting.html on troubleshooting. It’s written from the point of the client-side API but should help you to figure what’s going on

Do I need to add a .net connector and if so how?

no, you don’t need it.

if I want to add in sections for the tree for employees in different departments, do I add it in here with this code?

Please clarify how it should look like.
Do you want to place employees in different sections like in ‘tree timeline’ example https://docs.dhtmlx.com/scheduler/samples/06_timeline/03_tree.html
or do you also want to display the same employee under multiple different departments

If I add a new employee to the database will the timeline update adding the employee to the left hand side too?

A new employee will appear after you reload the timeline, it won’t be updated in real-time (unless you code it so)

#6

Hi Alex,

Here is how the Timeline View Looks Now.

TimelineViewNoData
TimelineViewNoData1895×903 31.4 KB

Here are both of my model classes

public partial class Employee
{
    public int UserID { get; set; }
    public int EmployeeID { get; set; }
    public string EmailID { get; set; }
    public string Password { get; set; }
    public int RoleID { get; set; }
    public string FullName { get; set; }
    public System.DateTime StartDate { get; set; }
    public int AreaID { get; set; }
    public int DisciplineID { get; set; }
    public int SiteID { get; set; }
    public string Shift { get; set; }
    public int ALCategory { get; set; }
    public Nullable<int> HoursCarriedForward { get; set; }
    public Nullable<int> HoursTaken { get; set; }
    public Nullable<int> AwardedLeave { get; set; }
    public Nullable<int> TotalHoursThisYear { get; set; }
    public Nullable<int> EntitlementRemaining { get; set; }

    public virtual Area Area { get; set; }
    public virtual Discipline Discipline { get; set; }
    public virtual Site Site { get; set; }
    public virtual UserRole UserRole { get; set; }
}
}

And

public partial class HolidayRequest
{
    public int RequestID { get; set; }
    public int EmployeeID { get; set; }
    public System.DateTime StartDate { get; set; }
    public System.DateTime FinishDate { get; set; }
    public int HoursTaken { get; set; }
    public Nullable<bool> AreaManagerApproval { get; set; }
    public Nullable<bool> ClientManagerApproval { get; set; }
    public string Comments { get; set; }

}

Would there be an email I could send the complete project to?

Yes I’d like Employees in different sections as the example given.

This is how I’d like the timeline view to look but with data from database. (This data was hardcoded).

timeline%20tree
timeline%20tree1688×509 30.2 KB

Thanks again for the help

Conor

#7

Hi Conor,
sorry for the delay.

I’ve made a quick prototype, please check:
https://files.dhtmlx.com/30d/426d0c1ecfcbc39fe3ba81a564cf66da/SchedulerTreeTimelineLoadOptions.zip

Pls also note that I’ve coded scheduler initialization using js api of the scheduler
https://docs.dhtmlx.com/scheduler/guides.html

In a moderately big project, you’ll likely have to switch to js api at some point, since complex dynamic behavior will have to be coded in js.

Best,