Exploiting DataRelations in a DataGrid

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:

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:

To accomplish this, you'll need to:

• Create a DataSet that contains a definition for each of the two tables (See Step 1 below)
• Create a DataRelation between those two tables, defining the Primary and Foreign key for each one (See Step 2 below)
• Add a DataGrid to your ASP.NET Web Form, and databind it to an instance of your DataSet (See Step 3 below)
• Modify the DataGrid column that will display the EmployeeTypeDescription, making it a TemplateColumn that as its data source calls a protected (or public) method which performs the special binding to this related piece of data (See Step 4 below) *

* 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

• In Visual Studio.NET, Create a new project by selecting the File | New | Project . . . menu option
• In the New Project dialog, select your language of choice (which will be VB.NET for this example) from the Project Types pane, and ASP.NET Web Application from the Templates pane.
• In the Location field, you should change the default value to something like: http://localhost/DataGridRelations
• Select the OK button. The new project should be created, and WebForm1.aspx loaded and ready to go in the main area of Visual Studio.NET.

STEP 1: Create a DataSet

• Select the File | Add New Item . . . menu option, and the Add New Item dialog appears.
• Make sure that Web Project Items is selected in the Categories pane, and Dataset is selected in the Templates pane.
• Rename the dataset to: Employees.xsd
• Select the Open button. The Employees.xsd file has been added to the project, and its designer should be visible.
• Now, go to the Server Explorer, find your database connection for the Pubs database, and drill down until you see the employee and jobs items in the Tables section of the tree view. Drag each of these tables on to the design surface. You should see the following screen:

STEP 2: Create a DataRelation

• In the Toolbox, under the XML Schema tab (See the picture below), drag and drop a DataRelation object on to the design surface.

• When this happens, the EditRelation dialog box appears. Selected as the Parent element the jobs table, and as the Child element select the employee table. The job_id field should populate both Fields columns . . . if it doesn't then make the selection in the drop down list box for each of the columns.
• Also, DO NOT put a check in the "Create foreign key constraint only" checkbox. Doing so will not create a DataRelation object in your DataSet, which we rely on later in this tip. When you are finished with these two steps, the screen should look something like this:

• Select the OK button, and the dialog will disappear, and now on the XSD Designer surface, you should see a DataRelation represented like so:

STEP 3: Add and DataBind a DataGrid to your DataSource

• Select the tab with WebForm1.aspx (or open it) and drag-and-drop the employee table from the Server Explorer to the design surface. This should create a Connection component and a DataAdapter component on the designer surface.
• Select the DataAdapter1 in the designer tray, then select the Configure Data Adapter . . . link beneath the Properties window. This opens the Data Adapter Configuration Wizard. Configure this DataAdapter using all the default selections (at least, for the purpose of this example. You can modify these as needed for your own applications).
• Drag-and-drop the jobs table from the Server Explorer to the design surface. This should only create a DataAdapter (it should use the Connection from earlier in this step, and not have to create a new one).
• Select the DataAdapter2 in the designer tray, then select the Configure Data Adapter . . . link beneath the Properties window. Again, use all the defaults values in the Wizard.
• Drag-and-drop a DataSet item from the Data tab of the Toolbox, at which point the Add Dataset dialog appears. Make sure that you select Typed Dataset and that the DataGridRelations.Employees DataSet is selected in the drop-down list box.

• Click the OK button to finish adding the DataSet to your design surface.
• Next, drag-and-drop a DataGrid item from the Toolbox to the designer surface. At this point, the WebForm1.aspx designer surface should look like this:

• Select the DataGrid and set the DataSource property to Employees1 (the DataSet instance that was added to your design surface in the previous steps) in the Properties window. Also, set the DataMember to the employees DataTable.
• Add the following code to the Page_Load procedure:

          SqlDataAdapter2.Fill(Employees1, "jobs")
          SqlDataAdapter1.Fill(Employees1, "employee")
          DataGrid1.DataBind()
  

Notice that I fill the jobs table first. You must do that because you have a foreign key constraint 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.

• At this point, try running the code to make sure you've done everything right. You should see a list of all the employees on your Web Form.

STEP 4: Modify the DataGrid Column that will display the related field.

This is where things get interesting.

• Select the DataGrid1 item on the designer, and select the Property Builder . . . link beneath the Properties window. This will pop open the DataGrid1 Properties dialog. Select the Columns tab on the right side.
• By default, the "Create columns automatically at runtime" checkbox is checked, but we'll want to uncheck this, then add the columns manually.
• In the Available Columns list box, select each of the following columns, then the button with an > arrow to move them to the Selected Columns list box: emp_id, fname, minit, lname, job_lvl, hire_date. DO NOT select the job_id . . . we have something special in store for this column.
• Next, scroll down in the Available Columns list box and select the Templated Column entry, then the button with an > arrow. In the Template Column Properties area, fill in the Header Text box with "Job Description". Select the OK button to continue.
• Switch to the HTML view of the WebForm1.aspx file, and modify the "Job Description" column to look like this:

  <asp:TemplateColumn HeaderText="Job Desc">
   <ItemTemplate>
    <asp:Label runat=server Text='<%# BindJob(Container.DataItem) %>' ID="Label1">
    </asp:Label>
   </ItemTemplate>
  </asp:TemplateColumn>
  

• A little explanation may be needed here. Notice that we are placing a Label inside the <ItemTemplate> section of the <TemplateColumn>. We then set the Text property of the Label to the return value of a method called BindJob() in our code. We pass in the Container.DataItem which contains the current DataRowView. This method will take the current DataRowView, find the relationship between the Employee table and the Job table, and return the Job Description field that corresponds with the current row.
• In the code-behind for WebForm1.aspx, we add the following public function.

      Public Function BindJob(ByVal o As Object) As String
  
          Dim drv As DataRowView = o
  
          Dim dr As Employees.employeeRow = drv.Row
  
          'Dim dp As Employees.jobsRow = dr.GetParentRow("jobsemployee")
          'Return dp.job_desc
          ' Alternatively, since this is strongly typed:
          Return dr.jobsRow.job_desc
  
      End Function
  

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:

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

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.

Rate this Tip To rate tips, you must be a member of SearchSOA.com. Register now to start rating these tips. Log in if you are already a member. Digg This!     StumbleUpon     Del.icio.us    RELATED CONTENT Microsoft .NET Web services Microsoft preps .NET 4.0 - framework improves on REST, MVC, JQuery support How do I balance throughput requirements and interoperability? APM software traces transactions across tiers, technologies How you can learn M Grammar for Oslo modeling Legacy modernization opens Windows for publisher Former .NET Web developers ride Ruby and Rails application framework Microsoft Oslo at PDC: Dial 'M' for modeling language Yahoo proxy fight looms New Microsoft site for architects LAMP coders go hybrid route Microsoft .NET Web services Research .NET Developer Programming Indigo DataSets and Web services don't mix Security in .NET 2.0 Tracking down managed memory leaks Handling exceptions in .NET .NET Compact Framework graphics The Data Access Application Block A great .NET resource: .Net2TheMax Delegates vs. interfaces in .NET Project structure best practices Web Services Basics Gartner Summit round-up Denmark is first to adopt Universal Business Language XML-based financial standard gets new support SOAP test tool adds WS-Security, MIME support Survey: IT spending back from the dead WASP Server adds to XML Schema support IBM, Microsoft on opposite sides of standards fence WS orchestration 'power struggle' under way, IDC says Management tools raise privacy alarms Internet millionaire gives integration a whirl RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary Common Language Infrastructure  (SearchSOA.com) Visual J#  (SearchSOA.com) RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.