json format data analysis tool PageElement class share of copy Session
- 2020-05-27 07:00:52
- OfStack
Test cases:
PageElement pe = new PageElement();
pe.LoadDataFromJsonString("[{\"A\":\"123\",\"B\":\"abc\"}]");
Console.WriteLine(pe["A"]); -- The output 123
pe["B"]=1000;
Console.WriteLine(pe["B"]); -- The output 1000
pe.DATATABLE: data-obtaining DataTable In the form of
pe.ToInsertSQL turn SQL INSERT statements
pe.ToUpdateSQL turn SQL UPDATE statements
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 attribute
/// </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);// Remove the last 1 A comma
jsonstr += "}";
return jsonstr;
}
}
}
/// <summary>
/// Whether the data has been loaded
/// </summary>
private bool _isloaded = false;
public bool IsLoaded
{
get
{
return _isloaded;
}
}
#region IDictionary<string,object> Members of the
void IDictionary<string, object>.Add(string key, object value)
{
//key existing , Do not add
foreach (KeyValuePair<string, object> k in list)
{
if (k.Key == key.Trim() || k.Key.ToLowerInvariant() == key.ToLowerInvariant().Trim())
{
return;
}
}
// to List add
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);// Delete the original node
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>> Members of the
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>> Members of the
public IEnumerator<KeyValuePair<string, object>> GetEnumerator()
{
return list.GetEnumerator();
}
#endregion
#region IEnumerable Members of the
System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
{
return list.GetEnumerator();
}
#endregion
public override string ToString()
{
return JSON;
}
/// <summary>
/// to FieldValue value
/// </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>
/// from XML Page element data is loaded in
/// </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>
/// from DataTable Page element data is loaded in
/// </summary>
/// <param name="xmlstr"></param>
public void LoadElementFromDataTable(DataTable dt)
{
_isloaded = false;
try
{
if (dt != null)
{
foreach (DataRow row in dt.Rows)
{
// Traverse line
foreach (DataColumn dc in dt.Columns)
{
this[dc.ColumnName] = row[dc];
}
}
}
}
catch { return; }
_isloaded = true;
}
/// <summary>
/// from JSON Page element data is loaded in
/// </summary>
/// <param name="xmlstr"></param>
public void LoadElementFromJSONString(string json)
{
_isloaded = false;
try
{
List<string> jsList = GetFieldsString(json);
// The generated column
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("\""))
{
// Remove the extra double quotes
int end = key.Length - 2;
key = key.Substring(1, end);
}
if (value.Trim().StartsWith("\"") && value.Trim().EndsWith("\""))
{
// Remove the extra double quotes
int end = value.Length - 2;
value = value.Substring(1, end);
//PageElement Content of type
if (value.StartsWith("{") && value.EndsWith("}"))
{
value = value.Replace("\\", string.Empty);// Remove redundant escape characters
PageElement peChild = new PageElement();
peChild.LoadElementFromJSONString(value);
this[key] = peChild;
}
else // Content resolution for common types
{
// If the column value exists
this[key] = ConvertToGB(value);
}
}
}
}
catch
{
return;
}
_isloaded = true;
}
/// <summary>
/// the Unicode Decoding into plain text
/// </summary>
/// <param name="unicodeString"> To decode Unicode Character set </param>
/// <returns> The decoded string </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>
/// For field Json string
/// </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();
// Remove fore and aft
if (json.StartsWith("[") && json.EndsWith("]"))
{
int length = json.Length - 2;
json = json.Substring(1, length);
}
// is json Format of a string , In order to { At the beginning , In order to } At the end
if (json.StartsWith("{") && json.EndsWith("}"))
{
int jsonlength = json.Length - 1;
string str = json.Substring(1, jsonlength - 1) + ",";
// Having the head and tail removed "{","}"
int startPos = 0; // Search the starting position pointer
int length = 0; // A pointer to the end of the search
int flagcount = 0; // The number of opening characters of an object , Exclude the end mark according to this number
// You iterate through it to get an internal string
while (startPos + length < str.Length) // If the search is not complete, continue the search
{
if (str[startPos + length] == '{')
{
flagcount += 1;
}
else if (str[startPos + length] == '}')
{
if (flagcount > 0) // If the number of opening characters is not equal to 0 , there is an object in the middle of the character, and the flag bit should be subtracted 1 And eliminate
{
flagcount -= 1;
}
}
else if (str[startPos + length] == ',')
{
if (flagcount == 0)
{
retfieldsstring.Add(str.Substring(startPos, length));
startPos = startPos + length + 1;// New starting position
length = 0; // New intercept length
}
}
length += 1; // End pointer plus 1, Through to the next 1 Sub-loop search
}
return retfieldsstring;
}
return retfieldsstring;
}
/// <summary>
/// to Sql Insert statements
/// </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);// Remove the last 1 A comma
values = values.Substring(0, values.Length - 1);// Remove the last 1 A comma
sql += fields + ") VALUES (" + values + ")";
return sql;
}
/// <summary>
/// to Sql Update statements
/// </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);// Remove the last 1 A comma
//sql += " WHERE " + wherefield + " = " + StringTool.SqlQ(this[wherefield].ToString());
return sql;
}
/// <summary>
/// to Sql The query statements
/// </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") // Front matching
{
sql += " and " + f.Key.Split('#')[0] + " like '%'+@" + f.Key + "";
}
else if (op.Trim() == "R") // Back matching
{
sql += " and " + f.Key.Split('#')[0] + " like @" + f.Key + "+'%'";
}
else if (op.Trim() == "E") // String equality
{
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("#")) // unconditional , Directly into the
{
sql += " and " + f.Key + " = @" + f.Key;
}
else
{
string op = f.Key.Split('#')[1].ToString();
if (op.Trim() == "G") // Is greater than
{
sql += " and " + f.Key.Split('#')[0] + " > @" + f.Key;
}
else if (op.Trim() == "L") // Less than
{
sql += " and " + f.Key.Split('#')[0] + " < @" + f.Key;
}
else if (op.Trim() == "NE") // Is not equal to
{
sql += " and " + f.Key.Split('#')[0] + " <> @" + f.Key;
}
else if (op.Trim() == "GE") // Greater than or equal to
{
sql += " and " + f.Key.Split('#')[0] + " >= @" + f.Key;
}
else if (op.Trim() == "LE") // Less than or equal to
{
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;
}
}
}