Scheduler with Sortable Columns (ASP.NET WebForms)

Features

This tutorial shows how to enable column sorting for the DayPilot ASP.NET scheduler control.

• ASP.NET scheduler control displaying reservation data for multiple resources
• Resources with multiple data columns (name, id)
• Resource data loaded from the database (SQL Server)
• Sorting the resources using column headers
• C# source code
• VB.NET source code
• Visual Studio 2012 solution

License

Licensed for testing and evaluation purposes. You can use the source code of the tutorial if you are a licensed user of DayPilot Pro for ASP.NET WebForms. Buy a license.

Scheduler Resource Column Headers

You can define row header columns using HeaderColumns collection.

<DayPilot:DayPilotScheduler
  ID="DayPilotScheduler1" 
  runat="server" 
  ...
  >
    <HeaderColumns>
      <DayPilot:RowHeaderColumn Title="Name" Width="80" />
      <DayPilot:RowHeaderColumn Title="Id" Width="80" />
    </HeaderColumns>
</DayPilot:DayPilotScheduler>

Loading Additional Column Data

The first row header column of the scheduler will automatically display the resource name. You can load the data for the additional columns using BeforeResHeaderRender event handler.

C#

protected void DayPilotScheduler1_OnBeforeResHeaderRender(object sender, BeforeResHeaderRenderEventArgs e)
{
  // e.DataItem is only available when resources are reloaded from the database using LoadResources()
  if (e.DataItem != null)
  {
      e.Columns[0].Html = "" + e.DataItem["id"];
  }
}

VB

Protected Sub DayPilotScheduler1_OnBeforeResHeaderRender(ByVal sender As Object, ByVal e As BeforeResHeaderRenderEventArgs)
  ' e.DataItem is only available when resources are reloaded from the database using LoadResources()
  If e.DataItem IsNot Nothing Then
    e.Columns(0).Html = "" & e.DataItem("id")
  End If
End Sub

Column Sorting

In order to activate column sorting, you need to add a hyperlink (<a> tag) to the column header:

<DayPilot:DayPilotScheduler
  ID="DayPilotScheduler1" 
  runat="server" 
  ...
  ClientObjectName="dps"
  OnCommand="DayPilotScheduler1_Command"
  >
    <HeaderColumns>
      <DayPilot:RowHeaderColumn Title="<a href='javascript:dps.commandCallBack(&quot;sort&quot;, { field: &quot;name&quot; }); '>Name</a>" Width="80" />
      <DayPilot:RowHeaderColumn Title="<a href='javascript:dps.commandCallBack(&quot;sort&quot;, { field: &quot;id&quot; }); '>Id</a>" Width="80" />
    </HeaderColumns>
</DayPilot:DayPilotScheduler>

The commandCallBack() call will invoke Command event handler on the server side.

C#

protected void DayPilotScheduler1_Command(object sender, CommandEventArgs e)
{
  switch (e.Command)
  {
    case "sort":
      LoadResources((string)e.Data["field"]);
      DayPilotScheduler1.DataSource = GetData(DayPilotScheduler1.StartDate, DayPilotScheduler1.EndDate.AddDays(1));
      DayPilotScheduler1.DataBind();
      DayPilotScheduler1.Update();
      break;
  }
}

VB

Protected Sub DayPilotScheduler1_Command(ByVal sender As Object, ByVal e As CommandEventArgs)
  Select Case e.Command
    Case "sort"
      LoadResources(CStr(e.Data("field")))
      DayPilotScheduler1.DataSource = GetData(DayPilotScheduler1.StartDate, DayPilotScheduler1.EndDate.AddDays(1))
      DayPilotScheduler1.DataBind()
      DayPilotScheduler1.Update()
  End Select
End Sub

The LoadResources() method loads the resources from the database using the specified sorting.

The new structure of Resources will be persisted during the subsequent callbacks.

C#

private void LoadResources(string orderBy)
{
  DayPilotScheduler1.Resources.Clear();
  foreach (DataRow r in GetResources(orderBy).Rows)
  {
      Resource res = new Resource((string)r["name"], Convert.ToString(r["id"]));
      res.DataItem = r;
      DayPilotScheduler1.Resources.Add(res);
  }
}

public DataTable GetResources(string orderBy)
{
  SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM [resource]", ConfigurationManager.ConnectionStrings["daypilot"].ConnectionString);
  DataTable dt = new DataTable();
  da.Fill(dt);

  dt.DefaultView.Sort = orderBy;

  return dt.DefaultView.ToTable();

}

VB

