Using The RadScheduler With The Sysschedules Table In Sql

[Ben]

I've tried to figure out how to set up a scheduling rule but i think im either reading the examples wrong or it's not what im trying to do. The project im currently working on needs to show a schedule of our sql jobs that are running. Ive built a table in my database with relation to the following criteria . I've noticed that the Rad Scheduler's recurrence rules are set with the same criteria. How would i use the criteria to set a recurrence rule? or supplying the data for the reccurene storage?

[Brian Mains]

Hey,

You can build a recurrence rules using the RecurrencePattern and RecurrenceRange objects.  Then, you create the rule using:

RecurrenceRule.FromPatternAndRange(...);

To create the rule, and assign it to the RecurrenceRules string property of the Appointment object.

[Peter]

Hi Ben,

You can also refer to this topic:
http://www.telerik.com/help/aspnet-ajax/schedule_serversideworkingwithrecurringappointments.html



Peter
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[Ben]

Thanks for the help guys, the example looks like it does what i need.

[Ben]

Alright so i think i have everything down pretty well..the example was a lot of help. I did however run into a problem referring back to my standards set. The Sysschedules table has the Freq_type column and freq_interval column. If the freq_type record is 16, then the job executes monthly specifying the day of the month for execution. If Freq_type is 32 then it specyfies a specific day such as thursday or monday.

If the Freq_type is 16, Freq_interval is 6 and the Freq_recurrence_factor is 2 then the description created up by sql is...
"Occurs every 2 month(s) on day 6 of that month at 12:00:00 AM. Schedule will be used starting on 11/10/2008."

If the Freq_type is 32, Freq_interval is 5 and the Freq_recurrence_factor is 6 then the description created up by sql is...
"Occurs every third Thursday of every 6 month(s) at 12:00:00 AM. Schedule will be used starting on 12/5/2008.

So my question now is how yould you use the MonthlyRecurrenceRule to specify the day of the week and that week of the month like "third thursday of every 6 months"?

I did see that you can do
monthlyrecurrencerule(dayofmonth,interval,range)
and
monthlyrecurrencerule(dayordinal,daysofweekmask,interval,range)

I figure its the 2nd one i should be using but im not quite sure what a dayordinal is other then every day of the year such as 1-365.

[Brian Mains]

Hey,

I setup this test for myself to determine what were the pattern/range values for an object.  For each of those scenarios is:

Subject: Month by day recur

Start: 3/3/2009 8:30:00 AM/End: 3/3/2009 9:30:00 AM

Recurs: DTSTART:20090303T083000Z DTEND:20090303T093000Z RRULE:FREQ=MONTHLY;COUNT=20;INTERVAL=2;BYMONTHDAY=5

Pattern: Day of Month: 5, Day Ordinal: 0, Day of Week Mask: None, First Day of Week: Sunday, Frequency: Monthly, Interval: 2, Month: None

Range: Start: 3/3/2009 8:30:00 AM, Duration: 01:00:00, Max Occurrences: 20, Recurs Until: 12/31/9999 11:59:59 PM

Exceptions:

 

 

 

Subject: Third Tuesday Recur

Start: 3/4/2009 9:00:00 AM/End: 3/4/2009 10:00:00 AM

Recurs: DTSTART:20090304T090000Z DTEND:20090304T100000Z RRULE:FREQ=MONTHLY;UNTIL=20110401T000000Z;INTERVAL=1;BYSETPOS=3;BYDAY=TU

Pattern: Day of Month: 0, Day Ordinal: 3, Day of Week Mask: Tuesday, First Day of Week: Sunday, Frequency: Monthly, Interval: 1, Month: None

Range: Start: 3/4/2009 9:00:00 AM, Duration: 01:00:00, Max Occurrences: 2147483647, Recurs Until: 4/1/2011 12:00:00 AM

Exceptions:

Of course, you have to tailor them a little bit, but HTH.

[Ben]

