C# ASP.NET: Convert a DataTable into Json format using JavaScriptSerializer -

In today’s blog we learn how can we convert a datatable into json format. So, I describe the whole blog into three parts in first part firstly we create a database table and insert some records into the table, after that we create a class for the structure of our list and finally the coding part of the tutorial in which we convert the whole table into json.

So, without wasting any time let’s start…

Create a table and insert some records into the table

Create Table

Firstly, We have to create a table with certain fields in SQL Server. I created the following table named as ‘tb_ank’ with fields named as ‘Id’, ‘name’, ’empid’, ‘city’. Code is as follows:

create table tb_ank(

Id int identity(1,1) primary key,

name varchar (max) not null,

empid int not null,

city varchar (max)

);

Insert records into table

Now, insert some records into the table. The code for insert data into the table is as follows:

insert into tb_ank (name, empid, city) values ('ankit',123,'new delhi');
insert into tb_ank (name, empid, city) values ('nitin',125,'allahabad');
insert into tb_ank (name, empid, city) values ('akash',124,'lucknow');
insert into tb_ank (name, empid, city) values ('rajat',126,'fatehpur');
insert into tb_ank (name, empid, city) values ('sanjeev',127,'gonda');

So, Your table must be look like the given image:

Create a class

In the second Step go to Solution Explorer in the Visual Studio IDE. Then right click and create a class as add new item.

Solution Explorer>>Add New Item>> Class

Following is the code and the structure of the class. I created a class named as ‘AnkClass’.

public class AnkClass
{
       public class EmpData
       {
        public string name { get; set; }
        public string empid { get; set; }
        public string city { get; set; }
       }
}

C# code for convert DataTable into Json

There are some important namespaces that is require for using SQL and JavaScriptSerializer.

using System.Data;

using System.Data.SqlClient;

using System.Web.Script.Serialization;

Now we code to convert the datatable into json format. The important steps to convert in json:

First add connection string that is global.
Write SQL command to fetch records from the table.
Store the DataSet into DataTable
Create a object to call the class that we create in second step
Now create a for loop to print the list and add it into the class object
Finally use JavaScriptSerializer() to serialize the whole records

public partial class datatable_to_json : System.Web.UI.Page

{
    SqlConnection con = new SqlConnection(@"Data Source=xxxxxxxxxx;Initial   Catalog=db_ank;User ID=sa;Password=12345");
    protected void Page_Load(object sender, EventArgs e)

    {
        SqlCommand cmd = new SqlCommand("select * from tb_ank", con);
        DataTable dt = new DataTable();
        DataSet ds = new DataSet();
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        sda.Fill(ds);
        dt= ds.Tables[0];
        List<AnkClass.EmpData> empdata = new List<AnkClass.EmpData>();

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            AnkClass.EmpData empdata1 = new AnkClass.EmpData
            {
                name = dt.Rows[i]["name"].ToString(),
                empid = Convert.ToString(dt.Rows[i]["empid"]),
                city = dt.Rows[i]["city"].ToString(),
            };
            empdata.Add(empdata1);
        }   
        var json = new JavaScriptSerializer().Serialize(empdata);
        Response.Write(json);
    }
}

Your output looks like follows(in Json format):

[{"name":"akash","empid":"124","city":"lucknow"},{"name":"ankit","empid":"123","city":"new delhi"},{"name":"nitin","empid":"125","city":"allahabad"},{"name":"rajat","empid":"126","city":"fatehpur"},{"name":"sanjeev","empid":"127","city":"gonda"}]