ADO NET Programming-ADO MD Example

ADO MD Example

There is no ADO MD equivalent in ADO .NET. However, it is expected that Microsoft will release some ADO MD functionality that runs natively on the .NET Framework with the next version of Analysis Services. For now, the best way for us to continue programming Analysis Services in .NET is to utilize ActiveX controls provided for Analysis Services. Of course, you can use the OLEDB .NET Data Provider and connect to the database through the OLEDB Provider for OLAP. Even then, you will have to write a lot of unmanaged code that will have a direct impact on application performance since OLEDB Provider for OLAP runs on top of the PivotTable Service, which is implemented as COM.

There are several ActiveX controls deployed with Analysis Services. The one that is the most illustrative and helpful is the CubeBrowser ActiveX control.

Using the CubeBrowser ActiveX Control

In this example, we look at how we can use the CubeBrowser ActiveX control to view multidimensional data inside client applications. There are several advantages and disadvantages to using this control in your applications.

The main advantage of the CubeBrowser control is that it is straightforward and there is not much code needed to populate it with data. In fact, all you need to specify are the server that it should connect to and the cube that it should display. This saves you the trouble of writing a lot of unmanaged code that would have to go through the COM interoperability services.

There are two main disadvantages to using this control: First, keep in mind that the control is based on the COM computing architecture. Although you may not visually notice anything at design time, the .NET Common Language Runtime wraps the ActiveX control inside several COM interoperability service libraries when the application runs. Your application may end up having a lot of performance problems. Additionally, you will have a lot of trouble deploying your application, especially if it is a client-server application with multiple users. It would be required for you to deploy and register the CubeBrowser control on every client machine running the application. Without a sophisticated setup program, this can be quite a pain.

Although the disadvantages may seem to be enormous, I believe that using this control is a much better approach than using the OLEDB .NET Data Provider. As mentioned above, the control does not require you to write a lot of code, which means that when the OLAP services are introduced inside ADO .NET, you would only need to delete the control, its references, and the other references to the COM interoperability service libraries. Then you can start writing managed code for the OLAP services. This task is much less arduous than having to convert unmanaged code.

Now that we have explored the pros and cons, let’s get to work. In this example, you would be using the control on a Windows Form object. To view the code for this example, open the Example 2 folder under the 09-Examples Windows Form project on the companion CD and open the frmOLAPManip1 form object.

The first step is to add the control to your toolbox:

Open the General tab on the toolbox.
Right-click inside the tab and click the Customize Toolbox option on the pop-up menu. This loads the Customize Toolbox dialog.
The control is an ActiveX control and based on COM, so select the COM Components tab. The dialog populates a list of available ActiveX controls.
Scroll down the list and select the control called OLAPCubeBrowser.CubeBrowser, as illustrated in Figure 9-9.

Figure 9-9: Selecting the OLAPCubeBrowser.CubeBrowser control
Click the OK button. The CubeBrowser control is added to the General tab of the toolbox.

The second step involves referencing ADO MD in your application:

From the Project menu, click Add Reference. This brings up the Add Reference window.
ADO MD is a COM library, so click on the COM tab to list all the COM libraries registered on your machine.
Click on the Microsoft ActiveX Data Objects (Multi-dimensional) 2.7 Library COM library. Click the Select button. This adds the library to the Selected Components list box, as illustrated in Figure 9-10.

Figure 9-10: Adding a library to the Selected Components list box
Click the OK button. Visual Studio adds references to the following libraries:
- ADODB: This is the legacy COM ADO library. It is referenced automatically because ADO MD requires ADO’s Connection object to connect to data sources. Do not confuse ADO DB with the ADO .NET classes found inside the System.Data namespace.
- ADOMD: This is the ADO MD class library that is required by the CubeBrowser control.

The third step involves adding the control to the form and writing some code.

