frmapis.cs

< SQL Server2005程序设计>

                            }
                            dgvResults.DataSource = dt;
                            break;
                        }
                    #endregion

                    #region Members
                    case "Members":
                        {
                            Level lev = cube.Dimensions["Geography"].Hierarchies["Geography"].Levels["Country"];
                            dt = new DataTable();
                            dt.Columns.Add("Name");
                            dt.Columns.Add("Description");
                            dt.Columns.Add("Caption");
                            dt.Columns.Add("UniqueName");
                            dt.Columns.Add("ParentLevel");
                            dt.Columns.Add("ChildCount");
                            foreach (Member m in lev.GetMembers())
                            {
                                DataRow newrow = dt.Rows.Add();
                                newrow[0] = m.Name;
                                newrow[1] = m.Description;
                                newrow[2] = m.Caption;
                                newrow[3] = m.UniqueName;
                                newrow[4] = m.ParentLevel.Name;
                                newrow[5] = m.ChildCount.ToString();
                            }
                            dgvResults.DataSource = dt;
                            break;
                        }
                    #endregion

                    #region KPIs
                    case "KPIs":
                        {
                            dt = new DataTable();
                            dt.Columns.Add("Name");
                            dt.Columns.Add("Description");
                            dt.Columns.Add("Caption");
                            dt.Columns.Add("StatusGraphic");
                            dt.Columns.Add("TrendGraphic");
                            dt.Columns.Add("DisplayFolder");
                            dt.Columns.Add("ParentCube");
                            dt.Columns.Add("ParentKpi");
                            foreach (Kpi k in cube.Kpis)
                            {
                                DataRow newrow = dt.Rows.Add();
                                newrow[0] = k.Name;
                                newrow[1] = k.Description;
                                newrow[2] = k.Caption;
                                newrow[3] = k.StatusGraphic;
                                newrow[4] = k.TrendGraphic;
                                newrow[5] = k.DisplayFolder;
                                newrow[6] = k.ParentCube;
                                newrow[7] = k.ParentKpi;
                            }
                            dgvResults.DataSource = dt;
                            break;
                        }
                    #endregion

                    #region Named Sets
                    case "Named Sets":
                        {
                            dt = new DataTable();
                            dt.Columns.Add("Name");
                            dt.Columns.Add("Description");
                            dt.Columns.Add("ParentCube");
                            dt.Columns.Add("Expression");
                            foreach (NamedSet ns in cube.NamedSets)
                            {
                                DataRow newrow = dt.Rows.Add();
                                newrow[0] = ns.Name;
                                newrow[1] = ns.Description;
                                newrow[2] = ns.ParentCube;
                                newrow[3] = ns.Expression;
                            }
                            dgvResults.DataSource = dt;
                            break;
                        }
                    #endregion

                    #region Calculated Members
                    case "Calculated Members*":
                        {
                            object[] Restrictions;
                            Restrictions = new object[7];
                            Restrictions[2] = "Sales"; //Cube Name
                            Restrictions[6] = 0; //Level Ordinal (This works for Adventure Works, but is not necessarily valid)
                            DataSet ds = conn.GetSchemaDataSet(AdomdSchemaGuid.Members, Restrictions);
                            ds.Tables[0].DefaultView.RowFilter = "Expression <> ''"; //Only _calculated_ members
                            dgvResults.DataSource = ds.Tables[0];
                            break;
                        }
                    #endregion

                    #region Actions - URL
                    case "Actions - URL*":
                        {
                            object[] Restrictions = new object[7];
                            Restrictions[2] = "Sales";

                            Restrictions[5] = "Geography.[Country - State-Province - City - Postal Code].City.Vancouver";
                            Restrictions[6] = MDACTION_COORDINATE_MEMBER;

                            DataTable results = conn.GetSchemaDataSet(AdomdSchemaGuid.Actions, Restrictions).Tables[0];
                            dgvResults.DataSource = results;
                            MessageBox.Show("Content = " + results.Rows[0]["Content"].ToString() + "\nClick OK to navigate to action URL");

                            wbrMain.BringToFront();
                            wbrMain.Navigate(results.Rows[0]["Content"].ToString());
                            break;
                        }
                    #endregion

                    #region Actions - Drillthrough
                    case "Actions - Drillthrough*":
                        {
                            object[] Restrictions = new object[7];
                            Restrictions[2] = "Sales";

                            Restrictions[5] = "(Geography.[Country - State-Province - City - Postal Code].City.Vancouver, Products.[Category Name].Beverages)";
                            Restrictions[6] = MDACTION_COORDINATE_CELL;

                            DataTable results = conn.GetSchemaDataSet(AdomdSchemaGuid.Actions, Restrictions).Tables[0];
                            dgvResults.DataSource = results;
                            MessageBox.Show("Content = " + results.Rows[0]["Content"].ToString() + "\nClick OK to See Drillthrough Data");

                            txtMDX.Text = results.Rows[0]["Content"].ToString();
                            ExecMDX(conn);
                            break;
                        }
                    #endregion

                }
            }
            catch 
            { 
                dgvResults.DataSource = null;
            }
            conn.Close();

        }
