Populating FullCalendar events from MVC

Question

I've having difficulty in populating FullCalendar from MVC and would like a little assistance on the matter please.

I have the following code for my controller:

    Function GetEvents(ByVal [start] As Double, ByVal [end] As Double) As JsonResult
    Dim sqlConnection As New SqlClient.SqlConnection

    sqlConnection.ConnectionString = My.Settings.sqlConnection

    Dim sqlCommand As New SqlClient.SqlCommand
    sqlCommand.CommandText = "SELECT tripID AS ID, tripName AS Title, DATEDIFF(s, '1970-01-01 00:00:00', dateStart) AS [Start], DATEDIFF(s, '1970-01-01 00:00:00', dateEnd) AS [End] FROM tblTrip WHERE userID=18 AND DateStart IS NOT NULL"
    sqlCommand.Connection = sqlConnection

    Dim ds As New DataSet
    Dim da As New SqlClient.SqlDataAdapter(sqlCommand)
    da.Fill(ds, "Meetings")
    sqlConnection.Close()

    Dim meetings = From c In ds.Tables("Meetings") Select {c.Item("ID"), c.Item("Title"), "False", c.Item("Start"), c.Item("End")}

    Return Json(meetings.ToArray(), JsonRequestBehavior.AllowGet)

End Function

This does indeed run correctly but the format that is returned is:

[[25,"South America 2008","False",1203033600,1227657600],[48,"Levant 2009","False",1231804800,1233619200],[49,"South America 2009","False",1235433600,1237420800],[50,"Italy 2009","False",1241049600,1256083200],[189,"Levant 2010a","False",1265414400,1267574400],[195,"Levant 2010a","False",1262736000,1262736000],[208,"Levant 2010a","False",1264982400,1267574400],[209,"Levant 2010a","False",1264982400,1265587200],[210,"Levant 2010","False",1264982400,1266969600],[211,"Levant 2010 b","False",1267056000,1267574400],[213,"South America 2010a","False",1268438400,1269648000],[214,"Levant 2010 c","False",1266364800,1264118400],[215,"South America 2010a","False",1268611200,1269648000],[217,"South America 2010","False",1268611200,1269561600],[218,"South America 2010 b","False",1268956800,1269388800],[227,"levant 2010 b","False",1265846400,1266192000]]

And this is totally different to what I've seen on the post from here: http://stackoverflow.com/questions/2445359/jquery-fullcalendar-json-date-issue

(note the lack of tag information and curly braces)

Can someone please explain to me what I may be doing wrong and why my output isn't correctly formatted.

TIA

Answer 1

You can't just use the JsonSerializer on your objects - as you can see, it's not the correct format that FullCalendar requires.

