Convert datatable to json in c#

To convert a DataTable into JSON in c# is really very easy, we will create a dictionary to keep column name and column values and then create the list of all the columns and values. It will be clear when we will write the code, so lets start:

Create function to convert DataTable into json string

public String ConvertDataTableTojSonString(DataTable dataTable)
{
   System.Web.Script.Serialization.JavaScriptSerializer serializer = 
          new System.Web.Script.Serialization.JavaScriptSerializer();

   List<Dictionary<String, Object>> tableRows = new List<Dictionary<String, Object>>();

  Dictionary<String, Object> row;

  foreach (DataRow dr in dataTable.Rows)
  {
      row = new Dictionary<String, Object>();
      foreach (DataColumn col in dataTable.Columns)
      {
         row.Add(col.ColumnName, dr[col]);
       }
       tableRows.Add(row);
  }
   return  serializer.Serialize(tableRows);
}

This is only the code which takes DataTable as parameter and returns json string, Now create method to get records from database into DataTable

public DataTable GetDataTable()
{
   DataTable dataTable = new DataTable();
   using (SqlConnection conn = 
new SqlConnection(ConfigurationManager.ConnectionStrings["dbConString"].ConnectionString))
  {
       SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = "Customer_Search";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@City", txtCity.Text);
        if (conn.State != ConnectionState.Open)
            conn.Open();

        SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        dataTable.Load(dr);                
    }
     return dataTable;
 }

Finally add a button and it's click event to call and see the result in action

protected void Button1_Click(object sender, EventArgs e)
{
     DataTable dataTable = GetDataTable();
     String jSonString = ConvertDataTableTojSonString(dataTable);
}

That's it, so we completed our task.

Ali Adravi Having 10+ years of experience in Microsoft Technologies (C#, ASP.Net, MVC and SQL Server). Worked with Metaoption LLC, for more than 8 years and still with the same company. Always ready to learn new technologies and tricks.
  • json
  • DataTable
By Ali Adravi On 09 Jun, 13  Viewed: 3,905

Other blogs you may like

Convert object to json and json to object in C#

In this article we will see how to convert an object to json string or how to convert a json string to an object in C#, we will try to write a generic class with two methods, one for converting an object to json and other for converting json string to an object. I searched internet to get if... By Ali Adravi   On 16 Jun 2013  Viewed: 3,904

MVC jquery autocomplete with value and text field

In MVC, autocomplete with jquery is the best way to pull data from database and render according to our requirements, so in this article we will use jquery to show the auto complete text box without any ajax controls. We need a method to get the data from database, a controller method to handle the... By Ali Adravi   On 29 Jun 2013  Viewed: 1,005

How to use linq query on a datatable in C#

Is it possible to perform a linq query on datatable, answer is no because datatable’s row collection, DataRowCollection does not implement IEnumerable<T> so we cannot perform linq query on it. But if there is any way to convert our datatable data row collection into IEnumerable, then surely we can.... By Charles Fuller   On 25 Jun 2013  Viewed: 500

How to fill datatable from datareader in C# without dataadapter?

Most of the developer use DataAdapter to fill the DataSet and then take the first table from the data set, which is quite slow, why can't we use DataReader to read the data and use datatable's Load method to load the reader into it, in this article we will see how we can load a table faster and... By Alicia Gonzalez   On 09 Jun 2013  Viewed: 2,247
Comments
Ben
Superb example, offering what other examples do not, especially the GetDataTable method.
By Ben on 18 Feb 2014 05:19:35 PM
ayesha
very nice. Thanks. Take a look into this article http://www.etechpulse.com/2012/09/convert-datatable-to-json-in-aspnet.html
By ayesha on 22 Apr 2014 12:16:55 PM
Sujan Kumar
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer(); Showing error no namespace are taking
By Sujan Kumar on 07 Aug 2014 12:06:15 PM
Sujan Kumar
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer(); Showing error no namespace are taking...So can U please Tell me any solution
By Sujan Kumar on 07 Aug 2014 12:13:14 PM
I accept terms & condition as well as privacy policy!
Note: If you are writing some code in comment and after post it is not formatting properly then please refresh the page.