Features

  • Public interface for patients
  • Doctor's interface for managing appointments
  • Manager's interface for scheduling shifts
  • Using pre-defined appointment slots
  • Appointment status: "free", "waiting", "confirmed"
  • SQL Server database
  • Visual Studio 2013
  • C# source code
  • VB.NET source code
  • Includes the trial version of DayPilot Pro for ASP.NET WebForms

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.

See Also

This tutorial is also available for AngularJS: AngularJS Doctor Appointment Scheduling (PHP)

1. Public Interface for Patients (Requesting an Appointment)

asp.net-doctor-appointment-public-interface.png

The public interface (Default.aspx) uses the ASP.NET event calendar control to display available appointment slots.

  • All appointment slots are created in the backend in advance.
  • The scheduled appointments are not visible in the public interface.
  • The user (patient) can click a free slot to request an appointment.

Requesting an Appointment

asp.net-doctor-appointment-scheduling-request.png

The user can click on a selected appointment slot and display a "Request an Appointment" modal dialog

<div>
    <p>Available time slots:</p>
    
    <DayPilot:DayPilotCalendar 
        runat="server" 
        ID="DayPilotCalendar1"
        
        ...
        
        EventClickHandling="JavaScript"
        EventClickJavaScript="edit(e)"
        />
</div>    
    
    
<script>
    function edit(e) {
        new DayPilot.Modal({
            onClosed: function (args) {
                if (args.result == "OK") {
                    dp.commandCallBack('refresh');
                }
            }
        }).showUrl("Request.aspx?id=" + e.id());
    }
</script>

asp.net-doctor-appointment-scheduling-waiting.png

After entering the name the appointment status will be changed to "waiting" and it will be displayed with an orange bar.

For the purposes of this demo the appointment is recognized using the current user session id.

C#

protected void ButtonOK_Click(object sender, EventArgs e)
{
  string name = TextBoxName.Text;

  int id = Convert.ToInt32(Request.QueryString["id"]);

  Db.RequestAppointment(id, name, Session.SessionID);
  Modal.Close(this, "OK");
}

VB

Protected Sub ButtonOK_Click(ByVal sender As Object, ByVal e As EventArgs)
  Dim name As String = TextBoxName.Text

  Dim id_Renamed As Integer = Convert.ToInt32(Request.QueryString("id"))

  Db.RequestAppointment(id_Renamed, name, Session.SessionID)
  Modal.Close(Me, "OK")
End Sub

The public interface displays all free slots and also all slots belonging to the current user. In this sample project, the user is recognized using the session ID (Session.SessionID) - normally this would be the ID from a user database.

C#

public static DataTable LoadFreeAndMyAppointments(DateTime start, DateTime end, string sessionId)
{
  SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM [Appointment] WHERE ([AppointmentStatus] = 'free' OR ([AppointmentStatus] <> 'free' AND [AppointmentPatientSession] = @session)) AND NOT (([AppointmentEnd] <= @start) OR ([AppointmentStart] >= @end))", ConfigurationManager.ConnectionStrings["daypilot"].ConnectionString);
  da.SelectCommand.Parameters.AddWithValue("session", sessionId);
  da.SelectCommand.Parameters.AddWithValue("start", start);
  da.SelectCommand.Parameters.AddWithValue("end", end);
  DataTable dt = new DataTable();
  da.Fill(dt);

  return dt;

}

VB

Public Shared Function LoadFreeAndMyAppointments(ByVal start As Date, ByVal [end] As Date, ByVal sessionId As String) As DataTable
  Dim da As New SqlDataAdapter("SELECT * FROM [Appointment] WHERE ([AppointmentStatus] = 'free' OR ([AppointmentStatus] <> 'free' AND [AppointmentPatientSession] = @session)) AND NOT (([AppointmentEnd] <= @start) OR ([AppointmentStart] >= @end))", ConfigurationManager.ConnectionStrings("daypilot").ConnectionString)
  da.SelectCommand.Parameters.AddWithValue("session", sessionId)
  da.SelectCommand.Parameters.AddWithValue("start", start)
  da.SelectCommand.Parameters.AddWithValue("end", [end])
  Dim dt As New DataTable()
  da.Fill(dt)

  Return dt

End Function

2. Manager's Interface (Scheduling Shifts)