Private Sub LoadResources(Optional ByVal orderBy As String = "name")
  DayPilotScheduler1.Resources.Clear()
  For Each r As DataRow In GetResources(orderBy).Rows
    Dim res As New Resource(DirectCast(r("name"), String), Convert.ToString(r("id")))
    res.DataItem = r
    DayPilotScheduler1.Resources.Add(res)
  Next r
End Sub

Public Function GetResources(ByVal orderBy As String) As DataTable
  Dim da As New SqlDataAdapter("SELECT * FROM [resource]", ConfigurationManager.ConnectionStrings("daypilot").ConnectionString)
  Dim dt As New DataTable()
  da.Fill(dt)

  dt.DefaultView.Sort = orderBy

  Return dt.DefaultView.ToTable()

End Function

Full Page Source (.aspx)

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" MasterPageFile="~/Site.master" Title="Scheduler with Sortable Columns (ASP.NET WebForms)" %>
<%@ Register Assembly="DayPilot" Namespace="DayPilot.Web.Ui" TagPrefix="DayPilot" %>
<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
	<script type="text/javascript" src="js/modal.js"></script>
	<script type="text/javascript" src="js/jquery-1.9.1.min.js"></script>
    <link href='css/main.css' type="text/css" rel="stylesheet" /> 
</asp:Content>

<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">

<div style="float:left; width: 150px">
    <DayPilot:DayPilotNavigator 
    ID="DayPilotNavigator1"
    runat="server" 
    ShowMonths="3"
    SkiptMonth="3"
    BoundDayPilotID="DayPilotScheduler1"
    />
</div>

<div style="margin-left:150px">
    <DayPilot:DayPilotScheduler
    ID="DayPilotScheduler1" 
    runat="server" 
    DataEndField="eventend"
    DataStartField="eventstart" 
    DataTextField="name" 
    DataIdField="id" 
    DataResourceField="resource"
    ClientObjectName="dps"
    OnCommand="DayPilotScheduler1_Command"
    OnBeforeResHeaderRender="DayPilotScheduler1_OnBeforeResHeaderRender"
    >
        <TimeHeaders>
            <DayPilot:TimeHeader GroupBy="Day" Format="ddd MMMM d, yyyy" />
            <DayPilot:TimeHeader GroupBy="Hour" Format="%h" />
        </TimeHeaders>
        <HeaderColumns>
            <DayPilot:RowHeaderColumn Title="<a href='javascript:dps.commandCallBack(&quot;sort&quot;, { field: &quot;name&quot; }); '>Name</a>" Width="80" />
            <DayPilot:RowHeaderColumn Title="<a href='javascript:dps.commandCallBack(&quot;sort&quot;, { field: &quot;id&quot; }); '>Id</a>" Width="80" />
        </HeaderColumns>
    </DayPilot:DayPilotScheduler>

</div>

</asp:Content>

Full Source Code

C#

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using DayPilot.Web.Ui;
using DayPilot.Web.Ui.Events;
using DayPilot.Web.Ui.Events.Scheduler;

public partial class _Default : System.Web.UI.Page 
{
  protected void Page_Load(object sender, EventArgs e)
  {
      if (!IsPostBack)
      {
          LoadResources();
          DayPilotScheduler1.DataSource = GetData(DayPilotScheduler1.StartDate, DayPilotScheduler1.EndDate.AddDays(1));
          DayPilotScheduler1.DataBind();
      }

  }

  protected void DayPilotScheduler1_Command(object sender, CommandEventArgs e)
  {
      switch (e.Command)
      {
          case "navigate":
              DayPilotScheduler1.StartDate = (DateTime)e.Data["day"];
              DayPilotScheduler1.DataSource = GetData(DayPilotScheduler1.StartDate, DayPilotScheduler1.EndDate.AddDays(1));
              DayPilotScheduler1.DataBind();
              DayPilotScheduler1.Update();
              break;
          case "refresh":
              DayPilotScheduler1.DataSource = GetData(DayPilotScheduler1.StartDate, DayPilotScheduler1.EndDate.AddDays(1));
              DayPilotScheduler1.DataBind();
              DayPilotScheduler1.Update();
              break;
          case "sort":
              LoadResources((string)e.Data["field"]);
              DayPilotScheduler1.DataSource = GetData(DayPilotScheduler1.StartDate, DayPilotScheduler1.EndDate.AddDays(1));
              DayPilotScheduler1.DataBind();
              DayPilotScheduler1.Update();
              break;
      }
  }


  private DataTable GetData(DateTime start, DateTime end)
  {
      SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM [event] WHERE NOT (([eventend] <= @start) OR ([eventstart] >= @end))", ConfigurationManager.ConnectionStrings["daypilot"].ConnectionString);
      da.SelectCommand.Parameters.AddWithValue("start", start);
      da.SelectCommand.Parameters.AddWithValue("end", end);

      DataTable dt = new DataTable();
      da.Fill(dt);

      return dt;
  }

