Use ADO Shape commands and DataReader in Visual Basic .NET

xiaoxiao2021-03-06 27

•summary

• Differences between hierarchical records and SQL JOIN and GROUP BY sentences • Different types of shapes

• Relationship-based hierarchy • Parameter-based hierarchy • Group-based hierarchy • Simplified syntax • Note •

• Simple relationship hierarchies • Composite relationship hierarchies • Multiple relationship hierarchies • Hierarchies using aggregate • Group hierarchies • Hierarchical structure • Multi-groups • Total • Complex hierarchies • Level Group-related parent grouping • Formal syntax of Shape clauses • Visual Basic .NET Shape Test

summary

This article describes the ActiveX data object (ADO) shape command syntax for generating hierarchical records and explains how to traverse hierarchical records. A code example is also available.

When you need to access the parent-child, you can use the hierarchical recordset to replace Join and Group By syntax. Hierarchical Record Sets For many products: XBase products use the set rate command, Microsoft Access is used inside the Segmentation Virtual Table to generate a reported level of reports, etc.

The hierarchical record set can be obtained by the MSDataShape provider. This provider is implemented by the client cursor engine. Level allows you to build one or more record sets to define groups and specify aggregation calculations on child record sets. Although you can implement similar features through your code, use this feature to make developers to complete a large number of examples of work.

The difference between the hierarchical record set and the SQL JOIN and the Group BY statement is different from the SQL Join and Group BY statements. In the SQL Join statement, the parent table field and sub-table fields are in the same recordset. In the hierarchical record set, the recordset contains only fields from the parent table. In addition, the record set includes a field representing the associated sub-data, you can specify this field to another recordset variable and traversal.

When using Group By and aggregate operators, only the aggregate value appears in the recordset. In the hierarchical record concentration, the aggregate value is present in the parent recordset, and the detailed record is rendered in the sub-recordset.

Different kinds of shapes you can create three shapes:

• Based on the relationship • Parameters - Based on the group, each type is available. You need to choose the mechanism that best suits your application requirements and environment running applications.

The level based on the relationship based on the relationship and parameter-based hierarchy can also be represented by the SQL JOIN statement. However, they are different from the form of the parent record and subcord. In the relationship-based level, you must first read all parent records and sub-records to the local cache before starting. Therefore, the initial overhead based on the relationship is higher in the retrieval record. However, the overhead after the initial search is low.

The parameter-based hierarchy initially reads only the parent record as needed, and then acquires subcord as needed. Despite the initial overhead, you must issue a new subquery to each parent record accessed, and you must keep the connection to the data source as long as the recordset is opened.

The group-based hierarchy is equivalent to generating a aggregated SQL statement integrated with a detailed SQL statement based on the group level. The group-based level is also equivalent to performing aggregated functions on non-standardized data. You cannot update the summary and computational columns because these columns are obtained from multiple records. Like relationship-based levels, all records must be read in advance.

Simplified syntax uses the Shape clause to generate a hierarchical recordset. This section provides a simplified syntax. Since the Shape grammar becomes very complicated, the normal syntax of the Shape clause is provided at the end of this article, which can be used to extend an example. You can test your own Shape statement using programs at the end of this article.

Shape {parent-statement}

Append aggregate

| ({Child-Statement} [as alias]

Relate Parent-Field To Child-Field | Parameter-Marker

[, Parent-Field to CHild-Field | Parameter-Marker ...])

[, Aggregate | ({child statement}) ...] shape {non-normalized-statement} [as alias]

Compute aggregate

| Alias

