将查询出来的数据集转化成JSON格式的C#类
jopen
10年前
using System; using System.Collections; using System.Collections.Generic; using System.Data; using System.Globalization; using System.Linq; using System.Reflection; using System.Text; using System.Web; namespace WikEasyUIDemo { /// <summary> /// JSON帮助类 /// </summary> public class JsonHelper { /// <summary> /// 生成表单编辑赋值 JSON格式 /// </summary> /// <param name="dt"></param> /// <param name="displayCount"></param> /// <returns></returns> public static string CreateJsonOne(DataTable dt, bool displayCount) { StringBuilder JsonString = new StringBuilder(); //Exception Handling if (dt != null && dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { JsonString.Append("{ "); for (int j = 0; j < dt.Columns.Count; j++) { if (j < dt.Columns.Count - 1) { JsonString.Append("ipt_" + dt.Columns[j].ColumnName.ToString().ToLower() + ":" + "\"" + dt.Rows[i][j].ToString() + "\","); } else if (j == dt.Columns.Count - 1) { JsonString.Append("ipt_" + dt.Columns[j].ColumnName.ToString().ToLower() + ":" + "\"" + dt.Rows[i][j].ToString() + "\""); } } if (i == dt.Rows.Count - 1) { JsonString.Append("} "); } else { JsonString.Append("}, "); } } return JsonString.ToString(); } else { return null; } } /// <summary> /// 将DataTable中的数据转换成JSON格式 /// </summary> /// <param name="dt">数据源DataTable</param> /// <param name="displayCount">是否输出数据总条数</param> /// <returns></returns> public static string CreateJsonParameters(DataTable dt, bool displayCount) { return CreateJsonParameters(dt, displayCount, dt.Rows.Count); } /// <summary> /// 将DataTable中的数据转换成JSON格式 /// </summary> /// <param name="dt">数据源DataTable</param> /// <returns></returns> public static string CreateJsonParameters(DataTable dt) { return CreateJsonParameters(dt, true); } /// <summary> /// 将DataTable中的数据转换成JSON格式 /// </summary> /// <param name="dt">数据源DataTable</param> /// <param name="displayCount">是否输出数据总条数</param> /// <param name="totalcount">JSON中显示的数据总条数</param> /// <returns></returns> public static string CreateJsonParameters(DataTable dt, bool displayCount, int totalcount) { StringBuilder JsonString = new StringBuilder(); //Exception Handling if (dt != null) { JsonString.Append("{ "); JsonString.Append("\"rows\":[ "); for (int i = 0; i < dt.Rows.Count; i++) { JsonString.Append("{ "); for (int j = 0; j < dt.Columns.Count; j++) { if (j < dt.Columns.Count - 1) { //if (dt.Rows[i][j] == DBNull.Value) continue; if (dt.Columns[j].DataType == typeof(bool)) { JsonString.Append("\"JSON_" + dt.Columns[j].ColumnName.ToLower() + "\":" + dt.Rows[i][j].ToString().ToLower() + ","); } else if (dt.Columns[j].DataType == typeof(string)) { JsonString.Append("\"JSON_" + dt.Columns[j].ColumnName.ToLower() + "\":" + "\"" + dt.Rows[i][j].ToString().Replace("\"", "\\\"") + "\","); } else { JsonString.Append("\"JSON_" + dt.Columns[j].ColumnName.ToLower() + "\":" + "\"" + dt.Rows[i][j] + "\","); } } else if (j == dt.Columns.Count - 1) { //if (dt.Rows[i][j] == DBNull.Value) continue; if (dt.Columns[j].DataType == typeof(bool)) { JsonString.Append("\"JSON_" + dt.Columns[j].ColumnName.ToLower() + "\":" + dt.Rows[i][j].ToString().ToLower()); } else if (dt.Columns[j].DataType == typeof(string)) { JsonString.Append("\"JSON_" + dt.Columns[j].ColumnName.ToLower() + "\":" + "\"" + dt.Rows[i][j].ToString().Replace("\"", "\\\"") + "\""); } else { JsonString.Append("\"JSON_" + dt.Columns[j].ColumnName.ToLower() + "\":" + "\"" + dt.Rows[i][j] + "\""); } } } /*end Of String*/ if (i == dt.Rows.Count - 1) { JsonString.Append("} "); } else { JsonString.Append("}, "); } } JsonString.Append("]"); if (displayCount) { JsonString.Append(","); JsonString.Append("\"total\":"); JsonString.Append(totalcount); } JsonString.Append("}"); return JsonString.ToString().Replace("\n", ""); } else { return null; } } #region object 2 json private static void WriteDataRow(StringBuilder sb, DataRow row) { sb.Append("{"); foreach (DataColumn column in row.Table.Columns) { sb.AppendFormat("\"{0}\":", column.ColumnName); WriteValue(sb, row[column]); sb.Append(","); } // Remove the trailing comma. if (row.Table.Columns.Count > 0) { --sb.Length; } sb.Append("}"); } private static void WriteDataSet(StringBuilder sb, DataSet ds) { sb.Append("{\"Tables\":{"); foreach (DataTable table in ds.Tables) { sb.AppendFormat("\"{0}\":", table.TableName); WriteDataTable(sb, table); sb.Append(","); } // Remove the trailing comma. if (ds.Tables.Count > 0) { --sb.Length; } sb.Append("}}"); } private static void WriteDataTable(StringBuilder sb, DataTable table) { sb.Append("{\"Rows\":["); foreach (DataRow row in table.Rows) { WriteDataRow(sb, row); sb.Append(","); } // Remove the trailing comma. if (table.Rows.Count > 0) { --sb.Length; } sb.Append("]}"); } private static void WriteEnumerable(StringBuilder sb, IEnumerable e) { bool hasItems = false; sb.Append("["); foreach (object val in e) { WriteValue(sb, val); sb.Append(","); hasItems = true; } // Remove the trailing comma. if (hasItems) { --sb.Length; } sb.Append("]"); } private static void WriteHashtable(StringBuilder sb, IDictionary e) { bool hasItems = false; sb.Append("{"); foreach (string key in e.Keys) { sb.AppendFormat("\"{0}\":", key.ToLower()); WriteValue(sb, e[key]); sb.Append(","); hasItems = true; } // Remove the trailing comma. if (hasItems) { --sb.Length; } sb.Append("}"); } private static void WriteObject(StringBuilder sb, object o) { MemberInfo[] members = o.GetType().GetMembers(BindingFlags.Instance | BindingFlags.Public); sb.Append("{"); bool hasMembers = false; foreach (MemberInfo member in members) { bool hasValue = false; object val = null; if ((member.MemberType & MemberTypes.Field) == MemberTypes.Field) { FieldInfo field = (FieldInfo)member; val = field.GetValue(o); hasValue = true; } else if ((member.MemberType & MemberTypes.Property) == MemberTypes.Property) { PropertyInfo property = (PropertyInfo)member; if (property.CanRead && property.GetIndexParameters().Length == 0) { val = property.GetValue(o, null); hasValue = true; } } if (hasValue) { sb.Append("\""); sb.Append(member.Name); sb.Append("\":"); WriteValue(sb, val); sb.Append(","); hasMembers = true; } } if (hasMembers) { --sb.Length; } sb.Append("}"); } private static void WriteString(StringBuilder sb, IEnumerable s) { sb.Append("\""); foreach (char c in s) { switch (c) { case '\"': sb.Append("\\\""); break; case '\\': sb.Append("\\\\"); break; case '\b': sb.Append("\\b"); break; case '\f': sb.Append("\\f"); break; case '\n': sb.Append("\\n"); break; case '\r': sb.Append("\\r"); break; case '\t': sb.Append("\\t"); break; default: int i = c; if (i < 32 || i > 127) { sb.AppendFormat("\\u{0:X04}", i); } else { sb.Append(c); } break; } } sb.Append("\""); } public static void WriteValue(StringBuilder sb, object val) { if (val == null || val == DBNull.Value) { sb.Append("null"); } else if (val is string || val is Guid) { WriteString(sb, val.ToString()); } else if (val is bool) { sb.Append(val.ToString().ToLower()); } else if (val is double || val is float || val is long || val is int || val is short || val is byte || val is decimal) { sb.AppendFormat(CultureInfo.InvariantCulture.NumberFormat, "{0}", val); } else if (val.GetType().IsEnum) { sb.Append((int)val); } else if (val is DateTime) { sb.Append("new Date(\""); sb.Append(((DateTime)val).ToString("MMMM, d yyyy HH:mm:ss", new CultureInfo("en-US", false).DateTimeFormat)); sb.Append("\")"); } else if (val is DataSet) { WriteDataSet(sb, val as DataSet); } else if (val is DataTable) { WriteDataTable(sb, val as DataTable); } else if (val is DataRow) { WriteDataRow(sb, val as DataRow); } else if (val is Hashtable) { WriteHashtable(sb, val as Hashtable); } else if (val is IEnumerable) { WriteEnumerable(sb, val as IEnumerable); } else { WriteObject(sb, val); } } /// <summary> /// /// </summary> /// <param name="o"></param> /// <returns></returns> public static string Convert2Json(object o) { StringBuilder sb = new StringBuilder(); WriteValue(sb, o); return sb.ToString(); } #endregion } } 查询最终结果： { "rows":[ { "JSON_id":"2","JSON_cardnumber":"5353435","JSON_username":"xixi","JSON_sex":"","JSON_tel":"4535345","JSON_address":"","JSON_jifen":"50","JSON_time":"2013-7-16 8:50:37","JSON_showhouse":"","JSON_remark":""} ],"total":1}