Sample Project

The sample project includes:

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.

Requirements

  • .NET Framework 4.0 or higher

  • Visual Studio 2019 or higher (optional)

  • Microsoft SQL Server (Express) 

Features

This tutorial shows how to create a restaurant table reservation web application using ASP.NET scheduler control.

  • Displaying tables in restaurant sections (Inside, Terrace)

  • Displaying table seats in an additional column

  • Disabling drag and drop operations for parent nodes (restaurant sections)

  • Time headers (day, hour, minutes)

  • Highlighting conflicting reservations

  • Reservation filter (find frees table by time and/or number of people)

This tutorial is also available for JavaScript/PHP:

1. Displaying Tables in Rows

asp.net-restaurant-table-reservation-rows.png

We will load the restaurant tables from the [Location] database table. The tables are located in sections (Inside, Terrace) - the section id is stored in [LocationParent] field.

The sections will be displayed as parent nodes, the tables as their children:

C#

protected void Page_Load(object sender, EventArgs e)
{
  if (!IsPostBack)
  {
    LoadResources();
  }
}

private void LoadResources()
{
  DataTable locations = new DataManager().GetTables();
  DayPilotScheduler1.Resources.Clear();
  foreach (DataRow location in locations.Rows)
  {
    int id = Convert.ToInt32(location["LocationId"]);

    Resource r = new Resource((string)location["LocationName"], null); // using null for resources that can't be used
    r.IsParent = true; // marking as parent for the case that no children are loaded
    r.Expanded = true;
    DayPilotScheduler1.Resources.Add(r);

    DataTable rooms = new DataManager().GetLocations(id, Filter.Seats);
    
    foreach (DataRow dr in rooms.Rows)
    {
      Resource c = new Resource((string)dr["LocationName"], Convert.ToString(dr["LocationId"]));
      r.Children.Add(c);
    }
  }
}

VB.NET

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
  If Not IsPostBack Then
    LoadResources()
  End If
End Sub

Private Sub LoadResources()
  Dim locations As DataTable = (New DataManager()).GetTables()
  DayPilotScheduler1.Resources.Clear()
  For Each location As DataRow In locations.Rows
    Dim id As Integer = Convert.ToInt32(location("LocationId"))

    Dim r As New Resource(CStr(location("LocationName")), Nothing) ' using null for resources that can't be used
    r.IsParent = True ' marking as parent for the case that no children are loaded
    r.Expanded = True
    DayPilotScheduler1.Resources.Add(r)

    Dim rooms As DataTable = (New DataManager()).GetLocations(id, Filter.Seats)

    For Each dr As DataRow In rooms.Rows
      Dim c As New Resource(CStr(dr("LocationName")), Convert.ToString(dr("LocationId")))
      r.Children.Add(c)
    Next dr
  Next location
End Sub

The header row width can be set using RowHeaderWidth property (in pixels):

RowHeaderWidth="120"

We will add RowHeaderWidthAutoFit to make sure that the row header width is increased automatically to fit the row header text (the table or section name):

RowHeaderWidthAutoFit="true"

2. Displaying Table Seats in an Additional Column

asp.net-restaurant-table-reservation-resources.png

The number of table seats is stored in [LocationSeats] field of the [Location] table. We will modify the LoadResources() method to add a new column with the seat count.

C#

foreach (DataRow dr in rooms.Rows)
{
  Resource c = new Resource((string)dr["LocationName"], Convert.ToString(dr["LocationId"]));
  c.Columns.Add(new ResourceColumn(dr["LocationSeats"] + " seats"));
  r.Children.Add(c);
}

VB.NET

For Each dr As DataRow In rooms.Rows
  Dim c As New Resource(CStr(dr("LocationName")), Convert.ToString(dr("LocationId")))
  c.Columns.Add(New ResourceColumn(dr("LocationSeats") & " seats"))
  r.Children.Add(c)
Next dr

The columns can be specified using <HeaderColumns>:

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

The automatic row header width adjusting (RowHeaderWidthAutoFit) will work for multiple columns as well.