#endregion
        
#region AdomdDataAdapter.Fill
        private void btnExecuteReader_Click(object sender, EventArgs e)
        {
            conn.ConnectionString = ConnectionString;
            conn.Open();
            ExecMDX(conn);
            conn.Close();
        }

        private void ExecMDX(AdomdConnection conn)
        {
            AdomdCommand cmd = new AdomdCommand(txtMDX.Text, conn);
            DataTable dt = new DataTable();
            AdomdDataAdapter da = new AdomdDataAdapter(cmd);
            try
            {
                dgvResults.BringToFront();
                da.Fill(dt);
                dgvResults.DataSource = dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
#endregion

#region AdomdCommand.ExecuteXmlReader
        private void btnExecXMLReader_Click(object sender, EventArgs e)
        {
            // This procedure brings back and XML cellset, transforms it using xamd.xsl
            // (included with XMLA SDK) and displays the resultant HTML in an IE WebBrowser control

            // ADO MD.NET:
            AdomdConnection conXML = new AdomdConnection(ConnectionString);
            AdomdCommand comXML = new AdomdCommand(txtMDX.Text, conXML);

            // System.Xml:
            XslCompiledTransform xslCellSetTable = new XslCompiledTransform();
            XmlTextWriter xtwCellSetTable = new XmlTextWriter(Path.GetFullPath(".") + "\\Execute.htm", null);

            try
            {
                // Show the Web Browser:
                wbrMain.BringToFront();

                // Open the connection:
                conXML.Open();

                // Load the stylesheet:
                xslCellSetTable.Load(Path.GetFullPath(".") + "\\xamd.xsl");

                // Fetch and transform the XML to an HTML file:
                xslCellSetTable.Transform(comXML.ExecuteXmlReader(), xtwCellSetTable);

                // Close the XmlTextWriter:
                xtwCellSetTable.Close();

                // Display generated HTML file in the WebBrowser control:
                wbrMain.Navigate(Path.GetFullPath(".") + "\\Execute.htm");
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            finally
            {
                // Close the connection:
                conXML.Close();
            }
        }
#endregion

#region AdomdCommand.ExecuteCellSet
        private void btnExecCellset_Click(object sender, EventArgs e)
        {
            lvwResults.BringToFront();
            lvwResults.Columns.Clear();
            cboPages.Enabled = true;

            // Grab CellSet:
            conn.ConnectionString = ConnectionString;
            conn.Open();
            AdomdCommand cmd = new AdomdCommand(txtMDX.Text, conn);
            cs = cmd.ExecuteCellSet();

            // Put page names in cboPages:
            foreach (Position p in cs.Axes[2].Positions)
            {
                cboPages.Items.Add(p.Members[0].Caption);
            }

            // Put column names in lvwResults.Columns collection
            lvwResults.Columns.Add(String.Empty);
            foreach (Position p in cs.Axes[0].Positions)
            {
                lvwResults.Columns.Add(p.Members[0].Caption);
            }

            // Display page 0 ([All] member)
            cboPages.SelectedIndex = 0;
            lvwResults.BringToFront();
            cboPages.Enabled = true;
            conn.Close();

        }

        private void cboPages_SelectedIndexChanged(object sender, EventArgs e)
        {
            // Row names and cells in lvwResults collection
            lvwResults.Items.Clear();
            foreach (Position p in cs.Axes[1].Positions)
            {
                ListViewItem newItem = lvwResults.Items.Add(p.Members[0].Caption);
                for (int i = 0; i < cs.Axes[0].Positions.Count; i++)
                {
                    newItem.SubItems.Add(cs.Cells[i, newItem.Index, cboPages.SelectedIndex].FormattedValue);
                }
            }
        }

#endregion

#region Xmla
        private void btnXmla_Click(object sender, EventArgs e)
        {

            // XMLA:
            Microsoft.AnalysisServices.Xmla.XmlaClient xmlac = new Microsoft.AnalysisServices.Xmla.XmlaClient();

            // System.Xml:
            XslCompiledTransform xslCellSetTable = new XslCompiledTransform();
            XmlTextWriter xtwCellSetTable = new XmlTextWriter(Path.GetFullPath(".") + "\\Execute.htm", null);
            XmlDocument xdcResults = new XmlDocument();

            string command = "<Statement>" + txtMDX.Text + "</Statement>";
            string properties = "<PropertyList>" +
                                "   <Catalog>Chapter18</Catalog>" +
                                "</PropertyList>";
            string results;

            try
            {