Post
Ask Question

How to Convert Microsoft ADOMD Data Source to JSON

Introduction

Hi all, I hope you are all fine. This article helps you to understand how to convert your ADOMD data source into the JavaScript Object Notation (JSON) format. If you are new to ADOMD, please read here.

Please see this article in my blog: How to Convert Microsoft ADOMD Data Source to JSON

As I said, we will convert the source to JSON, so you must understand what JSON is and its importance. Am I right?

Important of JSON

JSON stands for JavaScript Object Notation (basically JSON itself is JavaScript). It is a data format that we can format and analyze using JavaScript. It is easy to use. You can get more here.

Background

For the past few months I am working on ADOMD data sources. When you have become experienced in SQL, you may encounter some difficulties to play with sources. As far as my experience is concerned, playing with the data is not as easy as in SQL in ADOMD. The ADOMD data source may be an Excel file. In my case what exactly the client is doing was uploading the Excel files that they have created, to the DB.

Getting back to the point, when you have a client-side grid that accepts data in only JSON format in your ADOMD application, what will you do? I have searched for a solution to convert the source to the JSON for many days. But I could not find a solution. So I came up with the idea of converting the ADOMD data source (Cell Set) to a HTML table and in the client-side convert that to JSON. You can read that article here.

Now that was in the initial stage, where I was new to ADOMD. We developers never stop Googling, right? To be frank I am passionate about R&D work (in which we may need to search more and more). Now I have discovered some more methods to convert the source to JSON. I thought to share that information, so that someone may determine it is useful.

I am working in MVC, and I am using the below namespace for the process.

using Newtonsoft.Json;

The Process

In this I am not taking the ADOMD data source as a Cell Set that is popularly used in such applications. Here I will explain two methods or ways to do the process.
  1. Using the ADOMD data adapter
  2. Using the ADOMD data reader

In the first part we will use an adapter and fill the data into the data table and convert that to the JSON in the server-side itself. The problem in this method is, it needs many loops for formatting and creating.

In the second part we will use a data reader, while the object reads we will do the formatting and creating the JSON that needs only one loop. Sounds cool, right?

Using the code

Let us explain the first method.

  1. Using the ADOMD data adapter

    When you use an adapter and fill in the data table, the problem here you encounter is, the data table column name would be different and it will contain the hierarchy of the cube cells.

    For example: The data table header contains “.[MEMBER_CAPTION]”

    So here I am just determining the actual header name from all the header columns. I am using a loop for that.

    Please note that it is completely based on my requirements. You may want to do a different process of formatting.

    To format I am using the following function.
    1. public DataTable HiMapColumnExcuteQuery(string query, string adoMDConnection)  
    2. {  
    3.     string readerString = string.Empty;  
    4.     try  
    5.     {  
    6.         using (AdomdConnection conn = new AdomdConnection(adoMDConnection))  
    7.         {  
    8.             conn.Open();  
    9.             using (AdomdCommand cmd = new AdomdCommand(query, conn))  
    10.             {  
    11.                     DataTable dt = new DataTable();  
    12.                     AdomdDataAdapter da = new AdomdDataAdapter(cmd);                         
    13.                     da.Fill(dt);                             
    14.                     List<string> curColumn = new List<string>();  
    15.                     string col = string.Empty;  
    16.                     if (dt.Rows.Count > 0 && dt.Columns.Count > 0)  
    17.                     {                                  
    18.                         for (int i = 0; i < dt.Columns.Count; i++)  
    19.                         {  
    20.                             string columnName = dt.Columns[i].ColumnName.Replace(".[MEMBER_CAPTION]", "").Trim();  
    21.                             curColumn = columnName.Split(new string[] { "." }, StringSplitOptions.None).ToList();                                    
    22.                             col = curColumn[curColumn.Count - 1].Replace("[", "").Replace("]", "");  
    23.                             if (Convert.ToString(col.Trim()).ToLower() == "latitude")  
    24.                                 col = "lat";  
    25.                              if (Convert.ToString(col.Trim()).ToLower() == "longitude")  
    26.                                 col = "lon";  
    27.                             dt.Columns[i].ColumnName = col;                           
    28.                         }  
    29.                         dt.AcceptChanges();  
    30.                     }  
    31.                     return dt;  
    32.                   
    33.             }  
    34.         }  
    35.     }  
    36.     catch (Exception)  
    37.     {  
    38.         throw;  
    39.     }  
    40.     finally  
    41.     {  
    42.     }  
    43. } 
    The function expects two parameters, one is the query that you need to execute and other is the connection. Here I am using this source for the Hi maps, as you all know it is important to provide the lat and lon for loading the map. So I am doing such formatting here.

    Once the formatting is done, I can convert the data to the JSON foramt. For that I am using another function. You can see the function below.
    1. public string GetJsonWithZeroForNull(DataTable dt)  
    2. {  
    3.     try  
    4.     {  
    5.         if (dt == null)  
    6.         {  
    7.             throw new ArgumentNullException("dt");  
    8.         }  
    9.         System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();  
    10.         serializer.MaxJsonLength = int.MaxValue;  
    11.         List<Dictionary<string, object>> rows =  
    12.           new List<Dictionary<string, object>>();  
    13.         Dictionary<string, object> row = null;  
    14.   
    15.         foreach (DataRow dr in dt.Rows)  
    16.         {  
    17.             row = new Dictionary<string, object>();  
    18.             foreach (DataColumn col in dt.Columns)  
    19.             {  
    20.                 if (dr[col] == null || Convert.ToString(dr[col]).ToLower() == "undefined" || Convert.ToString(dr[col]).ToLower() == "unknown")  
    21.                     continue;  
    22.                 else  
    23.                 row.Add(col.ColumnName.Trim(), dr[col]);  
    24.             }  
    25.             rows.Add(row);  
    26.         }  
    27.         return serializer.Serialize(rows);  
    28.     }  
    29.     catch (Exception)  
    30.     {  
    31.         throw;  
    32.     }  
    33. } 
    Here I am omitting the data that all are not valid. At the end it will just serialize our rows and return the data in the format of JSON.

    You can determine that if the data is greater then looping through the data twice may impact the performance.

    And also we all know that a data reader is much better than a data adapter. If you do not understand why, please read here.

  2. Using the ADOMD data reader

    Now we will move to the next part. Please see the following function for doing that.
    1.    public string createJsonFromDataReader(string query, string adoMDConnection)  
    2.    {  
    3.       string readerString = string.Empty;  
    4.       try  
    5.       {  
    6.           List<string> curColumn = new List<string>();  
    7.           StringBuilder sb = new StringBuilder();  
    8.           StringWriter sw = new StringWriter(sb);  
    9.           string columnName = string.Empty;  
    10.           string fieldVal = string.Empty;  
    11.           string prevFieldVal = string.Empty;  
    12.           AdomdDataReader rdr;  
    13.           using (AdomdConnection conn = new AdomdConnection(adoMDConnection))  
    14.           {  
    15.               conn.Open();  
    16.               using (AdomdCommand cmd = new AdomdCommand(query, conn))  
    17.               {  
    18.                   //cmd.Properties.Add("ReturnCellProperties", true);   
    19.                   rdr = cmd.ExecuteReader();  
    20.                   if (rdr != null)  
    21.                   {  
    22.                       using (JsonWriter myJson = new JsonTextWriter(sw))  
    23.                       {  
    24.                           myJson.WriteStartArray();  
    25.   
    26.                           while (rdr.Read())  
    27.                           {  
    28.                               myJson.WriteStartObject();  
    29.                               int fields = rdr.FieldCount;  
    30.   
    31.                               for (int i = 0; i < fields; i++)  
    32.                               {  
    33.                                   if (rdr[i] != null )  
    34.                                   {  
    35.                                       fieldVal = rdr[i].ToString();  
    36.                                       if (i != 0 && rdr[i-1] != null)  
    37.                                           prevFieldVal = rdr[i - 1].ToString();  
    38.                                       else  
    39.                                           prevFieldVal = "First";  
    40.   
    41.                                       if ((fieldVal == null || fieldVal.ToLower().Trim() == "undefined" || fieldVal.ToLower().Trim() == "unknown")  
    42.                                           && (prevFieldVal == null || prevFieldVal.ToLower().Trim() == "undefined" || prevFieldVal.ToLower().Trim() == "unknown"))  
    43.                                       {  
    44.                                           continue;  
    45.                                       }  
    46.                                       else  
    47.                                       {  
    48.                                           columnName = rdr.GetName(i).Replace(".[MEMBER_CAPTION]", "").Trim();  
    49.   
    50.                                           curColumn = columnName.Split(new string[] { "." }, StringSplitOptions.None).ToList();  
    51.   
    52.                                           columnName = curColumn[curColumn.Count - 1].Replace("[", "").Replace("]", "");  
    53.   
    54.                                           if (Convert.ToString(columnName.Trim()).ToLower() == "latitude")  
    55.                                               columnName = "lat";  
    56.                                           if (Convert.ToString(columnName.Trim()).ToLower() == "longitude")  
    57.                                               columnName = "lon";  
    58.   
    59.                                           myJson.WritePropertyName(columnName);  
    60.   
    61.                                           myJson.WriteValue(rdr[i]);  
    62.                                       }  
    63.                                   }  
    64.                               }  
    65.                               myJson.WriteEndObject();  
    66.                           }  
    67.   
    68.                           myJson.WriteEndArray();  
    69.                       }  
    70.                   }  
    71.                   else  
    72.                   {  
    73.                       return "No Records to display";  
    74.                   }  
    75.               }  
    76.           }  
    77.           return sw.ToString();  
    78.       }  
    79.       catch (Exception)  
    80.       {  
    81.           throw;  
    82.       }  
    83.       finally  
    84.       {  
    85.       }  
    86.       
    87. } 
    Please understand that I have created an object (myJson) for the class JsonWriter. Our complete process is based on this object. Again the complete logic and formatting conditions are based on my requirements.

    Here we are using built-in functions of the JsonWriter class. Let us list them.

    • WriteStartArray()
    • WritePropertyName()
    • WriteValue()
    • WriteEndObject()
    • WriteEndArray()

    You can see that I am doing both the formatting and creating of the JSON with one loop that will improve the performance.
    In the process we are appending the values to the string builder and finally the function will return the JSON in the required format.

    You have done it. Great.

    Please use StringBuilder in your applications instead of string. Use a string variable only if it is necessary. Using string variables will create separate memory allocations whenever you assign values to it. Please read more here.

Note: I have implemented this for loading highmaps. That's why I have formatted it in this way. Please use your own formatting depending on your requirements.

Point of interest

ADOMD, ADOMD data source to JSON, ADOMD Cell Set to JSON, ADOMD data adapter to JSON, ADOMD data reader to JSON.


Conclusion

Please do not forget to give your valuable suggestions.

That is all for the day, will see you in another article.

Kindest Regards
Sibeesh


Similar Articles