How to: Use the FetchAppointments Event for Faster Appointment Loading

• Oct 29, 2020
• 4 minutes to read

To speed up processing when there is a large amount of data in the Scheduler Storage, you can use the SchedulerStorageBase.FetchAppointments event. Handle this event to fetch data for the visible time interval only. Before looking at the example, it is recommended that you review the FetchAppointments Event - Handling Large Datasets article.

Create an example that demonstrates the use of the FetchAppointments event by following the steps below.

1. Create a project with the Scheduler bound to SQL Server data.

   Important

   Add a mapping for the Appointment.Id property value using the AppointmentMappingInfo.AppointmentId property. This is required for correct appointment resizing beyond the visible interval when handling the SchedulerStorageBase.FetchAppointments event.

2. Add a parameterized query that will be executed in the FetchAppointment event handler. This query should combine two expressions:

   • select all regular appointments that belong to the given interval;
   • select all changed occurences that initially belonged to this interval (see the AppointmentMappingInfo.OriginalOccurrenceStart and AppointmentMappingInfo.OriginalOccurrenceEnd mappings). Since the Scheduler Storage loads all appointment patterns automatically, you need this second expression to let the Scheduler know an occurence that should have been in this interval was changed and there is no need to show it.
   SQL

   SELECT * FROM Appointments WHERE
   (OriginalOccurrenceStart <= @end AND OriginalOccurrenceEnd >= @start)
   OR
   (StartTime <= @end AND EndTime >= @start)
   

   Double-click the DataSet xsd file to open a data set designer. Right-click the AppointmentsTableAdapter and select Add Query… in the context menu.

   

   The TableAdapter Query Configuration Wizard is invoked. Enter the query text and click Finish.

3. We need a way to modify this query at runtime to specify resources. To access the TableAdapter queries, add a new property as shown in the following code snippet:

   View Example

4. Add the FetchAppointments handler, as shown in the following code snippet.

   To pad the time interval used to query appointments, we use a range of seven days (PADDING_DAYS constant). You can specify another interval arbitrarily.

   View Example

   Form1.cs

   void schedulerStorage1_FetchAppointments(object sender, FetchAppointmentsEventArgs e) {
       ResourceBaseCollection resourcesVisible = schedulerControl1.ActiveView.GetResources();
       var differentResources = resourcesVisible.Except(lastQueriedResources);
   
       // If a different resource is displayed or the visible time interval intersects the time interval used in a recent query, fetch the data.
       if ((differentResources.Count() != 0) || (e.Interval.Start < lastQueriedTimeInterval.Start) || (e.Interval.End > lastQueriedTimeInterval.End)) {
           QueryAppointmentDataSource(e, resourcesVisible);
           lastQueriedResources = resourcesVisible;
           lastQueriedTimeInterval = e.Interval;
       }
   }
   
   private void QueryAppointmentDataSource(FetchAppointmentsEventArgs e, ResourceBaseCollection resources) {
       string resListString = String.Join(",", resources.Select(res => res.Id.ToString()));
       // Modify the FillBy query to fetch appointments only for the specified resources. 
       appointmentsTableAdapter.Commands[1].CommandText =
           String.Format("SELECT Appointments.* FROM Appointments WHERE (OriginalOccurrenceStart <= @end AND OriginalOccurrenceEnd >= @start) OR (EndTime >= @start AND StartTime <= @end)", resListString);
       appointmentsTableAdapter.FillBy(this.scheduleTestDataSet.Appointments, e.Interval.Start.AddDays(-PADDING_DAYS), e.Interval.End.AddDays(PADDING_DAYS));
   
       queryExecutionCounter++;
   }
   

   Form1.vb

   Private Sub schedulerStorage1_FetchAppointments(ByVal sender As Object, ByVal e As FetchAppointmentsEventArgs)
       Dim resourcesVisible As ResourceBaseCollection = schedulerControl1.ActiveView.GetResources()
       Dim differentResources = resourcesVisible.Except(lastQueriedResources)
   
       ' If a different resource is displayed or the visible time interval intersects the time interval used in a recent query, fetch the data.
       If (differentResources.Count() <> 0) OrElse (e.Interval.Start < lastQueriedTimeInterval.Start) OrElse (e.Interval.End > lastQueriedTimeInterval.End) Then
           QueryAppointmentDataSource(e, resourcesVisible)
           lastQueriedResources = resourcesVisible
           lastQueriedTimeInterval = e.Interval
       End If
   End Sub
   
   Private Sub QueryAppointmentDataSource(ByVal e As FetchAppointmentsEventArgs, ByVal resources As ResourceBaseCollection)
       Dim resListString As String = String.Join(",", resources.Select(Function(res) res.Id.ToString()))
       ' Modify the FillBy query to fetch appointments only for the specified resources. 
       appointmentsTableAdapter.Commands(1).CommandText = String.Format("SELECT Appointments.* FROM Appointments WHERE (OriginalOccurrenceStart >= @Start) AND(OriginalOccurrenceEnd <= @End) AND (ResourceID IN ({0})) OR (Type != 0)", resListString)
       appointmentsTableAdapter.FillBy(Me.scheduleTestDataSet.Appointments, e.Interval.Start.AddDays(-PADDING_DAYS), e.Interval.End.AddDays(PADDING_DAYS))
   
       queryExecutionCounter += 1
   End Sub
   

5. Run the project. Navigate the schedule using the DateNavigator control, and observe the changes in the number of rows in the appointment data source.