C#中使用ADOMD.NET查詢多維數據集

2019-11-14 16:22:48

字體：大中小

來源：轉載

供稿：網友

ADOMD.NET 是用于與 Microsoft SQL Server Analysis Services 進行通信的 Microsoft .NET Framework 數據訪問接口。 ADOMD.NET 可使用 xml for Analysis 協議與分析數據源通信，方法為使用 TCP/ip 或 HTTP 連接傳輸和接收符合 XML for Analysis 規范的 SOAP 請求和響應。命令可通過多維表達式 (MDX)、數據挖掘擴展插件 (DMX)、Analysis Services 腳本語言 (ASSL) 或者甚至是有限 SQL 語法來發送，并且可能不返回結果。可以使用 ADOMD.NET 對象模型來查詢和操作分析數據、關鍵績效指標 (KPI) 和挖掘模型。使用 ADOMD.NET 時，還可通過檢索與 OLE DB 兼容的架構行集或者使用 ADOMD.NET 對象模型來查看和使用元數據。

ADOMD.NET 數據訪問接口由 Microsoft.AnalysisServices.AdomdClient 命名空間表示

ADOMD.NET安裝包下載地址：

實戰，連接并查詢多維數據集：

   string connectionString = "Data Source=localhost;Catalog=MDX Step-by-Step;ConnectTo=11.0;Integrated Security=SSPI";            AdomdConnection _connection = new AdomdConnection(connectionString);            if (_connection != null)                if (_connection.State == ConnectionState.Closed)                    _connection.Open();            AdomdCommand command = _connection.CreateCommand();            StringBuilder sb = new StringBuilder();            sb.Append("WITH");            sb.Append("  MEMBER [PRoduct].[Category].[All Products].[X] AS 1+1");            sb.Append("SELECT{ ([Date].[Calendar].[CY 2002]),([Date].[Calendar].[CY 2003])}*{([Measures].[Reseller Sales Amount]) } ON COLUMNS,");            sb.Append("{ ([Product].[Category].[accessories]),([Product].[Category].[Bikes]),([Product].[Category].[Clothing]),");            sb.Append("([Product].[Category].[Components]),([Product].[Category].[X])} ON ROWS");            sb.Append("  FROM [Step-by-Step]");            command.CommandText = sb.ToString();            var xmlreader = command.ExecuteXmlReader();            CellSet cellSet = CellSet.LoadXml(xmlreader);            _connection.Close();            var dt = ToDataTable(cellSet);           var v = dt.Rows.Count;

AdomdHelper.cs

