My First Practical Question - MVC and JSON

Well I posted a question or two while I was going through MVC tutorials, but now I have my first real question on something I am doing at work.

I need to write code that will have JSON posted to it containing search criteria for me to look up records in a database (like firstname, lastname) and I am to return a JSON string of records that match. We are using datatables on the frontend, but that is probably not relevant to my question. I just need to post JSON back, and the designer will render it.

Any idea how to do this in MVC? This is my first MVC application so I am not sure where to start. We have an existing solution that other developers are working on, so I am a piece of the whole and the foundation already exists in Visual Studio.

Thanks for any help.

I create JSON strings in my web services - but I'm not sure how MVC wants to have it delivered so I'll wait to see if no one else gives you assistance.

But I will share with you a really great web site that validates JSON strings

http://jsonlint.com/

There is a lot of "escaping" that goes on in JSON strings to make them web-transmittable - and that web site will take a JSON string that is giving you a problem and parse it and tell you exactly where you are missing a } or a ] character.

I'm with you so far. I also have a web service from a previous project that delivers JSON. I will revisit that architecture. I am unclear as to where I put my code in MVC, but more reading of tutorials should help me with that - somehow, the designer's code has to call mine with the serach criteria coming in.

I've used that validator and have found it to be a great help.

Quote:

---

Originally Posted by szlamany

http://jsonlint.com/

---

Noice! Thanks for sharing! :)

I found exactly what I am looking for, but I need a little help.

The code I am using as an example is here. He is building a jQuery grid. First he builds it by hardcoding it. I've got that working. But as he points out, you obviously don't want a hardcoded grid; you want to get your data from a database query. So he has that code too, and says

It’s time to hook this up to a real database. As you might guess, I’ll use the HaackOverflow database for this demo...

HaackOverflow database is a link, and I thought this would take me to a way to get his database but it doesn't.

So given code like this:

Code:

---

        public JsonResult DynamicGridData(string sidx, string sord, int page, int rows)
        {
            var context = new HaackOverflowDataContext();
            int pageIndex = Convert.ToInt32(page) - 1;
            int pageSize = rows;

            int totalRecords = context.Questions.Count();
            int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);

            var questions = context.Questions.OrderBy(sidx + " " + sord).Skip(pageIndex * pageSize).Take(pageSize);

            var jsonData = new
            {
                total = totalPages,
                page,
                records = totalRecords,
                rows = (
                    from question in questions
                    select new
                    {
                        i = question.Id,
                        cell = new string[] { question.Id.ToString(), question.Votes.ToString(), question.Title }
                    }).ToArray()
            };

            return Json(jsonData);
        }

---

how do I plug in my own stored procedure call?

This is another example from http://forums.asp.net/t/1700693.aspx...+call+from+MVC

Code:

---

public JsonResult GetMarkets()
{
    var entityContext = new ShareMarketDBEntities();
    List<ShareMarketReportDetail> smdetails = entityContext.GetDBMarkets();
    return Json(smdetails, JsonRequestBehavior.AllowGet);
}

---

with the comment (GetDBMarkets method is written in Entity Framework, it is associated with a SP in DB.)


which is exactly what I want to do - call a sproc, but what is the Entity Framework?

Seems it would be some kind of LIST or DICTIONARY of rows - right?

I don't get all the LINQ syntax - that's not something I'm familiar with.

Where do you put code like this?

Code:

---

public JsonResult GetMarkets()
{
    var entityContext = new ShareMarketDBEntities();
    List<ShareMarketReportDetail> smdetails = entityContext.GetDBMarkets();
    return Json(smdetails, JsonRequestBehavior.AllowGet);
}

---

That looks javascripty to me - where do you plave that?

Yeah, that is the problem I am finding with some of these code snippets. They don't give you the whole picture.

I believe the GetMarkets() code would be in one of your Controller.cs files.

Are you able to run any of these snippets with their test data?

I don't do C# - but this appears to be making a LIST type object

List<ShareMarketReportDetail> smdetails = entityContext.GetDBMarkets();

and smdetails must be the class that holds a row of data.

When I return rows from my web services to jQuery callbacks on the client I do it as an ARRAY of OBJECTS - with the OBJECTS being "fieldname: fieldvalue" pairs.

Are you doing your backend in C# or VB?

I downloaded two projects today. One is called JsonMVC and the other is called JQueryGridDemo. The former works fine; the problem is it creates data on the fly and populates it in a treeview which I am not using. But it was good for getting my feet wet as I am new to MVC.

If you don't know much about MVC either, that is part of my difficulty. I am learning it, and it is very different from Web Forms. But there are good examples out there, so it is coming along.

