Creating a JSON Result from an SQL Server Database

Question

I have a SQL server with the following layout

Table ( id int title varchar(40), start Date(), end Date(), allDay bool, username varchar(40) ); 

I got the following code from this blog to create a JSON object from the data that I want to use, however its data is stored in different ways. How to create the same object extracted from my database?

I assume that I need to make a .cshtml file, not a .js file, and use it:

 @{ var db = Database.Open("events"); var selectQueryString = "SELECT * FROM events"; } @foreach(var row in db.Query(selectQueryString)){ } 

But how do I configure this code to create the same JSON object?

Here is the relevant code from the blog, 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:

Now I am working with the following .cshtml code for the GetEvents command, but this will not work. Does anyone have any ideas?

  @{ 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"; } 

Answer 1

There are no controllers or actions in WebMatrix web pages. You need to write a separate .cshtml page that will query the database and serve JSON for the response:

 @{ 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"; } 

and then on the other page on which you want to display the calendar, you can configure it:

 $(document).ready(function() { $('#calendar').fullCalendar({ theme: true, header: { left: '', center: '', right: '' }, defaultView: 'agendaDay', editable: false, events: '/events.cshtml' }); }); 

---

UPDATE: Here is an example of using parameterized queries:

 @{ var origin = new DateTime(1970, 1, 1, 0, 0, 0, 0); var fromDate = origin.AddSeconds(int.Parse(Request["start"])); var toDate = origin.AddSeconds(int.Parse(Request["end"])); var db = Database.Open("events"); var sql = "SELECT * FROM events WHERE start >= @0 AND end <= @1"; var result = db.Query(sql, fromDate, toDate); 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"; } 

Now you can request a page as follows: /events.cshtml?start=5&end=10

Answer 2

 DECLARE @listCol VARCHAR(2000) DECLARE @query VARCHAR(4000) SELECT @listCol = STUFF(( SELECT distinct '], [' + [PSize] FROM Pattern FOR XML PATH('') ), 1, 2, '') + ']' SET @query = 'SELECT * FROM (SELECT PColour as Colour_Size_Matrix, PSize, PCode FROM Pattern ) src PIVOT (Count(PCode) FOR PSize IN (' + @listCol + ')) AS pvt' EXECUTE ( @query ) 

I want to get the result of this request as JSON