Drag the CubeBrowser control from the General tab onto the frmOLAPManip1 form. Visual Studio adds references to the following libraries:
- AxOLAPCubeBrowser: This is a wrapper for the CubeBrowser control. Visual Studio automatically creates a wrapper for every ActiveX control used in an application. This allows your control to run in a managed .NET environment, and it also facilitates your job as a programmer since it exposes the control as a fully featured Windows Form Control. This is something that some developers pay no attention to. It is important for you to understand that this wrapper class library is going through COM interoperability services at run time.
- MSComctLib: This is a COM class library that contains the implementation and definition of the Microsoft Windows Common Controls. Since some of these controls are used in the implementation of the CubeBrowser, this library is also automatically referenced.
- OLAPCubeBrowser: This is the class library for the CubeBrowser control.
- stdole: The COM architecture provides a standard implementation of general services, such as fonts, control locations, and positions of OLE components through interfaces. For example, a standard implementation of font objects is provided by the IFontDisp interface. The functionality is encapsulated by the StdFont object found within the stdole class library.
- These interfaces are implemented inside the stdole class library. This library is automatically referenced in the background when each COM component is compiled. Therefore, it is referenced automatically by Visual Studio.
Open the Property window for the control and rename it AxCubeBrowser.

The fourth step involves adding a few tweaks to the user interface and writing the code that will manipulate the CubeBrowser.

The CubeBrowser and the ComboBox would need to connect to and retrieve data from the database. For this, we will need a global connection string, a connection object, and a procedure to connect to the database. Add the following declarations in the code module to the form:

    Dim strConn As String
    Dim objADOConn As Object

A procedure to connect to the database is implemented like this:

    Sub Connect()
        'Connection string
        strConn = "Data Source= LOCALHOST; Provider=
          MSOLAP;" _
                & "Database=FoodMart 2000;UserId=;" _
                & "Password=;"
        'Creates an ADO connection object.
        objADOConn = CreateObject("ADODB.Connection")

        With objADOConn
            'Sets the connection string
            .ConnectionString = strConn
            'Connect to the database
            .open()
        End With
    End Sub

Add a ComboBox control to the form object and name it cbxCubeList. This control will hold the list of cubes present in the OLAP database. We already know how to do this, so let’s go ahead and code a procedure that will do it. The following code snippet is the implementation of the GetCubes() procedure and the call to Connect() and GetCubes() in the form’s Load event:

    Sub GetCubes()
        'Object to hold the Catalog object
        Dim objCatalog As Object
        'Object to hold one particular cube
        Dim Cube As Object

        'Instantiates a catalog object
        objCatalog = CreateObject("ADOMD.Catalog")

        'Set up the combo box
        With cbxCubeList
            .Items.Clear()
            .DropDownStyle = ComboBoxStyle.DropDownList
            .Sorted = True
        End With

        With objCatalog
            'Connect the catalog object to the (database)
            .ActiveConnection = strConn
            'Populate the drop down
            For Each Cube In .CubeDefs
                With cbxCubeList
                    .Items.Add(Cube.name)
                End With
            Next
        End With

        'Free memory
        objCatalog = Nothing
    End Sub

    Private Sub frmOLAPManip2_Load( _
        ByVal sender As System.Object, _
        ByVal e As System.EventArgs) _
        Handles MyBase.Load

        Connect()
        GetCubes()

    End Sub

User interface is everything! Let’s add some code to help our controls resize seamlessly:

   Private Sub frmOLAPManip1_Resize( _
        ByVal sender As Object, _
        ByVal e As System.EventArgs) _
        Handles MyBase.Resize

        With AxCubeBrowser
            .Left = 1
            .Width = Me.ClientSize.Width - 10
            .Height = Me.ClientSize.Height -
              cbxCubeList.Height - 10
        End With
        With cbxCubeList
            .Width = Me.ClientSize.Width
        End With
    End Sub

When the user selects a cube from the drop-down list, we want the CubeBrowser control to show information about the cube. This logic is implemented inside the cbxCubeList’s SelectedIndexChanged event handler:

    Private Sub cbxCubeList_SelectedIndexChanged( _
        ByVal sender As System.Object, _
        ByVal e As System.EventArgs) _
        Handles cbxCubeList.SelectedIndexChanged

        With AxCubeBrowser
            'Connect the browser to the database
            'and specify the cube that you want to 
            'browse.
            .Connect(objADOConn, cbxCubeList.SelectedItem)
        End With

    End Sub

This is all that is required to browse a cube using the CubeBrowser! As you can see, not much logic or unmanaged code is required. So I recommend that you take this approach for cube browsing.

The following code snippet is the full implementation of the frmOLAPManip1 class:

