Display Related Data in an ASP.NET GridView

Typically when you display a datagrid you will want to show data from a related table. There are 3 different ways to accomplish this with NetTiers. You can use a custom stored procedure, deepload functionality from the code behind, or a strongly typed datasource control. We'll provide an example of each.

1. Custom Stored Procedure
NetTiers allows you to write custom stored procedures which are translated into methods that return a standard DataSet. The stored procedure must be named _Table_Method (Substituting Table with the name of the table where you want to method to appear). In our example we are going to create a stored procedure with our related data for the Northwinds Orders table. Our stored procedure looks like the following:

 1CREATE PROCEDURE [dbo].[_Orders_OrderListJoined] 
 2
 3AS
 4
 5SELECT{BR} 
 6Orders.[OrderID],{BR} 
 7Orders.[CustomerID],{BR} 
 8Customers.CompanyName,{BR}
 9Customers.ContactName,{BR}
10Orders.[EmployeeID],{BR} 
11Orders.[OrderDate],{BR} 
12Orders.[RequiredDate],{BR} 
13Orders.[ShippedDate],{BR} 
14Orders.[ShipVia],{BR} 
15Orders.[Freight],{BR} 
16Orders.[ShipName],{BR} 
17Orders.[ShipAddress],{BR} 
18Orders.[ShipCity],{BR} 
19Orders.[ShipRegion],{BR} 
20Orders.[ShipPostalCode],{BR} 
21Orders.[ShipCountry]{BR} 
22FROM [dbo].[Orders]{BR}
23LEFT OUTER JOIN Customers on [dbo].[Orders].CustomerID = Customers.CustomerID{BR}
24
25GO

Notice the name of our stored procedure "_Orders_OrderListJoined", this will create a method under our "OrdersProvider" which we can access by using "DataRepository.OrdersProvider.OrderListJoined()".
Be sure that when you create the procedure that there are no spaces in front of the procedure and that the word "AS" is on its own line. If you get the error "Custom sp_TABLE_METHOD is not in the expected format." it is likely due to the way the stored procedure is formatted. SQL server may allow it, but NetTiers is a bit more particular.
To create the DataRepository.OrdersProvider.OrderListJoined() method regrnerate your code again. This method, like all custom methods, will return a DataSet which can be assigned to a GridView in the normal way.
Next add the GridView to the page:

1<asp:GridView ID="GridView1" runat="server">
2</asp:GridView>

Then in the code behind of the page add your "using" statements:

1using NetTiersDoc.Data;
2using NetTiersDoc.Entities;

Next create a method for binding your GridView:

 1private void BindGridView()
 2...{
 3  //Create the Dataset from the custom method
 4  DataSet dsOrderList = DataRepository.OrdersProvider.OrderListJoined();
 5
 6  //Assign the dataset to the gridview
 7  GridView1.DataSource = dsOrderList;
 8
 9  //bind the gridview
10  GridView1.DataBind();
11}

Call your method during page load and it wil fill your grid:

1protected void Page_Load(object sender, EventArgs e)
2...{
3    if (!IsPostBack)
4    ...{
5        BindGridView();
6    }
7}

Orders List Grid

2. Deepload from the code behind
This method is preferred over the "Custom Stored Procedure" method of getting related data, but you still may find times when the previous method may be a better fit.
Every entity has a corresponding "DeepLoad" method. This method uses the metadata in SQL to retrive information about the relationships between your tables. This makes it easy to get a related column.
In our example the Northwinds Order table has a foreign key named "CustomerID" which ties it to the Customers table. Deepload is not turned on by default and must be explicity called when you want to work with an entities related data. Once an entity is "deeploaded" its related data can be refrenced via "ForeignKeySource.ForeignTableProperty". As in our previous example we create a GridView to hold our data:

 1<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
 2    <Columns>
 3        <asp:BoundField DataField="OrderId" HeaderText="Order ID" />
 4        <asp:BoundField DataField="CustomerID" HeaderText="Customer ID" />
 5        <asp:TemplateField>
 6           <HeaderTemplate>Company Name</HeaderTemplate>
 7           <ItemTemplate>
 8              <%#Eval("CustomerIDSource.CompanyName") %>
 9           </ItemTemplate>
10        </asp:TemplateField>
11        <asp:TemplateField>
12           <HeaderTemplate>Company Name</HeaderTemplate>
13           <ItemTemplate>
14              <%#Eval("CustomerIDSource.ContactName") %>
15           </ItemTemplate>
16        </asp:TemplateField>
17    </Columns>
18 </asp:GridView>

This time however, you'll notice that we are using Template columns with the Eval method and we reference the related data by using our special deepload property "CustomerIDSource.ContactName".
Now in our code behind we can add the following code to our page load:

1//Get the list
2TList<Orders> orderList = DataRepository.OrdersProvider.GetAll();
3
4//Deepload the list
5DataRepository.OrdersProvider.DeepLoad(orderList, true, DeepLoadType.IncludeChildren, typeof(Customers));
6
7//Assign the list to the grid and bind it
8GridView1.DataSource = orderList;
9GridView1.DataBind();

The last parameter "typeof(Customers)" is simply the list of entities you want to explicity deepload. To add more just seperate the list with commas. Don't forget that if your adding a related collection that you'll need to use the collection type like:

1typeof(TList<Customers>)

. There is also a DeepLoadType.ExcludeChildren for times when it might be easier just to exclude the one or two entitites you don't want to deepload. Once the page loads we have a grid with related data.

Orders List Grid using Deepload and Template Columns

One of the reasons that this method is probably preferred is that we didn't have to do any additional coding to achieve the same result as creating a custom procedure. A big time saver!

3. Strongly typed datasource control.
It might be hard to believe, but it gets even easier. NetTiers provides us with a set of strongly typed datasources. When you use this method it does not require any code at all!
As before we create the GridView in the page, but this time we also add a datasource:

1<data:OrdersDataSource EnableDeepLoad="true" SelectMethod="GetAll" runat="server" ID="dsOrders">
2    <DeepLoadProperties>
3        <Types>
4            <data:OrdersProperty Name="Customers" />
5        </Types> 
6    </DeepLoadProperties> 
7</data:OrdersDataSource>

Now we add the GridView, pointing it to our new datasource by seeting the "DataSourceID".

 1<asp:GridView ID="GridView1" runat="server" DataSourceID="dsOrders" AutoGenerateColumns="False">
 2        <Columns>
 3            <asp:BoundField DataField="OrderId" HeaderText="Order ID" />
 4            <asp:BoundField DataField="CustomerID" HeaderText="Customer ID" />
 5            <asp:TemplateField>
 6               <HeaderTemplate>Company Name</HeaderTemplate>
 7               <ItemTemplate>
 8                  <%#Eval("CustomerIDSource.CompanyName") %>
 9               </ItemTemplate>
10            </asp:TemplateField>
11            <asp:TemplateField>
12               <HeaderTemplate>Company Name</HeaderTemplate>
13               <ItemTemplate>
14                  <%#Eval("CustomerIDSource.ContactName") %>
15               </ItemTemplate>
16            </asp:TemplateField>
17        </Columns>
18     </asp:GridView>

Now we have the same result, but this time without writing a single line of code.