How to get SQL Server query result's data into JSON Format ?

Question

I'm just getting to understand Ajax and JSON format. I'm building a very simple address book. So assume I have a table with for sake of simplicity has 3 columns:

Name, Email and Phone

My javascript / jquery is not the best just learning, but I want to put the data returned from my SQL Server into JSON format. Should I create a stored procedure that can create a json file and put it in a folder where I can use it in my javascript?

Or is this something like a client C# / VB.net app should be doing where it actually generates the file every say 5 minutes? Basically lets assume I get some data back:

George [email protected] 123-3333
Mike [email protected] 123-4433
Steve [email protected] 144-3333
Jill [email protected] 333-3333

I get this back from a simple select statement:

SELECT name, email, phone from myTable

How can I then get this as a json file so I can store the data in a .json and then use that file in my javascript code. Can someone explain this as well as how people generate json files?

Answer 1

Typically a better way to do this is to have the JSON served up via some web api.

Here's an example of how to do it in ASP.NET MVC:

http://www.asp.net/web-api/overview/getting-started-with-aspnet-web-api/tutorial-your-first-web-api

public class Contact
{
  public string Name {get;set;}
  public string Email {get;set;}
  public string Phone {get;set;}
}

public class ContactsController : ApiController
    {
        // instead of having the contacts in memory, you can load them from the database using Entity Framework, Dapper.NET - or you other favorite ORM.
        Contact[] contacts = new Contact[] 
        { 
            new Contact{ Name = "George", Email = "[email protected]", Phone = "123-3333" }, 
            new Contact{ Name = "Mike", Email = "[email protected]", Phone = "123-3333" }, 
            new Contact{ Name = "Steve", Email = "[email protected]", Phone = "123-3333" } 
        };

        public IEnumerable<Contact> GetAllContacts()
        {
            return contacts;
        }
    }

You would then browse to: http://localhost:xxxx/api/contacts/ and you can see your data. You can use javascript to retrieve the data in JSON format. The Web API takes care of converting it to JSON for you.

Behind the scenes ASP.NET MVC is using NewtonSoft's JSON.NET to convert the classes to JSON. That is open source and can be used in any .NET application.

http://james.newtonking.com/pages/json-net.aspx

Retrieveing the data using jQuery:

<script type="text/javascript">
    $(document).ready(function () {
        // Send an AJAX request
        $.getJSON("api/contacts/",
        function (data) {
            // On success, 'data' contains a list of contacts.
            $.each(data, function (key, val) {

                console.log(val.Name, val.Phone, val.Email);  
            });
        });
    });
</script>

If your project is using ASP.NET Web Forms, you can do the following instead:

asp.net web forms json return result

[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)] 
public List<Contact> GetAllContacts()
{
  return contacts;
}