如何将SQL Server查询结果的数据转换成JSON格式?

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:

我只是想了解Ajax和JSON格式。我正在构建一个非常简单的地址簿。假设我有一个表格为了简单起见有3列

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?

我的javascript / jquery并不是最好的学习工具，但是我想把SQL服务器返回的数据转换成JSON格式。我是否应该创建一个可以创建json文件的存储过程，并将其放在一个文件夹中，以便在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:

或者是像c# / VB.net这样的客户端应用应该在每隔5分钟就会生成文件的地方?假设我得到了一些数据

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语句中得到这个:

SELECT name, email, phone from myTable

从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?

然后如何将其作为json文件，以便将数据存储在.json中，然后在javascript代码中使用该文件。有人能解释这一点以及人们如何生成json文件吗?

2 个解决方案

#1

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

通常更好的方法是通过一些web api提供JSON。

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

这里有一个如何在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.

然后浏览到:http://localhost:xxxx/api/contacts/，可以看到数据。可以使用javascript以JSON格式检索数据。Web API负责将其转换为JSON。

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.

在幕后ASP。NET MVC使用的是NewtonSoft的JSON。NET将类转换为JSON。这是开源的，可以在任何。net应用程序中使用。

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

Retrieveing the data using jQuery:

使用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。网上表格，你可以做以下的代替:

asp.net web forms json return result

net web forms json返回结果

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

#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.

您可能可以使用我过去使用过的一些基本sql到json逻辑……但它可能是特定于我的数据集的。我试着归纳一下。

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" } ] */

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

#1