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.