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: 4,541

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: 4,344

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,061

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: 534

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,353