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:

CREATE PROCEDURE [dbo].[_Orders_OrderListJoined] 

AS

SELECT{BR} 
Orders.[OrderID],{BR} 
Orders.[CustomerID],{BR} 
Customers.CompanyName,{BR}
Customers.ContactName,{BR}
Orders.[EmployeeID],{BR} 
Orders.[OrderDate],{BR} 
Orders.[RequiredDate],{BR} 
Orders.[ShippedDate],{BR} 
Orders.[ShipVia],{BR} 
Orders.[Freight],{BR} 
Orders.[ShipName],{BR} 
Orders.[ShipAddress],{BR} 
Orders.[ShipCity],{BR} 
Orders.[ShipRegion],{BR} 
Orders.[ShipPostalCode],{BR} 
Orders.[ShipCountry]{BR} 
FROM [dbo].[Orders]{BR}
LEFT OUTER JOIN Customers on [dbo].[Orders].CustomerID = Customers.CustomerID{BR}

GO

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:

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

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

using NetTiersDoc.Data;
using NetTiersDoc.Entities;

Next create a method for binding your GridView:

private void BindGridView()
{
  //Create the Dataset from the custom method
  DataSet dsOrderList = DataRepository.OrdersProvider.OrderListJoined();

  //Assign the dataset to the gridview
  GridView1.DataSource = dsOrderList;

  //bind the gridview
  GridView1.DataBind();
}

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

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindGridView();
    }
}

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:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
    <Columns>
        <asp:BoundField DataField="OrderId" HeaderText="Order ID" />
        <asp:BoundField DataField="CustomerID" HeaderText="Customer ID" />
        <asp:TemplateField>
           <HeaderTemplate>Company Name</HeaderTemplate>
           <ItemTemplate>
              <%#Eval("CustomerIDSource.CompanyName") %>
           </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField>
           <HeaderTemplate>Company Name</HeaderTemplate>
           <ItemTemplate>
              <%#Eval("CustomerIDSource.ContactName") %>
           </ItemTemplate>
        </asp:TemplateField>
    </Columns>
 </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:

//Get the list
TList<Orders> orderList = DataRepository.OrdersProvider.GetAll();

//Deepload the list
DataRepository.OrdersProvider.DeepLoad(orderList, true, DeepLoadType.IncludeChildren, typeof(Customers));

//Assign the list to the grid and bind it
GridView1.DataSource = orderList;
GridView1.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: typeof(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.

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!{BR} As before we create the GridView in the page, but this time we also add a datasource:

<data:OrdersDataSource EnableDeepLoad="true" SelectMethod="GetAll" runat="server" ID="dsOrders">
    <DeepLoadProperties>
        <Types>
            <data:OrdersProperty Name="Customers" />
        </Types> 
    </DeepLoadProperties> 
</data:OrdersDataSource>

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

<asp:GridView ID="GridView1" runat="server" DataSourceID="dsOrders" AutoGenerateColumns="False">
        <Columns>
            <asp:BoundField DataField="OrderId" HeaderText="Order ID" />
            <asp:BoundField DataField="CustomerID" HeaderText="Customer ID" />
            <asp:TemplateField>
               <HeaderTemplate>Company Name</HeaderTemplate>
               <ItemTemplate>
                  <%#Eval("CustomerIDSource.CompanyName") %>
               </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField>
               <HeaderTemplate>Company Name</HeaderTemplate>
               <ItemTemplate>
                  <%#Eval("CustomerIDSource.ContactName") %>
               </ItemTemplate>
            </asp:TemplateField>
        </Columns>
     </asp:GridView>

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