You will need to provide your own serializer (converted from c#):

Public Class Meeting
    Public ID As Integer
    Public Title As Integer
    Public Start As DateTime
    Public [End] As DateTime
    Public AllDay As Boolean
End Class

Public Class MeetingJavaScriptConverter
    Inherits JavaScriptConverter
    Private Shared ReadOnly _supportedTypes As Type() = New Type(-1) {GetType(Meeting)}
    
    Public Overloads Overrides ReadOnly Property SupportedTypes() As IEnumerable(Of Type)
        Get
            Return _supportedTypes
        End Get
    End Property
    
    Public Overloads Overrides Function Serialize(ByVal obj As Object, ByVal serializer As JavaScriptSerializer) As IDictionary(Of String, Object)
        Dim meeting = TryCast(obj, Meeting)
        If meeting IsNot Nothing Then
            Dim dictionary = New Dictionary(Of String, Object)()
            
            dictionary.Add("id", meeting.ID)
            dictionary.Add("title", meeting.Title)
            dictionary.Add("start", meeting.Start.ToJson())
            dictionary.Add("end", meeting.[End].ToJson())
            
            dictionary.Add("allDay", If(meeting.AllDay, "true", "false"))
            
            Return dictionary
        End If
        Return New Dictionary(Of String, Object)()
    End Function
    
    Public Overloads Overrides Function Deserialize(ByVal dictionary As IDictionary(Of String, Object), ByVal type As Type, ByVal serializer As JavaScriptSerializer) As Object
        Throw New NotImplementedException()
    End Function
End Class

Public Module DateTimeExtensionMethods
    Private Sub New()
    End Sub
    <System.Runtime.CompilerServices.Extension> _
    Public Function ToJson(ByVal dateTime As DateTime) As String
        Return dateTime.ToString("s")
    End Function
End Module

You can then use it as follows, once you have populated your Meeting list from the query:

Dim serializer As New JavaScriptSerializer()
serializer.RegisterConverters(New () {New MeetingJavaScriptConverter()})
Dim jsonresult As String = serializer.Serialize(meetings.ToArray())

(Converted using: http://www.developerfusion.com/tools/convert/csharp-to-vb/ )

Answer 2

Slight issue with the code (maybe the conversion)....

This:

    Private Shared ReadOnly _supportedTypes As Type() = New () {GetType(Meeting)} 

Public Overloads Overrides ReadOnly Property SupportedTypes() As IEnumerable(Of Type) 
    Get 
        Return _supportedTypes 
    End Get 
End Property 

Isn't acceptable in VB.Net, showing an error on "= New ()"

What would the correct code be? I have tried simply having "= New Meeting" but get an error of "Value of type 'MVC.Meeting' cannot be converted to '1-dimensional array of System.Type'."

Answer 3

FAO CodeSleuth (and anyone else needing this).

Thanks to you, and you helping me 'understand' the problem, I now have it working.

My own code is as follows:

    Function GetEvents(ByVal [start] As Double, ByVal [end] As Double) As JsonResult
    Dim sqlConnection As New SqlClient.SqlConnection

    sqlConnection.ConnectionString = My.Settings.sqlConnection

    Dim sqlCommand As New SqlClient.SqlCommand
    sqlCommand.CommandText = "SELECT tripID AS ID, tripName AS Title, dateStart AS [Start], dateEnd AS [End] FROM tblTrip WHERE userID=18 AND DateStart IS NOT NULL"
    sqlCommand.Connection = sqlConnection

    Dim ds As New DataSet
    Dim da As New SqlClient.SqlDataAdapter(sqlCommand)
    da.Fill(ds, "Meetings")
    sqlConnection.Close()

    Dim meetings = From c In ds.Tables("Meetings") Select {c.Item("ID"), c.Item("Title"), "False", c.Item("Start"), c.Item("End"), "False"}

    Dim meetingsArray As New ArrayList()

    For Each dr As DataRow In ds.Tables("Meetings").Rows
        Dim m As New Meeting
        With m
            .AllDay = False
            .End = CDate(dr.Item("End")).ToJson()
            .ID = dr.Item("ID")
            .Start = CDate(dr.Item("Start")).ToJson()
            .Title = dr.Item("Title")
        End With
        meetingsArray.Add(m)
    Next

    Return Json(meetingsArray, JsonRequestBehavior.AllowGet)


End Function

and the following class definition:

Public Class Meeting
Public id As Integer
Public title As String
Public start As String
Public [end] As String
Public allDay As Boolean

End Class

and the following function, again thanks to you:

Public Module DateTimeExtensionMethods
Sub New()
End Sub
<System.Runtime.CompilerServices.Extension()> _
Public Function ToJson(ByVal dateTime As DateTime) As String
    Return dateTime.ToString("s")
End Function

End Module

That works perfectly, although I do need to tweak it slightly to read from the Linq results (or simply change the SQL Command to filter).

Again, many thanks for helping me understand the problem, I'm a firm believer that one needs to understand and now just copy code and your help has done that.

Jason

Answer 4

Sorry for being slow but I missed your comment when I checked briefly before.
This is the original code for my answer:

public class Meeting
{
    public int ID;
    public int Title;
    public DateTime Start;
    public DateTime End;
    public bool AllDay;
}

public class MeetingJavaScriptConverter : JavaScriptConverter
{
    private static readonly Type[] _supportedTypes = new[] { typeof(Meeting) };

    public override IEnumerable<Type> SupportedTypes
    {
        get { return _supportedTypes; }
    }

    public override IDictionary<string, object> Serialize(
        object obj, JavaScriptSerializer serializer)
    {
        var meeting = obj as Meeting;
        if (meeting != null)
        {
            var dictionary = new Dictionary<string, object>();

            dictionary.Add("id", meeting.ID);
            dictionary.Add("title", meeting.Title);
            dictionary.Add("start", meeting.Start.ToJson());
            dictionary.Add("end", meeting.End.ToJson());

            dictionary.Add("allDay", meeting.AllDay ? "true" : "false");

            return dictionary;
        }
        return new Dictionary<string, object>();
    }

    public override object Deserialize(IDictionary<string, object> dictionary,
        Type type, JavaScriptSerializer serializer)
    {
        throw new NotImplementedException();
    }
}

public static class DateTimeExtensionMethods
{
    public static string ToJson(this DateTime dateTime)
    {
        return dateTime.ToString("s");
    }
}

And the usage (when placed in the Page_Load of a normal ASP.NET website):

JavaScriptSerializer serializer = new JavaScriptSerializer();
serializer.RegisterConverters(new[] { new MeetingJavaScriptConverter() });
string jsonresult = serializer.Serialize(meetings.ToArray());

Response.Clear();
Response.ContentType = "text/javascript";
Response.Write(jsonresult);
Response.End();

I pasted the extra code that shows how to make the Page_Load work like a http handler because this is how I use it.

Obviously you don't have to use the extension method I wrote (as this is just something I wrote because I use many other different Json serializers that this was useful for).

So there you go. That's all my code, that works like a charm for me. Good luck! :)