3. Disabling Parent Nodes

asp.net-restaurant-table-reservation-parent-nodes.png

The scheduler parent tree nodes that represent restaurant sections (e.g. "Terrace") are disabled for drag and drop operations using TreePreventParentUsage property:

TreePreventParentUsage="true"

The cells are marked with prefix_cellparent CSS class (where "prefix" is replaced with Theme value).

.scheduler_8_cellparent {
	background-color: #f8f8f8;
}

4. Time Headers (Day/Hour/Minutes)

asp.net-restaurant-table-reservation-time-headers.png

The grid cell duration is set to 15 minutes using CellDuration property:

CellDuration="15"

By default, the header displays the column header and one level of column groups (CellGroupBy property). We will use two column header groups (day and hour):

C#

protected void Page_Load(object sender, EventArgs e)
{
  if (!IsPostBack)
  {
    DayPilotScheduler1.TimeHeaders.Clear();
    DayPilotScheduler1.TimeHeaders.Add(new TimeHeader(GroupByEnum.Day, "dddd, d MMMM yyyy"));
    DayPilotScheduler1.TimeHeaders.Add(new TimeHeader(GroupByEnum.Hour));
    DayPilotScheduler1.TimeHeaders.Add(new TimeHeader(GroupByEnum.Cell));
  }
}

VB.NET

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
  If Not IsPostBack Then
    DayPilotScheduler1.TimeHeaders.Clear()
    DayPilotScheduler1.TimeHeaders.Add(New TimeHeader(GroupByEnum.Day, "dddd, d MMMM yyyy"))
    DayPilotScheduler1.TimeHeaders.Add(New TimeHeader(GroupByEnum.Hour))
    DayPilotScheduler1.TimeHeaders.Add(New TimeHeader(GroupByEnum.Cell))
  End If
End Sub

5. Conflicting Reservations

restaurant-table-reservation-conflict.png

The reservation conflicts are not checked in advance. It is allowed to create a reservation for a table and time that are reserved already.

Instead, all reservations store a priority date in the [AssignmentPriority] field. This field is updated to the current time whenever the reservation resource (table) is changed.

C#

public void MoveReservation(int id, DateTime start, DateTime end, int location)
{
    DataRow original = GetReservation(id);

    // if the reservatino table was changed, update the priority
    bool tableChanged = (Int64)original["LocationId"] != location;
    
    if (tableChanged)  // update the priority 
    {
        using (var con = CreateConnection())
        {
            con.Open();

            var cmd = CreateCommand("update [Assignment] set [AssignmentStart] = @start, [AssignmentEnd] = @end, [LocationId] = @location, [AssignmentPriority] = @priority where [AssignmentId] = @id", con);
            AddParameterWithValue(cmd, "id", id);
            AddParameterWithValue(cmd, "start", start);
            AddParameterWithValue(cmd, "end", end);
            AddParameterWithValue(cmd, "location", location);
            AddParameterWithValue(cmd, "priority", DateTime.Now);

            cmd.ExecuteNonQuery();
        }
    }
    else
    {
        using (var con = CreateConnection())
        {
            con.Open();

            var cmd = CreateCommand("update [Assignment] set [AssignmentStart] = @start, [AssignmentEnd] = @end, [LocationId] = @location where [AssignmentId] = @id", con);
            AddParameterWithValue(cmd, "id", id);
            AddParameterWithValue(cmd, "start", start);
            AddParameterWithValue(cmd, "end", end);
            AddParameterWithValue(cmd, "location", location);

            cmd.ExecuteNonQuery();
        }
    }  
}

VB.NET

