//Recursive function . For each Tree node :
/* - Run EXEC SQL from node.
* - Put Results into dynamic object.
* - Call self agiain until all SQL Nodes have been run
* - Add results from child calls to dynamic object made.*/
public KeyValuePair <string, object> GetSQLData(SQLTreeItem sqlItem, string currentValue, string connectionStr) //SQLTreeItem item, string connectionStr, DataRow returnedRow, ExpandoObject prevObject
{
DataTable table = _SQLHandler.GetDataFromSQLItem(sqlItem, connectionStr, GetMappedFieldsWithValues(sqlItem.KeyFields, currentValue));
string json = "{ " + '"' + sqlItem.Header + '"' + ":" + JsonConvert.SerializeObject(table) + "}";
ExpandoObject data = JsonConvert.DeserializeObject <ExpandoObject>(json, new ExpandoObjectConverter());
IDictionary <string, object> tableDictionary = data as IDictionary <string, object>;
//Loop through Schema nodes
foreach (SQLTreeItem childItem in sqlItem.Items)
{
// try and locate field in table definition
string fieldName = childItem.Header.ToString().ToLower();
foreach (object lookup in ((List <object>)tableDictionary.FirstOrDefault().Value))
{
IDictionary <string, object> dict = lookup as IDictionary <string, object>;
if (dict.ContainsKey(fieldName))
{
// If we have a Child SQL node to Exectute
if (childItem.Items.Count > 0)
{
// Replace Value with SQL table Data
tableDictionary[fieldName] = GetSQLData(((SQLTreeItem)childItem.Items[0]), dict[fieldName].ToString(), connectionStr);
}
}
}
}
return(((IDictionary <string, object>)data).FirstOrDefault());
}
public List <SQLTreeItem> GetSQLNodesFromXML(List <XMLTreeView> items)
{
List <SQLTreeItem> result = new List <SQLTreeItem>();
foreach (XMLTreeView i in items)
{
SQLTreeItem newItem = new SQLTreeItem()
{
Header = i.Header,
SQL = i.SQL,
SchemaSQL = i.SchemaSQL,
KeyFields = i.KeyFields,
ItemType = i.ItemType
};
if (i.ChildNodes.Count > 0)
{
foreach (SQLTreeItem x in GetSQLNodesFromXML(i.ChildNodes))
{
newItem.Items.Add(x);
}
}
result.Add(newItem);
}
return(result);
}
public DataTable GetDataFromSQLItem(SQLTreeItem item, string connectionstring, string[] globalParams)
{
string sql = item.SQL;
Dictionary <string, string> dict = new Dictionary <string, string>();
foreach (string str in globalParams)
{
string[] tmp = str.Split('=');
dict.Add(tmp[0], tmp[1]);
}
foreach (KeyValuePair <string, string> pair in dict)
{
sql = sql.ToLower().Replace("[" + pair.Key.ToLower().Trim() + "]", "'" + pair.Value.Trim() + "'");
}
DataTable result = GetData(sql, connectionstring);
return(result);
}
public List <string> GetSQlSchemaFromSQLItem(SQLTreeItem item, string connectionstring)
{
List <string> result = GetSQLSchema(item.SchemaSQL, connectionstring);
return(result);
}
