Executing Queries That Use COMPUTE BY

[ Team LiB ] Recipe 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.
Nội dung trích xuất từ tài liệu:
Executing Queries That Use COMPUTE BY[ Team LiB ]Recipe 3.13 Executing Queries That Use COMPUTE BYProblemThe SQL Server .NET data provider does not support the COMPUTE BY clause but youwant to execute a COMPUTE BY statement using ADO.NET.SolutionUse the COMPUTE BY statement from the Command object of the OLE DB .NET dataprovider.The sample code defines a COMPUTE BY statement and executes it using theExecuteReader( ) method of the OleDbCommand object. Multiple result sets are returnedby 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 constantsusing 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 = result.ToString( );DiscussionThe SQL Server .NET data provider does not support the COMPUTE BY clause, but theOLE DB .NET data provider does. The results are returned as multiple pairs of resultsets, the first of which contains the selected details and the second containing the resultsof the aggregate functions specified (the sum of the quantity ordered for the product inthis example) in the COMPUTE BY clause. This pattern is repeated for the remainingpairs of result sets.Microsoft states that the COMPUTE and COMPUTE BY clauses are provided in SQLServer 7.0 and later versions for backward compatibility. The ROLLUP operatorprovides similar functionality and is recommended instead. The main difference is thatROLLUP returns a single result set instead of multiple result sets. For more informationabout the ROLLUP operator, see Microsoft SQL Server Books Online.[ Team LiB ]