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