How To: Traverse an ADO MD Cellset Object by Using Visual C# .NET (828279)

MORE INFORMATION

The sample application that is provided in this article shows how to traverse an ADO MD Cellset object by using Visual C# .NET. The sample application provides a user interface that permits you to enter a Multidimensional Expressions (MDX) query in a text box. When you click the button in the application, the application retrieves the Cellset object and displays the retrieved Cellset object in a tabular format.

Note The sample application assumes that the MDX query has at least two axes and at most three axes.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

To create the sample application, follow these steps:

1. Start Visual Studio .NET.
2. On the File menu, point to New, and then click Project.
3. Under Project Types, click Visual C# Projects.
4. Under Templates, click ASP.NET Web Application, and then click OK.
5. On the Project menu, click Add Reference.
6. In the Add Reference dialog box, click the COM tab.
7. Under Component Name, click Microsoft ActiveX Data Objects (Multi-dimensional) 2.7 Library.
   
   Note You can use ADO MD version 2.7 or later.
8. Click Select, and then click OK.
9. Add a TextBox control and a Button control from the toolbox to the WebForm1.aspx WebForm.
10. Add the following code to the Button1_Click event handler:

    string query;
    // Assign the MDX query to a string.
    query = TextBox1.Text.ToString();
    // Create an ADO MD catalog and an ADO MD Cellset.
    ADOMD.Catalog oAdoMDCat = new ADOMD.CatalogClass();
    ADOMD.Cellset oAdoMDCellSet = new ADOMD.Cellset();
    try
    {
    	// Open a connection to the Analysis Server, 
    	// and then run the MDX query.
    	oAdoMDCat.let_ActiveConnection("Provider=MSOLAP.1;" + 
    		"Data Source=localhost;" + 
    		"Initial Catalog=Foodmart 2000");
    	oAdoMDCellSet.Open(query, oAdoMDCat.ActiveConnection);
    
    	// Create the pos object to contain two ordinals 
    	// for the coordinate.
    	int numaxes = oAdoMDCellSet.Axes.Count;
    	object[] pos = new object[numaxes];
    	int posval = 350;
    
    	// Traverse through the pages.
    	for (int pages = 0; pages <= oAdoMDCellSet.Axes.Count ; pages++)
    	{
    
    		// Traverse through the columns to write a table header.
    		Response.Write("<Table style =" + '"' + "Z-ORDER = 103; LEFT: 20px;" + 
    				" WIDTH:" +	"800px; POSITION: absolute; TOP: " + 
    				posval.ToString()+"px" + '"' + "HEIGHT: 116px" + 
    				"cellSpacing=1 cellPadding=1 width=600 border=1> <TR>");
    		for (int ia = 0; ia <= oAdoMDCellSet.Axes[0].Positions.Count - 1; ia++)
    		{
    			// If a page exists, write the name of the page.
    			if (numaxes>2)
    				Response.Write("<TD>" + 
    					oAdoMDCellSet.Axes[2].Positions[pages].Members[0].
    							Caption.ToString() 
    					+ "</TD>");
    			// Write the name of the columns.
    			Response.Write("<TD>" + 
    				oAdoMDCellSet.Axes[0].Positions[ia].Members[0].
    							Caption.ToString()+
    				"</TD>");
    		}
    		Response.Write("</TR>");
    
    		// Traverse through the rows.
    		for (int j = 0; j <=oAdoMDCellSet.Axes[1].Positions.Count - 1; 
    					j ++)
    		{
    			// Write the dimension member name.
    			Response.Write("<TR><TD>" + 
    				oAdoMDCellSet.Axes[1].Positions[j].Members[0].
    						Caption.ToString() + 
    				"</TD>");
    
    			// Traverse through the columns.
    			for (int i = 0; i <= oAdoMDCellSet.Axes[0].Positions.Count - 1; 
    						i++)
    			{
    				// Load the cell coordinates to the pos object.
    				pos[0] = System.Convert.ToInt16(oAdoMDCellSet.Axes[0].
    								Positions[i].Ordinal.ToString());
    				pos[1] = System.Convert.ToInt16(oAdoMDCellSet.Axes[1].
    								Positions[j].Ordinal.ToString());
    				if (numaxes>2)
    					pos[2] = System.Convert.ToInt16(
    						oAdoMDCellSet.Axes[2].Positions[pages].
    								Ordinal.ToString());
    				// Retrieve the value of a cell.
    				ADOMD.Cell cell = oAdoMDCellSet.get_Item(ref pos);
    
    				// Write the FormattedValue property.
    				Response.Write("<TD>" + cell.FormattedValue + "</TD>");
    				posval=posval+25;
    			}
    			Response.Write("</TR>");
    		}
    		Response.Write("</Table>");
    	}
    	// Close the Cellset.
    	oAdoMDCellSet.Close();
    }
    catch (Exception goof)
    {
    	string err_message;
    	err_message=goof.Message.ToString();
    	Response.Write(err_message.ToString());
    }
    
    // Release the Cellset object and the Catalog object from the memory.
    System.Runtime.InteropServices.Marshal.
    			ReleaseComObject(oAdoMDCellSet);
    System.Runtime.InteropServices.Marshal.
    			ReleaseComObject(oAdoMDCat);
    GC.Collect();
    GC.GetTotalMemory(true);
    GC.WaitForPendingFinalizers();

11. On the Build menu, click Build Solution.
12. On the Debug menu, click Start.
    
    The WebForm1.aspx Web form is displayed in your Web browser.
13. Copy the following MDX query to the text box on the WebForm1.aspx WebForm:

    select {[Measures].Members} on columns, {[Promotion Media].Members} on rows from Sales

14. Click Button.
    
    A Cellset object that corresponds to the MDX query that you entered in step 13 is displayed in a tabular format.