Article relates to
RadScheduler for WPF Q3 2008
January, 10, 2009
Last modified by
HOW TO bind RadScheduler to SQL data source using LINQ?
One of the most common ways of storing and retrieving data for WPF applications is using a SQL database for storage of data and LINQ as a means of extracting and manipulating the data. This article shows how this approach can be applied to the RadScheduler control. Below are explained the main steps – database design, creating LINQ to SQL classes and SQL provider implementation for RadScheduler.
Create the SQL database
The database diagram used in the sample is shown below:
The SqlAppointments table stores the regular appointments, as well as the appointments being exceptions to regular ones. Regular and exception appointments are differentiated in the table by the integer Type field (0 means regular, 1 means exception). Because the RecurrenceRule property of appointments is quite complex to be stored in a single field, we worked around this by storing only the RecurrenceRule.Pattern property as a string in the [RecurrencePattern] field. Thus, it is quite easy to create the recurrence rules in the code. There is a drawback that we cannot recreate the RecurrenceRule.Exceptions property using the pattern. Here comes the AppointmentExceptions table.
The AppointmentExceptions table contains records, which represent exceptions to a given appointment. The [MasterUniqueId] field holds the UniqueId of the appointment that is Master of the exception, e.g. it “points out” the appointment, to which the exception applies. This relationship ensures that you to know each recurrent appointment’s exceptions and even better - with Linq to Sql classes there is a property called AppointmentExceptions applying for each SqlAppointment entity. If the [AppointmentUniqueId] field contains NULL value, this will mean that the exception is a deleted occurrence of a given appointment. Otherwise, this field will hold the UniqueId of an appointment, whose Type field is set to 1 meaning that this is an exception.
One more step concerning the database should be mentioned and it is related to the creation of one of the relationships between the two tables – Exceptions -> MasterAppointments (the one with a foreign key [MasterUniqueId] field). The delete Rule of the relationship should be set to "Cascade" in the database.
If you apply the cascade delete rule to the aforementioned database relationship, then you will not have to manually delete the children every time the parent is deleted when working with the DataContext. Later in this article you will see how this SQL setting is interpreted in the .dbml file.
Create LINQ to SQL classes
Once the database is ready, you can proceed with adding the LINQ to SQL classes file and then expose the database to it. Visual Studio will automatically generate classes for you - SqlAppointment, ExceptionAppointment and all the related code needed for the relationships between the two corresponding tables in the database. By Selecting each individual arrow that shows the associations, the IDE will give you the opportunity to modify the names of auto-generated properties, access, inheritance modifiers and so on. For better understanding, the name of the child property in the relation SqlAppointment.UniqueId->AppointmentException.MasterUniqueId was changed from AppointmentExceptions1 to RecurrenceRuleExceptions further down in the code.
<Association Name="SqlAppointment_AppointmentException1" Member="SqlAppointment1" ThisKey="MasterUniqueId" OtherKey="UniqueId" Type="SqlAppointment" IsForeignKey="true" DeleteRule="CASCADE" DeleteOnNull="true" />
As you can see, the DeleteRule="CASCADE" DeleteOnNull="true" were automatically inserted. If you choose not to cascade delete from the database and to do it manually from the code, you should manually add the following option to the .dbml file. Otherwise, an exception will be thrown:
Although in the example this problem has been avoided, sometimes you will not have the ability to modify the database you are working with. In such cases you will need to manually change the generated .dbml in order to avoid the above error.
As RadScheduler works with objects that implement the IAppointment interface you will have to make the SqlAppointment objects suitable. It is not a good idea to change the generated code as regenerating it will lose the code.
The generated classes are partial, so the only step you need to take is to create another file containing a partial SqlAppointment class and implement IAppointment.
There are several interesting points in this class:
• The RecurrenceRule property - as mentioned above to make it simpler you can store only the recurrence pattern for each recurrent appointment in the database. This means that you will need to find a way to “convert” the complex RecurrenceRule property of the appointment object used by RadScheduler to a simple string field of the database without any loss of information and vice versa. The code below shows how you can get the RecurrenceRule from the database:
As shown above, the TryParseRecurrencePattern() method from RadScheduler’s CalHelper class is used to make the conversion from a string to a RecurrencePattern object. The only drawback here is that because the new ReccurenceRule object is created using the pattern, you will not have information at this stage about any exceptions of that rule.
The code used to set the RecurrenceRule is: