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.

using Newtonsoft.Json;

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;
   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;
   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();
   26. while (rdr.Read())
   27. {
   28. myJson.WriteStartObject();
   29. int fields = rdr.FieldCount;
   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";
   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();
   50. curColumn = columnName.Split(new string[] { "." }, StringSplitOptions.None).ToList();
   52. columnName = curColumn[curColumn.Count - 1].Replace("[", "").Replace("]", "");
   54. if (Convert.ToString(columnName.Trim()).ToLower() == "latitude")
   55. columnName = "lat";
   56. if (Convert.ToString(columnName.Trim()).ToLower() == "longitude")
   57. columnName = "lon";
   59. myJson.WritePropertyName(columnName);
   61. myJson.WriteValue(rdr[i]);
   62. }
   63. }
   64. }
   65. myJson.WriteEndObject();
   66. }
   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. }
   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.
   
   
   • String builder vs string concatenation
   • Difference between string and StringBuilder in C#

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.