I'm not quite sure what your doing there...here is what i have...

        Dim rd As SqlClient.SqlDataReader = Data.SqlHelper.ExecuteReader(ConnString, "dbo.JobSelect")
        While rd.Read

            Dim da As New Appointment(rd.GetInt32(0), rd.GetDateTime(12), rd.GetDateTime(13), rd.GetString(1))
            Dim range As New RecurrenceRange
            range.Start = da.Start
            range.EventDuration = da.End - da.Start

            Select Case rd.GetInt32(5)
                Case 4
                    Dim DailyRR As New DailyRecurrenceRule(rd.GetInt32(6), range)
                Case 8
                    Dim WeeklyRR As New WeeklyRecurrenceRule(rd.GetInt32(10), rd.GetInt32(6), range)
                Case 16
                    Dim MonthlyRR As New MonthlyRecurrenceRule(rd.GetInt32(6), rd.GetInt32(10), range)
                Case 32
                    Dim MonthlyRelativeRR As New MonthlyRecurrenceRule(rd.GetInt32(6), rd.GetInt32(10), range)
            End Select

            Select Case rd.GetInt32(7)
                Case 4
                    Dim MinutesRR As New HourlyRecurrenceRule((60 / rd.GetInt32(8)), range)
                Case 8
                    Dim HourlyRR As New HourlyRecurrenceRule(rd.GetInt32(8), range)
            End Select

            'If rd.GetInt32 Then
            'Dim rrule As New HourlyRecurrenceRule(




            RadCal.InsertAppointment(da)
        End While

[Brian Mains]

For the Third Tuesday rule, for example, you'd use:

new MonthlyRecurrenceRule(3, DaysOfWeekMask.Tuesday, 1, range);

//where 3 is the 3rd week of, and  the 1 is the every one month interval parameters

 

For the other:

 

new MonthlyRecurrenceRule(5, 2, range);
//where 5 is day 5, and 2 is every 2 month interval parameters

[Ben]

But how would that work in relation to the sysschedules table? See with dayofweekmask its accurate up until freq_type is equal to 32...at that point the day's id's aren't the same as the day of weekmask...you can see what im talking about below

1 (once) freq_interval is unused (0)

4 (daily) Every freq_interval days

8 (weekly) freq_interval is one or more of the following: 1 = Sunday 2 = Monday 4 = Tuesday 8 = Wednesday 16 = Thursday 32 = Friday 64 = Saturday

16 (monthly) On the freq_interval day of the month

32 (monthly, relative) freq_interval is one of the following: 1 = Sunday 2 = Monday 3 = Tuesday 4 = Wednesday 5 = Thursday 6 = Friday 7 = Saturday 8 = Day 9 = Weekday 10 = Weekend day

64 (starts when SQL Server Agent service starts) freq_interval is unused (0)

128 (runs when computer is idle) freq_interval is unused (0)

[Brian Mains]

Hey,

DaysOfWeekMask is represented by the RecurrenceDay enumeration, which does have Weekdays or WeekendDays as options as well.  The only thing I didn't see was option 8 - Day.  What does that mean?

Is there another variance I didn't see?  I'm not quite sure as to all the rules the 32 option has...

It may be the case where multiple appointments handle this situation...

Brian

[Ben]

Yeah, im not sure about that...i doubt it will be used in my app though. All the 8-Day option is for  "The third Day of every 6 months".
However i do think i may have the code right...im just not sure how to actually trigger the recurrence to show up as appointments on the control.

