Search:
Welcome Guest | Register | Login
logo

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 Developed many project by using asp.net, C#, ajax, sql server, javascript, jquery etc.
  • json
  • DataTable
By Ali Adravi
at 09 Jun, 13
Viewed: 1,267
Other blogs you may like
Comments
Superb example, offering what other examples do not, especially the GetDataTable method.
By Ben on 18 Feb 2014 05:19:35 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.