Public Sub MoveReservation(ByVal id As Integer, ByVal start As Date, ByVal [end] As Date, ByVal location As Integer)
  Dim original As DataRow = GetReservation(id)

  ' if the reservatino table was changed, update the priority
  Dim tableChanged As Boolean = DirectCast(original("LocationId"), Int64) <> location

  If tableChanged Then ' update the priority
    Using con = CreateConnection()
      con.Open()

      Dim cmd = CreateCommand("update [Assignment] set [AssignmentStart] = @start, [AssignmentEnd] = @end, [LocationId] = @location, [AssignmentPriority] = @priority where [AssignmentId] = @id", con)
      AddParameterWithValue(cmd, "id", id)
      AddParameterWithValue(cmd, "start", start)
      AddParameterWithValue(cmd, "end", [end])
      AddParameterWithValue(cmd, "location", location)
      AddParameterWithValue(cmd, "priority", Date.Now)

      cmd.ExecuteNonQuery()
    End Using
  Else
    Using con = CreateConnection()
      con.Open()

      Dim cmd = CreateCommand("update [Assignment] set [AssignmentStart] = @start, [AssignmentEnd] = @end, [LocationId] = @location where [AssignmentId] = @id", con)
      AddParameterWithValue(cmd, "id", id)
      AddParameterWithValue(cmd, "start", start)
      AddParameterWithValue(cmd, "end", [end])
      AddParameterWithValue(cmd, "location", location)

      cmd.ExecuteNonQuery()
    End Using
  End If
End Sub

This priority field (AssignmentPriority) is used for sorting the overlapping events using EventSortExpression property:

EventSortExpression="AssignmentPriority"

All reservation boxes (events) are marked with prefix_event_lineX CSS class, depending on the line number inside of a table (resource) row. This way, we can use red color for all events that not are displayed  in line 0:

.scheduler_8_event_inner  
{
  background: #ed3b00;
}

.scheduler_8_event.scheduler_8_event_line0 .scheduler_8_event_inner 
{
  background: #09b2ef;
}

This will display reservations (events) with the highest priority on line 0 and highlight all reservations on the following other lines.

6. Reservation Filter

restaurant-table-reservation-filter.png

The tables can be filtered by number of people:

restaurant-table-reservation-asp-net-filter-people.png

And by the time slot:

restaurant-table-reservation-asp-net-filter-time.png

The filter is activated by storing the filter parameters in ClientState property.

The people filter is added on the client side, using seats() method (Scripts/main.js):

function seats(count) {
    if (!dp.clientState.filter) {
        dp.clientState.filter = {};
    }
    var seats = dp.clientState.filter.seats = {};
    seats.name = "People";
    seats.value = count;
    seats.count = count;

    dp.commandCallBack("refresh");
}

The time slot filter is activated on the server side, in TimeHeaderClick event handler:

C#

protected void DayPilotScheduler1_TimeHeaderClick(object sender, TimeHeaderClickEventArgs e)
{
  DayPilotScheduler1.DataSource = new DataManager().GetAssignments(DayPilotScheduler1);
  DayPilotScheduler1.DataBind();

  JsonData time = new JsonData();
  time["name"] = "Time";
  time["value"] = String.Format("{0} - {1}", TimeFormatter.GetHourMinutes(e.Start, DayPilotScheduler1.TimeFormat), TimeFormatter.GetHourMinutes(e.End, DayPilotScheduler1.TimeFormat));
  time["start"] = e.Start.ToString("s");
  time["end"] = e.End.ToString("s");

  if (DayPilotScheduler1.ClientState["filter"] == null)
  {
    DayPilotScheduler1.ClientState["filter"] = new JsonData();
  }
  DayPilotScheduler1.ClientState["filter"]["time"] = time;

  LoadResources();
  LoadSeparators();

  DayPilotScheduler1.Update();

}

VB.NET

Protected Sub DayPilotScheduler1_TimeHeaderClick(ByVal sender As Object, ByVal e As TimeHeaderClickEventArgs)
  DayPilotScheduler1.DataSource = (New DataManager()).GetAssignments(DayPilotScheduler1)
  DayPilotScheduler1.DataBind()

  Dim time As New JsonData()
  time("name") = "Time"
  time("value") = String.Format("{0} - {1}", TimeFormatter.GetHourMinutes(e.Start, DayPilotScheduler1.TimeFormat), TimeFormatter.GetHourMinutes(e.End, DayPilotScheduler1.TimeFormat))
  time("start") = e.Start.ToString("s")
  time("end") = e.End.ToString("s")

  If DayPilotScheduler1.ClientState("filter") Is Nothing Then
    DayPilotScheduler1.ClientState("filter") = New JsonData()
  End If
  DayPilotScheduler1.ClientState("filter")("time") = time

  LoadResources()
  LoadSeparators()

  DayPilotScheduler1.Update()