The management interface (Manager.aspx) allows creating appointment slots. This view displays a timeline for all doctors and allows scheduling shifts.

Shifts View

asp.net-doctor-appointment-scheduling-manager-shifts.png

This view uses the ASP.NET scheduler control to display appointments for all doctors.

Doctors are defined as resources:

C#

private void LoadDoctors()
{
  DataTable doctors = Db.LoadDoctors();

  DayPilotScheduler1.Resources.Clear();
  foreach (DataRow row in doctors.Rows)
  {
      DayPilotScheduler1.Resources.Add((string)row["DoctorName"], Convert.ToString(row["DoctorId"]));
  }
}

VB

Private Sub LoadDoctors()
  Dim doctors As DataTable = Db.LoadDoctors()

  DayPilotScheduler1.Resources.Clear()
  For Each row As DataRow In doctors.Rows
    DayPilotScheduler1.Resources.Add(DirectCast(row("DoctorName"), String), Convert.ToString(row("DoctorId")))
  Next row
End Sub

The time header displays a custom timeline. The timeline only includes the pre-defined shifts (morning shift from 9am to 13pm, afternoon shift from 2pm to 6pm).

C#

private const int MorningShiftStarts = 9;
private const int MorningShiftEnds = 13;

private const int AfternoonShiftStarts = 14;
private const int AfternoonShiftEnds = 18;

private void LoadTimelineShifts()
{
  DayPilotScheduler1.Scale = TimeScale.Manual;
  DayPilotScheduler1.Timeline.Clear();

  for (int i = 0; i < DayPilotScheduler1.Days; i++)
  {
      DateTime day = DayPilotScheduler1.StartDate.AddDays(i);

      DayPilotScheduler1.Timeline.Add(day.AddHours(MorningShiftStarts), day.AddHours(MorningShiftEnds));
      DayPilotScheduler1.Timeline.Add(day.AddHours(AfternoonShiftStarts), day.AddHours(AfternoonShiftEnds));
  }

  DayPilotScheduler1.TimeHeaders.Clear();
  DayPilotScheduler1.TimeHeaders.Add(new TimeHeader(GroupByEnum.Month));
  DayPilotScheduler1.TimeHeaders.Add(new TimeHeader(GroupByEnum.Day, "ddd d"));
  DayPilotScheduler1.TimeHeaders.Add(new TimeHeader(GroupByEnum.Cell, "tt"));

}

VB

Private Const MorningShiftStarts As Integer = 9
Private Const MorningShiftEnds As Integer = 13

Private Const AfternoonShiftStarts As Integer = 14
Private Const AfternoonShiftEnds As Integer = 18

Private Sub LoadTimelineShifts()
  DayPilotScheduler1.Scale = TimeScale.Manual
  DayPilotScheduler1.Timeline.Clear()

  For i As Integer = 0 To DayPilotScheduler1.Days - 1
    Dim day As Date = DayPilotScheduler1.StartDate.AddDays(i)

    DayPilotScheduler1.Timeline.Add(day.AddHours(MorningShiftStarts), day.AddHours(MorningShiftEnds))
    DayPilotScheduler1.Timeline.Add(day.AddHours(AfternoonShiftStarts), day.AddHours(AfternoonShiftEnds))

  Next i

  DayPilotScheduler1.TimeHeaders.Clear()
  DayPilotScheduler1.TimeHeaders.Add(New TimeHeader(GroupByEnum.Month))
  DayPilotScheduler1.TimeHeaders.Add(New TimeHeader(GroupByEnum.Day, "ddd d"))
  DayPilotScheduler1.TimeHeaders.Add(New TimeHeader(GroupByEnum.Cell, "tt"))

End Sub

Hours View

asp.net-doctor-appointment-scheduling-manager-hours.png

It is possible to switch to a more detailed "hours" scale.

  • The current cell size (shifts/hours) is stored in the ClientState property ("size").
  • A refresh of the Scheduler is requested using .commandCallBack() client-side method.

.aspx

<div class="space">Scale: <a href="javascript:scale('shifts')">Shifts</a> | <a href="javascript:scale('hours')">Hours</a></div> 

<script type="text/javascript">
    function scale(size) {
        dp.clientState.size = size;
        dp.commandCallBack("refresh");
    }
