.NET Tips and Tricks

Blog archive

Converting DataTables to JSON

When I started creating Web Services, I was using ADO.NET DataSets to retrieve data and then sending that data to my consumers using XML. Those Web Services are still there, but my consumers now want JSON.

The good news is that I don't have to rewrite my code to return the data in the right format. While I could switch to using SQL Server's new ability to convert query results into JSON, the existing code has that whole "working" feature that people like so much -- I have no desire to replace it.

The people who created NewtonSoft.JSON saw this problem coming and provided a solution for converting DataSet tables into JSON. First, you need to extract the DataTable holding your rows from your DataSet:

Dim dt As DataTable
dt = MyDataSet.Tables("Customers")

Then create a JsonServializer:

Dim js As JsonSerializer
js = JsonSerializer.CreateDefault

At this point you could set properties on the JsonSerializer to control how your JSON will be generated.

Next, pass your DataTable and Serializer to the FromObject method on NewtonSoft's JArray class. The FromObject method will convert all the rows in your DataTable into an array of JToken objects, held in a JArray object:

Dim rows As JArray
rows = JArray.FromObject(dt, js)

Now you can send the whole collection to the consumer:

Return rows.ToString()

Alternatively, you use LINQ to pull out the rows you want. This gets the first row, for example, and sends it to the consumer:

Dim row As JToken
row = rows.FirstOrDefault
Return row.ToString

Posted by Peter Vogel on 05/29/2018 at 11:03 AM


comments powered by Disqus

Featured

  • VS Code Now Has Apple Silicon Builds for Native Mac Development

    Goodbye Rosetta, hello M1. Visual Studio Code has been updated with new builds that let it run natively on machines with Apple Silicon (M1), the company's own ARM64 chips.

  • Visual Studio 2019 for Mac v8.9 Ships with .NET 6 Preview 1 Support

    During its Ignite 2021 online event for IT pros and developers this week, Microsoft shipped Visual Studio 2019 for Mac v8.9, arriving with out-of-the-box support for .NET 6 Preview 1, which the company also released recently.

  • Analyst: TypeScript Now Firmly in Top 10 Echelon (Ruby, Not So Much)

    RedMonk analyst Stephen O'Grady believes TypeScript has achieved the rare feat of firmly ensconcing itself into the top 10 echelon of his ranking, now questioning how high it might go.

  • Black White Wave IMage

    Neural Regression Using PyTorch: Training

    The goal of a regression problem is to predict a single numeric value, for example, predicting the annual revenue of a new restaurant based on variables such as menu prices, number of tables, location and so on.

Upcoming Events