Showing sql query results in json format

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;
}
}

After getting data into your dataset or datatable use below function to convert them in to json string.

public static object DataTableToJSON(DataTable table)
{
var list = new Liststring, object>>();
foreach (DataRow row in table.Rows)
{
var dict = new Dictionary<string, object>();
foreach (DataColumn col in table.Columns)
{
dict[col.ColumnName] = (Convert.ToString(row[col]));
}
list.Add(dict);
}
JavaScriptSerializer serializer = new JavaScriptSerializer
{
MaxJsonLength = Int32.MaxValue
};
return serializer.Serialize(list);
}
public static object DataSetToJSON(DataSet ds)
{
ArrayList root = new ArrayList();
Liststring, object>> table;
Dictionary<string, object> data;
foreach (DataTable dt in ds.Tables)
{
table = new Liststring, object>>();
foreach (DataRow dr in dt.Rows)
{
data = new Dictionary<string, object>();
foreach (DataColumn col in dt.Columns)
{
data.Add(col.ColumnName, dr[col]);
}
table.Add(data);
}
root.Add(table);
}
JavaScriptSerializer serializer = new JavaScriptSerializer
{
MaxJsonLength = Int32.MaxValue
};
return serializer.Serialize(root);
}

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajanikant Hawaldar7y ago
Hi Sunny,

Go through below article, explained with three ways to convert Datatable to JSON format.

https://www.c-sharpcorner.com/UploadFile/9bff34/3-ways-to-convert-datatable-to-json-string-in-Asp-Net-C-Sharp/

Thanks
Amit Mohanty7y ago
After getting data into your dataset or datatable use below function to convert them in to json string.
public static object DataTableToJSON(DataTable table)
{
    var list = new Liststring, object>>();
    foreach (DataRow row in table.Rows)
    {
        var dict = new Dictionary<string, object>();
        foreach (DataColumn col in table.Columns)
        {
            dict[col.ColumnName] = (Convert.ToString(row[col]));
        }
        list.Add(dict);
    }
    JavaScriptSerializer serializer = new JavaScriptSerializer
    {
        MaxJsonLength = Int32.MaxValue
    };
    return serializer.Serialize(list);
}

public static object DataSetToJSON(DataSet ds)
{
    ArrayList root = new ArrayList();
    Liststring, object>> table;
    Dictionary<string, object> data;

    foreach (DataTable dt in ds.Tables)
    {
        table = new Liststring, object>>();
        foreach (DataRow dr in dt.Rows)
        {
            data = new Dictionary<string, object>();
            foreach (DataColumn col in dt.Columns)
            {
                data.Add(col.ColumnName, dr[col]);
            }
            table.Add(data);
        }
        root.Add(table);
    }
    JavaScriptSerializer serializer = new JavaScriptSerializer
    {
        MaxJsonLength = Int32.MaxValue
    };
    return serializer.Serialize(root);
}