</script>

C#

protected void DayPilotScheduler1_OnCommand(object sender, CommandEventArgs e)
{
  switch (e.Command)
  {
      case "refresh":
          LoadTimeline();
          LoadDoctors();
          LoadAppointments();
          break;

      // ...

  }
}

private void LoadTimeline()
{

  string scaleSize = (string)DayPilotScheduler1.ClientState["size"];

  switch (scaleSize)
  {
      case "hours":
          LoadTimelineHours();
          break;
      case "shifts":
          LoadTimelineShifts();
          break;
  }
}

private void LoadTimelineHours()
{

  DayPilotScheduler1.Scale = TimeScale.Manual;
  DayPilotScheduler1.Timeline.Clear();

  for (int i = 0; i < DayPilotScheduler1.Days; i++)
  {
      DateTime day = DayPilotScheduler1.StartDate.AddDays(i);

      for (int x = MorningShiftStarts; x < MorningShiftEnds; x++)
      {
          DayPilotScheduler1.Timeline.Add(day.AddHours(x), day.AddHours(x + 1));
      }
      for (int x = AfternoonShiftStarts; x < AfternoonShiftEnds; x++)
      {
          DayPilotScheduler1.Timeline.Add(day.AddHours(x), day.AddHours(x + 1));
      }

  }

  DayPilotScheduler1.TimeHeaders.Clear();
  DayPilotScheduler1.TimeHeaders.Add(new TimeHeader(GroupByEnum.Month));
  DayPilotScheduler1.TimeHeaders.Add(new TimeHeader(GroupByEnum.Day, "ddd d"));
  DayPilotScheduler1.TimeHeaders.Add(new TimeHeader(GroupByEnum.Hour, "ht"));

}

VB

Protected Sub DayPilotScheduler1_OnCommand(ByVal sender As Object, ByVal e As CommandEventArgs)
  Select Case e.Command
    Case "refresh"
      LoadTimeline()
      LoadDoctors()
      LoadAppointments()
      
    ' ...
  End Select
End Sub

Private Sub LoadTimeline()

  Dim scaleSize As String = CStr(DayPilotScheduler1.ClientState("size"))
  Select Case scaleSize
    Case "hours"
      LoadTimelineHours()
    Case "shifts"
      LoadTimelineShifts()
  End Select
End Sub

Private Sub LoadTimelineHours()

  DayPilotScheduler1.Scale = TimeScale.Manual
  DayPilotScheduler1.Timeline.Clear()

  For i As Integer = 0 To DayPilotScheduler1.Days - 1
    Dim day As Date = DayPilotScheduler1.StartDate.AddDays(i)

    For x As Integer = MorningShiftStarts To MorningShiftEnds - 1
      DayPilotScheduler1.Timeline.Add(day.AddHours(x), day.AddHours(x + 1))
    Next x
    For x As Integer = AfternoonShiftStarts To AfternoonShiftEnds - 1
      DayPilotScheduler1.Timeline.Add(day.AddHours(x), day.AddHours(x + 1))
    Next x

  Next i

  DayPilotScheduler1.TimeHeaders.Clear()
  DayPilotScheduler1.TimeHeaders.Add(New TimeHeader(GroupByEnum.Month))
  DayPilotScheduler1.TimeHeaders.Add(New TimeHeader(GroupByEnum.Day, "ddd d"))
  DayPilotScheduler1.TimeHeaders.Add(New TimeHeader(GroupByEnum.Hour, "ht"))

End Sub

Appointment Status (Free, Waiting, Confirmed)

The scheduler displays appointments with a color bar depending on the appointment status.

asp.net-doctor-appointment-status-free.png

New appointment slots have a "free" status. They are displayed with a green bar.

asp.net-doctor-appointment-status-waiting.png

Slots booked by patients using the public interface have a "waiting" status and are displayed with an orange bar.

asp.net-doctor-appointment-status-confirmed.png

As soon as the appointment request is confirmed by the doctor the status changes to "confirmed" and the appointment is displayed with a red bar.

The status is stored in [AppointmentStatus] database field. This field is loaded as a special "tag" using DataTagFields property:

C#

