Click here to Skip to main content
14,669,678 members
Articles » Web Development » ASP.NET » Howto
Article
Posted 25 Feb 2015

Stats

16.3K views
12 bookmarked

Ways to Convert Microsoft ADOMD Data Source to JSON

Rate this:
3.80 (10 votes)
Please Sign up or sign in to vote.
3.80 (10 votes)
25 Feb 2015CPOL
Ways 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.

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

Importantance 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.

public DataTable HiMapColumnExcuteQuery(string query, string adoMDConnection)
{
    string readerString = string.Empty;
    try {
        using(AdomdConnection conn = new AdomdConnection(adoMDConnection)) {
            conn.Open();
            using(AdomdCommand cmd = new AdomdCommand(query, conn)) {
                DataTable dt = new DataTable();
                AdomdDataAdapter da = new AdomdDataAdapter(cmd);
                da.Fill(dt);
                List < string > curColumn = new List < string > ();
                string col = string.Empty;
                if (dt.Rows.Count > 0 && dt.Columns.Count > 0)
                {
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        string columnName = dt.Columns[i].ColumnName.Replace(".[MEMBER_CAPTION]",
                            "").Trim();
                        curColumn = columnName.Split(new string[] {
                            "."
                        },
                        StringSplitOptions.None).ToList();
                        col = curColumn[curColumn.Count - 1].Replace("[", "").Replace("]", "");
                        if (Convert.ToString(col.Trim()).ToLower() == "latitude")
                        col = "lat";
                        if (Convert.ToString(col.Trim()).ToLower() == "longitude")
                        col = "lon";
                        dt.Columns[i].ColumnName = col;
                    }
                    dt.AcceptChanges();
                }
                return dt;
            }
        }
    } catch (Exception)
    {
        throw;
    }
    finally
    {
    }
}

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.

public string GetJsonWithZeroForNull(DataTable dt)
{
    try
    {
        if (dt == null)
        {
            throw new ArgumentNullException("dt");
        }
        System.Web.Script.Serialization.JavaScriptSerializer serializer = new
        System.Web.Script.Serialization.JavaScriptSerializer();
        serializer.MaxJsonLength = int.MaxValue;
        List < Dictionary < string, object >> rows =
        new List < Dictionary < string, object >> ();
        Dictionary < string, object > row = null;
        foreach(DataRow dr in dt.Rows)
        {
            row = new Dictionary < string, object > ();
            foreach(DataColumn col in dt.Columns)
            {
                if (dr[col] == null || Convert.ToString(dr[col]).ToLower() == "undefined" ||
                Convert.ToString(dr[col]).ToLower() == "unknown")
                continue;
                else row.Add(col.ColumnName.Trim(), dr[col]);
            }
            rows.Add(row);
        }
        return serializer.Serialize(rows);
    } catch (Exception)
    {
        throw;
    }
}

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.

public string createJsonFromDataReader(string query, string adoMDConnection)
 {
    string readerString = string.Empty;
    try
    {
        List < string > curColumn = new List < string > ();
        StringBuilder sb = new StringBuilder();
        StringWriter sw = new StringWriter(sb);
        string columnName = string.Empty;
        string fieldVal = string.Empty;
        string prevFieldVal = string.Empty;
        AdomdDataReader rdr;
        using(AdomdConnection conn = new AdomdConnection(adoMDConnection))
        {
            conn.Open();
            using(AdomdCommand cmd = new AdomdCommand(query, conn))
            {
                //cmd.Properties.Add("ReturnCellProperties", true);
                rdr = cmd.ExecuteReader();
                if (rdr != null)
                {
                    using(JsonWriter myJson = new JsonTextWriter(sw))
                    {
                        myJson.WriteStartArray();
                        while (rdr.Read())
                        {
                            myJson.WriteStartObject();
                            int fields = rdr.FieldCount;
                            for (int i = 0; i < fields; i++)
                            {
                                if (rdr[i] != null)
                                {
                                    fieldVal = rdr[i].ToString();
                                    if (i != 0 && rdr[i - 1] != null)
                                    prevFieldVal = rdr[i - 1].ToString();
                                    else prevFieldVal = "First";
                                    if ((fieldVal == null || fieldVal.ToLower().Trim() == "undefined" ||
                                    fieldVal.ToLower().Trim() == "unknown")
                                    && (prevFieldVal == null || prevFieldVal.ToLower().Trim() ==
                                        "undefined" || prevFieldVal.ToLower().Trim() == "unknown"))
                                    {
                                        continue;
                                    } else
                                    {
                                        columnName = rdr.GetName(i).Replace(".[MEMBER_CAPTION]",
                                            "").Trim();
                                        curColumn = columnName.Split(new string[] {                                                "."
                                        },
                                        StringSplitOptions.None).ToList();
                                        columnName = curColumn[curColumn.Count - 1].Replace("[",
                                            "").Replace("]", "");
                                        if (Convert.ToString(columnName.Trim()).ToLower() == "latitude")
                                        columnName = "lat";
                                        if (Convert.ToString(columnName.Trim()).ToLower() == "longitude")
                                        columnName = "lon";
                                        myJson.WritePropertyName(columnName);
                                        myJson.WriteValue(rdr[i]);
                                    }
                                }
                            }
                            myJson.WriteEndObject();
                        }
                        myJson.WriteEndArray();
                    }
                } else
                {
                    return "No Records to display";
                }
            }
        }
        return sw.ToString();
    } catch (Exception)
    {
        throw;
    } finally
    {
    }
}

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sibeesh Passion
Software Developer
Germany Germany
I am Sibeesh Venu, an engineer by profession and writer by passion. I’m neither an expert nor a guru. I have been awarded Microsoft MVP 3 times, C# Corner MVP 5 times, DZone MVB. I always love to learn new technologies, and I strongly believe that the one who stops learning is old.

My Blog: Sibeesh Passion
My Website: Sibeesh Venu

Comments and Discussions

 
GeneralGood one Pin
Gaurav Aroraa14-Jun-15 1:17
professionalGaurav Aroraa14-Jun-15 1:17 
GeneralRe: Good one Pin
Sibeesh Passion14-Jun-15 2:39
professionalSibeesh Passion14-Jun-15 2:39 
QuestionMy vote of 5 Pin
VigneshNagaraju4-May-15 2:22
MemberVigneshNagaraju4-May-15 2:22 
AnswerRe: My vote of 5 Pin
Sibeesh Passion4-May-15 2:24
professionalSibeesh Passion4-May-15 2:24 
QuestionThis Article is really useful. Thanks Sibeesh Pin
Member 1011064713-Apr-15 23:05
MemberMember 1011064713-Apr-15 23:05 
AnswerRe: This Article is really useful. Thanks Sibeesh Pin
Sibeesh Passion13-Apr-15 23:13
professionalSibeesh Passion13-Apr-15 23:13 
GeneralMy vote of 5 Pin
Mahsa Hassankashi25-Feb-15 12:58
mvaMahsa Hassankashi25-Feb-15 12:58 
GeneralRe: My vote of 5 Pin
Sibeesh Passion25-Feb-15 16:28
professionalSibeesh Passion25-Feb-15 16:28 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.