End Sub

The filter value is checked in LoadResources() method:

C#

DataTable rooms;

if (Filter.IsTime)
{
  rooms = new DataManager().GetLocationsFiltered(id, Filter.Start, Filter.End, Filter.Seats);
}
else
{
  rooms = new DataManager().GetLocations(id, Filter.Seats);
}

VB.NET

Dim rooms As DataTable

If Filter.IsTime Then
  rooms = (New DataManager()).GetLocationsFiltered(id, Filter.Start, Filter.End, Filter.Seats)
Else
  rooms = (New DataManager()).GetLocations(id, Filter.Seats)
End If

The ClientState property is read using the FilterData helper class:

C#

private FilterData Filter
{
  get
  {
    return new FilterData(DayPilotScheduler1.ClientState["filter"]);
  }
}

private class FilterData
{
  internal DateTime Start = DateTime.MinValue;
  internal DateTime End = DateTime.MaxValue;
  internal bool IsTime = false;
  internal int Seats = 0;

  internal FilterData(JsonData filter)
  {
    if (filter == null)
    {
      return;
    }

    if (filter["time"] != null)
    {
      IsTime = true;
      Start = (DateTime)filter["time"]["start"];
      End = (DateTime)filter["time"]["end"];
    }

    if (filter["seats"] != null)
    {
      Seats = (int) filter["seats"]["count"];
    }
  }
}

VB.NET

Private ReadOnly Property Filter() As FilterData
  Get
    Return New FilterData(DayPilotScheduler1.ClientState("filter"))
  End Get
End Property

Private Class FilterData
  Friend Start As Date = Date.MinValue
  Friend [End] As Date = Date.MaxValue
  Friend IsTime As Boolean = False
  Friend Seats As Integer = 0

  Friend Sub New(ByVal filter As JsonData)
    If filter Is Nothing Then
      Return
    End If

    If filter("time") IsNot Nothing Then
      IsTime = True
      Start = CDate(filter("time")("start"))
      [End] = CDate(filter("time")("end"))
    End If

    If filter("seats") IsNot Nothing Then
      Seats = CInt(Fix(filter("seats")("count")))
    End If
  End Sub
End Class

SQL Server Database Schema

Assignment table:

CREATE TABLE [dbo].[Assignment] (
    [AssignmentId]       BIGINT         IDENTITY (1, 1) NOT NULL,
    [LocationId]         BIGINT         NOT NULL,
    [AssignmentNote]     VARCHAR (2000) NULL,
    [AssignmentStart]    DATETIME       NOT NULL,
    [AssignmentEnd]      DATETIME       NOT NULL,
    [AssignmentPriority] DATETIME       CONSTRAINT [DF_Assignment_AssignmentUpdated] DEFAULT (getdate()) NOT NULL,
    CONSTRAINT [PK_Assignment] PRIMARY KEY CLUSTERED ([AssignmentId] ASC)
);

Location table:

CREATE TABLE [dbo].[Location] (
    [LocationId]    BIGINT        IDENTITY (1, 1) NOT NULL,
    [LocationName]  VARCHAR (200) NOT NULL,
    [ParentId]      BIGINT        NULL,
    [LocationSeats] INT           NULL,
    CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED ([LocationId] ASC)
);

History

  • Feb 6, 2021: Visual Studio 2019, DayPilot Pro for ASP.NET WebForms 2021.1, jQuery dependency removed, using DayPilot.Modal.form() for modal dialogs

  • Sep 24, 2017: Visual Studio 2017, SQL Server 2014+, DayPilot Pro for ASP.NET WebForms 8.4

  • Jul 30, 2014: Visual Studio 2012, SQL Server 2012 database (LocalDB).

  • Oct 21, 2012: Initial release