DayPilot » Knowledge Base » How to set the time cell colors using database data in the Scheduler

It is possible to load the data for the background time cell colors from the database.

The following example will change the cell background color to red if any event from the background DataTable is overlapping this cell.

The background cell color is set using BeforeCellRender event which is called for each time cell.

Loading the data

We will load the DataTable in Page_Load and store it in background field.

    private DataTable background;

    protected void Page_Load(object sender, EventArgs e)
    {
        initData();

        DayPilotScheduler1.StartDate = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
        DayPilotScheduler1.Days = DateTime.DaysInMonth(DayPilotScheduler1.StartDate.Year, DayPilotScheduler1.StartDate.Month) + DateTime.DaysInMonth(DayPilotScheduler1.StartDate.AddMonths(1).Year, DayPilotScheduler1.StartDate.AddMonths(1).Month);
        DayPilotScheduler1.Separators.Add(DateTime.Today, Color.Red);

        background = getBackgroundData();

        if (!IsPostBack)
        {
            DayPilotScheduler1.SetScrollX(DateTime.Today);
            setDataSourceAndBind();
        }
    }

Here we are using a testing data set generated using getBackgroundData(). You will probably want to replace it with data loaded from a database.

    private DataTable getBackgroundData()
    {
        DataTable dt;
        dt = new DataTable();
        dt.Columns.Add("start", typeof(DateTime));
        dt.Columns.Add("end", typeof(DateTime));
        dt.Columns.Add("name", typeof(string));
        dt.Columns.Add("id", typeof(string));
        dt.Columns.Add("resource", typeof(string));

        DataRow dr;

        dr = dt.NewRow();
        dr["id"] = 1;
        dr["start"] = Convert.ToDateTime("15:00");
        dr["end"] = Convert.ToDateTime("16:00").AddDays(2);
        dr["name"] = "First";
        dr["resource"] = "A";
        dt.Rows.Add(dr);

        return dt;
    }

Changing the color

Each time cell is compared against all events in the data set using the following condition:

if (!(e.End <= start || e.Start >= end) && e.ResourceId == resource)

This will be true if the event overlaps the cell (even partially).

    protected void DayPilotScheduler1_BeforeCellRender(object sender, DayPilot.Web.Ui.Events.BeforeCellRenderEventArgs e)
    {
        foreach (DataRow dr in background.Rows)
        {
            DateTime start = (DateTime) dr["start"];
            DateTime end = (DateTime) dr["end"];
            string resource = (string) dr["resource"];

            // overlaps
            if (!(e.End <= start || e.Start >= end) && e.ResourceId == resource)
            {
                e.BackgroundColor = "red";
                return;
            }
        }
    }