json格式数据分析工具PageElement类分享(仿Session写法)

测试例:

PageElement pe = new PageElement();
pe.LoadDataFromJsonString("[{\"A\":\"123\",\"B\":\"abc\"}]");
Console.WriteLine(pe["A"]); --输出123
pe["B"]=1000;
Console.WriteLine(pe["B"]); --输出1000

pe.DATATABLE:获取数据的 DataTable 形式
pe.ToInsertSQL 转SQL INSERT语句
pe.ToUpdateSQL 转SQL UPDATE语句

namespace MyLib.ITSM.Base
{
    using System;
    using System.Collections.Generic;
    using System.Xml;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;

    public class PageElement : IDictionary<string, object>
    {
        public List<KeyValuePair<string, object>> list = new List<KeyValuePair<string, object>>();

        public PageElement() { }

        public PageElement(string XmlString)
        {
            LoadElementFromXMLString(XmlString);
        }

        /// <summary>
        /// JSON属性
        /// </summary>
        public string JSON
        {
            get
            {
                if (list == null || list.Count == 0) return string.Empty;
                {
                    string jsonstr = "{";
                    foreach (KeyValuePair<string, object> p in list)
                    {
                        jsonstr += p.Key + ":" + "\"" + p.Value.ToString() + "\",";
                    }
                    jsonstr = jsonstr.Substring(0, jsonstr.Length - 1);//去除最后一个逗号
                    jsonstr += "}";
                    return jsonstr;
                }
            }
        }

        /// <summary>
        /// 是否已加载数据
        /// </summary>
        private bool _isloaded = false;
        public bool IsLoaded
        {
            get
            {
                return _isloaded;
            }
        }

        #region IDictionary<string,object> 成员

        void IDictionary<string, object>.Add(string key, object value)
        {
            //key已存在,则不添加
            foreach (KeyValuePair<string, object> k in list)
            {
                if (k.Key == key.Trim() || k.Key.ToLowerInvariant() == key.ToLowerInvariant().Trim())
                {
                    return;
                }
            }
            //向List中添加
            list.Add(new KeyValuePair<string, object>(key, value));
        }

        public bool ContainsKey(string key)
        {
            foreach (KeyValuePair<string, object> k in list)
            {
                if (k.Key == key.Trim() || k.Key.ToLowerInvariant() == key.ToLowerInvariant().Trim())
                {
                    return true;
                }
            }
            return false;
        }

        public ICollection<string> Keys
        {
            get
            {
                string[] ks = new string[list.Count];
                for (int i = 0; i < list.Count; i++)
                {
                    ks[i] = list[i].Key;
                }
                return ks;
            }
        }

        public bool Remove(string key)
        {
            foreach (KeyValuePair<string, object> k in list)
            {
                if (k.Key == key.Trim() || k.Key.ToLowerInvariant() == key.ToLowerInvariant().Trim())
                {
                    list.Remove(k);
                    return true;
                }
            }
            return false;
        }

        public bool TryGetValue(string key, out object value)
        {
            foreach (KeyValuePair<string, object> k in list)
            {
                if (k.Key == key.Trim() || k.Key.ToLowerInvariant() == key.ToLowerInvariant().Trim())
                {
                    value = k.Value;
                    return true;
                }
            }
            value = string.Empty;
            return false;
        }

        public ICollection<object> Values
        {
            get
            {
                object[] vs = new object[list.Count];
                for (int i = 0; i < list.Count; i++)
                {
                    vs[i] = list[i].Value;
                }
                return vs;
            }
        }

        public object this[string key]
        {
            get
            {
                foreach (KeyValuePair<string, object> k in list)
                {
                    if (k.Key == key.Trim() || k.Key.ToLowerInvariant() == key.ToLowerInvariant().Trim())
                    {
                        return k.Value;
                    }
                }
                return null;
            }
            set
            {
                foreach (KeyValuePair<string, object> k in list)
                {
                    if (k.Key == key.Trim() || k.Key.ToLowerInvariant() == key.ToLowerInvariant().Trim())
                    {
                        list.Remove(k);//删除原节点
                        break;
                    }
                }
                KeyValuePair<string, object> knew = new KeyValuePair<string, object>(key, value);
                list.Add(knew);
            }
        }

