ADO.NET Cookbook

3.13. Executing Queries That Use COMPUTE BY

Problem

The SQL Server .NET data provider does not support the COMPUTE BY clause but you want to execute a COMPUTE BY statement using ADO.NET.

Solution

Use the COMPUTE BY statement from the Command object of the OLE DB .NET data provider.

The sample code defines a COMPUTE BY statement and executes it using the ExecuteReader( ) method of the OleDbCommand object. Multiple result sets are returned by the DataReader and then these are displayed.

The C# code is shown in Example 3-13.

Example 3-13. File: ComputeForm.cs

// Namespaces, variables, and constants using System; using System.Text; using System.Data; using System.Data.OleDb; // . . . StringBuilder result = new StringBuilder( ); String sqlSelect = "select OrderID, ProductID, Quantity " + "FROM [Order Details] " + "ORDER BY ProductID " + "COMPUTE SUM(quantity) by ProductID"; OleDbConnection conn = new OleDbConnection( ConfigurationSettings.AppSettings["OleDb_Shape_ConnectString"]); OleDbCommand cmd = new OleDbCommand(sqlSelect, conn); conn.Open( ); OleDbDataReader dr = cmd.ExecuteReader( ); do { result.Append("Order\tProduct\tQuantity" + Environment.NewLine); while(dr.Read( )) { result.Append(dr.GetInt32(0) + "\t" + dr.GetInt32(1) + "\t" + dr.GetInt16(2) + Environment.NewLine); } // Get the sum. dr.NextResult( ); dr.Read( ); result.Append("SUM\t\t" + dr.GetInt32(0) + Environment.NewLine); result.Append(Environment.NewLine); } while(dr.NextResult( )); dr.Close( ); conn.Close( ); resultTextBox.Text ...