Convert datatable to JSON in C

Question

I want to get records from database into a DataTable    Then convert the DataTable into a JSON object    Return the JSON object to my JavaScript function    I use this code by calling   string result   JsonConvert SerializeObject DatatableToDictionary queryResult   Title    Newtonsoft Json Formatting Indented     To convert a DataTable to JSON  it works correctly and return the following          1          viewCount   703       clickCount   98             2          viewCount   509       clickCount   85             3          viewCount   578       clickCount   86             4          viewCount   737       clickCount   108             5          viewCount   769       clickCount   130            But I would like it to return the following     records       Title   1   viewCount   703   clickCount   98       Title   2   viewCount   509   clickCount   85       Title   3   viewCount   578   clickCount   86       Title   4   viewCount   737   clickCount   108       Title   5   viewCount   769   clickCount   130         How can I do this

User · Answer

You can use the same way as specified by Alireza Maddah and if u want to use two data table into one json array following is the way:

public string ConvertDataTabletoString()
{
DataTable dt = new DataTable();
DataTable dt1 = new DataTable();
using (SqlConnection con = new SqlConnection("Data Source=SureshDasari;Initial Catalog=master;Integrated Security=true"))
{
    using (SqlCommand cmd = new SqlCommand("select title=City,lat=latitude,lng=longitude,description from LocationDetails", con))
    {
        con.Open();
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(dt);
        System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
        List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
        Dictionary<string, object> row;
        foreach (DataRow dr in dt.Rows)
        {
            row = new Dictionary<string, object>();
            foreach (DataColumn col in dt.Columns)
            {
                row.Add(col.ColumnName, dr[col]);
            }
            rows.Add(row);
        }
        SqlCommand cmd1 = new SqlCommand("_another_query_", con);
                SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
                da1.Fill(dt1);
                System.Web.Script.Serialization.JavaScriptSerializer serializer1 = new System.Web.Script.Serialization.JavaScriptSerializer();
                Dictionary<string, object> row1;
                foreach (DataRow dr in dt1.Rows) //use the old variable rows only
                {
                    row1 = new Dictionary<string, object>();
                    foreach (DataColumn col in dt1.Columns)
                    {
                        row1.Add(col.ColumnName, dr[col]);
                    }
                    rows.Add(row1); // Finally You can add into old json array in this way
                }
        return serializer.Serialize(rows);
    }
}
}

The same way can be used for as many as data tables as you want.

User · Answer

Convert datatable to JSON using C  net   public static object DataTableToJSON DataTable table                var list   new List lt Dictionary lt string  object gt  gt              foreach  DataRow row in table Rows                        var dict   new Dictionary lt string  object gt                  foreach  DataColumn col in table Columns                                dict col ColumnName     Convert ToString row col                               list Add dict                     JavaScriptSerializer serializer   new JavaScriptSerializer             return serializer Serialize list

User · Answer

Pass the datable to this method it would return json String   public DataTable GetTable                         string str    Select   from GL V               OracleCommand cmd   new OracleCommand str  con               cmd CommandType   CommandType Text              DataTable Dt   OracleHelper GetDataSet con  cmd  Tables 0                return Dt                     public string DataTableToJSONWithJSONNet DataTable table                        string JSONString   string Empty              JSONString   JsonConvert SerializeObject table               return JSONString               public static DataSet GetDataSet OracleConnection con  OracleCommand cmd                           create the data set               DataSet ds   new DataSet                try                                 checking current connection state is open                 if  con State    ConnectionState Open                      con Open                        create a data adapter to use with the data set                 OracleDataAdapter da   new OracleDataAdapter cmd                       fill the data set                 da Fill ds                             catch  Exception ex                                 throw                            return ds

User · Answer

I have simple function to convert datatable to json string    I have used Newtonsoft to generate string  I don t use Newtonsoft to totaly serialize Datatable  Be careful about this   Maybe this can be useful    private string DataTableToJson DataTable dt      if  dt    null       return              if  dt Rows Count  lt  1       return               JArray array   new JArray      foreach DataRow dr in dt Rows       JObject item   new JObject       foreach DataColumn col in dt Columns        item Add col ColumnName  dr col ColumnName   ToString             array Add item          return array ToString Newtonsoft Json Formatting Indented

User · Answer

