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

By | January 12, 2018

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# / app should be doing where it actually generates the file every say 5 minutes? Basically lets assume I get some data back:

George 123-3333
Mike 123-4433
Steve 144-3333
Jill 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?


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:

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 = "", Phone = "123-3333" }, 
            new Contact{ Name = "Mike", Email = "", Phone = "123-3333" }, 
            new Contact{ Name = "Steve", Email = "", 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.

Retrieveing the data using jQuery:

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

                console.log(val.Name, val.Phone, val.Email);  

If your project is using ASP.NET Web Forms, you can do the following instead: web forms json return result

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


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.


--sample table
    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');

    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
                FOR PropertyName IN (
                ) upvt
        SELECT  CASE WHEN cte2.RowNum2 = y.MinRowNum THEN '[' ELSE '' END,
                CASE WHEN cte2.RowNum = x.MinRowNum THEN '{' ELSE '' END,
                CASE WHEN cte2.RowNum <> x.MaxRowNum THEN ',' ELSE '' END,
                    WHEN cte2.RowNum = x.MaxRowNum THEN '}' + 
                        CASE WHEN cte2.RowNum2 = y.MaxRowNum THEN '' ELSE ',' END 
                    ELSE '' 
                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” } ]

Leave a Reply

Your email address will not be published. Required fields are marked *