        public object this[int index]
        {
            get
            {
                if (index <= list.Count)
                {
                    return list[index].Value;
                }
                return null;
            }
            set
            {
                string key;
                if (index <= list.Count)
                {
                    key = list[index].Key.ToString();
                    list.RemoveAt(index);
                    KeyValuePair<string, object> knew = new KeyValuePair<string, object>(key, value);
                    list.Insert(index, knew);
                }
            }
        }

        #endregion

        #region ICollection<KeyValuePair<string,string>> 成员

        public void Add(KeyValuePair<string, object> item)
        {
            throw new NotImplementedException();
        }

        public void Clear()
        {
            list = new List<KeyValuePair<string, object>>();
        }

        public bool Contains(KeyValuePair<string, object> item)
        {
            foreach (KeyValuePair<string, object> k in list)
            {
                if (k.Key == item.Key)
                {
                    return true;
                }
            }
            return false;
        }

        public void CopyTo(KeyValuePair<string, object>[] array, int arrayIndex)
        {
            throw new NotImplementedException();
        }

        public int Count
        {
            get { return list.Count; }
        }

        public bool IsReadOnly
        {
            get { throw new NotImplementedException(); }
        }

        public bool Remove(KeyValuePair<string, object> item)
        {
            foreach (KeyValuePair<string, object> k in list)
            {
                if (k.Key == item.Key)
                {
                    list.Remove(k);
                    return true;
                }
            }
            return false;
        }

        #endregion

        #region IEnumerable<KeyValuePair<string,string>> 成员

        public IEnumerator<KeyValuePair<string, object>> GetEnumerator()
        {
            return list.GetEnumerator();
        }

        #endregion

        #region IEnumerable 成员

        System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
        {
            return list.GetEnumerator();
        }

        #endregion

        public override string ToString()
        {
            return JSON;
        }

        /// <summary>
        /// 转为FieldValue值
        /// </summary>
        /// <returns></returns>
        public XmlDocument GetXmlObject()
        {
            //FieldValues fvs = new FieldValues();
            //foreach (KeyValuePair<string, object> p in list)
            //{
            //    fvs.Add(p.Key, p.Value.ToString());
            //}
            //return fvs.GetXmlObject();
            return null;
        }

        /// <summary>
        /// 从XML中载入页面元素数据
        /// </summary>
        /// <param name="xmlstr"></param>
        public void LoadElementFromXMLString(string xmlstr)
        {
            _isloaded = false;
            //try
            //{
            //    FieldValues fvs = new FieldValues(xmlstr);
            //    foreach (FieldValue fv in fvs)
            //    {
            //        this[fv.ID] = fv.Value;
            //    }
            //}
            //catch { return; }
            _isloaded = true;
        }

