How to Convert Datatable to JSON String Using JSON.Net

How to convert datatable to json string using json.net?

string json = JsonConvert.SerializeObject(table, Formatting.Indented);

Edit: You don't need indented formatting, of course, but it makes it nice and readable.

Convert datatable to JSON in C#

This code snippet from Convert Datatable to JSON String in C#, VB.NET might help you.
It uses System.Web.Script.Serialization.JavaScriptSerializer to serialize the contents to JSON format:

public string ConvertDataTabletoString()
{
    DataTable dt = new DataTable();
    using (SqlConnection con = new SqlConnection("Data Source=SureshDasari;Initial Catalog=master;Integrated Security=true"))
    {
        using (SqlCommand cmd = new SqlCommand("select title=City,lat=latitude,lng=longitude,description from LocationDetails", con))
        {
            con.Open();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
            List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
            Dictionary<string, object> row;
            foreach (DataRow dr in dt.Rows)
            {
                row = new Dictionary<string, object>();
                foreach (DataColumn col in dt.Columns)
                {
                    row.Add(col.ColumnName, dr[col]);
                }
                rows.Add(row);
            }
            return serializer.Serialize(rows);
        }
    }
}

How to convert datatable to json string output using json.net WCF Rest Service

U should use JsonConvert.SerializeObject followed by the datatable in the first parameter, and then the way you want to format it in the 2nd parameter.

string json = JsonConvert.SerializeObject(objAcctDTable, Formatting.None);

-edit-

if you are struggling with the escape quotes or slashes, you should put your string through this function before doing anything with it

public string EscapeQuotesMySql(string str)
        {

            string retVal = System.String.Empty;

            if (!System.String.IsNullOrEmpty(str))
            {

                // replace special quotes

                retVal = str.Replace((char)8216, '\'');

                retVal = retVal.Replace((char)8217, '\'');

                // escapes for SQL

                retVal = retVal.Replace(@"\", @"\\");

                retVal = retVal.Replace(@"'", @"\'");

            }

            return retVal;

        }

Convert DataTable to JSON with key per row

This is quite simple with JSON.NET. Just convert your data table into the equivalent dictionary of dictionaries:

public Dictionary<string, Dictionary<string, object>> DatatableToDictionary(DataTable dt, string id)
{
    var cols = dt.Columns.Cast<DataColumn>().Where(c => c.ColumnName != id);
    return dt.Rows.Cast<DataRow>()
             .ToDictionary(r => r[id].ToString(), 
                           r => cols.ToDictionary(c => c.ColumnName, c => r[c.ColumnName]));
}

Then call:

JsonConvert.SerializeObject(DatatableToDictionary(dt, "ID"), Newtonsoft.Json.Formatting.Indented);

Here's the full test:

var dt = new DataTable("MyTable");
dt.Columns.Add("ID");
dt.Columns.Add("Name");
dt.Columns.Add("Active");

dt.LoadDataRow(new[] {"ID1", "John", "True"}, true);
dt.LoadDataRow(new[] {"ID2", "Bill", "False"}, true);

JsonConvert.SerializeObject(DatatableToDictionary(dt, "ID"));

And the result:

{
  "ID1": {
    "Name": "John",
    "Active": "True"
  },
  "ID2": {
    "Name": "Bill",
    "Active": "False"
  }
}

Serialize DataTable into JSON Object in C# Newtonsoft

1) Stop working with DataTable. They're a poor abstraction for the data. Use strongly typed objects. Dapper is handy for this. It is a micro-ORM that works with any ADO.NET data provider. You provide the query, and it will map the results of the query to a strongly typed object (a class). Dapper is available on NuGet.

2) Your action method shouldn't return an IEnumerable. It should return an IEnumerable<T>

3) You should let the framework handle converting your objects to JSON, don't do it yourself. There's no need to involve JSON.NET. If you return your object from your action method, the framework will convert it to JSON for you.

4) You are not using the IDisposable pattern correctly on your disposable objects (SqlConnection). They need to be wrapped in a using statement or disposed of in a finally block.

5) You have two SqlConnection's and only need one.

