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ù)訪問(wèn)接口。 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ù)訪問(wèn)接口由 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>
/// 斷開(kāi)連接
/// </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á)式樹(shù)模糊搜索示例
- 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ù)實(shí)例
相關(guān)文章
c#使用htmlagilitypack解析html格式字符串
這篇文章主要介紹了c#使用htmlagilitypack解析html格式字符串的示例,需要的朋友可以參考下2014-03-03
這篇文章主要為大家詳細(xì)介紹了Unity實(shí)現(xiàn)圓形Image組件，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2022-01-01
C#使用表達(dá)式樹(shù)(LambdaExpression)動(dòng)態(tài)更新類的屬性值(示例代碼)
這篇文章主要介紹了C#使用表達(dá)式樹(shù)(LambdaExpression)動(dòng)態(tài)更新類的屬性值,本文通過(guò)示例代碼給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-01-01
本文主要介紹了c#多進(jìn)程通訊的實(shí)現(xiàn)示例，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2022-05-05
這篇文章介紹了C#自定義集合初始化器的方法，文中通過(guò)示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-07-07
C# 實(shí)現(xiàn)SDL2進(jìn)行視頻播放窗口截圖和字幕添加
這篇文章主要介紹了C# 實(shí)現(xiàn)SDL2進(jìn)行視頻播放窗口截圖和字幕添加，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-12-12
這篇文章主要為大家詳細(xì)介紹了Unity3D選擇本地圖片并加載，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2019-07-07
這篇文章主要介紹了C#之lock的使用及說(shuō)明，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-06-06
這篇文章主要為大家詳細(xì)介紹了C#控制臺(tái)程序使用Log4net日志組件，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-05-05
C#中的隨機(jī)數(shù)函數(shù)Random()
這篇文章介紹了C#生成隨機(jī)數(shù)的方法，文中通過(guò)示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-05-05