private void LoadAppointments()
{
  DataTable table = Db.LoadAppointments(DayPilotScheduler1.VisibleStart, DayPilotScheduler1.VisibleEnd);
  DayPilotScheduler1.DataSource = table;
  DayPilotScheduler1.DataIdField = "AppointmentId";
  DayPilotScheduler1.DataTextField = "AppointmentPatientName";
  DayPilotScheduler1.DataStartField = "AppointmentStart";
  DayPilotScheduler1.DataEndField = "AppointmentEnd";
  DayPilotScheduler1.DataTagFields = "AppointmentStatus";
  DayPilotScheduler1.DataResourceField = "DoctorId";
  DayPilotScheduler1.DataBind();
  DayPilotScheduler1.Update();
}

VB

Private Sub LoadAppointments()
  Dim table As DataTable = Db.LoadAppointments(DayPilotScheduler1.VisibleStart, DayPilotScheduler1.VisibleEnd)
  DayPilotScheduler1.DataSource = table
  DayPilotScheduler1.DataIdField = "AppointmentId"
  DayPilotScheduler1.DataTextField = "AppointmentPatientName"
  DayPilotScheduler1.DataStartField = "AppointmentStart"
  DayPilotScheduler1.DataEndField = "AppointmentEnd"
  DayPilotScheduler1.DataTagFields = "AppointmentStatus"
  DayPilotScheduler1.DataResourceField = "DoctorId"
  DayPilotScheduler1.DataBind()
  DayPilotScheduler1.Update()
End Sub

This tag is later available in BeforeEventRender event handler and we can use it to set a custom bar color:

C#

protected void DayPilotScheduler1_OnBeforeEventRender(object sender, BeforeEventRenderEventArgs e)
{
  string status = e.Tag["AppointmentStatus"];
  switch (status)
  {
      case "free":
          e.DurationBarColor = "green";
          e.EventDeleteEnabled = ScaleResolved == "hours";  // only allow deleting in the more detailed hour scale mode
          break;
      case "waiting":
          e.DurationBarColor = "orange";
          e.EventDeleteEnabled = false;
          break;
      case "confirmed":
          e.DurationBarColor = "#f41616";  // red            
          e.EventDeleteEnabled = false;
          break;
  }

}

VB

Protected Sub DayPilotScheduler1_OnBeforeEventRender(ByVal sender As Object, ByVal e As BeforeEventRenderEventArgs)
  Dim status As String = e.Tag("AppointmentStatus")
  Select Case status
    Case "free"
      e.DurationBarColor = "green"
      e.EventDeleteEnabled = ScaleResolved = "hours" ' only allow deleting in the more detailed hour scale mode
    Case "waiting"
      e.DurationBarColor = "orange"
      e.EventDeleteEnabled = False
    Case "confirmed"
      e.DurationBarColor = "#f41616" ' red
      e.EventDeleteEnabled = False
  End Select

End Sub

3. Doctor's Interface (Scheduling Appointments)

asp.net-doctor-appointments.png

The doctor's view (Doctor.aspx) displays a full week (all 24 hours each day) for the selected doctor.

It allows editing the appointment slots:

  • Changing the slot status
  • Moving a resizing appointment slots using drag and drop
  • Deleting appointment slots

asp.net-doctor-appointment-edit-slot.png

The Edit.aspx page displays appointment details in a modal dialog.

4. SQL Server Database Schema

asp.net-doctor-appointment-sql-server-database-schema.png

[Doctor] Table

CREATE TABLE [dbo].[Doctor] (
    [DoctorId]   INT            IDENTITY (1, 1) NOT NULL,
    [DoctorName] NVARCHAR (100) NOT NULL,
    PRIMARY KEY CLUSTERED ([DoctorId] ASC)
);

[Appointment] Table

CREATE TABLE [dbo].[Appointment] (
    [AppointmentId]             INT            IDENTITY (1, 1) NOT NULL,
    [AppointmentStart]          DATETIME       NOT NULL,
    [AppointmentEnd]            DATETIME       NOT NULL,
    [DoctorId]               INT            NOT NULL,
    [AppointmentPatientName]    NVARCHAR (100) NULL,
    [AppointmentStatus]         NVARCHAR (100) DEFAULT ('free') NOT NULL,
    [AppointmentPatientSession] NVARCHAR (100) NULL,
    PRIMARY KEY CLUSTERED ([AppointmentId] ASC)
);