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?

Possible duplicate of [DataTable to JSON](https://stackoverflow.com/questions/451460/datatable-to-json) — Evan Carroll, Dec 24 '17 at 05:51
For .net core see also https://stackoverflow.com/q/59780446/8479 — Rory, Feb 25 '22 at 19:55

score 205 · Accepted Answer · answered Jul 01 '13 at 05:44

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<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);
            }
            return serializer.Serialize(rows);
        }
    }
}

if i have 2 datatables and i need to convert them in one json string but i need each datatable in json array what shall i do? — User7291, Oct 23 '13 at 14:23
You can use Json.Net instead of JavaScriptSerializer: return Newtonsoft.Json.JsonConvert.SerializeObject(rows); — Diego, Feb 23 '15 at 21:50
For me (.NET Framework 4.5), the "Script" in System.Web.Script is not recognized/resolvable; neither is the "Serialize" in "seralizer.Serialize(rows)" — B. Clay Shannon-B. Crow Raven, Dec 30 '15 at 22:56

score 93 · Answer 2 · edited Dec 11 '18 at 12:33

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 && ds.Tables[0].Rows.Count > 0)
    {
        JsonString.Append("[");
        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        {
            JsonString.Append("{");
            for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
            {
                if (j < 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;
    }
}

this function is work fine ,but how to handle double quotation in DataTable row field. — Pranay Soni, Feb 03 '16 at 11:13
JsonString approach is about twice as fast as SerializeObject for some reason... performance is also not very good on large datasets. — hajikelist, Jan 16 '17 at 21:28
this code is good, but it will be much faster to read the data directly from data table parameter instead of creating another variable — ayanix, Apr 05 '18 at 10:04
This isn't a great solution. For starters, if your database has any values that have quote chars in it - this will fail. You're better off using JsonTextWriter from the Newtonsoft package — statictype, Jan 10 '19 at 04:29
using Using Newtonsoft Json.NET is probably the simplest and best solution IMO — user5328504, Jun 26 '19 at 19:36

Vijay Jagdale · Answer 3 · 2019-03-26T18:50:12.883

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 => r.Table.Columns.Cast<DataColumn>()
            .Select(c => new KeyValuePair<string, object>(c.ColumnName, r[c.Ordinal])
           ).ToDictionary(z=>z.Key,z=>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 => r.ItemArray.ToList()).ToList();

//convert to list of first column values only
var list2 = dt.AsEnumerable().Select(r => r.ItemArray[0]).ToList();

// parse a datatable with conditions and get CSV string
string MalesOver21 = string.Join(",",
    dt.AsEnumerable()
      .Where(r => r["GENDER"].ToString()=="M" && r.Field<int>("AGE")>21)
      .Select(r => r.Field<string>("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

Assuming I used this code, what would I put on the View to show the json data? — Jamie, Nov 08 '18 at 16:59
That's a very open-ended question. You can use a number of JavaScript Frameworks and tools to parse the data sent to the view. JQgrid for example. Or you could use JavaScript to parse the list using a For Loop and manually construct a table with html. In my last example malesover21, I concatenated the result into a string. You could build an html table or list in C# and just return the content. — Vijay Jagdale, Nov 10 '18 at 14:34
Ok thanks. I ended up using the jquery autocomplete widget to show the list. Your code helped tremendously, thank you! :) — Jamie, Nov 12 '18 at 15:14

score 20 · Answer 4 · answered Jan 11 '14 at 13:43

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 < 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 < Dt.Rows.Count; i++)
                {

                    string TempStr = HeadStr;

                    for (int j = 0; j < 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>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]", "");
            }

May I know why I am downvoted ? Its a working code-snippet ...Its being used in production environment. — Durai Amuthan.H, Jan 28 '15 at 07:34
Probably because you're manually creating a JSON structure when you could be using a more OO approach. It's pretty messy to do it this way. — Josh M., May 13 '15 at 18:18
@JoshM. - My answer is meant to show one more way to skin the mongoose. — Durai Amuthan.H, May 14 '15 at 06:48
Something tells me that a programmer should be able to write code just as much as use 3rd party tools and that writing custom code can be better than using a bloated tool for something this simple. — Adam Heeg, May 12 '16 at 13:47
This was written a long time ago, but as someone who hates using third-party libraries, I love this. Doing it manually is my favorite way of doing it because you just have so much more control over the application. Also, just because you did it manually doesn't mean it's not "OO." Just creating another class in your directory and using it like an import would fix that. Nicely done! — FastFinge, Jun 07 '22 at 17:12

score 13 · Answer 5 · answered Apr 25 '20 at 00:38

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..

score 6 · Answer 6 · answered Mar 13 '14 at 05:53

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.

score 5 · Answer 7 · edited Apr 02 '15 at 08:09

Convert datatable to JSON using C#.net

 public static object DataTableToJSON(DataTable table)
    {
        var list = new List<Dictionary<string, object>>();

        foreach (DataRow row in table.Rows)
        {
            var dict = new Dictionary<string, object>();

            foreach (DataColumn col in table.Columns)
            {
                dict[col.ColumnName] = (Convert.ToString(row[col]));
            }
            list.Add(dict);
        }
        JavaScriptSerializer serializer = new JavaScriptSerializer();

        return serializer.Serialize(list);
    }

Hasan Javaid · Answer 8 · 2016-05-12T06:15:23.810

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 > 0)
        {
            jsonString.Append("[");
            for (int rows = 0; rows < ds.Tables[0].Rows.Count; rows++)
            {
                jsonString.Append("{");
                for (int cols = 0; cols < ds.Tables[0].Columns.Count; cols++)
                {
                    jsonString.Append(@"""" + ds.Tables[0].Columns[cols].ColumnName + @""":");

                    /* 
                    //IF NOT LAST PROPERTY

                    if (cols < 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 < 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;
    }

After This Deserialize Your Array Like This. var deserializeArray = new JavaScriptSerializer().Deserialize(desrilizeDashboard); — Hasan Javaid, May 11 '16 at 12:29

score 3 · Answer 9 · answered Aug 27 '20 at 17:39

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<DataColumn>().Select(c => new { DataPropertyName = c.ColumnName, DataPropertyType = c.DataType.ToString()});
resp.ObjSchema = JsonConvert.SerializeObject(columns);
resp.Obj = JsonConvert.SerializeObject(dt);

resp.ObjSchema becomes;

[
  {
    "DataPropertyName": "RowId",
    "DataPropertyType ": "System.Int32"
  },
  {
    "DataPropertyName": "ItemName",
    "DataPropertyType ": "System.String"
  }
]

Instead of letting Json define the column definitions via dt = JsonConvert.DeserializeObject<DataTable>(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<List<dynamic>>(response.ObjSchema).ForEach(prop =>
{
    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<DataTable>(response.Obj), true, MissingSchemaAction.Ignore);

Thanks for this. I created an answer that builds from your answer. I'm just encapsulating it all in a class. Makes it easier and allows other people to simply copy and paste and use. — Michael Z., May 26 '21 at 05:35

score 2 · Answer 10 · answered Dec 06 '13 at 05:26

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 + "<br/>" + "Latitude: " + jsondata.lat);
            });
        },
        error: function (XHR, errStatus, errorThrown) {
            var err = JSON.parse(XHR.responseText);
            errorMessage = err.Message;
            alert(errorMessage);
        }
    });

Ecd · Answer 11 · 2018-06-12T11:54:34.870

1

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

edited Jun 12 '18 at 11:54

answered Sep 07 '17 at 12:06

Ecd

439
4
8

I am already using this code. What type of error do you get ? – Ecd Jun 09 '18 at 07:35
you can't be using that exact code, BECAUSE it does NOT compile! – Mitch Wheat Jun 09 '18 at 07:37
Are you sure that you have important right namespaces ? https://gist.github.com/ecdundar/df123aa2526b72cd8df976423ead8daf – Ecd Jun 11 '18 at 09:55
hint: item.Add(col.ColumnName, dr[col.ColumnName] ? .ToString()); – Mitch Wheat Jun 11 '18 at 11:12

Cinchoo · Answer 12 · 2019-09-03T12:30:03.480

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"
    }
  ]
}

score 1 · Answer 13 · answered Jun 30 '19 at 03:43

try this (ExtensionMethods):

public static string ToJson(this DataTable dt)
{
    List<Dictionary<string, object>> lst = new List<Dictionary<string, object>>();
    Dictionary<string, object> item;
    foreach (DataRow row in dt.Rows)
    {
            item = new Dictionary<string, object>();
                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();

score 1 · Answer 14 · answered May 26 '21 at 05:26

Building off of clamchoda's answer (which preserves data types) I have created a class for this which can be used like the following:

Convert DataTable to Json:

JsonDataTable j = JsonDataTable.FromDataTable(myDataTable);

Convert Json back to DataTable:

DataTable myDataTable = j.ToDataTable();

public class JsonDataTable
    {
        public string Schema { get; set; }
        public string Table { get; set; }

        public static JsonDataTable FromDataTable(DataTable dt)
        {
            JsonDataTable j = new JsonDataTable();
            var columns = dt.Columns.Cast<DataColumn>().Select(c => new { DataPropertyName = c.ColumnName, DataPropertyType = c.DataType.ToString() });
            j.Schema = JsonConvert.SerializeObject(columns);
            j.Table = JsonConvert.SerializeObject(dt);
            return j;
        }


        public DataTable ToDataTable()
        {
            DataTable dt = new DataTable();

            JsonConvert.DeserializeObject<List<dynamic>>(Schema).ForEach(prop =>
            {
                dt.Columns.Add(new DataColumn() { ColumnName = prop.DataPropertyName, DataType = Type.GetType(prop.DataPropertyType.ToString()) });
            });

            dt.Merge(JsonConvert.DeserializeObject<DataTable>(Table), true, MissingSchemaAction.Ignore);

            return dt;
        }

    }

score 0 · Answer 15 · answered Feb 03 '17 at 06:52

public static string ConvertIntoJson(DataTable dt)
{
    var jsonString = new StringBuilder();
    if (dt.Rows.Count > 0)
    {
        jsonString.Append("[");
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            jsonString.Append("{");
            for (int j = 0; j < dt.Columns.Count; j++)
                jsonString.Append("\"" + dt.Columns[j].ColumnName + "\":\"" 
                    + dt.Rows[i][j].ToString().Replace('"','\"') + (j < dt.Columns.Count - 1 ? "\"," : "\""));

            jsonString.Append(i < 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 < ds.Tables.Count; i++)
    {
        jsonString.Append("\"" + ds.Tables[i].TableName + "\":");
        jsonString.Append(ConvertIntoJson(ds.Tables[i]));
        if (i < ds.Tables.Count - 1)
            jsonString.Append(",");
    }
    jsonString.Append("}");
    return jsonString.ToString();
}

Heta77 · Answer 16 · 2017-04-03T07:53:25.630

//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<string> ColumnNames { get; set; }
    public List<string> DataTypes { get; set; }
    public List<Object> 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<string>();
            transfer.ColumnNames = new List<string>();                
            foreach (DataColumn c in dt.Columns)
            {
                transfer.ColumnNames.Add(c.ColumnName);
                transfer.DataTypes.Add(c.DataType.ToString());
            }

            transfer.Data = new List<object>();
            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 < 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 && o != null && 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<transferDataTable>(Response);
        if (transfer.Error == null)
        {
            DataTable dt = transfer.GetDataTable();
            dbGrid.ItemsSource = dt.DefaultView;
        }
        else
            MessageBox.Show(transfer.Error.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);

If You need send generic SQL request trought WebService API, You may use my solution. — Heta77, Apr 03 '17 at 08:20
If You need send generic SQL request trought WebService API, You may use my solution. class transferDataTable is seriazible object. This object will by send between client and server. Method LoadData (on Server) fill object transferDataTable with DataTable. Value DBNull.Value cannot be transfered throught web.api, then I replaced this value with null). After deserilaziton on Client null value will by replaced DBNull.Value. Method GetDataTable on Client restored DataTable sended by rest api. If come Error, then client show message. — Heta77, Apr 03 '17 at 08:28

score 0 · Answer 17 · answered Apr 12 '18 at 13:05

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

Umut Sürmeli · Answer 18 · 2020-01-13T19:58:56.557

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!="{"&&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);

Convert datatable to JSON in C#

18 Answers18

Linked

Related