Session 24—Beating the CRUD out of the DataGrid Control |
253 |
QUIZ YOURSELF
1.How do you turn on the editing mode for a DataGrid control? (See “Updating Your Data.”)
2.How can you use both a range and required field validator to validate user input? (See “Handling the OnUpdateCommand Event.”)
3.How can you allow the user to better control how a column is sorted? For instance could you use an external dropdown list to sort columns in a specific manner? (See “Sorting Columns with the DataGrid Control.”)
S E S S I O N
25
Data Shaping with ADO.NET
Session Checklist
Introducing data shaping
Handling hierarchical datasets
In this session we will explore the ability of the DataSet object to support the use of parent-child relationships between tables.
What Is Data Shaping?
Data shaping is simply the process of reflecting the parent-child relationships that exist between data objects. For instance, let’s look at demonstrating the parent-child relationship between two data objects: a set of stores and a set of titles sold by the stores. Figure 25-1 illustrates this relationship.
In ASP you could store these relationships in a hierarchical recordset by using the Shape Provider, and then produce a structured output representing the hierarchy to the user. The shaping syntax allowed you to build these parent-child relationships easily as illustrated in Listing 25-1.
256 |
Sunday Morning |
Figure 25-1 Diagram of a parent child relationship
Listing 25-1 Example of Using the ADO Shape Provider
<HTML>
<HEAD>
<SCRIPT LANGUAGE=”VBScript” RUNAT=”Server”> Function DisplayShape()
Dim myConnection Dim ShapeCommand Dim StoresRecordSet Dim SalesRecordSet
Set myConnection=Server.CreateObject(“ADODB.Connection”) myConnection.Provider =”MSDataShape” myConnection.ConnectionString=”DRIVER=SQL
Server;UID=sa;DATABASE=pubs;SERVER=(local);PWD=;”
myConnection.Open
‘Constructing the Shape Command to Build RecordSet
ShapeCommand = “SHAPE {SELECT stor_id, stor_name, stor_address FROM stores}” ShapeCommand = ShapeCommand &”APPEND ({select *, title from sales inner join
titles on sales.title_id=titles.title_id} AS Sales”
ShapeCommand = ShapeCommand &” RELATE stor_id TO stor_id)”
Set StoresRecordSet = Server.CreateObject(“ADODB.Recordset”)
StoresRecordSet.Open ShapeCommand, myConnection
Response.Write(“<H2>List of Titles Sold By Store</H2>”)
Do While Not StoresRecordSet.EOF
Response.Write(“<BR><B><I>”)
Session 25—Data Shaping with ADO.NET |
257 |
Response.Write(“Store Name: “ & StoresRecordSet(“stor_name”)) Response.Write(“</B></I>”)
Response.Write(“<p>Address: “ & StoresRecordSet(“stor_address”) & “</p>”) Set SalesRecordSet= StoresRecordSet(“Sales”).Value Response.Write(“<blockquote>”)
Response.Write(“<p><i>List of Titles sold by “ & StoresRecordSet(“stor_name”) &”</i></p>”)
Do While Not SalesRecordSet.EOF
Response.Write(“<p>Title = “& SalesRecordSet(“title”) & “</p>”) SalesRecordSet.movenext
Loop Response.Write(“</blockquote>”) StoresRecordSet.movenext
Loop
End Function </SCRIPT>
</HEAD>
<BODY>
<%=DisplayShape%>
</BODY>
</HTML>
Figure 25-2 shows the output from this code segment, producing the expected representation of the parent-child relationships.
Figure 25-2 Resulting output of using the hierarchical recordset in ASP
ADO.NET does not support the use of the MSDataShape Provider or the shape syntax. Instead ADO.NET utilizes a method of the DataSet object, the Relations() method.