6) You should utilize the repository pattern instead of doing data access directly in your controller. Follow separation of concerns, have a separate class responsible for this.

Data Repository

using Dapper; //add this to your using statements

public class EmployeeRepository
{
    private readonly string _connectionString;

    public EmployeeRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    public List<Employee> GetAllEmployees()
    {
        string query = "select EMPLOYEE_ID, FIRST_NAME, SALARY from Employee";

        using (SqlConnection connection = new SqlConnection(_connectionString))
        {
            // Query is an extension method from Dapper
            List<Employee> employees = connection.Query<Employee>(query).AsList();
            return employees;
        }
    }
}

Controller

public class EmployeeController
{
    private readonly EmployeeRepository _employeeRepository;

    public EmployeeController()
    {
        string connString = ConfigurationManager
                                .ConnectionStrings["DefaultConnection"]
                                .ConnectionString;

        _employeeRepository = new EmployeeRepository(connString);
    }

    [ActionName("EMPID")] //why does this say EMPID?
    public IEnumerable<Employee> Get()
    {            
        List<Employee> employees = _employeeRepository.GetAllEmployees();
        return employees;      
    }
}

Model

public class Employee
{
    public int EmployeeId { get; set; }

    public string FirstName { get; set; }

    public int Salary { get; set; }
}

---

If you didn't want to use Dapper, you can manually processes the results of your command:

var employees = new List<Employee>();

using (var connection = new SqlConnection(connectionString))
using (var command = new SqlCommand("select EMPLOYEE_ID, FIRST_NAME, SALARY from Employee", connection))
{
    connection.Open();

    using(var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Employee employee = new Employee();
            employee.EmployeeId = reader.GetInt32(reader.GetOrdinal("EMPLOYEE_ID"));
            employee.FirstName = reader.GetString(reader.GetOrdinal("FIRST_NAME"));
            employee.Salary = reader.GetInt32(reader.GetOrdinal("SALARY"));
            employees.Add(employee);
        }
    }
}

return employees;

How to convert the following data from a DataTable to a JSON object

One way to achieve the result you want is by using Json.Net's LINQ-to-JSON API. Here is how you would do that:

// Create a new JObject by grouping the data table rows by block and then
// selecting the groups into JProperties where the block is the property name
// and the property value is a JArray containing JObjects with the resident IDs 
// and corresponding house numbers in the group making up the properties of those. 

var obj = new JObject(
    table.Rows.Cast<DataRow>()
         .GroupBy(r => r["Block"])
         .Select(g => new JProperty("Block " + g.Key,
             new JArray(g.Select(r =>
                 new JObject(
                     new JProperty(r["ResidentId"].ToString(), r["HouseNo"])
                 )
             ))
         ))
);

// Convert the JObject to a JSON string
var json = obj.ToString();

Working demo: https://dotnetfiddle.net/smb9oW

Convert DataTable to JSON array

Try this:

JsonConvert.SerializeObject(dataTable.AsEnumerable().Select(r => r.ItemArray));

how to convert a named datatable to json using json.net

I've created a custom DataTable converter for JSON.NET that does this. Grab the source from here: https://github.com/chris-herring/DataTableConverter

Use it like this:

string json = JsonConvert.SerializeObject(table, new Serialization.DataTableConverter());
var o = JsonConvert.DeserializeObject<DataTable>(json, new Serialization.DataTableConverter());

It serializes/deserializes a System.Data.DataTable in this format:

{
    "TableName": "TestTable",
    "Columns": [
        {
            "AllowDBNull": false,
            "AutoIncrement": true,
            "AutoIncrementSeed": 2,
            "AutoIncrementStep": 1,
            "Caption": "PrimaryKey",
            "ColumnName": "PrimaryKey",
            "DataType": "Int32",
            "DateTimeMode": "UnspecifiedLocal",
            "DefaultValue": null,
            "MaxLength": -1,
            "Ordinal": 0,
            "ReadOnly": false,
            "Unique": true
        }
    ],
    "Rows": [
        [
            1
        ],
        [
            2
        ],
        [
            3
        ]
    ],
    "PrimaryKey": ["PrimaryKey"]
}

---

Related Topics