C#中使用ADOMD.NET查詢多維數(shù)據(jù)集的實(shí)現(xiàn)方法
本文實(shí)例講述了C#中使用ADOMD.NET查詢多維數(shù)據(jù)集的實(shí)現(xiàn)方法，分享給大家供大家參考。具體實(shí)現(xiàn)方法分析如下：
ADOMD.NET 是用于與 Microsoft SQL Server Analysis Services 進(jìn)行通信的 Microsoft .NET Framework 數(shù)據(jù)訪問接口。 ADOMD.NET 可使用 XML for Analysis 協(xié)議與分析數(shù)據(jù)源通信，方法為使用 TCP/IP 或 HTTP 連接傳輸和接收符合 XML for Analysis 規(guī)范的 SOAP 請(qǐng)求和響應(yīng)。 命令可通過(guò)多維表達(dá)式 (MDX)、數(shù)據(jù)挖掘擴(kuò)展插件 (DMX)、Analysis Services 腳本語(yǔ)言 (ASSL) 或者甚至是有限 SQL 語(yǔ)法來(lái)發(fā)送，并且可能不返回結(jié)果。 可以使用 ADOMD.NET 對(duì)象模型來(lái)查詢和操作分析數(shù)據(jù)、關(guān)鍵績(jī)效指標(biāo) (KPI) 和挖掘模型。 使用 ADOMD.NET 時(shí)，還可通過(guò)檢索與 OLE DB 兼容的架構(gòu)行集或者使用 ADOMD.NET 對(duì)象模型來(lái)查看和使用元數(shù)據(jù)。
ADOMD.NET 數(shù)據(jù)訪問接口由 Microsoft.AnalysisServices.AdomdClient 命名空間表示
ADOMD.NET安裝包點(diǎn)擊此處下載：
實(shí)戰(zhàn)，連接并查詢多維數(shù)據(jù)集：
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.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對(duì)象是State是否處于Open狀態(tài)。
public bool IsConnected(ref AdomdConnection connection)
{
return (!(connection == null)) && (connection.State != ConnectionState.Broken) && (connection.State != ConnectionState.Closed);
}
/// <summary>
/// 斷開連接
/// </summary>
/// <param name="connection">AdomdConnection對(duì)象的實(shí)例</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對(duì)象的實(shí)例</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數(shù)據(jù)庫(kù)。
/// </summary>
/// <param name="connection">AdomdConnection對(duì)象的實(shí)例</param>
/// <param name="connectionString">連接字符串</param>
/// <returns></returns>
public DataTable GetSchemaDataSet_Catalogs(ref AdomdConnection connection, string connectionString)
{
bool connected = true; //判斷connection在調(diào)用此函數(shù)時(shí)，是否已經(jīng)處于連接狀態(tài)
DataTable objTable = new DataTable();
try
{
// Check if a valid connection was provided.
if (IsConnected(ref connection) == false)
{
//如果連接不存在，則建立連接
Connect(ref connection, connectionString);
connected = false; //更改connection為未連接狀態(tài)。
}
objTable = connection.GetSchemaDataSet(AdomdSchemaGuid.Catalogs, null).Tables[0];
if (connected == false)
{
//關(guān)閉連接
Disconnect(ref connection, false);
}
}
catch (Exception err)
{
throw err;
}
return objTable;
}
/// <summary>
/// 通過(guò)SchemaDataSet的方式獲取立方體
/// </summary>
/// <param name="connection">AdomdConnection對(duì)象的實(shí)例</param>
/// <param name="connectionString">連接字符串</param>
/// <returns></returns>
public string[] GetSchemaDataSet_Cubes(ref AdomdConnection connection, string connectionString)
{
string[] strCubes = null;
bool connected = true; //判斷connection是否已與數(shù)據(jù)庫(kù)連接
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>
/// 通過(guò)SchemaDataSet的方式獲取制定立方體的維度
/// </summary>
/// <param name="connection">AdomdConnection對(duì)象的實(shí)例</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是否已與數(shù)據(jù)庫(kù)連接
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對(duì)象的實(shí)例</param>
/// <param name="connectionString">連接字符串</param>
/// <returns></returns>
public string[] GetCubes(ref AdomdConnection connection, string connectionString)
{
string[] strCubesName = null;
bool connected = true; //判斷connection是否已與數(shù)據(jù)庫(kù)連接
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對(duì)象的實(shí)例</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
}
}
完整實(shí)例代碼點(diǎn)擊此處本站下載。
希望本文所述對(duì)大家的C#程序設(shè)計(jì)有所幫助。
- c#反射表達(dá)式樹模糊搜索示例
- C#使用SqlBulkCopy批量復(fù)制數(shù)據(jù)到數(shù)據(jù)表
- C#中使用HttpDownLoadHelper下載文件實(shí)例
- C#中Forms.Timer、Timers.Timer、Threading.Timer的用法分析
- C#獲取真實(shí)IP地址實(shí)現(xiàn)方法
- C#調(diào)用sql2000存儲(chǔ)過(guò)程方法小結(jié)
- C#線程同步的三類情景分析
- C#生成隨機(jī)驗(yàn)證碼代碼分享
- C#泛型集合Dictionary<K,V>的使用方法
- C#中Dictionary的作用及用法講解
- C#之Expression表達(dá)式樹實(shí)例
相關(guān)文章
這篇文章主要介紹了C#使用ML.Net完成人工智能預(yù)測(cè)的詳細(xì)教程，本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2020-12-12
C#實(shí)現(xiàn)簡(jiǎn)單的計(jì)算器小程序
這篇文章主要為大家詳細(xì)介紹了C#實(shí)現(xiàn)簡(jiǎn)單的計(jì)算器小程序，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2022-01-01
我們給大家總計(jì)了C#中判斷英文單詞個(gè)數(shù)的方法以及排序的技巧，對(duì)此有需要的朋友可以測(cè)試下。2018-03-03
Unity實(shí)現(xiàn)角色受擊身體邊緣發(fā)光特效
這篇文章主要為大家詳細(xì)介紹了Unity實(shí)現(xiàn)角色受擊身體邊緣發(fā)光特效，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2020-04-04
這篇文章主要為大家詳細(xì)介紹了C#使用FileStream對(duì)象讀寫文件，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2019-05-05
基于WPF實(shí)現(xiàn)路徑圖標(biāo)控件
這篇文章主要介紹了如何利用WPF實(shí)現(xiàn)路徑圖標(biāo)控件，文中的示例代碼講解詳細(xì)，對(duì)我們學(xué)習(xí)或工作有一定幫助，需要的小伙伴可以參考一下2023-07-07
使用C#實(shí)現(xiàn)將Word?轉(zhuǎn)文本存儲(chǔ)到數(shù)據(jù)庫(kù)并進(jìn)行管理
這篇文章主要為大家詳細(xì)介紹了如何使用C#實(shí)現(xiàn)將Word?轉(zhuǎn)文本存儲(chǔ)到數(shù)據(jù)庫(kù)并進(jìn)行管理,文中的示例代碼講解詳細(xì),需要的小伙伴可以參考一下2024-03-03
本文主要介紹了C#調(diào)用C++的基本規(guī)則和方法,包括內(nèi)存對(duì)齊、調(diào)用約定、基本數(shù)據(jù)類型的傳遞、結(jié)構(gòu)體的傳遞以及數(shù)組的傳遞等,感興趣的可以了解一下2024-11-11
這篇文章主要介紹了c# 托管和非托管資源的相關(guān)資料，文中講解非常細(xì)致，幫助大家更好的理解和學(xué)習(xí)，感興趣的朋友可以了解下2020-07-07
WPF實(shí)現(xiàn)類似360安全衛(wèi)士界面的程序源碼分享
最近在網(wǎng)上看到了新版的360安全衛(wèi)士，感覺界面還不錯(cuò)，于是用WPF制作了一個(gè)，時(shí)間有限，一些具體的控件沒有制作，用圖片代替了。感興趣的朋友一起跟著小編學(xué)習(xí)WPF實(shí)現(xiàn)類似360安全衛(wèi)士界面的程序源碼分享2015-09-09