  public DataTable GetResources(string orderBy)
  {
      SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM [resource]", ConfigurationManager.ConnectionStrings["daypilot"].ConnectionString);
      DataTable dt = new DataTable();
      da.Fill(dt);

      dt.DefaultView.Sort = orderBy;

      return dt.DefaultView.ToTable();

  }

  private void LoadResources(string orderBy)
  {
      DayPilotScheduler1.Resources.Clear();
      foreach (DataRow r in GetResources(orderBy).Rows)
      {
          Resource res = new Resource((string)r["name"], Convert.ToString(r["id"]));
          res.DataItem = r;
          DayPilotScheduler1.Resources.Add(res);
      }
  }

  private void LoadResources()
  {
      LoadResources("name");
  }

  protected void DayPilotScheduler1_OnBeforeResHeaderRender(object sender, BeforeResHeaderRenderEventArgs e)
  {
      // e.DataItem is only available when resources are reloaded from the database using LoadResources()
      if (e.DataItem != null)
      {
          e.Columns[0].Html = "" + e.DataItem["id"];
      }
  }
}

VB

Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports DayPilot.Web.Ui
Imports DayPilot.Web.Ui.Events
Imports DayPilot.Web.Ui.Events.Scheduler

Partial Public Class _Default
	Inherits System.Web.UI.Page

	Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
		If Not IsPostBack Then
			LoadResources()
			DayPilotScheduler1.DataSource = GetData(DayPilotScheduler1.StartDate, DayPilotScheduler1.EndDate.AddDays(1))
			DayPilotScheduler1.DataBind()
		End If

	End Sub

	Protected Sub DayPilotScheduler1_Command(ByVal sender As Object, ByVal e As CommandEventArgs)
		Select Case e.Command
			Case "navigate"
				DayPilotScheduler1.StartDate = CDate(e.Data("day"))
				DayPilotScheduler1.DataSource = GetData(DayPilotScheduler1.StartDate, DayPilotScheduler1.EndDate.AddDays(1))
				DayPilotScheduler1.DataBind()
				DayPilotScheduler1.Update()
			Case "refresh"
				DayPilotScheduler1.DataSource = GetData(DayPilotScheduler1.StartDate, DayPilotScheduler1.EndDate.AddDays(1))
				DayPilotScheduler1.DataBind()
				DayPilotScheduler1.Update()
			Case "sort"
				LoadResources(CStr(e.Data("field")))
				DayPilotScheduler1.DataSource = GetData(DayPilotScheduler1.StartDate, DayPilotScheduler1.EndDate.AddDays(1))
				DayPilotScheduler1.DataBind()
				DayPilotScheduler1.Update()
		End Select
	End Sub


	Private Function GetData(ByVal start As Date, ByVal [end] As Date) As DataTable
		Dim da As New SqlDataAdapter("SELECT * FROM [event] WHERE NOT (([eventend] <= @start) OR ([eventstart] >= @end))", ConfigurationManager.ConnectionStrings("daypilot").ConnectionString)
		da.SelectCommand.Parameters.AddWithValue("start", start)
		da.SelectCommand.Parameters.AddWithValue("end", [end])

		Dim dt As New DataTable()
		da.Fill(dt)

		Return dt
	End Function

	Public Function GetResources(ByVal orderBy As String) As DataTable
		Dim da As New SqlDataAdapter("SELECT * FROM [resource]", ConfigurationManager.ConnectionStrings("daypilot").ConnectionString)
		Dim dt As New DataTable()
		da.Fill(dt)

		dt.DefaultView.Sort = orderBy

		Return dt.DefaultView.ToTable()

	End Function

	Private Sub LoadResources(Optional ByVal orderBy As String = "name")
		DayPilotScheduler1.Resources.Clear()
		For Each r As DataRow In GetResources(orderBy).Rows
			Dim res As New Resource(DirectCast(r("name"), String), Convert.ToString(r("id")))
			res.DataItem = r
			DayPilotScheduler1.Resources.Add(res)
		Next r
	End Sub

	Protected Sub DayPilotScheduler1_OnBeforeResHeaderRender(ByVal sender As Object, ByVal e As BeforeResHeaderRenderEventArgs)
		' e.DataItem is only available when resources are reloaded from the database using LoadResources()
		If e.DataItem IsNot Nothing Then
			e.Columns(0).Html = "" & e.DataItem("id")
		End If
	End Sub
End Class