Navigation

Home
Documentation
Download
Forums
Samples
FAQ
Team
Support
Project
Quick Search

Advanced Search »
Help Wanted! If you are using .netTiers and find it as invaluable as we do, please consider giving back to the .netTiers team by helping with our effort to fully document .netTiers. To help, simply create an account and you will then be able to edit this wiki.
Back History
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'''{BR}
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:
<code lang="SQL" linenumbers="false">
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
</CODE>
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()".{BR}
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.{BR}
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.{BR}
Next add the GridView to the page:
{BR}
<code lang="XML" linenumbers="false">
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</code>
Then in the code behind of the page add your "using" statements:
<code lang="C#" linenumbers="false">
using NetTiersDoc.Data;
using NetTiersDoc.Entities;
</code>
Next create a method for binding your GridView:
<code lang="C#" linenumbers="false">
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();
}
</code>
Call your method during page load and it wil fill your grid:
<code lang="C#" linenumbers="false">
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindGridView();
    }
}
</code>
{BR}
[imageauto|Orders List Grid|{UP}JoinedList1.jpg]

'''2. Deepload from the code behind'''{BR}
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.{BR}
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.{BR}
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:
<code lang="XML" linenumbers="false">
<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>
</code>  
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".{BR} 
Now in our code behind we can add the following code to our page load:
<code lang="C#" linenumbers="false">
//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();
</code>

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: <code lang="C#">typeof(TList<Customers>)</code>. 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.

[imageauto|Orders List Grid using Deepload and Template Columns|{UP}JoinedList2.jpg]

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.'''{BR}
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:
<code lang="XML" linenumbers="false">
<data:OrdersDataSource EnableDeepLoad="true" SelectMethod="GetAll" runat="server" ID="dsOrders">
    <DeepLoadProperties>
        <Types>
            <data:OrdersProperty Name="Customers" />
        </Types> 
    </DeepLoadProperties> 
</data:OrdersDataSource>
</code>{BR}
{BR}
Now we add the GridView, pointing it to our new datasource by seeting the "DataSourceID".
<code lang="XML" linenumbers="false">
<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>
</code>{BR}
Now we have the same result, but this time without writing a single line of code.