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