In this article we will learn how we can convert Microsoft ADOMD cell sets to JSON. There are few possible ways to convert a CellSet to JSON, Here by I am sharing you one among that options. We will create a function which accepts cellset as the argument and we will loop through the axes of cellset and bind the values according to a string builder using a JsonWriter class. I hope you will like it.

Background

For the past few months I have been working with Microsoft ADOMD data sources. And I have written some article also that will describe the problems I have encountered so far. If you are new to ADOMD I strongly recommend that read my previous articles that you may find useful when you work with ADOMD data sources. You can find those article links here.

Why

You might think, why am I again using the methods described in the preceding two articles. I will answer that. I have encountered some issues with those methods. When you use a data adapter or data reader as explained in the first link (How to Convert Microsoft ADOMD Data Source to JSON) you always get the values as normal values instead of formatted values. For example even if the value contains $ or %, you will always get values without those symbols. So your application won’t let the user identify which one is currency or which one is %. In my case it was high chart and high maps. When the user hovers over a specific area, I need to show the measure values in the tooltip.

So in that case I was forced to use the cell set again, where there is an option that we can select the formatted value. I will show you that in my function.

Using the code

The following is the function that does what was explained above.
[csharp]
private string BuildBubbleMap(CellSet cst)
{
try
{
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
string columnName = string.Empty;
string fieldVal = string.Empty;
//check if any axes were returned else throw error.
int axes_count = cst.Axes.Count;
if (axes_count == 0)
throw new Exception("No data returned for the selection");

//if axes count is not 2
if (axes_count != 2)
throw new Exception("The code support only queries with two axes");

//if no position on either row or column throw error
if (!(cst.Axes[0].Positions.Count > 0) && !(cst.Axes[1].Positions.Count > 0))
throw new Exception("No data returned for the selection");

int cur_row, cur_col, col_count, row_count, col_dim_count, row_dim_count;
row_dim_count = 0;

//Number of dimensions on the column
col_dim_count = cst.Axes[0].Positions[0].Members.Count;

//Number of dimensions on the row
if (cst.Axes[1].Positions.Count > 0)
{
if (cst.Axes[1].Positions[0].Members.Count > 0)
row_dim_count = cst.Axes[1].Positions[0].Members.Count;
}
//Total rows and columns
row_count = cst.Axes[1].Positions.Count + col_dim_count; //number of rows + rows for column headers
col_count = cst.Axes[0].Positions.Count + row_dim_count; //number of columns + columns for row headers

using (JsonWriter myJson = new JsonTextWriter(sw))
{
myJson.WriteStartArray();
for (cur_row = 0; cur_row < row_count-1; cur_row++)
{
myJson.WriteStartObject();
for (cur_col = 0; cur_col < col_count-1; cur_col++)
{
//Looping for dimension headers
columnName = cst.Axes[1].Positions[cur_row].Members[cur_col].ParentLevel.ToString().Replace("{", "").Replace("}", "").Trim();
fieldVal = cst.Axes[1].Positions[cur_row].Members[cur_col].Caption.Replace(",", " ");
//If the value is null, I dont need that to be included
if ((columnName == null || columnName == "" || columnName.ToLower() == "undefined" || columnName.ToLower() == "null" ||
columnName.ToLower() == "(null)" || columnName.ToLower() == "unknown")||(fieldVal == null || fieldVal == "" ||
fieldVal.ToLower() == "undefined" || fieldVal.ToLower() == "null" ||
fieldVal.ToLower() == "(null)" || fieldVal.ToLower() == "unknown"))
break;
//Map expect the header as lat and lon, so here we are changing that.
if (columnName.ToLower() == "latitude")
columnName = "lat";
else if (columnName.ToLower() == "longitude")
columnName = "lon";

myJson.WritePropertyName(columnName);
myJson.WriteValue(fieldVal);
}
//Looping for measure headers
myJson.WritePropertyName(cst.Axes[0].Positions[0].Members[0].Caption.Replace(",", " ").Trim());
myJson.WriteValue(cst[cur_row].FormattedValue);
//Please be noted that we are using FormattedValue here.
myJson.WriteEndObject();
}
myJson.WriteEndArray();
}
cst = null;
return sw.ToString();
}
catch (Exception)
{
cst = null;
throw;
}
}
[/csharp]

You can see that this function expects the parameter cell set. Here we are finding the axis (Axis0 and Axis 1). According to my ADOMD query my dimensions are in Axis 1, so that I need to determine the caption for each dimension from the cell set. Once I determine the dimensions I am writing that to the StringBuilder using the method WritePropertyName() of the JSON Writer class. To use this class you must include the Newtonsoft DLL as in the following.
[csharp]
using Newtonsoft.Json;
[/csharp]

Once that is over, I am writing the value for those dimensions using the WriteValue() method of the JSON writer class.

I am doing that for the measures also. You can see these implementations in my code. Finally I am creating the proper JSON here and returning that.

To use this function you need to build the cell set first. You can do that as follows.
[csharp]
using (AdomdConnection conn = new AdomdConnection(myConnection))
{
conn.Open();
using (AdomdCommand cmd = new AdomdCommand(query, conn))
{
cmd.CommandTimeout = connectionTimeout;
cst = cmd.ExecuteCellSet();
}
}
[/csharp]

Once you get the cell set, just pass the cell set to the preceding function, it will return the JSON you need. You can easily bind it to any client-side tool (for example, High Chart and High Maps).

Conclusion

Have you ever gone through this kind of requirement. Did I miss anything that you may think which is needed?. I hope you liked this article. Please share me your valuable suggestions and feedback.

Your turn. What do you think?

A blog isn’t a blog without comments, but do try to stay on topic. If you have a question unrelated to this post, you’re better off posting it on C-Sharp Corner, Stack Overflow, Asp.Net Forums or Code Project instead of commenting here. Tweet or email me a link to your question there and I’ll definitely try to help if I am able to.

Kindest Regards
Sibeesh Venu