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

ASP.NET web application project for managing restaurant table reservations. Finds a free table using a fast filter. Highlights conflicting reservations.

Downloads	Download Source Visual Studio 2017 solution. Microsoft SQL Server database (LocalDB). Includes C# and VB source code. 1748 kB
Languages	VB.NET, C#
Technologies	Microsoft SQL Server, ASP.NET

Sample Project

The sample project includes:

DayPilot Pro for ASP.NET WebForms 8.4 Trial
C# Source Code
VB.NET Source Code
Visual Studio 2017 Solution

Online Demo

Restaurant Table Reservation Tutorial Demo

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

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:

C#

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

private void LoadResources()
{
  DataTable locations = new DataManager().GetLocations();
  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()).GetLocations()
  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 if the [Location] table. We will modify 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 column widths can be specified using RowHeaderColWidths property:

RowHeaderColumnWidths="80,60"

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

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

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 an [AssignmentPriority] field. This field is updated to the current time whenever the reservation resource (table) is changed.

C#

public void MoveAssignment(int id, DateTime start, DateTime end, int location)
{
  DataRow original = GetAssignment(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 MoveAssignment(ByVal id As Integer, ByVal start As Date, ByVal [end] As Date, ByVal location As Integer)
  Dim original As DataRow = GetAssignment(id)

  ' if the reservatino table was changed, update the priority
  Dim tableChanged As Boolean = CLng(Fix(original("LocationId"))) <> 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/event_handling.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

Products

Resources

Tools

About Us

Contact

DayPilot Pro

Event calendar/scheduling AJAX components. Available for JavaScript/HTML5/jQuery, ASP.NET, ASP.NET MVC. Build resource booking, project management, time tracking applications, personal and shared event calendars.