Convert datatable to json in C#

To convert a DataTable into JSON in C# is really very easy. Is there any class or method in .Net which can help us to directly do it, then answer is No. We need to write a small utility class or method which can help to convert any datatable into json easily.What we are going to do is, create a dictionary to keep column name and column values and then create the list of all the columns and values and serialize this list will give the required result. 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 to convert into jSon. We are going to write the code which will open the connection and read all the records into DataReader and load records by using DataTable load method. I tested it and it is really very very fast, see the code

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);
}

I already used this code in my project and it works for every datatable and I never face any problem of any kind. Wasn't it so easy and powerful, enjoy the coding.

Convert DataTable to List in C#

If you want to convert your DataTable to list then it is more than easy, just one line of code will do all the work

IEnumerable<DataRow> dtList = dataTable.AsEnumerable();

or    

List<DataRow> dtList = dataTable.AsEnumerable().ToList();

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: 41,182

Convert xml, json to class in asp.net c# by one click

Convert XML or JSON into a class by using visual studio is as easy as just copy and two clicks, never matter how big or how complicated is our XML or JSON. In this article we will create some dummy XML and json and will try to convert them into class without writing a single property manually. It... By Ali Adravi   On 20 Dec 2014  Viewed: 2,058

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: 6,997

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: 3,899

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: 13,801