This tip discusses using a DataGrid to build and employ DataRelations for an application. The Web site contains illustrations for this tip, and you can download the necessary tables to follow along with this tip.
DataSets allow you to maintain related DataTables inside a single data structure. To accomplish this, you can create DataRelations between the tables at runtime in code, or at design time using the XML Schema Designer (I'll demonstrate this technique later in this tip). But let's suppose you have two tables of information, such as an Employee table that has an employee's name, address, etc. and it has a foreign key relationship with the Jobs table that contains the possible positions (president, janitor, etc.) at a given company, like so:
[Illustration #1]
AND you want to create an ASP.NET Web Form that displays information from the Employee table, as well as the position from the EmployeeType table (the actual EmployeeTypeDescription, not the EmployeeTypeID). So, your desired result would look something like this:
[Illustration #2]
To accomplish this, you'll need to:
* Actually, this is just one way to do it...I'll explain why I take this approach later in this tip.
If you would like to see this technique demonstrated in a narrated screen cam video, it is available in the Supporter's Area at LearnVisualStudio.NET in a video called "2415: Binding Related Tables in the DataGrid". Please visit www.LearnVisualStudio.NET for more details.
STEP 0: Creating an ASP.NET Web Forms Project
STEP 1: Create a DataSet
[Illustration #3]
STEP 2: Create a DataRelation
[Illustration #4]
[Illustration #5]
[Illustration #6]
STEP 3: Add and DataBind a DataGrid to your DataSource
[Illustration #7]
[Illustration #8]
Notice that I fill the jobs table first. You must do that because you have a foreign key constr
To continue reading for free, register below or login
To read more you must become a member of SearchSOA.com
aint defined via the relationship you added between the two tables. If you reverse the order of the DataAdapter Fills, then you'll get an error alluding to this fact.
STEP 4: Modify the DataGrid Column that will display the related field.
This is where things get interesting.
First, it's important that the Function must be Public, so that the WebForm can see the new Function! Second, we get a generic object reference, then cast it to a DataRowView. We then use the DataRowView (drv) to get a reference to the current row . . ., which can be referenced as a strongly typed DataRow from the Employee's table. Then, in the last line of code, we return the current DataRow's related Job row, and specifically the Job Description field in that related row. This value is returned to the .aspx file, and is rendered in the grid.
The finished result should look something like this:
[Illustration #9]
You might be wondering if all this effort was worth it. After all, you could have much more easily just created a stored procedure that did an INNER JOIN and returned the results as a single set of data, rather than having two DataTables, a DataRelation and some extra code to manage. But the dividends of this approach pay off in the next tip, which will show how to edit the values in this grid using "in line editing". When we go into edit mode, instead of a standard text box, we'll use a drop down list box containing all of the values from the Jobs DataTable in the list, with the correct item selected from the list of values. Stay tuned.
Download the code for this tip here.
About the Author
[IMAGE]Robert Tabor is a Microsoft Certified Professional in Visual Basic with over six years of experience developing n-tier Microsoft-centric applications for some of the world's most prestigious companies and consulting organizations, such as Ernst & Young, KPMG, Cambridge Technology Partners, Sprint, American Heart Association, and the Mary Kay Corporation. Bob is the author of Microsoft .NET XML Web services by Sams Publishing, and contributes to
SoapWebservices.com and LearnVisualStudio.NET. He is currently working on initiatives within Mary Kay, the second largest eCommerce site in retail volume on the net, of how to utilize .NET within their e-business group.
