ASP.NET Doctor Appointment Scheduling (C#, VB.NET, SQL Server)

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 (LocalDB)

• Visual Studio 2017+

• 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 PHP: HTML5 Doctor Appointment Scheduling (JavaScript/PHP/MySQL)

1. Public Interface for Patients (Requesting an Appointment)

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

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());
    }


    function edit(e) {
        var form = [
            { name: "Start", id: "start", dateFormat: "MMMM d, yyyy hh:mm tt", disabled: true },
            { name: "End", id: "end", dateFormat: "MMMM d, yyyy hh:mm tt", disabled: true },
            { name: "Your name", id: "name" },
        ];

        var data = {
            id: e.id(),
            start: e.start(),
            end: e.end()
        };

        DayPilot.Modal.form(form, data).then(function(modal) {
            if (modal.canceled) {
                return;
            }
            DayPilot.Http.ajax({
                url: "Default.aspx/SaveRequest",
                data: modal.result,
                success: function(ajax) {
                    dp.commandCallBack('refresh');
                    dp.message("Saved");
                }
            });
        });
    }
</script>

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

The modal dialog saves the changes using a call to a SaveRequest() WebMethod. This tutorial doesn’t use user authentication for simplicity. Instead, the current session is is used to detect the appointment owner.

[WebMethod(EnableSession = true), ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public static string SaveRequest(int id, string name)
{
    // int id = Convert.ToInt32(idStr);
    Db.RequestAppointment(id, name, HttpContext.Current.Session.SessionID);
    Hashtable result = new Hashtable();
    result["status"] = "OK";
    return JsonConvert.SerializeObject(result);
}

<WebMethod(EnableSession:=True), ScriptMethod(ResponseFormat:=ResponseFormat.Json)>
Public Shared Function SaveRequest(ByVal id As Integer, ByVal name As String) As String
  Db.RequestAppointment(id, name, HttpContext.Current.Session.SessionID)
  Dim result As Hashtable = New Hashtable()
  result("status") = "OK"
  Return JsonConvert.SerializeObject(result)
End Function

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.

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;

}

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

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

Doctors are defined as resources:

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"]));
  }
}

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

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"));

}

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

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.

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

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"));

}

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.

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

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

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:

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();
}

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:

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

}

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 = "#6aa84f"  ' green
      e.EventDeleteEnabled = ScaleResolved = "hours" ' only allow deleting in the more detailed hour scale mode
    Case "waiting"
      e.DurationBarColor = "#e69138"  ' orange
      e.EventDeleteEnabled = False
    Case "confirmed"
      e.DurationBarColor = "#cc0000" ' red
      e.EventDeleteEnabled = False
  End Select

End Sub

3. Doctor's Interface (Scheduling Appointments)

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

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

4. SQL Server Database Schema

[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)
);

History

• December 21, 2020: DayPilot Pro for ASP.NET WebForms 2020.4.3775; using DayPilot.Modal.form() for dialogs

• Sep 21, 2017: Visual Studio 2017, SQL Server 2014+, DayPilot Pro for ASP.NET WebForms 8.4

• Jun 30, 2015: Initial release