Executing Commands Against an Analytical Data Source

  • Article

New: 5 December 2005

After establishing a connection to an analytical data source, you can use an AdomdCommand object to run commands against and return results from that data source. These commands can retrieve data by using Multidimensional Expressions (MDX), Data Mining Extensions (DMX), or even a limited syntax of SQL. Additionally, you can use Analysis Services Scripting Language (ASSL) commands to modify the underlying database.

Creating a Command

Before running a command, you must create it. You can create a command using one of two methods:

  • The first method uses the AdomdCommand constructor, which can take a command to run at the data source, and an AdomdConnection object on which to run the command.
  • The second method uses the CreateCommand method of the AdomdConnection object.

The text of the command to be run can be queried and modified using the CommandText property. The commands that you create do not have to return data after they run.

Running a Command

After you have created an AdomdCommand object, there are several Execute methods that your command can use to perform various actions. The following table lists some of these actions.

To Use this method

Return results as a stream of data

ExecuteReader to return an AdomdDataReader object

Return a CellSet object

ExecuteCellSet

Run commands that do not return rows

ExecuteNonQuery

Return an XMLReader object that contains the data in an XML for Analysis (XMLA) compliant format

ExecuteXmlReader

Example of Running a Command

This example uses the AdomdCommand to run an XMLA command that will process the Adventure Works DW cube on the local server, without returning data.

        void ExecuteXMLAProcessCommand()
        {
            //Open a connection to the local server
            AdomdConnection conn = new AdomdConnection("Data Source=localhost");
            conn.Open();

            //Create a command, and assign it an XMLA command to process the cube.
            AdomdCommand cmd = conn.CreateCommand();
            cmd.CommandText = "<Process xmlns=\"https://schemas.microsoft.com/analysisservices/2003/engine\">\r\n" +
  @"<Object>
    <DatabaseID>Adventure Works DW Standard Edition</DatabaseID>
    <CubeID>Adventure Works DW</CubeID>
  </Object>
  <Type>ProcessFull</Type>
  <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>";

            //Execute the command
            int result = cmd.ExecuteNonQuery();

            //Close the connection
            conn.Close();
        }