From DataTable in C# .NET to JSON

Although the JavaScriptSerializer (System.Web.Script.Serialization.JavaScriptSerializer) cannot convert a DataTable directly into JSON, it is possible to unpack a DataTable into a List that may then be serialized.

The following function converts an arbitrary DataTable into a JSON string (without prior knowledge about field names or data types):

public static string 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] = row[col];        }        list.Add(dict);    }    JavaScriptSerializer serializer = new JavaScriptSerializer();    return serializer.Serialize(list);}

You could use the JSON.NET library: http://json.codeplex.com/ to serialize/deserialize the DataTable.

string json = JsonConvert.SerializeObject(table);

which serializes to something like this:

[ { "Column1": "Row Value", "Column2": "2" } ]

If you need to serialize more info about the DataTable e.g. column schema, primary key, table name then you could use the custom converter I wrote: https://github.com/chris-herring/DataTableConverter. Use it like this:

string json = JsonConvert.SerializeObject(table, new Serialization.DataTableConverter());DataTable table = JsonConvert.DeserializeObject<DataTable>(json, new Serialization.DataTableConverter());

which serializes to something like this:

{    "TableName": "TestTable",    "Columns": [        {            "AllowDBNull": false,            "AutoIncrement": true,            "AutoIncrementSeed": 2,            "AutoIncrementStep": 1,            "Caption": "PrimaryKey",            "ColumnName": "PrimaryKey",            "DataType": "Int32",            "DateTimeMode": "UnspecifiedLocal",            "DefaultValue": null,            "MaxLength": -1,            "Ordinal": 0,            "ReadOnly": false,            "Unique": true        }    ],    "Rows": [        [            1        ],        [            2        ],        [            3        ]    ],    "PrimaryKey": ["PrimaryKey"]}

Instead of a datatable you should use a datareader. Your code is inefficient and somewhat hard to read - you may want to do something like this:

StringBuilder json = new StringBuilder();using(SqlConnection cnn = new SqlConnection(your_connection_string)) {    cnn.open();    using(SqlCommand cmd = new SqlCommand("name_of_stored_procedure", cnn))     {        cmd.Paramters.AddWithValue("@Param", "value");        using(SqlDataReader reader = cmd.ExecuteReader())         {            while(reader.Read())             {                json.AppendFormat("{{\"name\": \"{0}\"}}", reader["name"]);            }        }    }    cnn.close();} 

you can then use json.ToString to get the outpt