Restaurant Table Reservation Tutorial (ASP.NET, C#, VB.NET)

Sample Project

The sample project includes:

• DayPilot Pro for ASP.NET WebForms 2021.1 Trial

• C# Source Code

• VB.NET Source Code

• Visual Studio 2019 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.

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:

• PHP Restaurant Table Reservation Application (HTML5/JavaScript Frontend)

1. Displaying Tables in Rows

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:

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

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.

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

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)

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):

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

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.

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

The tables can be filtered by number of people:

And by the time slot:

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:

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:

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:

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