using System; using System.Collections; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Web.Script.Serialization; using System.Data.Common; using Newtonsoft.Json; using Newtonsoft.Json.Converters; namespace MES.Common { public class JSONHelper { /// <summary> /// 将JSON转换为指定类型的对象 /// </summary> /// <typeparam name="T">对象类型</typeparam> /// <param name="json">json字符串</param> /// <returns></returns> public static T ConvertToObject<T>(string json) { var jsetting = new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore }; //jsetting.DefaultValueHandling = DefaultValueHandling.Include; return JsonConvert.DeserializeObject<T>(json, jsetting); } /// <summary> /// 类对像转换成json格式 /// </summary> /// <returns></returns> public static string ToJson(object t) { return new JavaScriptSerializer().Serialize(t); } /// <summary> /// 类对像转换成json格式 /// </summary> /// <returns></returns> public static string ToDateJson(object t) { IsoDateTimeConverter timeFormat = new IsoDateTimeConverter(); timeFormat.DateTimeFormat = "yyyy-MM-dd"; return JsonConvert.SerializeObject(t, Newtonsoft.Json.Formatting.Indented, timeFormat); } public static string ToPageJson(DataTable t, int startIndex, int endIndex) { IsoDateTimeConverter timeFormat = new IsoDateTimeConverter(); timeFormat.DateTimeFormat = "yyyy-MM-dd"; int rowCount = t.Rows.Count; DataRow[] jsonDT = t.Select("rn >= " + startIndex + " and rn <=" + endIndex); DataTable dt = t.Clone(); foreach (var item in jsonDT) { dt.ImportRow(item); } DataView dv = dt.DefaultView; dv.Sort = "REPORT_DATE desc"; DataTable dt2 = dv.ToTable(); string json = JsonConvert.SerializeObject(dt2, Newtonsoft.Json.Formatting.Indented, timeFormat); return "{\"total\": " + rowCount + ",\"rows\":" + json + "}"; } /// <summary> /// 类对像转换成json格式 /// </summary> /// <returns></returns> public static string ToDateTimeJson(object t) { IsoDateTimeConverter timeFormat = new IsoDateTimeConverter(); timeFormat.DateTimeFormat = "yyyy-MM-dd hh:mm:ss"; return JsonConvert.SerializeObject(t, Newtonsoft.Json.Formatting.Indented, timeFormat); } /// <summary> /// 类对像转换成json格式 /// </summary> /// <returns></returns> public static string To24DateTimeJson(object t) { IsoDateTimeConverter timeFormat = new IsoDateTimeConverter(); timeFormat.DateTimeFormat = "yyyy-MM-dd HH:mm:ss"; return JsonConvert.SerializeObject(t, Newtonsoft.Json.Formatting.Indented, timeFormat); } /// <summary> /// json格式转换 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="strJson"></param> /// <returns></returns> public static T FromJson<T>(string strJson) where T : class { return new JavaScriptSerializer().Deserialize<T>(strJson); } /// <summary> /// 获取树格式对象的JSON /// </summary> /// <param name="commandText">commandText</param> /// <param name="id">ID的字段名</param> /// <param name="pid">PID的字段名</param> /// <returns></returns> public static string GetArrayJSON(string commandText, string id, string pid) { var o = ArrayToTreeData(commandText, id, pid); return ToJson(o); } /// <summary> /// 获取树格式对象的JSON /// </summary> /// <param name="command">command</param> /// <param name="id">ID的字段名</param> /// <param name="pid">PID的字段名</param> /// <returns></returns> public static string GetArrayJSON(DbCommand command, string id, string pid) { var o = ArrayToTreeData(command, id, pid); return ToJson(o); } public static string GetArrayTreeAttrJSON(DbCommand command, string id, string pid) { var o = ArrayToTreeAttrData(command, id, pid); return ToJson(o); } /// <summary> /// 获取树格式对象的JSON /// </summary> /// <param name="dt">存放树数据的datatable</param> /// <param name="id">ID的字段名</param> /// <param name="pid">PID的字段名</param> /// <returns></returns> public static string GetArrayJSON(DataTable dt, string id, string pid) { var o = DatatableToTreeData(dt, id, pid); return ToJson(o); } /// <summary> /// 获取树格式对象的JSON /// </summary> /// <param name="list">线性数据</param> /// <param name="id">ID的字段名</param> /// <param name="pid">PID的字段名</param> /// <returns></returns> public static string GetArrayJSON(IList<Hashtable> list, string id, string pid) { var o = ArrayToTreeData(list, id, pid); return ToJson(o); } /// <summary> /// 获取树格式对象 /// </summary> /// <param name="command">command</param> /// <param name="id">id的字段名</param> /// <param name="pid">pid的字段名</param> /// <returns></returns> public static object ArrayToTreeData(DbCommand command, string id, string pid) { var reader = DbHelper.Db.ExecuteReader(command); var list = DbReaderToHash(reader); return JSONHelper.ArrayToTreeData(list, id, pid); } /// <summary> /// 获取树格式对象 /// </summary> /// <param name="command">command</param> /// <param name="id">id的字段名</param> /// <param name="pid">pid的字段名</param> /// <returns></returns> public static object ArrayToTreeAttrData(DbCommand command, string id, string pid) { var reader = DbHelper.Db.ExecuteReader(command); var list = DbReaderToHash(reader); return JSONHelper.ArrayToTreeAttrData(list, id, pid); } /// <summary> /// 获取树格式对象 /// </summary> /// <param name="commandText">sql</param> /// <param name="id">ID的字段名</param> /// <param name="pid">PID的字段名</param> /// <returns></returns> public static object ArrayToTreeData(string commandText, string id, string pid) { var reader = DbHelper.Db.ExecuteReader(commandText); var list = DbReaderToHash(reader); return JSONHelper.ArrayToTreeData(list, id, pid); } /// <summary> /// 获取树格式对象 /// </summary> /// <param name="list">线性数据</param> /// <param name="id">ID的字段名</param> /// <param name="pid">PID的字段名</param> /// <returns></returns> public static object ArrayToTreeData(IList<Hashtable> list, string id, string pid) { var h = new Hashtable(); //数据索引 var r = new List<Hashtable>(); //数据池,要返回的 foreach (var item in list) { if (!item.ContainsKey(id)) continue; h[item[id].ToString()] = item; } foreach (var item in list) { if (!item.ContainsKey(id)) continue; if (!item.ContainsKey(pid) || item[pid] == null || !h.ContainsKey(item[pid].ToString())) { r.Add(item); } else { var pitem = h[item[pid].ToString()] as Hashtable; if (!pitem.ContainsKey("children")) pitem["children"] = new List<Hashtable>(); var children = pitem["children"] as List<Hashtable>; children.Add(item); } } return r; } /// <summary> /// 获取树格式对象 /// </summary> /// <param name="list">线性数据</param> /// <param name="id">ID的字段名</param> /// <param name="pid">PID的字段名</param> /// <returns></returns> public static object ArrayToTreeAttrData(IList<Hashtable> list, string id, string pid) { IList<Hashtable> listNew = new List<Hashtable>(); foreach (var item in list) { var h1 = new Hashtable(); var h2 = new Hashtable(); foreach (var i in item.Keys) { if (i.ToString() == "ID" || i.ToString() == "TEXT") { h1[i] = item[i]; } else if (i.ToString() == pid || i.ToString() == id) { h1[i] = item[i]; h2[i] = item[i]; } else if (i.ToString() == "ISCHECK") { h1[i] = item[i].ToString() == "1" ? true : false; } else { h2[i] = item[i]; } } if (h2.Count > 0) { h1["attributes"] = h2; } listNew.Add(h1); } var h = new Hashtable(); //数据索引 var r = new List<Hashtable>(); //数据池,要返回的 foreach (var item in listNew) { if (!item.ContainsKey(id)) continue; h[item[id].ToString()] = item; } foreach (var item in listNew) { if (!item.ContainsKey(id)) continue; if (!item.ContainsKey(pid) || item[pid] == null || !h.ContainsKey(item[pid].ToString())) { r.Add(item); //去除根节点的checked属性 item.Remove("ISCHECK"); } else { var pitem = h[item[pid].ToString()] as Hashtable; if (!pitem.ContainsKey("children")) pitem["children"] = new List<Hashtable>(); var children = pitem["children"] as List<Hashtable>; children.Add(item); //如果是父节点,则去除checked属性 pitem.Remove("ISCHECK"); } } return r; } /// <summary> /// 执行SQL 返回json /// </summary> /// <param name="command"></param> /// <returns></returns> public static string ExecuteCommandToJSON(DbCommand command) { var o = ExecuteReaderToHash(command); return ToJson(o); } /// <summary> /// 执行SQL 返回json /// </summary> /// <param name="commandText"></param> /// <returns></returns> public static string ExecuteCommandToJSON(string commandText) { var o = ExecuteReaderToHash(commandText); return ToJson(o); } /// <summary> /// 将db reader转换为Hashtable列表 /// </summary> /// <param name="commandText"></param> /// <returns></returns> public static List<Hashtable> ExecuteReaderToHash(string commandText) { var reader = DbHelper.Db.ExecuteReader(commandText); return DbReaderToHash(reader); } /// <summary> /// 将db reader转换为Hashtable列表 /// </summary> /// <param name="command"></param> /// <returns></returns> public static List<Hashtable> ExecuteReaderToHash(DbCommand command) { var reader = DbHelper.Db.ExecuteReader(command); return DbReaderToHash(reader); } /// <summary> /// 将db reader转换为Hashtable列表 /// </summary> /// <param name="reader"></param> /// <returns></returns> private static List<Hashtable> DbReaderToHash(IDataReader reader) { var list = new List<Hashtable>(); while (reader.Read()) { var item = new Hashtable(); for (var i = 0; i < reader.FieldCount; i++) { var name = reader.GetName(i).ToUpper(); var value = reader[i]; item[name] = value; } list.Add(item); } return list; } /// <summary> /// DataTable转树对象格式 /// </summary> /// <param name="commandText">sql</param> /// <param name="id">ID的字段名</param> /// <param name="pid">PID的字段名</param> /// <returns></returns> public static object DatatableToTreeData(DataTable dtConvert, string id, string pid) { var list = DatatableToHash(dtConvert); return JSONHelper.ArrayToTreeData(list, id, pid); } /// <summary> /// 将datatable转换为Hashtable列表 /// </summary> /// <param name="dtConvert"></param> /// <returns></returns> private static List<Hashtable> DatatableToHash(DataTable dtConvert) { var list = new List<Hashtable>(); int j; foreach (DataRow dr in dtConvert.Rows) { var item = new Hashtable(); j = 0; foreach (DataColumn dc in dtConvert.Columns) { var name = dc.ColumnName; var value = dr[j]; item[name] = value; j++; } list.Add(item); } return list; } #region Datatable转换为Json /// <summary> /// Datatable转换为Json /// </summary> public static string ToJson(DataTable dt) { if (dt.Rows.Count > 0) { StringBuilder jsonString = new StringBuilder(); jsonString.Append("["); DataRowCollection drc = dt.Rows; for (int i = 0; i < drc.Count; i++) { jsonString.Append("{"); for (int j = 0; j < dt.Columns.Count; j++) { string strKey = dt.Columns[j].ColumnName; string strValue = drc[i][j].ToString(); Type type = dt.Columns[j].DataType; jsonString.Append("\"" + strKey + "\":"); strValue = StringFormat(strValue, type); if (j < dt.Columns.Count - 1) jsonString.Append(strValue + ","); else jsonString.Append(strValue); } jsonString.Append("},"); } jsonString.Remove(jsonString.Length - 1, 1); jsonString.Append("]"); return jsonString.ToString(); } else return "[]"; } /// <summary> /// DataTable转换为Json /// </summary> public static string ToJson(DataTable dt, string jsonName) { StringBuilder Json = new StringBuilder(); if (string.IsNullOrEmpty(jsonName)) jsonName = dt.TableName; Json.Append("{\"" + jsonName + "\":["); if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { Json.Append("{"); for (int j = 0; j < dt.Columns.Count; j++) { Type type = dt.Rows[i][j].GetType(); Json.Append("\"" + dt.Columns[j].ColumnName.ToString() + "\":" + StringFormat(dt.Rows[i][j].ToString(), type)); if (j < dt.Columns.Count - 1) Json.Append(","); } Json.Append("}"); if (i < dt.Rows.Count - 1) Json.Append(","); } } Json.Append("]}"); return Json.ToString(); } #endregion #region DataSet转换成Json格式 /// <summary> /// DataSet转换成Json格式 /// </summary> /// <param name="ds">DataSet</param> /// <returns></returns> public static string Dataset2Json(DataSet ds, int total = -1) { StringBuilder json = new StringBuilder(); if (ds != null) { foreach (DataTable dt in ds.Tables) { json.Append("{\"total\":"); if (total == -1) { json.Append(dt.Rows.Count); } else { json.Append(total); } json.Append(",\"rows\":["); json.Append(DataTable2Json(dt)); json.Append("]}"); } } else { json.Append("{\"total\":"); json.Append("0"); json.Append(",\"rows\":["); json.Append("]}"); } return json.ToString(); } #endregion #region dataTable转换成Json格式 /// <summary> /// dataTable转换成Json格式 /// </summary> /// <param name="dt"></param> /// <returns></returns> public static string DataTable2Json(DataTable dt) { StringBuilder jsonBuilder = new StringBuilder(); for (int i = 0; i < dt.Rows.Count; i++) { jsonBuilder.Append("{"); for (int j = 0; j < dt.Columns.Count; j++) { jsonBuilder.Append("\""); jsonBuilder.Append(dt.Columns[j].ColumnName); jsonBuilder.Append("\":\""); jsonBuilder.Append(dt.Rows[i][j].ToString()); jsonBuilder.Append("\","); } if (dt.Columns.Count > 0) { jsonBuilder.Remove(jsonBuilder.Length - 1, 1); } jsonBuilder.Append("},"); } if (dt.Rows.Count > 0) { jsonBuilder.Remove(jsonBuilder.Length - 1, 1); } return jsonBuilder.ToString(); } #endregion dataTable转换成Json格式 #region treegrid分页 /// <summary> /// DataSet转换成Json格式 /// </summary> /// <param name="ds">DataSet</param> /// <returns></returns> public static string DatasetToTreeGridJson(DataSet ds, int total = -1) { StringBuilder json = new StringBuilder(); if (ds != null) { foreach (DataTable dt in ds.Tables) { json.Append("{\"total\":"); if (total == -1) { json.Append(dt.Rows.Count); } else { json.Append(total); } json.Append(",\"rows\":["); json.Append(DataTableToTreegridJson(dt)); json.Append("]}"); } } else { json.Append("{\"total\":"); json.Append("0"); json.Append(",\"rows\":["); json.Append("]}"); } return json.ToString(); } /// <summary> /// 根据父级Id找到对应所有子节点数据 /// </summary> /// <param name="pid">父级id</param> /// <param name="categoryid">分类id</param> /// <returns></returns> public static DataTable GetDataFromParentId(string pid, string categoryid) { StringBuilder sql = new StringBuilder(); sql.Append(" SELECT *"); sql.Append(" FROM A_DICS T"); sql.AppendFormat(" START WITH T.PARENTID ='{0}'", pid); sql.AppendFormat(" AND CATEGORYID = {0}", categoryid); sql.Append(" CONNECT BY PRIOR T.KEYID = T.PARENTID"); DataSet ds = DbHelper.Db.Db.ExecuteDataSet(CommandType.Text, sql.ToString()); return ds.Tables[0]; } /// <summary> /// 将datatable转成treegrid分页形式的json /// </summary> /// <param name="dt"></param> /// <returns></returns> public static string DataTableToTreegridJson(DataTable dt) { StringBuilder jsonBuilder = new StringBuilder(); DataTable dtChildren = null; for (int i = 0; i < dt.Rows.Count; i++) { //根据父节点查询出对应子节点的数据 dtChildren = GetDataFromParentId(dt.Rows[i]["KEYID"].ToString(), dt.Rows[i]["CATEGORYID"].ToString()); //父节点开始 jsonBuilder.Append("{"); for (int j = 0; j < dt.Columns.Count; j++) { if (dt.Columns[j].ColumnName.ToLower() == "keyid") { jsonBuilder.Append("\""); jsonBuilder.Append("id"); jsonBuilder.Append("\":\""); jsonBuilder.Append(dt.Rows[i][j].ToString()); jsonBuilder.Append("\","); } else if (dt.Rows[i][j].ToString().IndexOf("\\") >= 0) { jsonBuilder.Append("\""); jsonBuilder.Append(dt.Columns[j].ColumnName); jsonBuilder.Append("\":\""); jsonBuilder.Append(dt.Rows[i][j].ToString().Replace("\\", "\\\\")); jsonBuilder.Append("\","); } else { jsonBuilder.Append("\""); jsonBuilder.Append(dt.Columns[j].ColumnName); jsonBuilder.Append("\":\""); jsonBuilder.Append(dt.Rows[i][j].ToString()); jsonBuilder.Append("\","); } } if (dt.Columns.Count > 0) { jsonBuilder.Remove(jsonBuilder.Length - 1, 1); } jsonBuilder.Append("},"); //循环添加子节点数据 for (int k = 0; k < dtChildren.Rows.Count; k++) { jsonBuilder.Append("{"); for (int l = 0; l < dtChildren.Columns.Count; l++) { jsonBuilder.Append("\""); if (dtChildren.Columns[l].ColumnName.ToLower() == "keyid") { jsonBuilder.Append("id"); jsonBuilder.Append("\":\""); jsonBuilder.Append(dtChildren.Rows[k][l].ToString()); jsonBuilder.Append("\","); } else if (dtChildren.Columns[l].ColumnName.ToLower() == "parentid" && dtChildren.Rows[k][l].ToString() != "0") { jsonBuilder.Append("_parentId"); jsonBuilder.Append("\":\""); jsonBuilder.Append(dtChildren.Rows[k][l].ToString()); jsonBuilder.Append("\","); } else if (dtChildren.Rows[k][l].ToString().IndexOf("\\") >= 0) { jsonBuilder.Append(dtChildren.Columns[l].ColumnName); jsonBuilder.Append("\":\""); jsonBuilder.Append(dtChildren.Rows[k][l].ToString().Replace("\\","\\\\")); jsonBuilder.Append("\","); } else { jsonBuilder.Append(dtChildren.Columns[l].ColumnName); jsonBuilder.Append("\":\""); jsonBuilder.Append(dtChildren.Rows[k][l].ToString()); jsonBuilder.Append("\","); } } if (dt.Columns.Count > 0) { jsonBuilder.Remove(jsonBuilder.Length - 1, 1); } jsonBuilder.Append("},"); } } if (dt.Rows.Count > 0) { jsonBuilder.Remove(jsonBuilder.Length - 1, 1); } return jsonBuilder.ToString(); } #endregion /// <summary> /// 格式化字符型、日期型、布尔型 /// </summary> public static string StringFormat(string str, Type type) { if (type == typeof(string)) { str = StringFilter(str); str = "\"" + str + "\""; } else if (type == typeof(DateTime) || type == typeof(DateTime?)) { str = "\"" + str + "\""; } else if (type == typeof(bool)) { str = str.ToLower(); } else if (type == typeof(Guid)) { str = "\"" + str + "\""; } else if (type != typeof(string) && string.IsNullOrEmpty(str)) { str = "\"" + str + "\""; } return str; } /// <summary> /// 过滤字符串 /// </summary> public static string StringFilter(string str) { StringBuilder sb = new StringBuilder(); for (int i = 0; i < str.Length; i++) { char c = str.ToCharArray()[i]; switch (c) { case '\"': sb.Append("\\\""); break; 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: sb.Append(c); break; } } return sb.ToString(); } } }