title: Creating a JSON result from SQL server database
body: <p>I have an SQL server with the following layout </p>
  
  <pre><code>Table (    id int
             title varchar(40),
             start Date(),
             end Date(),
             allDay bool,
             username varchar(40)
        );
  </code></pre>
  
  <p>I have gotten the following code from <a href="http://weblogs.asp.net/gunnarpeipman/archive/2010/02/03/using-fullcalendar-jquery-component-with-asp-net-mvc.aspx" rel="nofollow">this blog</a> to create a JSON object from the data I wish to use, however his data is stored differently. How do I create the same object, extracted from my database?</p>
  
  <p>I am guessing I need to make the file a .cshtml file rather than a .js file and use this :</p>
  
  <pre><code>@{
      var db = Database.Open("events"); 
      var selectQueryString = "SELECT * FROM events";
  }
  
   @foreach(var row in db.Query(selectQueryString)){ }
  </code></pre>
  
  <p>But how do I adapt this code to produce the same JSON object?</p>
  
  <p>Here is the relevant code from the blog, my attempt is below :</p>
  
  <pre><code>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&lt;IEventRepository&gt;();
      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);           
  }
  </code></pre>
  
  <p>Edit :</p>
  
  <p>I am now working with the following .cshtml code for the GetEvents command, but it will not work. Does anybody have any ideas ?</p>
  
  <pre><code>   @{ 
          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 =&gt; 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";
      }
  </code></pre>