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("sort", { field: "name" }); '>Name</a>" Width="80" /> <DayPilot:RowHeaderColumn Title="<a href='javascript:dps.commandCallBack("sort", { field: "id" }); '>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("sort", { field: "name" }); '>Name</a>" Width="80" /> <DayPilot:RowHeaderColumn Title="<a href='javascript:dps.commandCallBack("sort", { field: "id" }); '>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