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.

Shift Scheduling Tutorial (ASP.NET, SQL Server, C#, VB.NET)

This tutorial shows how to display a shift schedule for multiple locations/positions.

Features:

  • Overview page displaying all locations and assignments

  • Easy assignment creating and moving using drag & drop

  • Asynchronous detection of conflicting assignments

1. Shift Schedule Overview Page

asp.net shift scheduling tutorial c vb.net sql server overview

The shift schedule overview page uses DayPilot ASP.NET Scheduler to show assignments for all locations.

Locations are displayed on the Y axis.

C#

protected void Page_Load(object sender, EventArgs e)
{
  if (!IsPostBack)
  {
    LoadResources();
    DayPilotScheduler1.StartDate = Week.FirstDayOfWeek(DateTime.Now);
    DayPilotScheduler1.DataSource = new DataManager().GetAssignments(DayPilotScheduler1);
    DayPilotScheduler1.DataBind();

    DayPilotScheduler1.SetScrollX(DateTime.Today);
  }
}

VB.NET

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
  If Not IsPostBack Then
    LoadResources()
    DayPilotScheduler1.StartDate = Week.FirstDayOfWeek(Date.Now)
    DayPilotScheduler1.DataSource = (New DataManager()).GetAssignments(DayPilotScheduler1)
    DayPilotScheduler1.DataBind()

    DayPilotScheduler1.SetScrollX(Date.Today)
  End If
End Sub

The Resources collection is filled using data from the [Location] table:

C#

private void LoadResources()
{
  DataTable locations = new DataManager().GetLocations();
  DayPilotScheduler1.Resources.Clear();
  foreach(DataRow dr in locations.Rows)
  {
    DayPilotScheduler1.Resources.Add((string)dr["LocationName"], Convert.ToString(dr["LocationId"]));
  }
} 

VB.NET

Private Sub LoadResources()
  Dim locations As DataTable = (New DataManager()).GetLocations()
  DayPilotScheduler1.Resources.Clear()
  For Each dr As DataRow In locations.Rows
    DayPilotScheduler1.Resources.Add(CStr(dr("LocationName")), Convert.ToString(dr("LocationId")))
  Next dr
End Sub

2. Assignments by Location

asp.net shift scheduling tutorial c vb.net sql server locations

This page shows assignment for a location selected using a DropDownList.

It uses delayed loading - the selected location is detected on the client side and a calendar refresh is requested:

<script type="text/javascript">
  function updateCalendar() {
      const value = document.getElementById("<%= DropDownListLocation.ClientID %>").value;
      dp.clientState.location = parseInt(value);
      dp.commandCallBack('refresh');
  }

  document.addEventListener("DOMContentLoaded", e => {
      updateCalendar();
  });
</script>

The selected location is stored in the ClientState property (ClientState["location"]) and is persisted during the subsequent callbacks.

The ClientState is updated and a refresh is requested whenever the users selects a new location using the DropDownList control (see the onchange attribute).

 <div>Location: 
  <asp:DropDownList 
    ID="DropDownListLocation" 
    runat="server"
    DataTextField="LocationName"
    DataValueField="LocationId"
    onchange="updateCalendar();"
  />
</div>

The JavaScript code uses commandCallBack() method to fire the Command event on the server side. 

C#

protected void DayPilotCalendar1_Command(object sender, CommandEventArgs e)
{
  switch (e.Command)
  {
    // ...
    case "refresh":
      DayPilotCalendar1.DataSource = new DataManager().GetAssignmentsForLocation(DayPilotCalendar1);
      DayPilotCalendar1.DataBind();
      DayPilotCalendar1.Update();
      break;
    // ...
  }
}

VB.NET

Protected Sub DayPilotCalendar1_Command(ByVal sender As Object, ByVal e As CommandEventArgs)
  Select Case e.Command
    '...
    Case "refresh"
      DayPilotCalendar1.DataSource = (New DataManager()).GetAssignmentsForLocation(DayPilotCalendar1)
      DayPilotCalendar1.DataBind()
      DayPilotCalendar1.Update()
    End Select
End Sub

The selected location is loaded from the ClientState property in the GetAssignmentsForLocation() method:

C#

public DataTable GetAssignmentsForLocation(DayPilotCalendar calendar)
{
  DataTable dt = new DataTable();

  // ...

  SqlDataAdapter da = new SqlDataAdapter("select * from [Assignment] join [Person] on [Assignment].[PersonId] = [Person].[PersonId] where NOT (([AssignmentEnd] <= @start) OR ([AssignmentStart] >= @end)) and [LocationId] = @location and [AssignmentRecurrence] is null", ConfigurationManager.ConnectionStrings["daypilot"].ConnectionString);
  da.SelectCommand.Parameters.AddWithValue("start", calendar.StartDate);
  da.SelectCommand.Parameters.AddWithValue("end", calendar.EndDate.AddDays(1));
  da.SelectCommand.Parameters.AddWithValue("location", (int) calendar.ClientState["location"]);
  da.Fill(dt);
  return dt;
}

VB.NET

Public Function GetAssignmentsForLocation(ByVal calendar As DayPilotCalendar) As DataTable
  Dim dt As New DataTable()

  ' ...

  Dim da As New SqlDataAdapter("select * from [Assignment] join [Person] on [Assignment].[PersonId] = [Person].[PersonId] where NOT (([AssignmentEnd] <= @start) OR ([AssignmentStart] >= @end)) and [LocationId] = @location and [AssignmentRecurrence] is null", ConfigurationManager.ConnectionStrings("daypilot").ConnectionString)
  da.SelectCommand.Parameters.AddWithValue("start", calendar.StartDate)
  da.SelectCommand.Parameters.AddWithValue("end", calendar.EndDate.AddDays(1))
  da.SelectCommand.Parameters.AddWithValue("location", CInt(calendar.ClientState("location")))
  da.Fill(dt)
  Return dt
End Function

3. Assignments by Person

asp.net shift scheduling tutorial c vb.net sql server people

This page shows assignments for the selected person (DropDownList) in a week view using DayPilot ASP.NET calendar control.

Again, we are storing the selected person id in the ClientState and calling commandCallBack('refresh') after the first page load.

<script type="text/javascript">
  function updateCalendar() {
      const value = document.getElementById("<%= DropDownListPerson.ClientID %>").value;
      dp.clientState.person = parseInt(value);
      dp.commandCallBack('refresh');
  }

  document.addEventListener("DOMContentLoaded", e => {
      updateCalendar();
  });
</script>

We send a request for update after every DropDownListPerson change:

 <div>Person: 
  <asp:DropDownList 
    ID="DropDownListPerson" 
    runat="server"
    DataTextField="PersonFullName"
    DataValueField="PersonId"
    onchange="updateCalendar();"
/>
</div>

The Command event handler reloads the events.

C#

protected void DayPilotCalendar1_Command(object sender, CommandEventArgs e)
{
  switch (e.Command)
  {
    // ...
    case "refresh":
      DayPilotCalendar1.DataSource = new DataManager().GetAssignmentsForPerson(DayPilotCalendar1);
      DayPilotCalendar1.DataBind();
      DayPilotCalendar1.Update();
      break;
  }
}

VB.NET

Protected Sub DayPilotCalendar1_Command(ByVal sender As Object, ByVal e As CommandEventArgs)
  Select Case e.Command
    ' ...
    Case "refresh"
      DayPilotCalendar1.DataSource = (New DataManager()).GetAssignmentsForPerson(DayPilotCalendar1)
      DayPilotCalendar1.DataBind()
      DayPilotCalendar1.Update()

  End Select
End Sub

The selected person id is loaded from the ClientState:

C#

public DataTable GetAssignmentsForPerson(DayPilotCalendar calendar)
{
  DataTable dt = new DataTable();

  // ...

  SqlDataAdapter da = new SqlDataAdapter("select * from [Assignment] join [Location] on [Assignment].[LocationId] = [Location].[LocationId] where NOT (([AssignmentEnd] <= @start) OR ([AssignmentStart] >= @end)) and [PersonId] = @person and [AssignmentRecurrence] is null", ConfigurationManager.ConnectionStrings["daypilot"].ConnectionString);
  da.SelectCommand.Parameters.AddWithValue("start", calendar.StartDate);
  da.SelectCommand.Parameters.AddWithValue("end", calendar.EndDate.AddDays(1));
  da.SelectCommand.Parameters.AddWithValue("person", (int)calendar.ClientState["person"]);
  da.Fill(dt);
  return dt;
}

VB.NET

Public Function GetAssignmentsForPerson(ByVal calendar As DayPilotCalendar) As DataTable
  Dim dt As New DataTable()

  ' ...

  Dim da As New SqlDataAdapter("select * from [Assignment] join [Location] on [Assignment].[LocationId] = [Location].[LocationId] where NOT (([AssignmentEnd] <= @start) OR ([AssignmentStart] >= @end)) and [PersonId] = @person and [AssignmentRecurrence] is null", ConfigurationManager.ConnectionStrings("daypilot").ConnectionString)
  da.SelectCommand.Parameters.AddWithValue("start", calendar.StartDate)
  da.SelectCommand.Parameters.AddWithValue("end", calendar.EndDate.AddDays(1))
  da.SelectCommand.Parameters.AddWithValue("person", CInt(calendar.ClientState("person")))
  da.Fill(dt)
  Return dt
End Function

4. Agenda

asp.net shift scheduling tutorial c vb.net sql server agenda

Agenda is a simple GridView of the upcoming assignments for the selected person.

<div>Person: 
  <asp:DropDownList 
    ID="DropDownListPerson" 
    runat="server"
    DataTextField="PersonFullName"
    DataValueField="PersonId" 
    onselectedindexchanged="DropDownListPerson_SelectedIndexChanged"
    AutoPostBack="true"
    AppendDataBoundItems="true">
  </asp:DropDownList>
</div> 

<asp:GridView ID="GridViewAgenda" runat="server" AutoGenerateColumns="False">
  <Columns>
    <asp:BoundField DataField="AssignmentStart" HeaderText="Start" />
    <asp:BoundField DataField="AssignmentEnd" HeaderText="End" />
    <asp:BoundField DataField="LocationName" HeaderText="Location" />
    <asp:BoundField DataField="AssignmentNote" HeaderText="Note" />
  </Columns>
</asp:GridView>

It is updated using traditional PostBack for simplicity:

C#

protected void Page_Load(object sender, EventArgs e)
{
  if (!IsPostBack)
  {
    DropDownListPerson.DataSource = new DataManager().GetPeople();

    if (IsSelected)
    {
      DropDownListPerson.SelectedValue = Request.QueryString["id"];
    }
    else
    {
      DropDownListPerson.SelectedIndex = 0;
    }
    DropDownListPerson.DataBind();

    DataTable dt = new DataManager().GetAssignmentsForPersonExpanded(PersonId, DateTime.Now, DateTime.Now.AddMonths(1));
    GridViewAgenda.DataSource = dt;

    Label1.Text = dt.Rows.Count.ToString();

    GridViewAgenda.DataBind();
  }
}

VB.NET

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
  If Not IsPostBack Then
    DropDownListPerson.DataSource = (New DataManager()).GetPeople()

    If IsSelected Then
      DropDownListPerson.SelectedValue = Request.QueryString("id")
    Else
      DropDownListPerson.SelectedIndex = 0
    End If
    DropDownListPerson.DataBind()

    Dim dt As DataTable = (New DataManager()).GetAssignmentsForPersonExpanded(PersonId, Date.Now, Date.Now.AddMonths(1))
    GridViewAgenda.DataSource = dt

    Label1.Text = dt.Rows.Count.ToString()

    GridViewAgenda.DataBind()
  End If
End Sub

5. Conflict Warning

Conflict means there are two or more concurrent assignments for the same person.

asp.net shift scheduling tutorial c vb.net sql server conflict warning

The conflicts are not being detected before an assignment change is stored in the database but asynchronously after every page call. Pages with DayPilot controls run the afterRender() function using AfterRenderJavaScript:

<DayPilot:DayPilotScheduler
  ID="DayPilotScheduler1" 
  runat="server" 
  ...
  AfterRenderJavaScript="afterRender();"
/>

All other pages run it using jQuery:

 <script type="text/javascript">
  document.addEventListener("DOMContentLoaded", ev => {
    afterRender();
  });
</script>

The afterRender() function sends an AJAX request to ConflictCount.aspx and updates the menu with the returned HTML.

async function afterRender() {
    const msg = await fetch("ConflictCount.aspx").then(response => response.text());
    const target = document.getElementById("conflicts");
    target.innerHTML = msg;
}

The ConflictCount.aspx page uses a ConflictDetector class to check for blocks of overlapping events and return the number of conflicts.

C#

protected void Page_Load(object sender, EventArgs e)
{
  Response.Cache.SetCacheability(HttpCacheability.NoCache);

  DateTime start = DateTime.Today;
  DateTime end = DateTime.Today.AddMonths(1);

  DataTable data = new DataManager().GetAssignmentsExpanded(start, end);
  var detector = new ConflictDetector();
  detector.Load(data, "AssignmentStart", "AssignmentEnd", "PersonId", start, end);

  Response.Clear();
  if (detector.Count > 0)
  {
    Response.Write(String.Format("<span style='background-color:red; color:white;'>({0})</span>", detector.Count));
  }
  else
  {
    Response.Write(String.Format("<span>({0})</span>", detector.Count));
  }
} 

VB.NET

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
  Response.Cache.SetCacheability(HttpCacheability.NoCache)

  Dim start As Date = Date.Today
  Dim [end] As Date = Date.Today.AddMonths(1)

  Dim data As DataTable = (New DataManager()).GetAssignmentsExpanded(start, [end])
  Dim detector = New ConflictDetector()
  detector.Load(data, "AssignmentStart", "AssignmentEnd", "PersonId", start, [end])

  Response.Clear()
  If detector.Count > 0 Then
    Response.Write(String.Format("<span style='background-color:red; color:white;'>({0})</span>", detector.Count))
  Else
    Response.Write(String.Format("<span>({0})</span>", detector.Count))
  End If
End Sub

6. List of Conflicts

asp.net shift scheduling tutorial c vb.net sql server conflict list

The Conflicts page uses the ConflictDetector class to analyze a data set for overlapping events.

C#

protected void Page_Load(object sender, EventArgs e)
{

  if (!IsPostBack)
  {
    DateTime start = DateTime.Today;
    DateTime end = DateTime.Today.AddMonths(1);

    DataTable data = new DataManager().GetAssignmentsExpanded(start, end);

    var detector = new ConflictDetector();
    detector.Load(data, "AssignmentStart", "AssignmentEnd", "PersonId", start, end);
    GridView1.DataSource = detector.List;

    Label1.Text = detector.List.Count.ToString();
    DataBind();
  }
}

VB.NET

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)

  If Not IsPostBack Then
    Dim start As Date = Date.Today
    Dim [end] As Date = Date.Today.AddMonths(1)

    Dim data As DataTable = (New DataManager()).GetAssignmentsExpanded(start, [end])

    Dim detector = New ConflictDetector()
    detector.Load(data, "AssignmentStart", "AssignmentEnd", "PersonId", start, [end])
    GridView1.DataSource = detector.List

    Label1.Text = detector.List.Count.ToString()
    DataBind()
  End If
