frmapis.cs

< SQL Server2005程序设计>

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

                // Fetch the XML into a string:
                xmlac.Connect(ConnectionString);
                xmlac.Execute(command, properties, out results, false, true);

                // Load the XML text into an XmlDocument
                xdcResults.LoadXml(results);

                // Strip off the outer <return> tag (which the stylesheet is not expecting):
                xdcResults.LoadXml(xdcResults.FirstChild.InnerXml);

                // Transform the XML to an HTML file:
                xslCellSetTable.Transform(xdcResults, xtwCellSetTable);

                // Close XmlTextWriter:
                xtwCellSetTable.Close();

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

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

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

#region AMO

        private Microsoft.AnalysisServices.Server s = new Microsoft.AnalysisServices.Server();
        private Microsoft.AnalysisServices.Cube c;

        private void btnMeasureDetails_Click(object sender, EventArgs e)
        {
            this.Cursor = Cursors.WaitCursor;
            
            // Connect:
            s.Connect("Data Source=" + txtServer.Text);
            try
            {
                c = s.Databases.FindByName(txtDatabase.Text).Cubes.FindByName(cboCubes.Text);

                // Load Measure Groups:
                lstMeasureGroups.Items.Clear();
                foreach (Microsoft.AnalysisServices.MeasureGroup mg in c.MeasureGroups)
                {
                    lstMeasureGroups.Items.Add(mg.Name);
                }

                // Select first Measure Group as default (will populate cboDisplayFolders):
                lstMeasureGroups.SelectedIndex = 0;

                // UI:
                pnlMeasureDetails.BringToFront();
            }
            catch { }

            // Disconnect
            s.Disconnect();

            this.Cursor = Cursors.Default;
        }

        private void lstMeasureGroups_SelectedIndexChanged(object sender, EventArgs e)
        {
            // Load Display Folders in selected MeasureGroup (via its Measures collection):
            lstDisplayFolders.Items.Clear();
            foreach (Microsoft.AnalysisServices.Measure m in c.AllMeasures)
            {
                if (!(m.DisplayFolder == null))
                {
                    if (!lstDisplayFolders.Items.Contains(m.DisplayFolder) & m.Parent.Name == lstMeasureGroups.Text)
                        lstDisplayFolders.Items.Add(m.DisplayFolder);
                }
                else
                    if (!lstDisplayFolders.Items.Contains("None"))
                        lstDisplayFolders.Items.Add("None");
            }

            // Select first Display Folder as default (will populate cboMeasures):
            lstDisplayFolders.SelectedIndex = 0;
        }

        private void lstDisplayFolders_SelectedIndexChanged(object sender, EventArgs e)
        {
            // Load Measures in selected Display Folder (or Measure Group):
            lstMeasures.Items.Clear();
            foreach (Microsoft.AnalysisServices.Measure m in c.AllMeasures)
            {
                if ((m.Parent.Name == lstMeasureGroups.Text) & (m.DisplayFolder == lstDisplayFolders.Text | (m.DisplayFolder == null & lstDisplayFolders.Text == "None")))
                    lstMeasures.Items.Add(m.Name);
            }
            // Load Calculated Measures in selected Display Folder (or Measure Group):
            foreach (Microsoft.AnalysisServices.CalculationProperty cp in c.MdxScripts[0].CalculationProperties)
            {
                // Non-null AssociatedMeasureGroupID indicates this calculation is a Calculated Member, not a Calculated Set:
                if (!(cp.AssociatedMeasureGroupID == null))
                    if ((c.MeasureGroups[cp.AssociatedMeasureGroupID].Name == lstMeasureGroups.Text) & (cp.DisplayFolder == lstDisplayFolders.Text | (cp.DisplayFolder == null & lstDisplayFolders.Text == "None")))
                    {
                        // Confirm this is Calculated _Measure_ not Calculated Dimension Member by checking if cp.CalculationReference starts with "[MEASURES].":
                        if (cp.CalculationReference.Substring(0, cp.CalculationReference.IndexOf(".")).ToUpper() == "[MEASURES]")
                        {
                            // Strip CalcMeasName from "[MEASURES].[CalcMeasName]"

                            // Remove "[MEASURES].":
                            string CalcMeasName = cp.CalculationReference.Substring(cp.CalculationReference.IndexOf(".") + 1);
                            // Remove remaining square brackets:
                            CalcMeasName = CalcMeasName.Substring(1, CalcMeasName.Length - 2);

                            // Add the Calculated Measure to cboMeasures!:
                            lstMeasures.Items.Add(CalcMeasName);
                        }
                    }
            }

            // Select first Measure as default
            lstMeasures.SelectedIndex = 0;

        }

        private void btnAMODDL_Click(object sender, EventArgs e)
        {
            this.Cursor = Cursors.WaitCursor;

            //************************************************************************
            // Connect:
            //************************************************************************
            Microsoft.AnalysisServices.Server s = new Microsoft.AnalysisServices.Server();
            s.Connect(ConnectionString);
            //************************************************************************

            //************************************************************************
            // New Database
            //************************************************************************

            // Drop database if it already exists:
            if (s.Databases.Contains("NewDatabase"))
            {
                s.Databases.Remove("NewDatabase");
                s.Update();
            }

            // Create and add new database NewDatabase:
            Microsoft.AnalysisServices.Database dbNew = new Microsoft.AnalysisServices.Database("NewDatabase");
            s.Databases.Add(dbNew);
            dbNew.Update();
            //************************************************************************

            //************************************************************************
            // New Dimension
            //************************************************************************

            // Drop dimension if it already exists:
            Microsoft.AnalysisServices.Database dbAW = s.Databases.GetByName("Adventure Works DW");
            if (dbAW.Dimensions.Contains("NewDim"))
            {
                dbAW.Dimensions.Remove("NewDim");
                dbAW.Update();
            }

            // Create new dimension object NewDim:
            Microsoft.AnalysisServices.Dimension dimNew;
            dimNew = s.Databases.GetByName("Adventure Works DW").Dimensions.Add("NewDim");
            dimNew.Type = Microsoft.AnalysisServices.DimensionType.Regular;
            dimNew.UnknownMember = Microsoft.AnalysisServices.UnknownMemberBehavior.Visible;
            dimNew.AttributeAllMemberName = "All";
            dimNew.Source = new Microsoft.AnalysisServices.DataSourceViewBinding("Adventure Works DW");
            dimNew.StorageMode = Microsoft.AnalysisServices.DimensionStorageMode.Molap;

            // Create new dimension attribute NewAttr:
            Microsoft.AnalysisServices.DimensionAttribute atrNew = new Microsoft.AnalysisServices.DimensionAttribute("NewAttr");
            atrNew.KeyColumns.Add(new Microsoft.AnalysisServices.DataItem("dbo_DimPromotion", "EnglishPromotionCategory", System.Data.OleDb.OleDbType.WChar, 50));
            atrNew.Usage = Microsoft.AnalysisServices.AttributeUsage.Key;
            atrNew.OrderBy = Microsoft.AnalysisServices.OrderBy.Name;
            atrNew.ID = "EnglishPromotionCategory";
            atrNew.Type = Microsoft.AnalysisServices.AttributeType.Regular;

            // Add the new attribute to the dimension:
            dimNew.Attributes.Add(atrNew);
            dimNew.Update();
            //************************************************************************

            //************************************************************************
            // Populate cboCatalogs and preselect first one:
            //************************************************************************

            // Disconnect and reconnect to ensure accurate reading of Databases collection
            s.Disconnect();
            s.Connect(ConnectionString);

            // Loop thorugh databases collection and populate cboCatalogs:
            cboCatalogs.Items.Clear();
            foreach (Microsoft.AnalysisServices.Database d in s.Databases)
            {
                cboCatalogs.Items.Add(d.Name);
            }

            // UI
            cboCatalogs.Enabled = true;
            cboCatalogs.SelectedIndex = 0;
            //************************************************************************

            //************************************************************************
            // Disconnect
            //************************************************************************
            s.Disconnect();
            //************************************************************************

            this.Cursor = Cursors.Default;
        }

