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