ADO.NET 3.5 Cookbook, 2nd Edition

4.9. 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 solution defines a COMPUTE BY statement and executes it using the ExecuteReader() method of the OleDbCommand object. Multiple result sets are returned by the DataReader, which are then displayed.

The C# code in Program.cs in the project ExecuteComputeByQuery is shown in Example 4-10.

Example 4-10. Program.cs for ExecuteComputeByQuery solution

using System; using System.Data; using System.Data.OleDb; namespace ExecuteComputeByQuery { class Program { static void Main(string[] args) { string oledbConnectString = "Provider=SQLOLEDB;Data Source=(local);" + "Integrated Security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT SalesOrderID, ProductID, OrderQty " + "FROM Sales.SalesOrderDetail " + "WHERE SalesOrderID BETWEEN 43683 AND 43699 " + "ORDER BY ProductID " + "COMPUTE SUM(OrderQty) BY ProductID"; using (OleDbConnection connection = new OleDbConnection(oledbConnectString)) { OleDbCommand command = new OleDbCommand(sqlSelect, connection); connection.Open(); OleDbDataReader dr = command.ExecuteReader(); do { // Output the detail result set. Console.WriteLine("Order\tProduct\tQuantity"); while (dr.Read()) Console.WriteLine("{0}\t{1}\t{2}", dr.GetInt32(0), dr.GetInt32(1), dr.GetInt16(2)); Console.WriteLine(); ...