Converts a dataset (containing many datatables) into a List<Dictionary<string, object>> which can be converted into JSON and returned from an API. Stored procedure returns the required data in a known format which is then read in C# and push out in JSON. Should be fairly generic i.e. what ever you return from the database will be converted and s…

DatasetToJson.cs

private IEnumerable<Dictionary<string, object>> DataSetToOrder(DataSet orderDetails)
        {            
            var s = orderDetails.Tables[0].Rows[0][0].ToString();

            var tableDefinitions = s.Replace(", ", ",").Split(',');

            var tables = tableDefinitions.Select(tableDefinition => tableDefinition.Split('/')).Select(temp => new Tuple<int, int, string>(int.Parse(temp[0]), int.Parse(temp[1]), temp[2])).ToList();

            var parent = tables.First(t => t.Item1.Equals(1));

            var ordersDataSet = orderDetails;

            // Rename all the tables
            for (var i = 1; i < ordersDataSet.Tables.Count - 1; i++)
            {
                ordersDataSet.Tables[i].TableName = tables[i - 1].Item1 + "_" + tables[i - 1].Item3;
            }

            var parentTable = ordersDataSet.Tables.Cast<DataTable>().First(t => t.TableName.Equals(parent.Item1 + "_" + parent.Item3));

            var orders = new List<Dictionary<string, object>>();

            for (var r = 0; r < parentTable.Rows.Count; r++)
            {
                var order = new Dictionary<string, object>();
                for (var i = 0; i < parentTable.Columns.Count; i++)
                {
                    if (!parentTable.Columns[i].ColumnName.StartsWith("_"))
                    {
                        order.Add(parentTable.Columns[i].ColumnName, parentTable.Rows[r][i]);
                    }
                }

                AddChildren(1, parentTable.Rows[r]["_id"].ToString(), tables, ordersDataSet.Tables, order);

                orders.Add(order);
            }

            return orders;
        }

        public static void AddChildren(int tableId, string parentId, List<Tuple<int, int, string>> tables, DataTableCollection dataTableCollection, Dictionary<string, object> parent)
        {
            // find tables that have tableId as a parent
            foreach (var t in tables.Where(t => t.Item2 == tableId))
            {
                // find the matching table and go through all rows where the _pid column = the passed in parentId value
                var dt = dataTableCollection.Cast<DataTable>().First(tab => tab.TableName.Equals(string.Format("{0}_{1}", t.Item1, t.Item3)));
                var lineFilter = string.Format("{0} = '{1}'", "_pid", parentId);

                DataRow[] matchingLines = dt.Columns.Count > 0 ? dt.Select(lineFilter) : new DataRow[0];

                var children = new List<Dictionary<string, object>>();
                
                foreach (DataRow row in matchingLines)
                {
                    var linesDetails = new Dictionary<string, object>();

                    for (var x = 0; x < dt.Columns.Count; x++)
                    {
                        if (!dt.Columns[x].ColumnName.StartsWith("_"))
                        {
                            linesDetails.Add(dt.Columns[x].ColumnName, row[x]);
                        }
                    }

                    AddChildren(t.Item1, row["_id"].ToString(), tables, dataTableCollection, linesDetails);

                    children.Add(linesDetails);
                }

                parent.Add(t.Item3, children);             
            }
        }