The other project, JQueryGridDemo, has a grid - a jquery grid. Ultimately, we will be using a datatable (not a sql server datatable but a datatables.net datatable. It's a design thing that also presents your data in a grid format). I don't really care what we use, all I have to do is return a JSON string of rows of data to the client, and my job is done.

JQueryGridDemo has three examples - a static grid (got that working) and then two different kinds of grids, one uses LINQ one doesn't. But I do not know how to replace this key piece of code to call my own stored procedure:

Code:

---

        public JsonResult DynamicGridData(string sidx, string sord, int page, int rows)
        {
            var context = new HaackOverflowDataContext();
            int pageIndex = Convert.ToInt32(page) - 1;
            int pageSize = rows;

            int totalRecords = context.Questions.Count();
            int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);

            var questions = context.Questions.OrderBy(sidx + " " + sord).Skip(pageIndex * pageSize).Take(pageSize);

            var jsonData = new
            {
                total = totalPages,
                page,
                records = totalRecords,
                rows = (
                    from question in questions
                    select new
                    {
                        i = question.Id,
                        cell = new string[] { question.Id.ToString(), question.Votes.ToString(), question.Title }
                    }).ToArray()
            };

            return Json(jsonData);
        }

---

"from question in questions select new..." - what the heck is that all about?

The static grid data just looks like this:

Code:

---

        public JsonResult GridData(string sidx, string sord, int page, int rows)
        {
            int totalPages = 1; // we'll implement later
            int pageSize = rows;
            int totalRecords = 3; // implement later

            var jsonData = new
            {
                total = totalPages,
                page = page,
                records = totalRecords,
                rows = new[]{
                    new {id = 1, cell = new[] {"1", "-7", "Is this a good question?"}},
                    new {id = 2, cell = new[] {"2", "15", "Is this a blatant ripoff?"}},
                    new {id = 3, cell = new[] {"3", "23", "Why is the sky blue?"}}
                }
            };
            return Json(jsonData);
        }

---

That is simple. But rather...stagnant!

Are you going to use VB or C#? This looks like C# - right?

Code:

---

                rows = new[]{
                    new {id = 1, cell = new[] {"1", "-7", "Is this a good question?"}},
                    new {id = 2, cell = new[] {"2", "15", "Is this a blatant ripoff?"}},
                    new {id = 3, cell = new[] {"3", "23", "Why is the sky blue?"}}
                }

---

ROWS is an array - and array of object pairs - one is ID with a number and the second is CELL with an ARRAY of data elements for each field in the row.

Don't you think you will be using PUSH into an ARRAY structure in VB?

Or are you using C#?

I will be using C# (I had to put that in a full sentence, because when I tried to post just C# it asked for at least two more words. That's the first time anyone's accused me of not saying enough!)

btw - that "from question in questions select new" is LINQ I believe - right? Have you ever used that - I have not.

Then it seems as though you need to build a structure like this using a loop through whatever comes back from the SPROC.

Code:

---

                rows = new[]{
                    new {id = 1, cell = new[] {"1", "-7", "Is this a good question?"}},
                    new {id = 2, cell = new[] {"2", "15", "Is this a blatant ripoff?"}},
                    new {id = 3, cell = new[] {"3", "23", "Why is the sky blue?"}}
                }

---

[edit] I see you posted in the DB forum - I replied with better info in that thread [/edit]

Yes it is LINQ. No I have not used it. That's why I want to know how to replace it. (I shouldn't have said what the heck is that all about, but maybe a few posts ago I wasn't certain that it was LINQ...).

Yes since I've drilled down to this part I figured I'd go over to Database Development. Thanks for following me!

Quote:

---

Originally Posted by MMock

which is exactly what I want to do - call a sproc, but what is the Entity Framework?

---

Entity Framework is a framework which sits above your Database. You can either create standard POCO's and have Entity Framework create the database for you, or you can have Entity Framework point at a database and create you a DataContext for your Database. This includes creating methods, properties, etc, to house all the work that the Database can do, including providing methods for Stored Procedures.

Have a look here:

http://msdn.microsoft.com/en-us/library/bb386876.aspx

Gary

Quote:

---

Originally Posted by MMock

Yeah, that is the problem I am finding with some of these code snippets. They don't give you the whole picture.

I believe the GetMarkets() code would be in one of your Controller.cs files.

---

GetMarkets is likely to be one of the methods that was created for him by Entity Frameworks. There is likely a table called Markets within his Database, and as a result, a method was created called GetMarkets, which returns all the rows from that table. This method is housed within the DataContext object that was created by Entity Framwork.

Gary

Excellent series of articles here: http://www.asp.net/mvc/overview/models-(data)

If you've got time I would suggest the PluralSight video series here: http://www.asp.net/mvc. Especially #4 Working with Data: Entity Framework

Agreed, both Pluralsight and Tekpub offer a number of great video series and if you can justify the cost, are very useful to have.

Gary