C # Convert DataSet in JSON

A guess without knowing the return is complicated to say, but let's assume in a minimal example that this select ( SELECT * FROM teste; ) returns two fields, the Id field and the Nome field, and another point to observe is that you do not 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 are from the table to a text in the format json then to the object of that json and then return by its action of controller may become unfeasible for very large data , then this example could be modified to the 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);
}

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

• This form does not use MVC , because, there is no need.

I have 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 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"
    }
  ]
}