Sample Project

The sample project includes:

Online 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 2010 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

restaurant-table-reservation-resources.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().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

restaurant-table-reservation-table-seats.png

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

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 CssClassPrefix value).

.scheduler_8_cellparent {
	background-color: #f8f8f8;
}

4. Time Headers (Day/Hour/Minutes)

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

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/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