#endregion


#region UI
        private void frmMain_Load(object sender, EventArgs e)
        {
            cboQueryOptions.SelectedIndex = 0;

        }

        private void cboCubes_SelectedIndexChanged(object sender, EventArgs e)
        {
            btnFillDataBind.Enabled = (cboCubes.SelectedIndex != -1);
        }

        private void rdoMetadata_CheckedChanged(object sender, EventArgs e)
        {
            GroupBoxChange();
        }

        private void rdoData_CheckedChanged(object sender, EventArgs e)
        {
            GroupBoxChange();
        }

        private void GroupBoxChange()
        {
            grpData.Enabled = rdoData.Checked;
            grpMetadata.Enabled = rdoMetadata.Checked;
            txtMDX.Enabled = rdoData.Checked;
            cboCubes.Enabled = rdoMetadata.Checked;
            btnFillDataBind.Enabled = rdoData.Checked;
        }

        private void rdo2Axis_CheckedChanged(object sender, EventArgs e)
        {
            AxisCountChange();
        }

        private void rdo3Axis_CheckedChanged(object sender, EventArgs e)
        {
            AxisCountChange();
        }

        private void AxisCountChange()
        {
            if (rdo2Axis.Checked)
            {
                txtMDX.Text = "SELECT\t[Shippers].[Shipper Name].MEMBERS ON COLUMNS,\r\n\t[Time].[Year].MEMBERS ON ROWS\r\nFROM\t[Sales]\r\nWHERE\t[Measures].[Total Sales]";
            }
            else
            {
                txtMDX.Text = "SELECT\tShippers.[Shipper Name].MEMBERS ON COLUMNS,\r\n\tGeography.Country.MEMBERS ON ROWS,\r\n\tTime.Year.MEMBERS ON PAGES\r\nFROM\tSales\r\nWHERE\t(Measures.[Total Sales], Products.[Category Name].Condiments)";
            }
            btnFillDataBind.Enabled = rdo2Axis.Checked;
            btnExecXMLReader.Enabled = rdo2Axis.Checked;
            btnXmla.Enabled = rdo2Axis.Checked;
            btnExecCellSet.Enabled = rdo3Axis.Checked;
            lblPages.Enabled = rdo3Axis.Checked;
            cboPages.Enabled = false;
            cboPages.Items.Clear();
        }

#endregion


    } //class
} //namespace