First of all, we have to fetch the records from the database (MS Sqlserver) into the C# DataTable, or we can add dynamic rows to our DataTable. So our code looks like as written below.
//*
public DataTable getData()
{
DataTable dt = new DataTable();
dt.Columns.Add("UserId", typeof(Int32));
dt.Columns.Add("UserName", typeof(string));
dt.Columns.Add("Education", typeof(string));
dt.Columns.Add("Location", typeof(string));
dt.Rows.Add(1, "Satinder Singh", "Bsc Com Sci", "Mumbai");
dt.Rows.Add(2, "Amit Sarna", "Mstr Com Sci", "Mumbai");
dt.Rows.Add(3, "Andrea Ely", "Bsc Bio-Chemistry", "Queensland");
dt.Rows.Add(4, "Leslie Mac", "MSC", "Town-ville");
dt.Rows.Add(5, "Vaibhav Adhyapak", "MBA", "New Delhi");
dt.Rows.Add(6, "Johny Dave", "MCA", "Texas");
return dt;
}
方法一：Fetch each data (value), and append to our jsonString StringBuilder. This is how our code looks like
public string DataTableToJsonWithStringBuilder(DataTable table)
{
var jsonString = new StringBuilder();
if (table.Rows.Count > 0)
{
jsonString.Append("[");
for (int i = 0; i < table.Rows.Count; i++)
{
jsonString.Append("{");
for (int j = 0; j < table.Columns.Count; j++)
{
if (j < table.Columns.Count - 1)
{
jsonString.Append("\"" + table.Columns[j].ColumnName.ToString()
+ "\":" + "\""
+ table.Rows[i][j].ToString() + "\",");
}
else if (j == table.Columns.Count - 1)
{
jsonString.Append("\"" + table.Columns[j].ColumnName.ToString()
+ "\":" + "\""
+ table.Rows[i][j].ToString() + "\"");
}
}
if (i == table.Rows.Count - 1)
{
jsonString.Append("}");
}
else
{
jsonString.Append("},");
}
}
jsonString.Append("]");
}
return jsonString.ToString();
}
//*
方法 2: Convert DataTable to JSON using JavaScriptSerializer:
public string DataTableToJsonWithJavaScriptSerializer(DataTable table)
{
JavaScriptSerializer jsSerializer = new JavaScriptSerializer();
List<Dictionary<string, object>> parentRow = new List<Dictionary<string, object>>();
Dictionary<string, object> childRow;
foreach (DataRow row in table.Rows)
{
childRow = new Dictionary<string, object>();
foreach (DataColumn col in table.Columns)
{
childRow.Add(col.ColumnName, row[col]);
}
parentRow.Add(childRow);
}
return jsSerializer.Serialize(parentRow);
}
方法3（推薦）: Convert DataTable to JSON using Json.Net DLL (Newtonsoft):
public string DataTableToJsonWithJsonNet(DataTable table)
{
string jsonString=string.Empty;
jsonString = JsonConvert.SerializeObject(table);
return jsonString;
}