Creating a JSON result from SQL server database

Question

I have an SQL server with the following layout

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

I have gotten the following code from this blog 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?

I am guessing I need to make the file a .cshtml file rather than a .js file and use this :

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

 @foreach(var row in db.Query(selectQueryString)){ }

But how do I adapt this code to produce the same JSON object?

Here is the relevant code from the blog, my attempt is 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 working with the following .cshtml code for the GetEvents command, but it will not work. Does anybody 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";
    }

What's wrong with JavacriptSerializer?

Grant Thomas
– Grant Thomas

2012-02-23 15:54:07 +00:00
Commented Feb 23, 2012 at 15:54 — Grant Thomas
– Grant Thomas, Commented Feb 23, 2012 at 15:54
How would I use JavascriptSerializer in this case?

Simon Kiely
– Simon Kiely

2012-02-23 15:55:43 +00:00
Commented Feb 23, 2012 at 15:55 — Simon Kiely
– Simon Kiely, Commented Feb 23, 2012 at 15:55

Darin Dimitrov · Accepted Answer · 2012-02-24 11:11:24Z

5

There are no controllers and actions in WebMatrix WebPages. You need to write a separate .cshtml page that will query the database and serve the JSON to 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 in another page in which you want to display the calendar you could configure it:

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

UPDATE: Here's an example of how you could use parametrized 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 could query the page like this: /events.cshtml?start=5&end=10

edited Feb 24, 2012 at 11:11

answered Feb 23, 2012 at 15:58

Darin Dimitrov

1.0m277 gold badges3.3k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Simon Kiely Over a year ago

Thank you for your response. I have tried to integrate your code into mine, but it does not appear to be working, unfortunately - there are no events being produced for the calendar. I have two theories - the first is that the start/end times are stored differently and need to be changed, and the second is that in the first .cshtml file, containing the connection to the database, it doesn't seem to be returning anything? Do you have any ideas, as I have been slaving on this since your response to no avail! Thank you very much for the response :)

Simon Kiely Over a year ago

Also, in my code above, the start and end time of the current view are provided as parameters, but in your C# function, this data appears not to be used at all?

Darin Dimitrov Over a year ago

@SimonKiely, in your updated answer you seem to be using something that doesn't look much like C# syntax. You concatenate the SQL string with dots. Also you seem to be calling the ConvertFromUnixTimestamp function with undefined variables: start and end. If you wanted to read this from the request you would have used Request["start"] and Request["end"]. I would recommend you though to first get this working without additional filtering parameters. Also you wrapped the code in a HTML file and haven't returned the JSON data. Also look with FireBug if the script sends correct JSON.

Darin Dimitrov Over a year ago

@SimonKiely, see my updated answer for an example of how to properly use parametrized queries with web pages. Also you can put breakpoints in your code and try to debug it.

Darin Dimitrov Over a year ago

Yes, I completely agree with you that FireBug is fantastic. If you want to render it for specific user you could use the username column in your SQL query to constrain the resultset.

|

Vitthal · Accepted Answer · 2012-11-05 11:12:29Z

0

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 the result of this query as JSON

answered Nov 5, 2012 at 11:12

Vitthal

5463 silver badges19 bronze badges

Collectives™ on Stack Overflow

Creating a JSON result from SQL server database

2 Answers 2

12 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related