O'Reilly logo

ADO.NET Cookbook by Bill Hamilton

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required