Write to UsHandling Parent-Child Tables
Introduction
The Database
The Script Basics
The Second Table
Calling the Second Table
Creating the Parent-Child Relationship
Creating the Parent-Child Output
The End
Sometimes you will encounter the need to output the contents of two tables in a sequential manner (usually after one row from the main table is outputted, at least one row from the child table will follow, and so on). In ASP.NET, doing this is easier than you think. For this tutorial, we'll choose two tables: groups and forums, with groups being the parent table, and forums being the child table. The structure of these tables is as follows:
For table groups:
groid int 4
gname varchar 50
grdesc varchar 500
And for table forums:
fid int 4
groid int 4
fornme varchar 50
fordesc varchar 500
For forums and groups to be related, you need at least one variable to connect them both. The integer called groid is that variable. The output will be something like this:
ScienceEverything you want to know about science is here. |
|
| Biology | Want to study life? Start here |
| Physics | |
| Chemistry | We know more than the periodic table |
EngineeringDesign, science, technology. We have it all |
|
| Electrical | We have all the circuits you need |
| Mechanical | Yes. We know more than NASA does. |
| Industrial | It is almost business in disguise, we still like it. |
ComplainsYou have something to say about our forum? Say it here |
|
| Leaving? | Yes, tell us you are leaving in this forum. |
The words Science, Engineering and Complains belong to table groups, and the rest belongs to forums. Now let's examine the C-Sharp script used to develop the relationship between tables:
|
<%@ Page Language="C#" debug="TRUE" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <script runat=server> void Page_Load(Object sender , EventArgs e) // Grab the Categories and Products table |
If you are familiar with ADO.NET, or if you have read the previous tutorials, the script as it is so far will seem easy to you. Here we have conForCateg as a SqlConnection to the database. AdapForCateg is a SqlDataAdapter that is taking all the values available in a table called groups. When conForCateg is opened, the values taken by AdapForCateg are filled into the DataSet (called dasetForCateg) and stored in a table called groups1. |
|
AdapForCateg.SelectCommand = new SqlCommand( "Select * From forums", conForCateg ); AdapForCateg.Fill( dasetForCateg, "forums1" ); conForCateg.Close(); |
Now it gets a little more complicated. We return to the SqlDataAdapter (AdapForCateg), and we employ the property SqlCommand to select all the values from a second table: forums. Those values are also sent to the DataSet called dasetForCateg, but they are stored in a table called forums1. |