CODEWITHSUNDEEP
asp.netjqueryjson.net-3.5datatable
Anthony
Anthony

Reputation: 7300

DataTable to Json using jquery

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

$.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).

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.

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

Upvotes: 6

Views: 46655

Answers (9)

john
john

Reputation: 86

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.

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.

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

Upvotes: 6

Carlos Diaz
Carlos Diaz

Reputation: 21

It works for very well for me with a 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

Upvotes: 2

Isidore
Isidore

Reputation: 1

I found this C# class very useful:

[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;
}

Upvotes: 0

Nathan Prather
Nathan Prather

Reputation: 2106

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

Upvotes: 4

Anthony
Anthony

Reputation: 7300

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: 

    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

Upvotes: 8

Al W
Al W

Reputation: 7713

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

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.

Upvotes: 0

James Newton-King
James Newton-King

Reputation: 49032

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

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

Upvotes: 4

Joel Coehoorn
Joel Coehoorn

Reputation: 415620

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

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1062550

I must admit I'm not hugely surprised - DataTable basically breaks most of the rules of structured data. Why not simply project from the data-table into a typed object? A related question came up earlier... or if you know the schema of the DataTable just do the conversion in 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.

Upvotes: 1

Related Questions