Convert datatable to JSON in C#

问题:

1. I want to get records from database into a DataTable.
2. Then convert the DataTable into a JSON object.
3. 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?

回答1:

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

回答2:

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

回答3:

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\"))
 );

回答4:

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]\", \"\");
            }

回答5:

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.

回答6:

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

回答7:

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

回答8:

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

回答9:

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

回答10:

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

回答11:

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

回答12:

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

回答13:

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