Showing sql query results in json format in web api

Question

I am having multiple tables in my database and from one single query I am getting the desired output. I want to show the query results in json format. I am getting the output in json format but it is showing collections instead of single array

    public class ValuesController : ApiController
    {
        Database_Access_layer.service db = new Database_Access_layer.service();

        public DataSet GetRecord()
        {
            DataSet ds = db.GetRecords();
            return ds;
        }
    }

    public class Service
    {
        string st = ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString;
        public DataSet GetRecords()
        {
            SqlConnection con = new SqlConnection(st);
            string adp = "";
            adp = @"select   s.State_Id, RTRIM(s.State_Name) as state_name,count(1) as totalNo
            from  BUyer_details                  
            inner join Vehicles_Details vd  on 
            BUyer_details.Vehicles_Id=vd.Vehicles_Id                  
            inner join Model_Registration mr on vd.Model_Code=mr.xEV_Model_Code                   
            left outer join OEM_Registration om on BUyer_details.OEM_Id = om.OEM_Id     
            inner join Dealer_Registration dr  on BUyer_details.Dealer_Id= dr.Dealer_Id
            inner join Mst_Vehicle_Category vc on mr.Vihecle_cat_id=vc.Vihecle_cat_id 
            inner join tbl_MasterState s on dr.State_Id=s.State_Id 
            where 1=1    group by s.State_Name,s.State_Id order by State_Id asc";
            SqlDataAdapter aa = new SqlDataAdapter(adp, con);
            DataSet ds = new DataSet();
            aa.Fill(ds);
            return ds;
        }
    }

Possible duplicate of [Convert datatable to JSON in C#](https://stackoverflow.com/questions/17398019/convert-datatable-to-json-in-c-sharp) — Bosco, Aug 07 '19 at 08:24

score 0 · Answer 1 · answered Aug 07 '19 at 06:55

0

I guess this is what you want to do Convert datatable to JSON in C#

answered Aug 07 '19 at 06:55

harmath

183
5

Ram Anugandula · Answer 2 · 2019-08-07T08:29:04.150

0

Convert your Datatable to JSON.

public string GetRecord()
{
     DataSet ds = db.GetRecords();
     var jsonString = JSONConvert.SerializeObject(ds);
     return jsonString;
}

OR If your want reusable method

Checkout below code :

public string GetRecord()
{
     DataSet ds = db.GetRecords();
     return DataTableToJSON(ds);
}

Create one genric method to convert datable to Json Add JSON.Net DLL from nuget package :

using Newtonsoft.JSON; 

public string DataTableToJSON(DataTable table) {  
   string JSONString=string.Empty;  
   JSONString = JSONConvert.SerializeObject(table);  
   return JSONString;  
}

edited Aug 07 '19 at 08:29

answered Aug 07 '19 at 07:01

Ram Anugandula

582
4
15

Your method is not going to compile, you are returning `jsonString` for `DataSet` return type – Bosco Aug 07 '19 at 08:24

Showing sql query results in json format in web api

2 Answers2