如何获得SQL Server的查询结果的数据转换成JSON格式？ [英] How to get SQL Server query result's data into JSON Format ?

问题描述

我刚开始了解Ajax和JSON格式。我建立一个非常简单的地址簿。 所以，假设我有一个表，为了简便起见有3列：

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:

姓名，电子邮件和电话

我的JavaScript / jQuery是不是最好的刚学的，但我希望把从我的SQL Server成JSON格式返回的数据。我应该创建一个可以创建一个JSON文件，并把它放在一个文件夹中，我可以在我的javascript使用存储过程？

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?

或者这有点像客户端的C＃/ VB.net应用程序应该做它实际产生每说5分钟的文件吗​​？基本上让我们假设我得到的一些数据传回：

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 [e-mail] 123-3333
Mike [e-mail] 123-4433
Steve [e-mail] 144-3333
Jill [e-mail] 333-3333

我从一个简单的SELECT语句这回：

I get this back from a simple select statement:

SELECT姓名，电子邮件，从myTable的电话

我怎样才能再拿到这是一个JSON文件，所以我可以存储在 .json 的数据，然后使用该文件在我的JavaScript code。有人可以解释这个问题，以及人们如何生成JSON文件？

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担任了通过一些网络API。

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

下面是如何做到这一点的ASP.NET MVC的例子：

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

<一个href="http://www.asp.net/web-api/overview/getting-started-with-aspnet-web-api/tutorial-your-first-web-api">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 = "[e-mail]", Phone = "123-3333" }, 
            new Contact{ Name = "Mike", Email = "[e-mail]", Phone = "123-3333" }, 
            new Contact{ Name = "Steve", Email = "[e-mail]", Phone = "123-3333" } 
        };

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

这样，你会浏览到：​​的http：//本地主机：XXXX / API /联系人/ ，你可以看到你的数据。您可以使用JavaScript来检索JSON格式的数据。在Web API需要将其转换为JSON为你的照顾。

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.

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

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.

<一个href="http://james.newtonking.com/pages/json-net.aspx">http://james.newtonking.com/pages/json-net.aspx

Retrieveing​​使用jQuery的数据：

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>

如果你的项目使用ASP.NET Web窗体，您可以执行以下操作来代替：

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

asp.net web表单

asp.net web forms json return result

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

这篇关于如何获得SQL Server的查询结果的数据转换成JSON格式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！