DayPilot Knowledge Base

AJAX Calendar/Scheduling Controls
DayPilot Pro (AJAX Calendar Control)
» DayPilot AJAX Calendar
DayPilot Pro (AJAX Monthly Calendar Control)
» DayPilot AJAX Monthly Calendar
DayPilot Pro (AJAX Scheduler Control)
» DayPilot AJAX Scheduler
DayPilot » Knowledge Base » How to set the time cell colors using database data in the Scheduler

How to set the time cell colors using database data in the Scheduler

Last revision: Feb 10, 2011

scheduler-time-cells-db.png

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

Related

How to save the exported PNG image to a file (without a web page)
How to load Scheduler resource tree from a database (SQL Server)
How to implement Copy & Paste in the Calendar
How to show one month per cell in Scheduler (CellDuration = Month)