| ({Child-Statement} [as alias] Relate Parent-Field To

Child-Field | Parameter-Marker

[, Aggregate | alias | ({child-statement} ...)]

[By grouping-field [, grouping-field]]

Shape {non-normalized-statement} [as alias]

By Grouping-Field [, Grouping-Field]

Note

• If your selected field is from the same field from different tables, you may need to fist names for these fields to make the Shape analyzer work normally. • The function of the Shape Append syntax is similar to the Outer Join statement, even if the corresponding sub-record does not exist, a parent record will be returned. • Aggregate can only operate in the fields in the adjacent subcord of records. To operate the fields in the sub child-level, you must generate an intermediate polymerization. See the group hierarchical presentation example with aggregation. • If the aggregate function is used through the Shape Append syntax, the aggregate value will occupy the additional field of the parent result set, which also contains a field from the parent statement. Instead, Shape Compute and Shape BY create a new parent, not a regulatory statement, is a child recordset. • The Shape Provider requires you to include an alias of a non-standardized statement in the compute clause when using Shape Compute. Otherwise, even if you don't receive a syntax error, you will receive an error message indicating that you do not support this feature.

Example This section provides a diagram example. These examples use the Northwind sample databases.

Simple relationship level

Shape {selection * from customers}

Append ({Select * from Orders} as rsorder

Relate Customerid To Customerid) Generate:

Customers. *

Rsorders

-- Orders. * In the following chart, the parent record set includes all fields in the Customers table and fields called RSORDERS. RsORDERS provides a reference to subcoses and contains all fields for the Orders table. Other examples use similar tags.

Parametric level

Shape {selection * from customers}

Append ({SELECT * from ORDERS WHERE CUSTOMERID =?} As rsorders

Relate Customerid to Parameter 0) The same level as the simple relationship hierarchy.

Composite relationship level This example demonstrates a three level level of Customers, Orders and Order Details ::

Shape {selection * from customers}

Append ((shape {selection * from orderers)

Append ({Select * from [Order Details]} as rsdetails

Relate Orderid to Orderid)) AS Rsorders

Relate Customerid To Customerid) Generate:

Customers. *

Rsorders

--- Orders. *

Rsdetails

-- [Order Details]. *

Multiple Relational Level This example demonstrates the hierarchy containing a parent recordset and two sub-record sets (one for parameterized record sets: shape {selection * from customers}

Append ({SELECT *

From Orders

Where orderdate <# 1/1/1998 # and customerid =?}

Relate Customerid to Parameter 0) As RsoldORDERS,

({SELECT *

From Orders

Where orderdate> = # 1/1/1998 #}

Relate Customerid To Customerid) AS RSRECENTORDERS generated:

Customers. *

Rsoldorders

--- Orders. *

RSRecentorders

--- Orders. *

Aggregated level

Shape (SELECT *

Append ({SELECT OD.Orderid, Od.Unitprice * Od.quantity As ExtendedPrice

From [Order Details] AS OD}

Relate Orderid to Orderid) As RsDetails,

SUM (ExtendedPrice) as Ordertotal Generation:

ORDERS. *

Rsdetails

-- OrderID

ExtendedPrice

Ordertotal

Group level

Shape {Select Customers.customerid as Cust_ID, ORDERS. *

From customer inner join order

ON Customers.customerid = Orders.customerid} as rsorders

Compute RsORDERS BY CUST_ID Generation:

Rsorders

---- Cust_ID

ORDERS. *

CUST_ID

Group of polymerization

Remarks: in this example

Inline Shape clause

The statement used in the form of polymerization is the same.

Shape

(Shape {Select Customers. *, ORDERS.OrderId, Orders.orderdate

From customer inner join order

On Customers.customerid = Orders.customerid}

Append ({SELECT OD.ORDERID,

Od.Unitprice * Od.quantity As ExtendedPrice

From [Order Details] AS OD} as rsdetails

Relate Orderid to Orderid,

Sum (RsDetails.extendedPrice) as ordertotal) as rsorders

Compute Rsorders,

Sum (Rsorders.OrDotal) as Custtotal,

ANY (Rsorders.ContactName) AS Contact

By Customerid generated:

Rsorders

---- Customers. *

ORDERID

ORDERDATE

Rsdetails

-- OrderID

ExtendedPrice

Ordertotal

Customertotal

Contact

Customerid

Multi-group

Shape

(Shape {Select Customers. *, Od.Unitprice * Od.quantity As ExtendedPrice

From (Customers Inner Join Orders

On Customers.customerid = Orders.customerid) Inner Join

[Order Details] as Od on Orders.Orderid = Od.OrderId}

As RsDetail

Compute any (rsdetail.contactname) AS Contact,

ANY (RSDetail.Region) As region,

Sum (Rsdetail.extendedPrice) as customtotal,

Rsdetail

By Customerid) AS RSCUSUMMARY

Compute RSCUSUMMARY

By Region Generation:

RSCUSUMMARY

--- Contact

Region

Custtotal

Rsdetail

---- Customers. *

ExtendedPrice

Customerid

Region

total

Shape

(Shape {Select Customers. *,

Od.Unitprice * Od.quantity As ExtendedPrice

From (Customers Inner Join Orders

On Customers.customerid = Orders.customerid) Inner Join

[Order Details] as Od on Orders.Orderid = Od.OrderId}

As RsDetail

Compute any (rsdetail.contactname) AS Contact,

Sum (Rsdetail.extendedPrice) as customtotal,

Rsdetail

By Customerid) AS RSCUSUMMARY

Compute sum (RSCUSUMMARY.CUSTTOAL) AS GrandTotal,

RSCUSUMMARY Note that the BY clause is lacking in the outer layer. This example defines a total because the parent row set contains a total single record and a pointer to subcos.

Grandtotal

RSCUSUMMARY

--- Contact

Custtotal

Rsdetail

---- Customers. *

ExtendedPrice

Customerid

Complex Hierarchy This example demonstrates a set of parent line collections, two sub-line sets (one for parameterization) and a subtle purpose.

Shape {Select Customers. * From customers} as rsdetail

Compute Rsdetail,

ANY (RSDetail.comPanyName) As Company,

({SELECT * from ORDERS WHERE CUSTOMERID =?}

Relate Customerid to Parameter 0) As Rsorders,

Count (Rsorders.Orderid) as Ordercount

By Customerid generated:

Rsdetail

---- Customers. *

Company

Rsorders

--- Orders. *

ORDERCOUNT

Customerid

Group parental associated with groups

Shape

(Shape {SELECT * from Customers}

Append ((Shape {Select ORDERS. *, Year (ORDERDATE) AS ORDERYAR, MONTH (ORDERDATE) AS ORDERMONTH

From Orders} as rsorders

Compute Rsorders

By Customerid, Orderyear, OrderMonth)

Relate Customerid To Customerid) As RsordbyMonth)

As RSCustomers

Compute RSCustomers

By Region Generation:

RSCustomers

--- Customers. *

RsordbyMonth

----- rsorders

--- Orders. *

Customerid

ORDERYEAR

ORDERMONTH

region

Shape clause regular syntax

:: = Shape [as ]

[]

:: = append

| Compute

[By ]

| By

:: = {}

| ()

:: = [,

:: = [as ]

:: = () |

:: = [as ] Relate

:: = [, ...]

:: = to

:: = | parameter

:: = |

:: = ]

:: = SUM ()

| AVG ()

| Min ()

| Max () | Count ()

| SDEV ()

| Any ()

| CALC ()

:: = . |

:: =

:: = "" | '' |

:: = alpha [alpha | DIGIT | _ | # ...]

:: = DIGIT [DIGIT ...]

:: = Unicode-char [unicode-char ...]]]

:: = an expression recognized by the jet

Expression Service Whose OPERANDS ARE

Other Non-Calc Column in The Same Row.

Visual Basic .NET Shape Test The following Visual Basic .NET code allows you to type your own Shape command and display the location of the field hierarchy or indicate syntax errors. This example uses ADO.NET

DataReader object retrieves hierarchical data.

Important: You must use the OLE DB managed provider when using the MSDataShape provider. You cannot use SQL or ODBC hosting providers.

1. Create a Windows application project in Visual Basic .NET. 2. Add two TextBox controls and a Button control. TEXTBOX1, TEXTBOX2, and Button 1.3 are added by default. Set the following properties in TextBox1 and TextBox2:

Multiline: True

Scrollbars: Vertical

4. Expand these two text boxes to display multi-line text. 5. Add the following code to the code module of the default form: imports system.data.oledb6. Click to expand the "Windows Form Designer Generated Code" area and add the following code to the InitializeComponent call, so that Place a default Shape statement in TextBox1. After copying this code, please hide the Windows Form Designer area. Me.TextBox1.text = "Shape {selection * from customers} append ({selection * from orderers}" & _

"As CustOrDers Relate Customerid To Customerid" 7. Add the following code to the Code of Windows Form Designer below: DIM CN AS OLEDBConnection

DIM CMD AS OLEDBCommand

DIM DRCUST AS OLEDBDATAREADER

Private sub button1_click (Byval Sender As System.Object, Byval E as System.EventArgs)

Handles button1.click

CN = New OLEDBCONNECTION ("provider = msdatashape; data provider = SQLOLEDB;" & _

"Data Source = (local); Initial Catalog = Northwind; user ID = sa; password = password")

cn.open ()

CMD = New OLEDBCommand (TextBox1.text, CN)

DRCUST = cmd.executeReader ()

TextBox2.clear ()

Listchapteredfields (DRCUST, 0)

End Sub

Private Sub Listchapteredfields (Byval Dr AS OledbDataReader, Byval Lnglevel As Long)

DIM I as integer

Dim Drorders as OledbDataReader

Dr.read ()

For i = 0 to Dr.fieldcount - 1

Logtext (Space (LNGLEVEL * 3) & Dr.GetName (I) & VBTAB)

'Looking for fieldtype of system.data.idataareader

IF TypeOf Dr (I) Is IdataReader Then

DRORDERS = Dr.GetValue (i)

Listchapteredfields (DRORDERS, LNGLEVEL 1)

END IF

End Sub

Private sub logtext (byval strline as string)

If TextBox2.text = "" "

TextBox2.text = strline

Else

TextBox2.text = textbox2.text & vbcrlf & strline

END IF

End Sub8. Make sure you update the connection string of the OLEDBConnection object to use your server, username, and password. 9. Run the project, and then click the Command button. You will notice the hierarchy of the fields appear in TextBox2. 10. Type (or copy and paste) another shape command in TextBox1 and click the Command button. You will notice the hierarchy of the fields appear in TextBox2.

The information in this article applies to:

• Microsoft .NET Framework 1.1 Service Pack 1 • Microsoft Visual .NET 2002 Standard Edition • Microsoft Data Access Components 2.6 Service Pack 2 • Microsoft Data Access Components 2.7 Service Pack 1

Keywords: kbhowto kbhowtomaster kbsystemdata kboledb kb308045

转载请注明原文地址:https://www.9cbs.com/read-77116.html

9cbs

New Post(0)