使用 jquery 将数据表转换为 Json

DataTable to Json using jquery

提问by Anthony

I'm trying to execute a web service which returns a DataTable with the following piece of code:

我正在尝试执行一个 Web 服务，该服务返回一个带有以下代码的 DataTable：

$.ajax({  
    type: "POST",  
    url: url,  
    data: data,   
    contentType: "application/json; charset=utf-8",  
    dataType: "json",  
    success: function(msg) {  
        //do things  
        }  
    }); 

If the webservice returns a class then it works so it has nothing to do with the input paramters etc. It only fails when the web method returns a datatable (the datatable only has 2 columns and 2 rows for the test I'm doing).

如果 webservice 返回一个类，那么它就可以工作，所以它与输入参数等无关。它只在 web 方法返回数据表时失败（数据表只有 2 列和 2 行，用于我正在做的测试）。

The WebService class is decorated with the [ScriptService] attribute so I thought that ASP.NET would automatically serialize the return value as JSON. It doesn't seem to work with datatable.

WebService 类使用 [ScriptService] 属性进行修饰，因此我认为 ASP.NET 会自动将返回值序列化为 JSON。它似乎不适用于数据表。

The only solution I've found was to return a string (a manually JSON serialized object) but it doesn't seem right to me to do it this way.
I'm using Visual Studio 2008 with .Net 3.5

我发现的唯一解决方案是返回一个字符串（一个手动 JSON 序列化的对象），但对我来说这样做似乎不正确。
我正在使用带有 .Net 3.5 的 Visual Studio 2008

回答by Anthony

In the end, I've decided to use the JavaScriptSerializer class to convert the DataTable into a JSON string. Unfortunately, this class doesn't work with a DataTable so I converted the DataTable into a list of dictionnaries and pass that list to the JavaScriptSerializer class. It takes only a few lines of code and it works fine.
Example in VB.net:

最后，我决定使用 JavaScriptSerializer 类将 DataTable 转换为 JSON 字符串。不幸的是，这个类不适用于 DataTable，因此我将 DataTable 转换为字典列表并将该列表传递给 JavaScriptSerializer 类。它只需要几行代码，并且运行良好。
VB.net 中的示例：

    Public Function GetJson(ByVal dt As DataTable) As String

        Dim serializer As System.Web.Script.Serialization.JavaScriptSerializer = New System.Web.Script.Serialization.JavaScriptSerializer()
        Dim rows As New List(Of Dictionary(Of String, Object))
        Dim row As Dictionary(Of String, Object)

        For Each dr As DataRow In dt.Rows
            row = New Dictionary(Of String, Object)
            For Each col As DataColumn In dt.Columns
                row.Add(col.ColumnName, dr(col))
            Next
            rows.Add(row)
        Next
        Return serializer.Serialize(rows)
    End Function

回答by john

Easiest way is to use the LINQ to DataSet extensions. First need to create a generic list (SearchSerialResults is just a DTO in this case) from the DataTable using LINQ to DataSet.

最简单的方法是使用 LINQ to DataSet 扩展。首先需要使用 LINQ to DataSet 从 DataTable 创建一个通用列表（在这种情况下，SearchSerialResults 只是一个 DTO）。

var resultItems = (from DataRow dr in _returnedData.AsEnumerable() select new SearchSerialResults {
  ContractLineItem = (int) dr["fldContractLineItemID"],
    SearchItem = (string) dr["Search Item"],
    Customer = (string) dr["Customer"],
    DeviceFound = (string) dr["Device Found"],
    Country = (string) dr["Country"],
    City = (string) dr["City"],
    ContractNumber = (string) dr["Contract Number"],
    QuoteNumber = (string) dr["Quote Number"],
    BeginDate = (string) dr["Begin Date"],
    EndDate = (string) dr["End Date"]
}).ToList();

_returnedData is the DataTable in this case. Step 2 is to do the conversion. In this case, I am returning a Json object for a jqGrid.

在这种情况下，_returnedData 是 DataTable。第二步是进行转换。在这种情况下，我为 jqGrid 返回一个 Json 对象。

var jsonObject = new {
  total = totalPages,
    pageSize,
    records = totalRecords,
    rows = (from SearchSerialResults item in resultItems select new {
      id = item.ContractLineItem,
        cell = new [] {
          item.ContractLineItem.ToString(),
            item.SearchItem,
            item.DeviceFound,
            item.Customer,
            item.ContractNumber,
            item.QuoteNumber,
            item.Country,
            item.City,
            item.BeginDate,
            item.EndDate,
            ""
        }
    }).ToArray()
};
return Json(jsonObject) // for MVC

回答by James Newton-King

Json.NET has the ability to write DataSets/DataTables to JSON.

Json.NET 能够将数据集/数据表写入 JSON。

http://james.newtonking.com/archive/2008/09/06/dataset-datatable-serialization-with-json-net.aspx

http://james.newtonking.com/archive/2008/09/06/dataset-datatable-serialization-with-json-net.aspx

回答by Nathan Prather

http://schotime.net/blog/index.php/2008/07/27/dataset-datatable-to-json/

http://schotime.net/blog/index.php/2008/07/27/dataset-datatable-to-json/

回答by Carlos Diaz

It works for very well for me with a WebService

它对我来说非常适合使用 WebService

    Imports System.Web.Script.Serialization

    Dim wsServicio As New ["YourWsInstance"]
    Dim dsInstEstado As New DataSet
    Dim sSql As String

    sSql = " Your SQL Statement"
    dsInstEstado = wsServicio.getData("YourWebServiceParameters")
    Dim jsonString = DataTableToJSON(dsInstEstado.Tables("CA_INSTITUCION"))
    Return Json(jsonString, JsonRequestBehavior.AllowGet)

    Function DataTableToJSon(dt As DataTable) As Object
    Dim arr(dt.Rows.Count - 1) As Object
    Dim column As DataColumn
    For i = 0 To dt.Rows.Count - 1
        Dim dict As New Dictionary(Of String, Object)
        For Each column In dt.Columns
            dict.Add(column.ColumnName, dt.Rows(i)(column))
        Next
        arr(i) = dict
    Next
   Return arr
 End Function

回答by Marc Gravell

I must admit I'm not hugely surprised - DataTablebasically breaks most of the rules of structured data. Why not simply project from the data-table into a typed object? A related questioncame up earlier... or if you know the schema of the DataTablejust do the conversion in C#...

我必须承认我并不感到惊讶 -DataTable基本上打破了结构化数据的大部分规则。为什么不简单地从数据表投影到类型对象？之前出现了一个相关的问题……或者如果您知道DataTable在 C# 中进行转换的架构……

Manually building the JSON might work, but there are a lot of edge-cases to avoid; I'd rather let an existing framework handle it, to be honest.

手动构建 JSON 可能可行，但有很多边缘情况需要避免；老实说，我宁愿让现有的框架来处理它。

回答by Joel Coehoorn

.Net 3.5 has a JSONSerializer that should be able to handle a datatable. You may want to look at your service code again and try getting it to use that. Also, I put some code together to do it manually in this question.

.Net 3.5 有一个 JSONSerializer 应该能够处理数据表。您可能想再次查看您的服务代码并尝试让它使用它。另外，我在这个问题中将一些代码放在一起手动完成。

回答by Al W

Like Marc, I too am not surprised that the DataTable breaks your webservice/json exchange. I'd like to endorse Json.NET also.

像 Marc 一样，我对 DataTable 破坏您的 webservice/json 交换也并不感到惊讶。我也想支持 Json.NET。

But if you decide to not go with it, you still don't have to build the json manually. Just make your own lean custom class with all the properties you need and then return an array of that class. You will of course have to write code to "convert" your data table into your new class. I know, it could be a lot of code writing, but it's a lot less error prone then trying to manually make a json string.

但是如果您决定不使用它，您仍然不必手动构建 json。只需使用您需要的所有属性制作您自己的精益自定义类，然后返回该类的数组。您当然必须编写代码将数据表“转换”为新类。我知道，这可能需要编写大量代码，但与尝试手动创建 json 字符串相比，它更不容易出错。

回答by Isidore

I found this C# class very useful:

我发现这个 C# 类非常有用：

[Serializable]
public class TableMethod
{
    private int m_total; public int total { get { return this.m_total; } set { this.m_total = value; } }
    private int m_page; public int page { get { return this.m_page; } set { this.m_page = value; } }
    private int m_records; public int records { get { return this.m_records; } set { this.m_records = value; } }
    private IList<RowElement> m_rows; public IList<RowElement> rows { get { return this.m_rows; } set { this.m_rows = value; } }
    public TableMethod()
    {
        this.m_records = 20;
        this.m_total = 20;
        this.m_page = 1;
    }
}
[Serializable]
public class RowElement
{
    public string id;
    public string[] cell;
}