using System;using System.Data;using Microsoft.AnalysisServices.AdomdClient;namespace Wpfapplication1{    /// <summary>    /// Summary description for AdomdHelper.    /// </summary>    public class AdomdHelper    {        #region "== Enum ============================================================"        public enum Versions        {            Server,            Provider,            Client        }        #endregion        #region "== Methods ============================================================"        //判斷連接AdomdConnection對象是State是否處于Open狀態。        public bool IsConnected(ref AdomdConnection connection)        {            return (!(connection == null)) && (connection.State != ConnectionState.Broken) && (connection.State != ConnectionState.Closed);        }        /// <summary>        /// 斷開連接        /// </summary>        /// <param name="connection">AdomdConnection對象的實例</param>        /// <param name="destroyConnection">是否銷毀連接</param>        public void Disconnect(ref AdomdConnection connection, bool destroyConnection)        {            try            {                if (!(connection == null))                {                    if (connection.State != ConnectionState.Closed)                    {                        connection.Close();                    }                    if (destroyConnection == true)                    {                        connection.Dispose();                        connection = null;                    }                }            }            catch (Exception ex)            {                throw ex;            }        }        /// <summary>        /// 建立連接        /// </summary>        /// <param name="connection">AdomdConnection對象的實例</param>        /// <param name="connectionString">連接字符串</param>        public void Connect(ref AdomdConnection connection, string connectionString)        {            if (connectionString == "")                throw new ArgumentNullException("connectionString", "The connection string is not valid.");            //    Ensure an AdomdConnection object exists and that its ConnectionString property is set.            if (connection == null)                connection = new AdomdConnection(connectionString);            else            {                Disconnect(ref connection, false);                connection.ConnectionString = connectionString;            }            try            {                connection.Open();            }            catch (Exception ex)            {                throw ex;            }        }        /// <summary>        /// 獲取OLAP數據庫。        /// </summary>        /// <param name="connection">AdomdConnection對象的實例</param>        /// <param name="connectionString">連接字符串</param>        /// <returns></returns>        public DataTable GetSchemaDataSet_Catalogs(ref AdomdConnection connection, string connectionString)        {            bool connected = true;    //判斷connection在調用此函數時，是否已經處于連接狀態            DataTable objTable = new DataTable();            try            {                // Check if a valid connection was provided.                if (IsConnected(ref connection) == false)                {                    //如果連接不存在，則建立連接                    Connect(ref connection, connectionString);                    connected = false;       //更改connection為未連接狀態。                        }                objTable = connection.GetSchemaDataSet(AdomdSchemaGuid.Catalogs, null).Tables[0];                if (connected == false)                {                    //關閉連接                    Disconnect(ref connection, false);                }            }            catch (Exception err)            {                throw err;            }            return objTable;        }        /// <summary>        /// 通過SchemaDataSet的方式獲取立方體        /// </summary>        /// <param name="connection">AdomdConnection對象的實例</param>        /// <param name="connectionString">連接字符串</param>        /// <returns></returns>        public string[] GetSchemaDataSet_Cubes(ref AdomdConnection connection, string connectionString)        {            string[] strCubes = null;            bool connected = true;   //判斷connection是否已與數據庫連接            DataTable objTable = new DataTable();            if (IsConnected(ref connection) == false)            {                try                {                    Connect(ref connection, connectionString);                    connected = false;                }                catch (Exception err)                {                    throw err;                }            }            string[] strRestriction = new string[] { null, null, null };            objTable = connection.GetSchemaDataSet(AdomdSchemaGuid.Cubes, strRestriction).Tables[0];            if (connected == false)            {                Disconnect(ref connection, false);            }            strCubes = new string[objTable.Rows.Count];            int rowcount = 0;            foreach (DataRow tempRow in objTable.Rows)            {                strCubes[rowcount] = tempRow["CUBE_NAME"].ToString();                rowcount++;            }            return strCubes;        }        /// <summary>        /// 通過SchemaDataSet的方式獲取制定立方體的維度        /// </summary>        /// <param name="connection">AdomdConnection對象的實例</param>        /// <param name="connectionString">連接字符串</param>        /// <returns></returns>        public string[] GetSchemaDataSet_Dimensions(ref AdomdConnection connection, string connectionString, string cubeName)        {            string[] strDimensions = null;            bool connected = true;   //判斷connection是否已與數據庫連接            DataTable objTable = new DataTable();            if (IsConnected(ref connection) == false)            {                try                {                    Connect(ref connection, connectionString);                    connected = false;                }                catch (Exception err)                {                    throw err;                }            }            string[] strRestriction = new string[] { null, null, cubeName, null, null };            objTable = connection.GetSchemaDataSet(AdomdSchemaGuid.Dimensions, strRestriction).Tables[0];            if (connected == false)            {                Disconnect(ref connection, false);            }            strDimensions = new string[objTable.Rows.Count];            int rowcount = 0;            foreach (DataRow tempRow in objTable.Rows)            {                strDimensions[rowcount] = tempRow["DIMENSION_NAME"].ToString();                rowcount++;            }            return strDimensions;        }        /// <summary>        /// 以connection的方式獲取立方體        /// </summary>        /// <param name="connection">AdomdConnection對象的實例</param>        /// <param name="connectionString">連接字符串</param>        /// <returns></returns>        public string[] GetCubes(ref AdomdConnection connection, string connectionString)        {            string[] strCubesName = null;            bool connected = true;   //判斷connection是否已與數據庫連接            if (IsConnected(ref connection) == false)            {                try                {                    Connect(ref connection, connection.ConnectionString);                    connected = false;                }                catch (Exception err)                {                    throw err;                }            }            int rowcount = connection.Cubes.Count;            strCubesName = new string[rowcount];            for (int i = 0; i < rowcount; i++)            {                strCubesName[i] = connection.Cubes[i].Caption;            }            if (connected == false)            {                Disconnect(ref connection, false);            }            return strCubesName;        }        /// <summary>        /// 獲取立方體的維度        /// </summary>        /// <param name="connection">AdomdConnection對象的實例</param>        /// <param name="connectionString">連接字符串</param>        /// <param name="CubeName">立方體名稱</param>        /// <returns></returns>        public string[] GetDimensions(ref AdomdConnection connection, string connectionString, string CubeName)        {            string[] strDimensions = null;            bool connected = true;            if (IsConnected(ref connection) == false)            {                try                {                    Connect(ref connection, connection.ConnectionString);                    connected = false;                }                catch (Exception err)                {                    throw err;                }            }            int rowcount = connection.Cubes[CubeName].Dimensions.Count;            strDimensions = new string[rowcount];            for (int i = 0; i < rowcount; i++)            {                strDimensions[i] = connection.Cubes[CubeName].Dimensions[i].Caption.ToString();            }            if (connected == false)            {                Disconnect(ref connection, false);            }            return strDimensions;        }        #endregion    }}