public static string ConvertIntoJson DataTable dt        var jsonString   new StringBuilder        if  dt Rows Count  gt  0                jsonString Append               for  int i   0  i  lt  dt Rows Count  i                          jsonString Append                   for  int j   0  j  lt  dt Columns Count  j                    jsonString Append        dt Columns j  ColumnName                                  dt Rows i  j  ToString   Replace              j  lt  dt Columns Count - 1                                jsonString Append i  lt  dt Rows Count - 1                                  return jsonString Append      ToString              else               return               public static string ConvertIntoJson DataSet ds        var jsonString   new StringBuilder        jsonString Append           for  int i   0  i  lt  ds Tables Count  i                  jsonString Append        ds Tables i  TableName                   jsonString Append ConvertIntoJson ds Tables i             if  i  lt  ds Tables Count - 1              jsonString Append                 jsonString Append           return jsonString ToString

User · Answer

try this  ExtensionMethods    public static string ToJson this DataTable dt        List lt Dictionary lt string  object gt  gt  lst   new List lt Dictionary lt string  object gt  gt         Dictionary lt string  object gt  item      foreach  DataRow row in dt Rows                    item   new Dictionary lt string  object gt                     foreach  DataColumn col in dt Columns                                        item Add col ColumnName   Convert IsDBNull row col     null   row col                              lst Add item                 return Newtonsoft Json JsonConvert SerializeObject lst       and use   DataTable dt   new DataTable          var json   dt ToJson

User · Answer

An alternative way without using javascript serializer       public static string DataTableToJSON DataTable Dt                                string   StrDc   new string Dt Columns Count                    string HeadStr   string Empty                  for  int i   0  i  lt  Dt Columns Count  i                                           StrDc i    Dt Columns i  Caption                      HeadStr           StrDc i              StrDc i    i ToString                                                       HeadStr   HeadStr Substring 0  HeadStr Length - 1                    StringBuilder Sb   new StringBuilder                     Sb Append                        for  int i   0  i  lt  Dt Rows Count  i                                           string TempStr   HeadStr                       for  int j   0  j  lt  Dt Columns Count  j                                                   TempStr   TempStr Replace Dt Columns j    j ToString           Dt Rows i  j  ToString   Trim                                                 Sb AppendFormat     0      TempStr                        Sb Append     TempStr                                             Sb   new StringBuilder Sb ToString   Substring 0  Sb ToString   Length - 1                     if Sb ToString   Length gt 0                  Sb Append                        return StripControlChars Sb ToString                      To strip control characters     A character that does not represent a printable character but   serves to initiate a particular action               public static string StripControlChars string s                                return Regex Replace s       x20- x7F

User · Answer

I am using this function for describe table  Use it after fill datatable  static public string DataTableToJSON DataTable dataTable bool readableformat true                        string JSONString                  string JSONRow              string colVal              foreach DataRow dataRow in dataTable Rows                                if JSONString         JSONString                           JSONRow                       if  readableformat    JSONRow      r n                     JSONRow                          foreach  DataColumn col in dataTable Columns                                        colVal   dataRow col  ToString                        colVal   colVal Replace                                    colVal   colVal Replace                                   if JSONRow      amp  amp JSONRow    r n                               JSONRow                                                    JSONRow           col ColumnName             colVal                                            JSONRow                         JSONString    JSONRow                            JSONString      r n                return JSONString              MySQL Query    DESCRIBE TableName    DataTableToJSON dataTable  Example Output       Field   id   Type   int 5    Null   NO   Key   PRI   Default      Extra   auto increment      Field   ad   Type   int 11  unsigned   Null   NO   Key   MUL   Default      Extra         Field   soyad   Type   varchar 20    Null   YES   Key      Default      Extra         Field   ulke   Type   varchar 20    Null   YES   Key      Default      Extra         Field   alan   Type   varchar 20    Null   YES   Key      Default      Extra         Field   numara   Type   varchar 20    Null   NO   Key      Default      Extra          Tested With PHP    X      Field   id   Type   int 5    Null   NO   Key   PRI   Default      Extra   auto increment      Field   ad   Type   int 11  unsigned   Null   NO   Key   MUL   Default      Extra         Field   soyad   Type   varchar 20    Null   YES   Key      Default      Extra         Field   ulke   Type   varchar 20    Null   YES   Key      Default      Extra         Field   alan   Type   varchar 20    Null   YES   Key      Default      Extra         Field   numara   Type   varchar 20    Null   NO   Key      Default      Extra           Y json decode  X true   echo  Y 0   Field    var dump  Y

