My SQL server has the following table layout:
Table ( id int,
title varchar(40),
start Date(),
end Date(),
allDay bool,
username varchar(40)
);
I found some code on this blog to create a JSON object from my data, but it doesn't match how my data is stored. How can I create a similar object based on my database structure?
I think I need to convert the file to a .cshtml instead of a .js and use this code snippet:
@{
var db = Database.Open("events");
var selectQueryString = "SELECT * FROM events";
}
@foreach(var row in db.Query(selectQueryString)){ }
But I'm not sure how to modify this code to generate the same JSON object.
Here is the relevant code from the blog, along with my attempt below :
public JsonResult GetEvents(double start, double end)
{
var userName = Session["UserName"] as string;
if(string.IsNullOrEmpty(userName))
{
return null;
}
var fromDate = ConvertFromUnixTimestamp(start);
var toDate = ConvertFromUnixTimestamp(end);
var rep = Resolver.Resolve<IEventRepository>();
var events = rep.ListEventsForUser(userName,fromDate,toDate);
var eventList = from e in events
select new {
id = e.Id,
title = e.Title,
start = e.FromDate.ToString("s"),
end = e.ToDate.ToString("s"),
allDay = false
};
var rows = eventList.ToArray();
return Json(rows,JsonRequestBehavior.AllowGet);
}
Edit :
I am now trying to work with the following .cshtml code for the GetEvents command, however it seems to be failing. Any suggestions?
@{
var origin = new DateTime(1970, 1, 1, 0, 0, 0, 0);
var fromDate = origin.AddSeconds((Request["start"]));
var toDate = origin.AddSeconds(Request["end"]);
var db = Database.Open("events");
var result = db.Query("SELECT * FROM events");
var data = result.Select(x => new
{
id = x.id,
title = x.title,
start = x.start.ToString("s"),
end = x.end.ToString("s"),
allDay = false
}).ToArray();
Json.Write(data, Response.Output);
Response.ContentType = "application/json";
}