End Sub

The ConflictDetector class accepts a DataTable with event records. You need to specify the column with assignment start (AssignmentStart), assignment end (AssignmentEnd), and with resource identifier (PersonId). It will check for overlapping events with the same resource id, within the range specified by start and end variables.

ConflictDetector.List holds a list of Conflict classes which contain details about the conflicts:

public class Conflict
{
  public List<ConflictEvent> Events;  // List of conflicting events
  public DateTime Start; // Start of the conflict (start of the first conflicting event in this block).
  public DateTime End; // End of the conflict (end of the last conflicting event in this block).
  public int Level; // Conflict level. The max parallel event count.
  public string Resource; // Resource id.
}

ConflictEvent class has information about the source event:

 public class ConflictEvent
{
  public DateTime Start; // Event start
  public DateTime End;  // Event end
  public string Value; // Event id
  public int Position; // Position of the event in a block of overlapping events, corresponds to the column number when displayed in the Calendar control
  public DataItemWrapper DataItem; // Source data object (DataRow)
}

The detected conflicts are displayed using a simple GridView:

 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
  <Columns>
    <asp:TemplateField HeaderText="Conflict">
      <ItemTemplate>
        <a href='People.aspx?person=<%# Eval("Resource") %>&date=<%# Convert.ToDateTime(Eval("Start")).ToString("s") %>'>Show</a>
      </ItemTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="Person">
      <ItemTemplate>
        <%# Eval("Events[0].DataItem.Source[\"PersonFullName\"]")%>
      </ItemTemplate>
    </asp:TemplateField>
    <asp:BoundField DataField="Start" HeaderText="Start" />
    <asp:BoundField DataField="End" HeaderText="End" />
    <asp:TemplateField HeaderText="Events in Conflict">
      <ItemTemplate>
        <%# Eval("Events.Count") %>
      </ItemTemplate>
    </asp:TemplateField>
  </Columns>