User · Answer

Very Simple these days    string json   JsonConvert SerializeObject YourDataTable  Formatting Indented     Now Convert your Json to a DataTable   YourDataTable    DataTable JsonConvert DeserializeObject json   typeof DataTable       Works for DataSets as well

User · Answer

Common DLL client  server public class transferDataTable       public class myError               public string Message   get  set            public int Code   get  set               public myError Error   get  set        public List lt string gt  ColumnNames   get  set        public List lt string gt  DataTypes   get  set        public List lt Object gt  Data   get  set        public int Count   get  set       public static class ExtensionMethod       public static transferDataTable LoadData this transferDataTable transfer  DataTable dt                if  dt    null                        transfer DataTypes   new List lt string gt                 transfer ColumnNames   new List lt string gt                                 foreach  DataColumn c in dt Columns                                transfer ColumnNames Add c ColumnName                   transfer DataTypes Add c DataType ToString                                transfer Data   new List lt object gt                 foreach  DataRow dr in dt Rows                                foreach  DataColumn col in dt Columns                                        transfer Data Add dr col     DBNull Value   null   dr col                                                transfer Count   dt Rows Count                                return transfer                     public static DataTable GetDataTable this transferDataTable transfer  bool ConvertToLocalTime   true                if  transfer Error    null    transfer ColumnNames    null    transfer DataTypes    null    transfer Data    null              return null           int columnsCount   transfer ColumnNames Count          DataTable dt   new DataTable            for  int i   0  i  lt  columnsCount  i                           Type colType   Type GetType transfer DataTypes i                dt Columns Add new DataColumn transfer ColumnNames i   colType                       int index   0          DataRow row   dt NewRow            foreach  object o in transfer Data                        if  ConvertToLocalTime  amp  amp  o    null  amp  amp  o GetType      typeof DateTime                                 DateTime dat   Convert ToDateTime o                   row index    dat ToLocalTime                              else                 row index    o    null   DBNull Value   o               index                 if  columnsCount    index                                index   0                  dt Rows Add row                   row   dt NewRow                                    return dt             Server      OperationContract       WebInvoke Method    GET   ResponseFormat   WebMessageFormat Json  BodyStyle   WebMessageBodyStyle WrappedRequest  UriTemplate    json data        transferDataTable  Data         public transferDataTable  Data                 try                       using  SqlConnection con   new SqlConnection ConfigurationManager AppSettings  myConnString                                   con Open                    DataSet ds   new DataSet                    SqlDataAdapter myAdapter   new SqlDataAdapter  SELECT   FROM tbGalleries   con                   myAdapter Fill ds   table                    DataTable dt   ds Tables  table                    return new transferDataTable   LoadData dt                                   catch Exception ex                        return new transferDataTable     Error   new transferDataTable myError     Message   ex Message  Code   ex HResult                         Client         Response   Vossa getAPI serviceUrl    json data            transferDataTable transfer   new JavaScriptSerializer   Deserialize lt transferDataTable gt  Response           if  transfer Error    null                        DataTable dt   transfer GetDataTable                dbGrid ItemsSource   dt DefaultView                    else             MessageBox Show transfer Error Message   Error   MessageBoxButton OK  MessageBoxImage Error

User · Answer

