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 g@yahoo.com 123-3333
Mike m@gmail.com 123-4433
Steve s@gmail.com 144-3333
Jill r@gmail.com 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 = "g@yahoo.com", Phone = "123-3333" }, 
            new Contact{ Name = "Mike", Email = "m@yahoo.com", Phone = "123-3333" }, 
            new Contact{ Name = "Steve", Email = "s@yahoo.com", 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;
}

Answer 2

You may be able to utilize some of my rudimentary sql to json logic that i've used in the past... but it may be kind of specific to my dataset. I tried to genericize it a bit.

SET NOCOUNT ON;

--sample table
CREATE TABLE #Temp(
    Id INT Identity(1,1),
    Column1 INT,
    Column2 VARCHAR(10),
    Column3 VARCHAR(10)
    )
;

INSERT INTO #Temp(Column1, Column2, Column3) VALUES (10,'Test', 'Test2'), (20, 'Test3', 'Test4'), (30, 'Test5', 'Test6');

WITH 
    cte AS(
        SELECT  Id AS RowId,
                CAST(Id AS VARCHAR(100)) AS Id,
                CAST(Column1 AS VARCHAR(100)) AS Column1,
                CAST(Column2 AS VARCHAR(100)) AS Column2,
                CAST(Column3 AS VARCHAR(100)) AS Column3
        FROM #Temp
        ),
    cte2 AS (
        SELECT  RowId,
                '"' + PropertyName + '"' + ':' + CASE WHEN ISNUMERIC(Value) = 1 THEN Value ELSE '"' + Value + '"' END AS Value,
                ROW_NUMBER() OVER(PARTITION BY RowId ORDER BY CASE WHEN PropertyName = 'Id' THEN '' ELSE PropertyName END) AS RowNum,
                ROW_NUMBER() OVER(ORDER BY RowId) AS RowNum2
        FROM cte
            UNPIVOT(
                Value
                FOR PropertyName IN (
                    Id,
                    Column1,
                    Column2,
                    Column3
                    )
                ) upvt
        )
        SELECT  CASE WHEN cte2.RowNum2 = y.MinRowNum THEN '[' ELSE '' END,
                CASE WHEN cte2.RowNum = x.MinRowNum THEN '{' ELSE '' END,
                cte2.value,
                CASE WHEN cte2.RowNum <> x.MaxRowNum THEN ',' ELSE '' END,
                CASE 
                    WHEN cte2.RowNum = x.MaxRowNum THEN '}' + 
                        CASE WHEN cte2.RowNum2 = y.MaxRowNum THEN '' ELSE ',' END 
                    ELSE '' 
                END,
                CASE WHEN cte2.RowNum2 = y.MaxRowNum THEN ']' ELSE '' END
        FROM cte2
            INNER JOIN (
                SELECT  RowId, 
                        MIN(RowNum) AS MinRowNum, 
                        MAX(RowNum) AS MaxRowNum
                FROM cte2
                GROUP BY RowId
                ) x
                    ON cte2.RowId = x.RowId
            CROSS JOIN (
                SELECT  MIN(RowNum2) AS MinRowNum, 
                        MAX(RowNum2) AS MaxRowNum
                FROM cte2
                ) y
;

/* --output would be as follows:

[ { "Id":1 ,
"Column1":10 ,
"Column2":"Test" ,
"Column3":"Test2" },
{ "Id":2 ,
"Column1":20 ,
"Column2":"Test3" ,
"Column3":"Test4" },
{ "Id":3 ,
"Column1":30 ,
"Column2":"Test5" ,
"Column3":"Test6" } ] */