C# Convert Dataset to JSON

Question

I need to convert a Dataset into a JSON in order to recover in the View. The query there is just an example, because it is a large query. I’ve seen things like:

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

But it didn’t work...

[HttpGet]
public JsonResult GetData()
{
    DataSet ds = new DataSet();

    using (SqlConnection con = new SqlConnection("Data Source=xxxxxx;Initial Catalog=xxx;User ID=xxxx;Password=xxxx"))
    {
       string query = select * from teste;
            using (SqlCommand cmd = new SqlCommand(query))
        {
            cmd.Connection = con;
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {

                sda.Fill(ds);
            }
        }
        return ???
    }
}

Answer 1

I made an example where I use two Tableadapters in 1 Dataset

string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
using (SqlConnection connection = new SqlConnection(connectionString))
{
    string customerCommandText = "select * from customers";
    SqlDataAdapter customerAdapter = new SqlDataAdapter(customerCommandText, connection);

    string ordersCommandText = "select * from Orders";
    SqlDataAdapter ordersAdapter = new SqlDataAdapter(ordersCommandText, connection);

    DataSet customerOrders = new DataSet();
    customerAdapter.Fill(customerOrders, "Customers");
    ordersAdapter.Fill(customerOrders, "Orders");

    DataRelation relation = customerOrders.Relations.Add("CustomerOrders",
        customerOrders.Tables["Customers"].Columns["CustomerId"],
        customerOrders.Tables["Orders"].Columns["CustomerId"]);

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

DDL of the tables

CREATE TABLE [dbo].[Customers] (
    [CustomerId] [int] NOT NULL IDENTITY,
    [Valor] [decimal](18, 2) NOT NULL,
    CONSTRAINT [PK_dbo.Customers] PRIMARY KEY ([CustomerId])
)
CREATE TABLE [dbo].[Orders] (
    [OrderId] [int] NOT NULL IDENTITY,
    [CustomerId] [int] NOT NULL,
    [Teste] [nvarchar](max),
    CONSTRAINT [PK_dbo.Orders] PRIMARY KEY ([OrderId])
)

Serialized dataset

{
  "Customers": [
    {
      "CustomerId": 1,
      "Valor": 920.00
    },
    {
      "CustomerId": 2,
      "Valor": 930.00
    },
    {
      "CustomerId": 3,
      "Valor": 932.00
    }
  ],
  "Orders": [
    {
      "OrderId": 1,
      "CustomerId": 1,
      "Teste": "636510290829216964"
    },
    {
      "OrderId": 2,
      "CustomerId": 1,
      "Teste": "636510290829216964"
    },
    {
      "OrderId": 3,
      "CustomerId": 1,
      "Teste": "636510290829216964"
    },
    {
      "OrderId": 4,
      "CustomerId": 2,
      "Teste": "636510290839303071"
    },
    {
      "OrderId": 5,
      "CustomerId": 2,
      "Teste": "636510290839303071"
    },
    {
      "OrderId": 6,
      "CustomerId": 2,
      "Teste": "636510290839303071"
    },
    {
      "OrderId": 7,
      "CustomerId": 3,
      "Teste": "636510290849322161"
    },
    {
      "OrderId": 8,
      "CustomerId": 3,
      "Teste": "636510290849322161"
    },
    {
      "OrderId": 9,
      "CustomerId": 3,
      "Teste": "636510290849322161"
    }
  ]
}

Answer 2

An assumption without knowing the return becomes complicated to say, but let’s assume in a minimal example that this select (SELECT * FROM teste;) return two fields, the field Id and the countryside Nome and another point to note is no need to use SqlDataAdapter to return the data of this SQL, simple example:

[HttpGet]
public JsonResult GetData()
{
    DataSet ds = new DataSet();
    using (SqlConnection con = new SqlConnection(""))
    {
        string query = "SELECT id, nome FROM teste;";
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            con.Open();
            ds.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges, "teste");
            con.Close();
        }
    }
    var json = Newtonsoft.Json.JsonConvert
        .SerializeObject(ds.Tables[0]);

    var obj = Newtonsoft.Json.JsonConvert
        .DeserializeObject(json, (new [] { new { id = 0, nome = "" } }).GetType());

    return Json(obj, JsonRequestBehavior.AllowGet);
}

in this example there are two conversions that is from table to text in format json then to the object of that json and then return by your action of controller perhaps this becomes unviable for very large data, so this example could be modified to the one below that has better logic and superior performance:

[HttpGet]
public JsonResult GetData()
{       
    List<object> listas = new List<object>();
    using (SqlConnection con = new SqlConnection(""))
    {
        string query = "SELECT id, nome FROM teste;";
        con.Open();
        using (SqlCommand cmd = new SqlCommand(query, con))
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            while(reader.Read())
            {
                listas.Add(new { id = reader.GetInt32(0), nome = reader.GetString(1) });
            }
        }
    }            
    return Json(listas, JsonRequestBehavior.AllowGet);
}

_Observing:

• on the line listas.Add(new { id = reader.GetInt32(0), nome = reader.GetString(1) }); are the fields you saw in the result, if you have more should be added in that objeto.

• to be converted to shares in the MVC must use Json and also the second step to release type requests GET (JsonRequestBehavior.AllowGet).

• this form does not use DataSet, because there’s no need.