Public Class frmOLAPManip1
    Inherits System.Windows.Forms.Form

    Dim strConn As String
    Dim objADOConn As Object

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form
        'Designer.
        InitializeComponent()

        'Add any initialization after the
        'InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component
    'list.
    Protected Overloads Overrides Sub Dispose
      (ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the
    'Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents AxCubeBrowser As
      AxOLAPCubeBrowser.AxCubeBrowser
    Friend WithEvents cbxCubeList As
      System.Windows.Forms.ComboBox

    <System.Diagnostics.DebuggerStepThrough()> Private Sub
       InitializeComponent()
        Dim resources As System.Resources.ResourceManager
            = New System.Resources.ResourceManager
            (GetType(frmOLAPManip1))
          Me.AxCubeBrowser = New
            AxOLAPCubeBrowser.AxCubeBrowser()
          Me.cbxCubeList = New
            System.Windows.Forms.ComboBox()
          CType(Me.AxCubeBrowser, System.ComponentModel.
            ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'AxCubeBrowser
        '
        Me.AxCubeBrowser.Enabled = True
        Me.AxCubeBrowser.Location = New
          System.Drawing.Point(0, 24)
          Me.AxCubeBrowser.Name = "AxCubeBrowser"
          Me.AxCubeBrowser.OcxState =
            CType(resources.GetObject("AxCubeBrowser.
            OcxState"), System.Windows.Forms.AxHost.
            State)
        Me.AxCubeBrowser.Size = New
           System.Drawing.Size(448, 384)
        Me.AxCubeBrowser.TabIndex = 0
        '
        'cbxCubeList
        '
        Me.cbxCubeList.DropDownStyle =
          System.Windows.Forms.ComboBoxStyle.Simple
        Me.cbxCubeList.Name = "cbxCubeList"
        Me.cbxCubeList.Size = New System.Drawing.Size(448,
          21)
        Me.cbxCubeList.TabIndex = 1
        '
        'frmOLAPManip1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5,
          13)
        Me.ClientSize = New System.Drawing.Size(456, 411)
        Me.Controls.AddRange(New System.Windows.Forms.
          Control() {Me.cbxCubeList, Me.AxCubeBrowser})
        Me.Name = "frmOLAPManip1"
        Me.Text = "Olap Example 1"
        CType(Me.AxCubeBrowser, System.ComponentModel.
          ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub frmOLAPManip2_Load( _
        ByVal sender As System.Object, _
        ByVal e As System.EventArgs) _
        Handles MyBase.Load

        Connect()
        GetCubes()
    End Sub

    Private Sub frmOLAPManip1_Resize( _
        ByVal sender As Object, _
        ByVal e As System.EventArgs) _
        Handles MyBase.Resize

        With AxCubeBrowser
            .Left = 1
            .Width = Me.ClientSize.Width - 10
            .Height = Me.ClientSize.Height -
                cbxCubeList.Height - 10
        End With
        With cbxCubeList
            .Width = Me.ClientSize.Width
        End With
    End Sub


    Sub Connect()
        'Connection string
        strConn = "Data Source= LOCALHOST; Provider=
                  MSOLAP;" _
                & "Database=FoodMart 2000;UserId=;" _
                & "Password=;"
        'Creates an ADO connection object.
        objADOConn = CreateObject("ADODB.Connection")

        With objADOConn
            'Sets the connection string
            .ConnectionString = strConn
            'Connect to the database
            .open()
        End With
    End Sub

    Private Sub cbxCubeList_SelectedIndexChanged( _
        ByVal sender As System.Object, _
        ByVal e As System.EventArgs) _
        Handles cbxCubeList.SelectedIndexChanged

        With AxCubeBrowser
            'Connect the browser to the database
            'and specify the cube that you want to 
            'browse.
            .Connect(objADOConn, cbxCubeList.SelectedItem)
        End With
    End Sub


    Sub GetCubes()
        'Object to hold the Catalog object
        Dim objCatalog As Object
        'Object to hold one particular cube
        Dim Cube As Object

        'Instantiates a catalog object
        objCatalog = CreateObject("ADOMD.Catalog")

        'Set up the combo box
        With cbxCubeList
            .Items.Clear()
            .DropDownStyle = ComboBoxStyle.DropDownList
            .Sorted = True
        End With

        With objCatalog
            'Connect the catalog object to the (database)
            .ActiveConnection = strConn
            'Populate the drop down
            For Each Cube In .CubeDefs
                With cbxCubeList
                    .Items.Add(Cube.name)
                End With
            Next
        End With

        'Free memory
        objCatalog = Nothing
    End Sub

    Protected Overrides Sub Finalize()
        objADOConn = Nothing
        MyBase.Finalize()
    End Sub
End Class