Displaying Nested Relational Data

Displaying Nested Relational Data:
example:
结果：
Professional ATL Com
  ISBN: 1861001401   Release Date: 1998-8-20 0:00:00
  Author(s): Richard Grimes,
  Price: CAN:83.95   GBP:55.49   USD:59.99  
Beginning Visual Basic 5 Objects
  ISBN: 1861001452   Release Date: 1998-4-1 0:00:00
  Author(s):
  Price: CAN:48.95   GBP:32.49   USD:34.99  

Professional MTS & MSMQ Programming With VB And ASP
  ISBN: 1861001460   Release Date: 1998-5-1 0:00:00
  Author(s): Alex Homer, David Sussman,
  Price: CAN:69.99   GBP:46.99   USD:49.99  

关键代码：
<wrox:connect id="ctlConnectStrings" runat="server"/>

<%-- insert the control that creates the DataSet --%>
<wrox:getdataset id="ctlDataSet" runat="server"/>

<div id="divResults" runat="server"></div>

<script language="C#" runat="server">

 void Page_Load(Object sender, EventArgs e)
 {

  // get connection string from ../global/connect-strings.ascx user control
  string strConnect = ctlConnectStrings.OLEDBConnectionString;

  // create a variable to hold an instance of a DataSet object
  DataSet objDataSet;

  // get dataset from get-dataset-control.ascx user control
  objDataSet = ctlDataSet.BooksDataSet(strConnect, "ISBN LIKE '18610014%'");

  if (objDataSet == null)
   return;

  // now we're ready to display the contents of the DataSet object
  // create a string to hold the results
  string strResult = "";

  // create a reference to our main Books table in the DataSet
               //创建datatable
  DataTable objTable = objDataSet.Tables["Books"];

  // create references to each of the relationship objects in the DataSet
               //得到datarelation   .BookAuthors,BookPrices是关系名
  DataRelation objAuthorRelation = objTable.ChildRelations["BookAuthors"];
  DataRelation objPriceRelation = objTable.ChildRelations["BookPrices"];

  // now we can iterate through the rows in the Books table
                //叠代DataRow
  foreach (DataRow objRow in objTable.Rows)
  {
   // get the book details and append them to the "results" string
                       
   strResult += "<b>" + objRow["Title"] + "</b><br />&nbsp; ISBN: " + objRow["ISBN"]
      + " &nbsp; Release Date: " + objRow["PublicationDate"] + "<br />";

   // get a collection (array) of all the matching Author table rows for this row
                     //利用GetChildRows方法取得当前行的所有子表中匹配的行集合（以ISBN为匹配标准）objAuthorRelation是关系。类似的，也可以用GetParentRows方法取得当前行的所有父表中匹配的行集合。当然，这样的话就不是以Book名作为显示次序了。而是以作者名称或者价格作为显示次序了。
   DataRow[] colChildRows = objRow.GetChildRows(objAuthorRelation);

   strResult += "&nbsp; Author(s): ";

   // iterate through all the matching Author records adding to the result string       
                     //遍历所有子表中匹配的行集
   foreach (DataRow objChildRow in colChildRows)
    strResult += objChildRow["FirstName"] + " " + objChildRow["LastName"] + ", ";
   strResult += "<br />";

   // repeat using the Price table relationship to display data from matching Price records
                     //类似的处理价格子表中匹配的行集
   colChildRows = objRow.GetChildRows(objPriceRelation);
   strResult += "&nbsp; Price: ";
   foreach (DataRow objChildRow in colChildRows)
    strResult += objChildRow["Currency"] + ":" + objChildRow["Price"] + " &nbsp; ";
   strResult += "<p />";

  } // and repeat for next row in Books table

  divResults.InnerHtml = strResult;  // display the results

 }
</script>

 DataSet BooksDataSet(string strConnect, string strWhere)的定义：

 public DataSet BooksDataSet(string strConnect, string strWhere)
 {
  outConnect.InnerText = strConnect;  //display connection string

  // specify the SELECT statement to extract the BookList data
  string strSelectBooks = "SELECT * FROM BookList WHERE " + strWhere;
  outSelectBooks.InnerText = strSelectBooks;   // and display it

  // specify the SELECT statement to extract the BookAuthor data
  string strSelectAuthors = "SELECT * FROM BookAuthors WHERE " + strWhere;
  outSelectAuthors.InnerText = strSelectAuthors;   // and display it

  // specify the SELECT statement to extract the BookPrices data
  string strSelectPrices = "SELECT * FROM BookPrices WHERE " + strWhere;
  outSelectPrices.InnerText = strSelectAuthors;   // and display it

  // declare a variable to hold a DataSet object
  // note that we have to create it outside the Try..Catch block
  // as this is a separate block and so is a different scope
  DataSet objDataSet = new DataSet();

  try
  {

   // create a new Connection object using the connection string
   OleDbConnection objConnect = new OleDbConnection(strConnect);

   // create a new Command object
   OleDbCommand objCommand = new OleDbCommand();

   // set the properties
   objCommand.Connection = objConnect;
   objCommand.CommandType = CommandType.Text;
   objCommand.CommandText = strSelectBooks;

   // create a new DataAdapter object
   OleDbDataAdapter objDataAdapter = new OleDbDataAdapter();
   // and assign the Command object to it
   objDataAdapter.SelectCommand = objCommand;

   // get the data from the "BookList" table in the database and
   // put it into a table named "Books" in the DataSet object
   objDataAdapter.Fill(objDataSet, "Books");

   // change the SELECT statement in the ADOCommand object
   objCommand.CommandText = strSelectAuthors;
   // then get data from "BookAuthors" table into the DataSet
   objDataAdapter.Fill(objDataSet, "Authors");

   // and do the same again to get the "BookPrices" data
   objCommand.CommandText = strSelectPrices;
   objDataAdapter.Fill(objDataSet, "Prices");
                        //以上为Dataset创建了三个表

   // declare a variable to hold a DataRelation object
   DataRelation objRelation;

   // create a Relation object to link Books and Authors
                        //定义名为BookAuthors的关系：Books表为父表，Authors为子表，ISBN是连接标准
   objRelation = new DataRelation("BookAuthors",
         objDataSet.Tables["Books"].Columns["ISBN"],
         objDataSet.Tables["Authors"].Columns["ISBN"]);
   // and add it to the DataSet object's Relations collection
                       //添加关系
   objDataSet.Relations.Add(objRelation);

   // now do the same to link Books and Prices
                       //类似处理另一个关系
   objRelation = new DataRelation("BookPrices",
         objDataSet.Tables["Books"].Columns["ISBN"],
         objDataSet.Tables["Prices"].Columns["ISBN"]);
   objDataSet.Relations.Add(objRelation);
  }
  catch (Exception objError)
  {
   // display error details
   outError.InnerHtml = "<b>* Error while accesing data</b>.<br />"
       + objError.Message + "<br />" + objError.Source;
   // return Nothing on error
   return null;
  }

  // display a success message
  outError.InnerHtml = "DataSet created and loaded.";

  // return our new disconnected DataSet object
  return objDataSet;

 }