        /// <summary>
        /// 从DataTable中载入页面元素数据
        /// </summary>
        /// <param name="xmlstr"></param>
        public void LoadElementFromDataTable(DataTable dt)
        {
            _isloaded = false;
            try
            {
                if (dt != null)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        //遍历行
                        foreach (DataColumn dc in dt.Columns)
                        {
                            this[dc.ColumnName] = row[dc];
                        }
                    }
                }
            }
            catch { return; }
            _isloaded = true;
        }
        /// <summary>
        /// 从JSON中载入页面元素数据
        /// </summary>
        /// <param name="xmlstr"></param>
        public void LoadElementFromJSONString(string json)
        {
            _isloaded = false;
            try
            {
                List<string> jsList = GetFieldsString(json);
                //生成列
                foreach (string s in jsList)
                {
                    string[] keyvalueSeparator = { ":" };
                    string key = s.Substring(0, s.IndexOf(':')).Trim();
                    string value = s.Substring(s.IndexOf(':') + 1).Trim();
                    if (key.Trim().StartsWith("\"") && key.Trim().EndsWith("\""))
                    {
                        //去除多余的双引号
                        int end = key.Length - 2;
                        key = key.Substring(1, end);
                    }
                    if (value.Trim().StartsWith("\"") && value.Trim().EndsWith("\""))
                    {
                        //去除多余的双引号
                        int end = value.Length - 2;
                        value = value.Substring(1, end);
                        //PageElement类型的内容
                        if (value.StartsWith("{") && value.EndsWith("}"))
                        {
                            value = value.Replace("\\", string.Empty);//祛除多余转义符
                            PageElement peChild = new PageElement();
                            peChild.LoadElementFromJSONString(value);
                            this[key] = peChild;
                        }
                        else  //普通类型的内容解析
                        {
                            //若列值存在
                            this[key] = ConvertToGB(value);
                        }
                    }
                }
            }
            catch
            {
                return;
            }
            _isloaded = true;
        }

        /// <summary>  
        /// 把Unicode解码为普通文字  
        /// </summary>  
        /// <param name="unicodeString">要解码的Unicode字符集</param>  
        /// <returns>解码后的字符串</returns>  
        private string ConvertToGB(string unicodeString)
        {
            string[] strArray = unicodeString.Split(new string[] { @"\u" }, StringSplitOptions.None);
            string result = string.Empty;
            for (int i = 0; i < strArray.Length; i++)
            {
                if (strArray[i].Trim() == "" || strArray[i].Length < 2 || strArray.Length <= 1)
                {
                    result += i == 0 ? strArray[i] : @"\u" + strArray[i];
                    continue;
                }
                for (int j = strArray[i].Length > 4 ? 4 : strArray[i].Length; j >= 2; j--)
                {
                    try
                    {
                        result += char.ConvertFromUtf32(Convert.ToInt32(strArray[i].Substring(0, j), 16)) + strArray[i].Substring(j);
                        break;
                    }
                    catch
                    {
                        continue;
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// 获取字段Json字符串
        /// </summary>
        /// <param name="json"></param>
        /// <returns></returns>
        private List<string> GetFieldsString(string jsonS)
        {
            List<string> retfieldsstring = new List<string>();
            if (jsonS == string.Empty)
                return retfieldsstring;
            string json = jsonS.Trim();
            //祛除首尾
            if (json.StartsWith("[") && json.EndsWith("]"))
            {
                int length = json.Length - 2;
                json = json.Substring(1, length);
            }

            //是json格式的字串,以{开头,以}结尾
            if (json.StartsWith("{") && json.EndsWith("}"))
            {
                int jsonlength = json.Length - 1;
                string str = json.Substring(1, jsonlength - 1) + ",";
                //祛除头尾的"{","}"
                int startPos = 0; //搜索开始的位置指针
                int length = 0;  //搜索结束的位置指针
                int flagcount = 0; //对象开始字符的个数,根据此个数排除结束标志
                //遍历得到内部字符串
                while (startPos + length < str.Length)  //未搜索完成，则继续搜索
                {
                    if (str[startPos + length] == '{')
                    {
                        flagcount += 1;
                    }
                    else if (str[startPos + length] == '}')
                    {
                        if (flagcount > 0)  //若开始字符的个数不等于0，则字符中间存在对象，应将标志位减1并且排除
                        {
                            flagcount -= 1;
                        }
                    }
                    else if (str[startPos + length] == ',')
                    {
                        if (flagcount == 0)
                        {
                            retfieldsstring.Add(str.Substring(startPos, length));
                            startPos = startPos + length + 1;//新的起始位置
                            length = 0; //新的截取长度
                        }
                    }
                    length += 1; //末尾指针加1,进入下一次循环的搜索
                }
                return retfieldsstring;
            }
            return retfieldsstring;
        }

        /// <summary>
        /// 转为Sql Insert 语句
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public string ToInsertSQL(string TableName)
        {
            string sql = @"INSERT INTO " + TableName + "(";
            string fields = string.Empty;
            string values = string.Empty;
            foreach (KeyValuePair<string, object> p in list)
            {
                fields += p.Key + ",";
                //values += StringTool.SqlQ(p.Value.ToString()) + ",";
            }
            fields = fields.Substring(0, fields.Length - 1);//去除最后一个逗号
            values = values.Substring(0, values.Length - 1);//去除最后一个逗号
            sql += fields + ") VALUES (" + values + ")";
            return sql;
        }

        /// <summary>
        /// 转为Sql Update 语句
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public string ToUpdateSQL(string TableName, string wherefield)
        {
            string sql = @"UPDATE " + TableName + " Set ";
            foreach (KeyValuePair<string, object> p in list)
            {
                //sql += p.Key + " = " + StringTool.SqlQ(p.Value.ToString()) + ",";
            }
            sql = sql.Substring(0, sql.Length - 1);//去除最后一个逗号
            //sql += " WHERE " + wherefield + " = " + StringTool.SqlQ(this[wherefield].ToString());
            return sql;
        }

        /// <summary>
        /// 转为Sql 查询 语句
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public object[] ToWhereSQL()
        {
            object[] o = new object[2];
            string sql = @" where 1=1 ";
            DbParameter[] dbp = new DbParameter[list.Count];
            int index = 0;
            foreach (KeyValuePair<string, object> f in list)
            {
                if (f.Value is string)
                {
                    if (!f.Key.Contains("#"))
                    {
                        sql += " and " + f.Key + " like '％'+@" + f.Key + "+'％'";
                    }
                    else
                    {
                        string op = f.Key.Split('#')[1].ToString();
                        if (op.Trim() == "L") //前半部相配
                        {
                            sql += " and " + f.Key.Split('#')[0] + " like '％'+@" + f.Key + "";
                        }
                        else if (op.Trim() == "R") //后半部相配
                        {
                            sql += " and " + f.Key.Split('#')[0] + " like  @" + f.Key + "+'％'";
                        }
                        else if (op.Trim() == "E")  //字符串相等
                        {
                            sql += " and " + f.Key.Split('#')[0] + " = @" + f.Key;
                        }
                    }
                }
                if (f.Value is int || f.Value is decimal || f.Value is double)
                {
                    if (!f.Key.Contains("#")) //无条件,直接带入
                    {
                        sql += " and " + f.Key + " = @" + f.Key;
                    }
                    else
                    {
                        string op = f.Key.Split('#')[1].ToString();
                        if (op.Trim() == "G") //大于
                        {
                            sql += " and " + f.Key.Split('#')[0] + " > @" + f.Key;
                        }
                        else if (op.Trim() == "L") //小于
                        {
                            sql += " and " + f.Key.Split('#')[0] + " < @" + f.Key;
                        }
                        else if (op.Trim() == "NE") //不等于
                        {
                            sql += " and " + f.Key.Split('#')[0] + " <> @" + f.Key;
                        }
                        else if (op.Trim() == "GE") //大于等于
                        {
                            sql += " and " + f.Key.Split('#')[0] + " >= @" + f.Key;
                        }
                        else if (op.Trim() == "LE") //小于等于
                        {
                            sql += " and " + f.Key.Split('#')[0] + " <= @" + f.Key;
                        }
                        else if (op.Trim() == "E")
                        {
                            sql += " or " + f.Key.Split('#')[0] + " = @" + f.Key;
                        }
                    }
                }
                SqlParameter sp = new SqlParameter(f.Key, f.Value);
                dbp[index] = sp;
                index += 1;
            }
            o[0] = sql;
            o[1] = dbp;
            return o;
        }

    }
}