C#中使用ADOMD.NET查询多维数据集的实现方法

本文实例讲述了C#中使用ADOMD.NET查询多维数据集的实现方法,分享给大家供大家参考。具体实现方法分析如下:

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
    }
}

完整实例代码点击此处本站下载。

希望本文所述对大家的C#程序设计有所帮助。

(0)

相关推荐

  • C#获取真实IP地址实现方法

    本文实例讲述了C#获取真实IP地址实现方法,分享给大家供大家参考.具体实现方法如下: 通常来说,大家获取用户IP地址常用的方法是: 复制代码 代码如下: string IpAddress = ""; if((HttpContext.Current.Request.ServerVariables["HTTP_X_FORWARDED_FOR"]!=null && HttpContext.Current.Request.ServerVariables[&q

  • C#中使用HttpDownLoadHelper下载文件实例

    本文实例讲述了C#使用HttpDownLoadHelper下载文件的方法.分享给大家供大家参考.具体实现方法如下: 复制代码 代码如下: using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.IO; using System.Threading; namespace ProjectWenDangManage.Framework {     /// <sum

  • C#调用sql2000存储过程方法小结

    本文实例总结了C#调用sql2000存储过程方法,分享给大家供大家参考.具体实现方法如下: 调试环境:VS.NET 2005,SQL 2000,WINDOWS XP sp2. 语言:C# 一.调用带输入参数的存储过程 首先自然是在查询分析器里创建一个存储过程喽~~   如下所示: 复制代码 代码如下: create proc proc_1 @uid int, @pwd varchar(255) as select UserName from users where uid = @uid and

  • C#中Forms.Timer、Timers.Timer、Threading.Timer的用法分析

    本文实例讲述了C#中Forms.Timer.Timers.Timer.Threading.Timer的用法分析,分享给大家供大家参考.具体分析如下: 在.NET Framework里面提供了三种Timer ① System.Windows.Forms.Timer ② System.Timers.Timer ③ System.Threading.Timer 现分述如下: 一.System.Windows.Forms.Timer 1.基于Windows消息循环,用事件方式触发,在界面线程执行:是使用

  • C#之Expression表达式树实例

    本文实例讲述了C#之Expression表达式树,分享给大家供大家参考.具体实现方法如下: 表达式树表示树状数据结构的代码,树状结构中的每个节点都是一个表达式,例如一个方法调用或类似 x < y 的二元运算 1.利用 Lambda 表达式创建表达式树 复制代码 代码如下: Expression<Func<int, int, int, int>> expr = (x, y, z) => (x + y) / z; 2.编译表达式树,该方法将表达式树表示的代码编译成一个可执行

  • C#生成随机验证码代码分享

    使用YZMHelper帮助类即可 复制代码 代码如下: using System; using System.Web; using System.Drawing; using System.Security.Cryptography; namespace ProjectWenDangManage.Framework {     /// <summary>     /// 验证码类     /// </summary>     public class Rand     {     

  • C#使用SqlBulkCopy批量复制数据到数据表

    本文实例讲述了C#使用SqlBulkCopy批量复制数据到数据表的方法.分享给大家供大家参考.具体实现方法如下: 使用 SqlBulkCopy 类只能向 SQL Server 表写入数据.但是,数据源不限于 SQL Server:可以使用任何数据源,只要数据可加载到 DataTable 实例或可使用 IDataReader 实例读取数据 1.使用Datatable作为数据源的方式: 下面的代码使用到了ColumnMappings,因为目标表和数据源Datatable的结构不一致,需要这么一个映射

  • C#泛型集合Dictionary<K,V>的使用方法

    1.要使用Dictionary集合,需要导入C#泛型命名空间 System.Collections.Generic(程序集:mscorlib) 2.描述 1).从一组键(Key)到一组值(Value)的映射,每一个添加项都是由一个值及其相关连的键组成 2).任何键都必须是唯一的 3).键不能为空引用null(VB中的Nothing),若值为引用类型,则可以为空值 4).Key和Value可以是任何类型(string,int,custom class 等) 3.创建及初始化 复制代码 代码如下:

  • C#中Dictionary的作用及用法讲解

    Dictionary<string, string>是一个泛型 他本身有集合的功能有时候可以把它看成数组 他的结构是这样的:Dictionary<[key], [value]> 他的特点是存入对象是需要与[key]值一一对应的存入该泛型 通过某一个一定的[key]去找到对应的值 举个例子: 复制代码 代码如下: //实例化对象 Dictionary<int, string> dic = new Dictionary<int, string>(); //对象打

  • c#反射表达式树模糊搜索示例

    复制代码 代码如下: public static Expression<Func<T, bool>> GetSearchExpression<T>(string SearchString)        {            Expression<Func<T, bool>> filter = null; if (string.IsNullOrEmpty(SearchString)) return null;            var l

  • C#线程同步的三类情景分析

    本文实例讲述了C#线程同步的三类情景,分享给大家供大家参考.具体分析如下: C# 已经提供了我们几种非常好用的类库如 BackgroundWorker.Thread.Task等,借助它们,我们就能够分分钟编写出一个多线程的应用程序. 比如这样一个需求:有一个 Winform 窗体,点击按钮后,会将窗体中的数据导出到一个 output.pdf 文件中.原先的代码没有采用多线程技术,所以当点击按钮后,整个窗体就变成无响应了.为了解决这个问题,可以使用 Task.Run(()=>{...导出文件的代码

随机推荐