How to convert datatable to json format in c#

Minifycode 2020-02-02 Viewed 6.3K times ASP.NET

This article explains how to convert DataTable to JSON string in Asp.net C#. ex. serialize DataTable to JSON array in C#.
For example, let’s say you have SQL database and now want to return SQL data to JSON in C# application ex. how to return JSON String from DataTable in Asp.net C#. There are 1 ways to achieve this task and that are: By using JavaScriptSerializer, By using Json.Net DLL ( newtonsoft )

public ActionResult convertjson()
        {
                SqlTransaction objTrans = null;
                string strConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; // get it from Web.config file 
                SqlConnection objConn = new SqlConnection(strConnString);
                objConn.Open();
                objTrans = objConn.BeginTransaction();            

                SqlCommand cmd = new SqlCommand("select  * from test order by id desc", objConn, objTrans);
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                objTrans.Commit();
                return Json(MRPLookup.DataTableToJSONWithJSONNet(dt), JsonRequestBehavior.AllowGet);
        }

Now in this method we will convert our C# Datatable to JSON using the Newtonsoft DLL.

public static string DataTableToJSONWithJSONNet(DataTable table)
        {
            string JSONString = string.Empty;
            JSONString = JsonConvert.SerializeObject(table);
            return JSONString;
        }

This technic explains how to convert a DataTable to JSON in ASP.NET C#. In any words how to return a JSON String from a DataTable in ASP.NET C#.
 In any words, how to serialize a 
DataTable to a JSON array in C#. 

public string DataTableToJSONJavaScriptSerializer(DataTable table) 
{  
    JavaScriptSerializer jsSerializer = new JavaScriptSerializer();  
    List < Dictionary < string, object >> pRow = 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]);  
        }  
        pRow.Add(childRow);  
    }  
    return jsSerializer.Serialize(pRow);  
}

Convert datatable to JSON using C#.net

public static object DataTableToJSON(DataTable table)
    {
        var lst= new List<Dictionary<string, 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]));
            }
            lst.Add(dict);
        }
        JavaScriptSerializer serializer = new JavaScriptSerializer();
        return serializer.Serialize(lst);
    }

ExtensionMethods

public static string ToJson(this DataTable dt)
{
    List<Dictionary<string, object>> lst = new List<Dictionary<string, object>>();
    Dictionary<string, object> item;
    foreach (DataRow row in dt.Rows)
    {
            item = new Dictionary<string, object>();
                foreach (DataColumn col in dt.Columns)
                {
                    item.Add(col.ColumnName, (Convert.IsDBNull(row[col]) ? null : row[col]));       
        }
        lst.Add(item);
    }
        return Newtonsoft.Json.JsonConvert.SerializeObject(lst);
}

To access the convert datatable value in Json method

$.ajax({
        type: "POST",
        url: "/Services.asmx/MethodName",
        data: "{}",
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        success: function (data) {
            var parsed = $.parseJSON(data.d);
            $.each(parsed, function (i, jsondata) {
            $("#dividtodisplay").append("Title: " + jsondata.title + "<br/>" + "Latitude: " + jsondata.lat);
            });
        },
        error: function (XHR, errStatus, errorThrown) {
            var err = JSON.parse(XHR.responseText);
            errorMessage = err.Message;
            alert(errorMessage);
        }
    });
Tag

Convert Datatable to JSON C#: This article explains how to convert DataTable to JSON string in Asp.net C#. ex. serialize DataTable to JSON array in C#. For example, let’s say you have SQL database and now want to return SQL data to JSON in C# application ex. how to return JSON String from DataTable in Asp.net C#. There are 1 ways to achieve this task and that are: By using JavaScriptSerializer, By using Json.Net DLL ( newtonsoft )
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!DOCTYPE html> <html> <head> <title>Asp.Net Repeater Control</title> </head> <body> <form id="form1" runat="server"> <div style="font:13px Verdana;width:310px;"> <asp:ScriptManager ID="scriptManager" runat="server" EnablePageMethods="true" /> <asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional"> <ContentTemplate> <h3>Asp.Net Repeater Control</h3> <%--THE REPEATER CONTROL.--%> <asp:Repeater id="rp" runat="server"> <%--HEADER OF THE REPEATER--%> <HeaderTemplate> <table border="0" width="200px"> </HeaderTemplate> <%--SHOWING ITEMS--%> <ItemTemplate> <tr> <td style="padding:2px; border:solid 1px #CCC;"> <asp:Label Text='<%# Container.DataItem.ToString() %>' runat="server"></asp:Label> </td> </tr> </ItemTemplate> <%--ALTERNATE TEMPLATE (SHOWING ITEMS IN DIFFERENT COLOR--%> <AlternatingItemTemplate> <tr> <td style="padding:2px; border:solid 1px #CCC; background:#EAF7FB; width:200px;"> <asp:Label Text='<%# Container.DataItem.ToString() %>' runat="server"></asp:Label> </td> </tr> </AlternatingItemTemplate> <%--REPEATER FOOTER--%> <FooterTemplate> </table> <div style="padding:20px 0;"> <asp:Label ID="lblFoot" runat="server"></asp:Label> </div> </FooterTemplate> </asp:Repeater> <%--BUTTON CONTROL--%> <asp:Button ID="btVF" Text="View Files" AutoPostBack="true" OnClick="Show" Font-Names="sanserif" Font-Italic="true" Font-Size="155%" runat="server" /> </ContentTemplate> <Triggers> <asp:AsyncPostBackTrigger ControlID="PostBackTrigger" /> </Triggers> </asp:UpdatePanel> </div> </form> </body> </html> using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.IO; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) {} protected void Show(object sender, EventArgs e) { DirectoryInfo Folder = new DirectoryInfo(Server.MapPath("Files\\")); FileInfo[] fileList = Folder.GetFiles("*.*"); //BIND THE FILE LIST WITH THE REPEATER CONTROL. rp.DataSource = fileList; rp.DataBind(); } } Adding the <AlternatingTemplate> below the <ItemTemplate> will show files with a different background color for every alternate row. <AlternatingItemTemplate> <div style="padding:3px; border:solid 1px #FFF; background:#black"> <asp:Label Text='<%# Container.DataItem.ToString() %>' runat="server"></asp:Label> </div> </AlternatingItemTemplate> </AlternatingItemTemplate> <SeparatorTemplate><hr /> </SeparatorTemplate> Add the <HeaderTemplate> element just above the <ItemTemplate> <HeaderTemplate> <div style="padding-bottom:20px"><strong>Files</strong> (Header)</div> </HeaderTemplate> Repeater with <FooterTemplate> element <FooterTemplate> <div style="padding-top:20px"><asp:Label ID="lblFooter" runat="server"></asp:Label></div> </FooterTemplate> Code Behind In C# Control ft = rp.Controls[rep.Controls.Count - 1].Controls[0]; Label lbl_Footer = ft.FindControl("lblFooter") as Label; lbl_Footer.Text = "Showing total <b>" + fileList.Length + "</b> files in the Footer.";

Recent Posts