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 ...

Get ADO.NET Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.