How to: Use the FetchAppointments Event for Faster Appointment Loading

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

    FetchAppointmentsExample-01

    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:

  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.

    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++;
    }
    
  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.
See Also