Sqldatasource to JSON ... possible ?

Question

User-775831949 posted

I have a lot of sqldatasource created in the past and they are great.

Now there are so many codes or plug in that consumes JSON rather than sqldatasource

Is there a code to create a JSON object FROM sqldatasource and then I can use this JSON to be consumed ?

Any idea ? Thanks

Answer 1

User-1509636757 posted

As far as I know, there is no direct way or something that able to convert an SqlDataSource output to JSON. But, I suggest you refer to this resolved thread for possible alternative. It is on similar topic: How to create a JSON output | The ASP.NET Forums

Also, if you are working with Sql Server 2016 then you may would like to refer JSON support in SQL Server 2016 - Simple Talk

Answer 2

User-1838255255 posted

Hi khbeer,

According to your description, as far as I know, datasource is consist of table, so I make a sample use table, please check:

Sample Code:

<asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Script.Serialization;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebSite
{
    public partial class Page554 : System.Web.UI.Page
    {
        DataTable table = new DataTable();

        protected void Page_Load(object sender, EventArgs e)
        {
            table.Columns.Add("Dosage", typeof(int));
            table.Columns.Add("Drug", typeof(string));
            table.Columns.Add("Patient", typeof(string));
            table.Columns.Add("Date", typeof(DateTime));

            // Here we add five DataRows.
            table.Rows.Add(25, "Indocin", "David", DateTime.Now);
            table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
            table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
            table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
            table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);

        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            string json = DataTableToJSONWithJavaScriptSerializer(table);
            Response.Write(json);
        }

        public string DataTableToJSONWithJavaScriptSerializer(DataTable table)
        {
            JavaScriptSerializer jsSerializer = new JavaScriptSerializer();
            List<Dictionary<string, object>> parentRow = new List<Dictionary<string, object>>();
            Dictionary<string, object> childRow;
            foreach (DataRow row in table.Rows)
            {
                childRow = new Dictionary<string, object>();
                foreach (DataColumn col in table.Columns)
                {
                    childRow.Add(col.ColumnName, row[col]);
                }
                parentRow.Add(childRow);
            }
            return jsSerializer.Serialize(parentRow);
        }
    }
}

For more details, please check the following tutorial: 3 Ways to Convert DataTable to JSON String in ASP.NET C#:

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

Best Regards,

Eric Du

Answer 3

User-775831949 posted

Eric Du

Hi khbeer,

According to your description, as far as I know, datasource is consist of table, so I make a sample use table, please check:

Sample Code:

    public partial class Page554 : System.Web.UI.Page
    {
        DataTable table = new DataTable();

        protected void Page_Load(object sender, EventArgs e)
        {
            table.Columns.Add("Dosage", typeof(int));
            table.Columns.Add("Drug", typeof(string));
            table.Columns.Add("Patient", typeof(string));
            table.Columns.Add("Date", typeof(DateTime));

            // Here we add five DataRows.
            table.Rows.Add(25, "Indocin", "David", DateTime.Now);
            table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
            table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
            table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
            table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);

        }

Best Regards,

Eric Du

Thanks. As I already have tons of live sqldatasource controls on aspx pages, I really need to get data from it to feed to json.

I saw your code take the "table" data inside aspx which is good.
Could you or someone give me a code to get data out from sqldatasource control to "table" above?

I searched and found this. Is this applicable to the code above ?

DataTable dt = ((DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty)).Table;

Thanks

Answer 4

User-775831949 posted

OK Seems there are quite a number of sqldatasource > DataTable code out there.

I will try myself later so this is good enough for the moment.

Thanks all