All of these answers are really great for moving data  Where they fail is preserving the column type of data being moved  This becomes a problem when you want to do things like merge datatables that appear to be the same  JsonConvert will look at the first row of data to determine the column datatype  which may be guessed incorrectly  To get around this   Serialize the DataTable and DataColumn definitions in separate response objects  Deserialize the DataColumn definitions in the response before reading in the table  Deserialize and merge the DataTable ignoring the schema defined by Json   It sounds like a lot  but its only three additional lines of code     Get our Column definitions and serialize them using an anoymous function  var columns   dt Columns Cast lt DataColumn gt    Select c   gt  new   DataPropertyName   c ColumnName  DataPropertyType   c DataType ToString      resp ObjSchema   JsonConvert SerializeObject columns   resp Obj   JsonConvert SerializeObject dt    resp ObjSchema becomes             quot DataPropertyName quot    quot RowId quot        quot DataPropertyType  quot    quot System Int32 quot                quot DataPropertyName quot    quot ItemName quot        quot DataPropertyType  quot    quot System String quot         Instead of letting Json define the column definitions via dt   JsonConvert DeserializeObject lt DataTable gt  response  we can use LINQ on our resp ObjSchema to define them ourselves  We ll use MissingSchemaAction Ignore to ignore the schema provided by Json     If your environment does not support dynamic you ll need to create a class for with DataPropertyName and DataPropertyType  JsonConvert DeserializeObject lt List lt dynamic gt  gt  response ObjSchema  ForEach prop   gt        dt Columns Add new DataColumn     ColumnName   prop DataPropertyName  DataType   Type GetType prop DataPropertyType ToString               Merge the results ignoring the JSON schema  dt Merge JsonConvert DeserializeObject lt DataTable gt  response Obj   true  MissingSchemaAction Ignore

User · Answer

This has similar approach to the accepted answer  but uses LINQ to convert datatable to list in a single line of code     convert datatable to list using LINQ  Input datatable is  dt   returning list of  name value  tuples var lst   dt AsEnumerable        Select r   gt  r Table Columns Cast lt DataColumn gt                 Select c   gt  new KeyValuePair lt string  object gt  c ColumnName  r c Ordinal                ToDictionary z  gt z Key z  gt z Value        ToList      now serialize it var serializer   new System Web Script Serialization JavaScriptSerializer    return serializer Serialize lst     This is an incredibly useful way to enumerate a datatable  which would normally take a ton of coding  Here are some variations     convert to list with array of values for each row var list1   dt AsEnumerable   Select r   gt  r ItemArray ToList    ToList       convert to list of first column values only var list2   dt AsEnumerable   Select r   gt  r ItemArray 0   ToList        parse a datatable with conditions and get CSV string string MalesOver21   string Join          dt AsEnumerable          Where r   gt  r  GENDER   ToString     M   amp  amp  r Field lt int gt   AGE   gt 21         Select r   gt  r Field lt string gt   FULLNAME          This is off topic to the original question but for completeness sake  I d mention that if you just want to filter out rows from an existing datatable  See this answer

User · Answer

Try this custom function                                                                                                    public static string DataTableToJsonObj DataTable dt                DataSet ds   new DataSet            ds Merge dt           StringBuilder jsonString   new StringBuilder             if  ds Tables 0  Rows Count  gt  0                        jsonString Append                   for  int rows   0  rows  lt  ds Tables 0  Rows Count  rows                                  jsonString Append                       for  int cols   0  cols  lt  ds Tables 0  Columns Count  cols                                          jsonString Append         ds Tables 0  Columns cols  ColumnName                                                           IF NOT LAST PROPERTY                      if  cols  lt  ds Tables 0  Columns Count - 1                                                GenerateJsonProperty ds  rows  cols  jsonString                                                IF LAST PROPERTY                      else if  cols    ds Tables 0  Columns Count - 1                                                GenerateJsonProperty ds  rows  cols  jsonString  true                                                                     var b    cols  lt  ds Tables 0  Columns Count - 1                            GenerateJsonProperty ds  rows  cols  jsonString                             cols    ds Tables 0  Columns Count - 1                               GenerateJsonProperty ds  rows  cols  jsonString  true                                     jsonString Append rows    ds Tables 0  Rows Count - 1                                          jsonString Append                   return jsonString ToString                      return null             private static bool GenerateJsonProperty DataSet ds  int rows  int cols  StringBuilder jsonString  bool isLast   false                    IF LAST PROPERTY THEN REMOVE  COMMA   IF NOT LAST PROPERTY THEN ADD  COMMA          string addComma   isLast                      if  ds Tables 0  Rows rows  cols     DBNull Value                        jsonString Append   null     addComma                     else if  ds Tables 0  Columns cols  DataType    typeof DateTime                         jsonString Append            DateTime ds Tables 0  Rows rows  cols   ToString  yyyy-MM-dd HH   mm   ss              addComma                     else if  ds Tables 0  Columns cols  DataType    typeof string                         jsonString Append          ds Tables 0  Rows rows  cols             addComma                     else if  ds Tables 0  Columns cols  DataType    typeof bool                         jsonString Append Convert ToBoolean ds Tables 0  Rows rows  cols      true     fasle                      else                       jsonString Append ds Tables 0  Rows rows  cols    addComma                      return true

