Using the Data Shaping Service for OLE DB
Hierarchical rowsets are often generated with the Microsoft® Data Shaping Service for OLE DB. This provider supports the Shape language, which allows rowset hierarchies to be constructed from rowsets obtained from an OLE DB data provider. The Shape Append command appends one or more child rowsets as columns to a parent rowset, and assigns a reference to a chapter to each row value in each appended column. For example:SHAPE {SELECT au_id, au_lname, au_fname FROM authors}
APPEND ({SELECT au_id, title FROM titleauthor TA, titles TS
WHERE TA.title_id = TS.title_id}
AS title_chap RELATE au_id TO au_id)This command creates a parent rowset from table authors and appends a child rowset in a column named title_chap. Each row value in title_chap is a reference to the subset of the child rowset that has the same value in its au_id column as in the au_id column of the parent rowset for that row. The tables referenced by this command are in the pubs database that is supplied with Microsoft SQL Server™ 2000.
Private Function sGetTitleAuthors() As String
Dim rstParent As ADODB.Recordset
Dim rstChild As ADODB.Recordset
Dim sBuf As String
Const CONNECT_PUBS = "PROVIDER=MSDataShape;DATA PROVIDER=SQLOLEDB;" & _
"SERVER=;DATABASE=pubs;UID=sa;PWD=;"
Const SHAPE_TITLEAUTHORS = _
"SHAPE {SELECT au_id, au_lname, au_fname FROM authors} " & _
"APPEND ({SELECT au_id, title FROM titleauthor TA, titles TS " & _
"WHERE TA.title_id = TS.title_id} " & _
"AS title_chap RELATE au_id TO au_id)"
'----- create rowsets
Set rstParent = New ADODB.Recordset
rstParent.Open SHAPE_TITLEAUTHORS, CONNECT_PUBS
'----- process parent rowset
Do While Not rstParent.EOF
sBuf = sBuf & rstParent("au_id") & vbTab & _
rstParent("au_lname") & ", " & rstParent("au_fname") & vbCrLf
'----- process chapter of child rowset
Set rstChild = rstParent("title_chap").Value
Do While Not rstChild.EOF
sBuf = sBuf & vbTab & vbTab & rstChild("title") & vbCrLf
rstChild.MoveNext
Loop
rstParent.MoveNext
Loop
sGetTitleAuthors = sBuf
End Function
Hierarchical rowsets are often generated with the Microsoft® Data Shaping Service for OLE DB. This provider supports the Shape language, which allows rowset hierarchies to be constructed from rowsets obtained from an OLE DB data provider. The Shape Append command appends one or more child rowsets as columns to a parent rowset, and assigns a reference to a chapter to each row value in each appended column. For example:SHAPE {SELECT au_id, au_lname, au_fname FROM authors}
APPEND ({SELECT au_id, title FROM titleauthor TA, titles TS
WHERE TA.title_id = TS.title_id}
AS title_chap RELATE au_id TO au_id)This command creates a parent rowset from table authors and appends a child rowset in a column named title_chap. Each row value in title_chap is a reference to the subset of the child rowset that has the same value in its au_id column as in the au_id column of the parent rowset for that row. The tables referenced by this command are in the pubs database that is supplied with Microsoft SQL Server™ 2000.
Private Function sGetTitleAuthors() As String
Dim rstParent As ADODB.Recordset
Dim rstChild As ADODB.Recordset
Dim sBuf As String
Const CONNECT_PUBS = "PROVIDER=MSDataShape;DATA PROVIDER=SQLOLEDB;" & _
"SERVER=;DATABASE=pubs;UID=sa;PWD=;"
Const SHAPE_TITLEAUTHORS = _
"SHAPE {SELECT au_id, au_lname, au_fname FROM authors} " & _
"APPEND ({SELECT au_id, title FROM titleauthor TA, titles TS " & _
"WHERE TA.title_id = TS.title_id} " & _
"AS title_chap RELATE au_id TO au_id)"
'----- create rowsets
Set rstParent = New ADODB.Recordset
rstParent.Open SHAPE_TITLEAUTHORS, CONNECT_PUBS
'----- process parent rowset
Do While Not rstParent.EOF
sBuf = sBuf & rstParent("au_id") & vbTab & _
rstParent("au_lname") & ", " & rstParent("au_fname") & vbCrLf
'----- process chapter of child rowset
Set rstChild = rstParent("title_chap").Value
Do While Not rstChild.EOF
sBuf = sBuf & vbTab & vbTab & rstChild("title") & vbCrLf
rstChild.MoveNext
Loop
rstParent.MoveNext
Loop
sGetTitleAuthors = sBuf
End Function
Microsoft Visual Studio\MSDN98\98VS\2052\SAMPLES\VB98\Msflexgd如果没有,我发给你
[email protected]