</asp:GridView>

8. Database Schema (SQL Server)

shift schedule sql schema

There are three tables in the database: Assignment, Location, and Person.

CREATE TABLE [dbo].[Person](
  [PersonId] [bigint] IDENTITY(1,1) NOT NULL,
  [PersonName] [varchar](200) NOT NULL,
  [PersonLastName] [varchar](200) NOT NULL,
  CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
  (
    [PersonId] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO
CREATE TABLE [dbo].[Location](
  [LocationId] [bigint] IDENTITY(1,1) NOT NULL,
  [LocationName] [varchar](200) NOT NULL,
  CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED 
  (
    [LocationId] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO
CREATE TABLE [dbo].[Assignment](
  [AssignmentId] [bigint] IDENTITY(1,1) NOT NULL,
  [LocationId] [bigint] NOT NULL,
  [PersonId] [bigint] NOT NULL,
  [AssignmentNote] [varchar](2000) NULL,
  [AssignmentStart] [datetime] NOT NULL,
  [AssignmentEnd] [datetime] NOT NULL,
  [AssignmentRecurrence] [varchar](500) NULL,
  CONSTRAINT [PK_Assignment] PRIMARY KEY CLUSTERED 
  (
    [AssignmentId] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO

ASP.NET Core Shift Planning

asp.net core shift planning

There is also an ASP.NET Core shift planning tutorial available which uses a slightly different approach to shift scheduling: