How to: Use the FetchAppointments Event for Faster Appointment Loading

• Jan 23, 2019
• 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. A new parameterized query is required for the FetchAppointment event handler. It will contain From and To parameters, which specify the time interval in which the data will be queried. Note that appointments of the AppointmentType.Pattern type should always be loaded, otherwise all recurring appointments for the series based on this pattern will be lost. To load changed and deleted occurrences, compare the dates contained in the fields mapped with AppointmentMappingInfo.OriginalOccurrenceStart and AppointmentMappingInfo.OriginalOccurrenceEnd mappings instead of Start and End. Enter the following query string in the FillBy Method of the AppointmentsTableAdapter.

   SQL

   
   SELECT Appointments.*
   FROM Appointments 
   WHERE 
   (OriginalOccurrenceStart >= @Start) 
   AND (OriginalOccurrenceEnd <= @End) 
   OR  (Type != 0)
   

   The underlying data source does not contain all appointments in a recurrence series. Only pattern, changed and deleted appointments are stored. When a pattern appointment is loaded into the SchedulerControl, it calculates all appointments in the series and displays them in the View. Thus, the loading of appointment patterns does not result in performance issues in the case of a large data source.

   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:

   Note

   A complete sample project is available at https://github.com/DevExpress-Examples/how-to-use-fetchappointments-event-for-handling-large-appointment-sets-e2730

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.

   Note

   A complete sample project is available at https://github.com/DevExpress-Examples/how-to-use-fetchappointments-event-for-handling-large-appointment-sets-e2730

   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 >= @Start) AND(OriginalOccurrenceEnd <= @End) AND (ResourceID IN ({0})) OR (Type != 0)", 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.