Here's what i have...

            Select Case rd.GetInt32(5)
                Case 4
                    Dim DailyRR As New DailyRecurrenceRule(rd.GetInt32(6), range)
                    For Each occurrence As DateTime In DailyRR.Occurrences
                        Dim rec As New Appointment(rd.GetInt32(0), rd.GetDateTime(12), rd.GetDateTime(13), rd.GetString(1), DailyRR.ToString)
                        RadCal.InsertAppointment(rec)
                    Next
                Case 8
                    Dim WeeklyRR As New WeeklyRecurrenceRule(rd.GetInt32(10), rd.GetInt32(6), range)
                    For Each occurrence As DateTime In WeeklyRR.Occurrences
                        Dim rec As New Appointment(rd.GetInt32(0), rd.GetDateTime(12), rd.GetDateTime(13), rd.GetString(1), WeeklyRR.ToString)
                        RadCal.InsertAppointment(rec)
                    Next
                Case 16
                    Dim MonthlyRR As New MonthlyRecurrenceRule(rd.GetInt32(6), rd.GetInt32(10), range)
                    For Each occurrence As DateTime In MonthlyRR.Occurrences
                        Dim rec As New Appointment(rd.GetInt32(0), rd.GetDateTime(12), rd.GetDateTime(13), rd.GetString(1), MonthlyRR.ToString)
                        RadCal.InsertAppointment(rec)
                    Next
                Case 32
                    Dim MonthlyRelativeRR As New MonthlyRecurrenceRule(rd.GetInt32(9), rd.GetInt32(6), rd.GetInt32(10), range)
                    For Each occurrence As DateTime In MonthlyRelativeRR.Occurrences
                        Dim rec As New Appointment(rd.GetInt32(0), rd.GetDateTime(12), rd.GetDateTime(13), rd.GetString(1), MonthlyRelativeRR.ToString)
                        RadCal.InsertAppointment(rec)
                    Next
            End Select

            Select Case rd.GetInt32(7)
                Case 4
                    Dim MinutesRR As New HourlyRecurrenceRule((60 / rd.GetInt32(8)), range)
                    For Each occurrence As DateTime In MinutesRR.Occurrences
                        Dim rec As New Appointment(rd.GetInt32(0), rd.GetDateTime(12), rd.GetDateTime(13), rd.GetString(1), MinutesRR.ToString)
                        RadCal.InsertAppointment(rec)
                    Next
                Case 8
                    Dim HourlyRR As New HourlyRecurrenceRule(rd.GetInt32(8), range)
                    For Each occurrence As DateTime In HourlyRR.Occurrences
                        Dim rec As New Appointment(rd.GetInt32(0), rd.GetDateTime(12), rd.GetDateTime(13), rd.GetString(1), HourlyRR.ToString)
                        RadCal.InsertAppointment(rec)
                    Next
            End Select

[Brian Mains]

Hey,

It's hard to see in your code the various paths.  I can say for the third day in 6 months, it would be:

new MonthlyRecurrenceRule(3, 6, range);

[Ben]

Right right, i understand that im sorry haha. Really what i was looking for was to see if the for each statement looked right? After your previous examples i think i understand the requirements for the rules much better now though.

[Brian Mains]

Oh, and so even though this is implemented, you aren't seeing the recurring appointments in the Scheduler?

[Ben]

i am but they are back to back to back when i go to the next month in the month view. If i stay in initial month or the start date...they are fine. If my code should work then maybe its how i'm passing my data into the recurring appointments?

[Ben]

Yeah im really not sure whats going on to tell you the truth...it says they are recurring but the appointments are only showing up on the month that they started. Also it shows them recurring when they aren't.

[Brian Mains]

Hey,

Are you binding appointments using a DataSourceControl, using the DataSource property and calling DataBind, using the in-built provider, or simply by adding appointments to the appointments collection?  If you are using InsertAppointment to generate the appointments, I think you have to do that on every page load...

When you switch views, it needs bound again, so that may be the issue.

[Ben]

