C#中使用ADOMD.NET查詢多維數(shù)據(jù)集的實現(xiàn)方法
本文實例講述了C#中使用ADOMD.NET查詢多維數(shù)據(jù)集的實現(xiàn)方法，分享給大家供大家參考。具體實現(xiàn)方法分析如下：
ADOMD.NET 是用于與 Microsoft SQL Server Analysis Services 進行通信的 Microsoft .NET Framework 數(shù)據(jù)訪問接口。 ADOMD.NET 可使用 XML for Analysis 協(xié)議與分析數(shù)據(jù)源通信，方法為使用 TCP/IP 或 HTTP 連接傳輸和接收符合 XML for Analysis 規(guī)范的 SOAP 請求和響應(yīng)。 命令可通過多維表達式 (MDX)、數(shù)據(jù)挖掘擴展插件 (DMX)、Analysis Services 腳本語言 (ASSL) 或者甚至是有限 SQL 語法來發(fā)送，并且可能不返回結(jié)果。 可以使用 ADOMD.NET 對象模型來查詢和操作分析數(shù)據(jù)、關(guān)鍵績效指標 (KPI) 和挖掘模型。 使用 ADOMD.NET 時，還可通過檢索與 OLE DB 兼容的架構(gòu)行集或者使用 ADOMD.NET 對象模型來查看和使用元數(shù)據(jù)。
ADOMD.NET 數(shù)據(jù)訪問接口由 Microsoft.AnalysisServices.AdomdClient 命名空間表示
ADOMD.NET安裝包點擊此處下載：
實戰(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對象是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對象的實例</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數(shù)據(jù)庫。
/// </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在調(diào)用此函數(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>
/// 通過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是否已與數(shù)據(jù)庫連接
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是否已與數(shù)據(jù)庫連接
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是否已與數(shù)據(jù)庫連接
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#程序設(shè)計有所幫助。
相關(guān)文章
這篇文章主要介紹了C#使用ML.Net完成人工智能預(yù)測的詳細教程，本文給大家介紹的非常詳細，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下2020-12-12
這篇文章主要為大家詳細介紹了C#實現(xiàn)簡單的計算器小程序，文中示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2022-01-01
我們給大家總計了C#中判斷英文單詞個數(shù)的方法以及排序的技巧，對此有需要的朋友可以測試下。2018-03-03
這篇文章主要為大家詳細介紹了Unity實現(xiàn)角色受擊身體邊緣發(fā)光特效，文中示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2020-04-04
這篇文章主要為大家詳細介紹了C#使用FileStream對象讀寫文件，具有一定的參考價值，感興趣的小伙伴們可以參考一下2019-05-05
這篇文章主要介紹了如何利用WPF實現(xiàn)路徑圖標控件，文中的示例代碼講解詳細，對我們學(xué)習(xí)或工作有一定幫助，需要的小伙伴可以參考一下2023-07-07
使用C#實現(xiàn)將Word?轉(zhuǎn)文本存儲到數(shù)據(jù)庫并進行管理
這篇文章主要為大家詳細介紹了如何使用C#實現(xiàn)將Word?轉(zhuǎn)文本存儲到數(shù)據(jù)庫并進行管理,文中的示例代碼講解詳細,需要的小伙伴可以參考一下2024-03-03
本文主要介紹了C#調(diào)用C++的基本規(guī)則和方法,包括內(nèi)存對齊、調(diào)用約定、基本數(shù)據(jù)類型的傳遞、結(jié)構(gòu)體的傳遞以及數(shù)組的傳遞等,感興趣的可以了解一下2024-11-11
這篇文章主要介紹了c# 托管和非托管資源的相關(guān)資料，文中講解非常細致，幫助大家更好的理解和學(xué)習(xí)，感興趣的朋友可以了解下2020-07-07
WPF實現(xiàn)類似360安全衛(wèi)士界面的程序源碼分享
最近在網(wǎng)上看到了新版的360安全衛(wèi)士，感覺界面還不錯，于是用WPF制作了一個，時間有限，一些具體的控件沒有制作，用圖片代替了。感興趣的朋友一起跟著小編學(xué)習(xí)WPF實現(xiàn)類似360安全衛(wèi)士界面的程序源碼分享2015-09-09