User · Answer

We can accomplish the task in two simple way one is using Json NET dll and another is by using StringBuilder class   Using Newtonsoft Json NET  string JSONresult  JSONresult   JsonConvert SerializeObject dt     Response Write JSONresult     Reference Link  Newtonsoft  Convert DataTable to JSON object in ASP Net C   Using StringBuilder  public string DataTableToJsonObj DataTable dt        DataSet ds   new DataSet        ds Merge dt       StringBuilder JsonString   new StringBuilder        if  ds    null  amp  amp  ds Tables 0  Rows Count  gt  0                JsonString Append               for  int i   0  i  lt  ds Tables 0  Rows Count  i                          JsonString Append                   for  int j   0  j  lt  ds Tables 0  Columns Count  j                                  if  j  lt  ds Tables 0  Columns Count - 1                                        JsonString Append        ds Tables 0  Columns j  ColumnName ToString                    ds Tables 0  Rows i  j  ToString                                               else if  j    ds Tables 0  Columns Count - 1                                        JsonString Append        ds Tables 0  Columns j  ColumnName ToString                    ds Tables 0  Rows i  j  ToString                                                        if  i    ds Tables 0  Rows Count - 1                                JsonString Append                                 else                               JsonString Append                                        JsonString Append               return JsonString ToString              else               return null

User · Answer

To access the convert datatable value in Json method follow the below steps     ajax           type   POST           url    Services asmx YourMethodName           data                contentType   application json  charset utf-8           dataType   json           success  function  data                var parsed     parseJSON data d                 each parsed  function  i  jsondata                    dividtodisplay   append  Title      jsondata title     lt br  gt      Latitude      jsondata lat                                      error  function  XHR  errStatus  errorThrown                var err   JSON parse XHR responseText               errorMessage   err Message              alert errorMessage

User · Answer

With Cinchoo ETL - an open source library  you can export DataTable to JSON easily with few lines of code  StringBuilder sb   new StringBuilder    string connectionstring     Data Source  localdb  MSSQLLocalDB Initial Catalog Northwind Integrated Security True   using  var conn   new SqlConnection connectionstring         conn Open        var comm   new SqlCommand  SELECT   FROM Customers   conn       SqlDataAdapter adap   new SqlDataAdapter comm        DataTable dt   new DataTable  Customer        adap Fill dt        using  var parser   new ChoJSONWriter sb           parser Write dt      Console WriteLine sb ToString       Output        Customer                  CustomerID    ALFKI          CompanyName    Alfreds Futterkiste          ContactName    Maria Anders          ContactTitle    Sales Representative          Address    Obere Str  57          City    Berlin          Region   null         PostalCode    12209          Country    Germany          Phone    030-0074321          Fax    030-0076545                      CustomerID    ANATR          CompanyName    Ana Trujillo Emparedados y helados          ContactName    Ana Trujillo          ContactTitle    Owner          Address    Avda  de la Constituci  n 2222          City    M  xico D F           Region   null         PostalCode    05021          Country    Mexico          Phone     5  555-4729          Fax     5  555-3745

User · Answer

This code snippet from  Convert Datatable to JSON String in C   VB NET might help you  It uses System Web Script Serialization JavaScriptSerializer to serialize the contents to JSON format   public string ConvertDataTabletoString         DataTable dt   new DataTable        using  SqlConnection con   new SqlConnection  Data Source SureshDasari Initial Catalog master Integrated Security true                  using  SqlCommand cmd   new SqlCommand  select title City lat latitude lng longitude description from LocationDetails   con                         con Open                SqlDataAdapter da   new SqlDataAdapter cmd               da Fill dt               System Web Script Serialization JavaScriptSerializer serializer   new System Web Script Serialization JavaScriptSerializer                List lt Dictionary lt string  object gt  gt  rows   new List lt Dictionary lt string  object gt  gt                 Dictionary lt string  object gt  row              foreach  DataRow dr in dt Rows                                row   new Dictionary lt string  object gt                     foreach  DataColumn col in dt Columns                                        row Add col ColumnName  dr col                                      rows Add row                             return serializer Serialize rows

[c#] Convert datatable to JSON in C#

Examples related to c#

Examples related to json

Examples related to datatable