Here's the complete sub...im pulling data with the sqlhelper...Also the sub is called in the page load but have the scheduler control in an update panel.

    Public Sub AddAppointments()
        RadCal.Appointments.Clear()

        'Dim WeeklyRR As WeeklyRecurrenceRule
        'Dim DailyRR As DailyRecurrenceRule
        'Dim MonthlyRR As MonthlyRecurrenceRule

        Dim rd As SqlClient.SqlDataReader = Data.SqlHelper.ExecuteReader(ConnString, "dbo.JobSelect")
        While rd.Read

            Dim da As New Appointment(rd.GetInt32(0), rd.GetDateTime(12), rd.GetDateTime(13), rd.GetString(1))
            Dim range As New RecurrenceRange
            range.Start = da.Start
            range.EventDuration = da.End - da.Start


            Select Case rd.GetInt32(5)
                Case 4
                    Dim DailyRR As New DailyRecurrenceRule(rd.GetInt32(6), range)
                    For Each occurrence As DateTime In DailyRR.Occurrences
                        Dim rec As New Appointment(rd.GetInt32(0), rd.GetDateTime(12), rd.GetDateTime(13), rd.GetString(1), DailyRR.ToString)
                        RadCal.InsertAppointment(rec)
                    Next
                Case 8
                    Dim WeeklyRR As New WeeklyRecurrenceRule(rd.GetInt32(10), rd.GetInt32(6), range)
                    For Each occurrence As DateTime In WeeklyRR.Occurrences
                        Dim rec As New Appointment(rd.GetInt32(0), rd.GetDateTime(12), rd.GetDateTime(13), rd.GetString(1), WeeklyRR.ToString)
                        RadCal.InsertAppointment(rec)
                    Next
                Case 16
                    Dim MonthlyRR As New MonthlyRecurrenceRule(rd.GetInt32(6), rd.GetInt32(10), range)
                    For Each occurrence As DateTime In MonthlyRR.Occurrences
                        Dim rec As New Appointment(rd.GetInt32(0), rd.GetDateTime(12), rd.GetDateTime(13), rd.GetString(1), MonthlyRR.ToString)
                        RadCal.InsertAppointment(rec)
                    Next
                Case 32
                    Dim MonthlyRelativeRR As New MonthlyRecurrenceRule(rd.GetInt32(9), rd.GetInt32(6), rd.GetInt32(10), range)
                    For Each occurrence As DateTime In MonthlyRelativeRR.Occurrences
                        Dim rec As New Appointment(rd.GetInt32(0), rd.GetDateTime(12), rd.GetDateTime(13), rd.GetString(1), MonthlyRelativeRR.ToString)
                        RadCal.InsertAppointment(rec)
                    Next
            End Select

            Select Case rd.GetInt32(7)
                Case 4
                    Dim MinutesRR As New HourlyRecurrenceRule((60 / rd.GetInt32(8)), range)
                    For Each occurrence As DateTime In MinutesRR.Occurrences
                        Dim rec As New Appointment(rd.GetInt32(0), rd.GetDateTime(12), rd.GetDateTime(13), rd.GetString(1), MinutesRR.ToString)
                        RadCal.InsertAppointment(rec)
                    Next
                Case 8
                    Dim HourlyRR As New HourlyRecurrenceRule(rd.GetInt32(8), range)
                    For Each occurrence As DateTime In HourlyRR.Occurrences
                        Dim rec As New Appointment(rd.GetInt32(0), rd.GetDateTime(12), rd.GetDateTime(13), rd.GetString(1), HourlyRR.ToString)
                        RadCal.InsertAppointment(rec)
                    Next
            End Select


            RadCal.InsertAppointment(da)
        End While

        '------------------------------------------------
        '------------Dummy Data--------------------------
        '------------------------------------------------
        RadCal.DataKeyField = 0
        RadCal.DataStartField = "10/15/1988"
        RadCal.DataEndField = "10/15/1988"
        RadCal.DataSubjectField = "Date"
        '------------------------------------------------
    End Sub

[Brian Mains]

Hey,

OK, a few things, in response to your data below:

RadCal.DataKeyField = 0

RadCal.DataStartField = "10/15/1988"

RadCal.DataEndField = "10/15/1988"

RadCal.DataSubjectField = "Date"

These fields should be the name of the fields you bind to; so you can  bind a data source against the scheduler, and these would be the field names to bind to, not the field data itself.

For testing purposes, try calling your AddAppointments method on every page load, or are you?

I just saw something: try changing your code to something like this instead:

case 4:
   //create recurrence rule and specify range as you do
   Dim DailyRR as new DailyRecurrenceRule(...);
   da.RecurrenceRules = DailyRR.ToString();

And that's it; instead of looping through using the occurrences collection, try just set the RecurrenceRules property to the string of the recurrence rule.
  

[Ben]

Alright, that seemed to work but it only shows the recursive appointments after i try to edit one of the appointments and hit cancel. 

[Brian Mains]

Could that be a querying data issue (the underlying data source isn't retrieving that data) or could it be a delay in some binding?  Have you debugged and found that those case statements are executing?

It might take some debugging to figure that out; in my experience with the scheduler, something wasn't getting bound at the right time.  One of the issues was I was binding data for the current date (available through the VisibleStartDate and VisibileEndDate properties) in the NavigationCommand event, instead of the NavigateComplete event.  Could that be another part of the issue?

[Ben]

Yeah i think debugging it would be best...i appreciate your assistance with this, you have been a great help.

[Brian Mains]

Not a problem; I was just working with the Scheduler and dealing with the issues, so it's fresh in my mind.