I'm a big fan of the XSD Schema or DataSet Designer in Visual Studio 2005. Knowing how to use this tool effectively can save you hundreds of hours in writing database layer code. Some balk at the idea, but I say I'm not a plumber - I'd rather spend time polishing the user interface than mucking around in the bowels of database connections.
One thing I've wondered since I first saw the Designer, was how to leverage the Relationships defined in the designer. For example, here is a simple DataSet based on some tables from the AdvertureWorks example database:
Now, our goal here is to leverage these relations while keeping our ObjectDataSource and GridView/DetailView features. Otherwise, what's the point of a tool that requires a code behind file for every page? Normally, an ObjectDataSource invokes a TableAdapter method to load a single DataTable. What we need to do is also load the other tables in the DataSet, and return the DataTable attached to the DataSet. Since TableAdapters are partial classes, we have a perfect place to do this.
using System;
using System.ComponentModel;
namespace ProductsTableAdapters {
public partial class ProductTableAdapter {
[DataObjectMethod(DataObjectMethodType.Select, false)]
public Products.ProductDataTable GetAllWithDataSet() {
Products products = new Products();
ProductModelTableAdapter taModel = new ProductModelTableAdapter();
ProductInventoryTableAdapter taInv = new ProductInventoryTableAdapter();
taModel.Connection = this.Connection;
taInv.Connection = this.Connection;
this.Connection.Open();
this.FillAll(products.Product);
taModel.FillAll(products.ProductModel);
taInv.FillAll(products.ProductInventory);
this.Connection.Close();
return products.Product;
}
}
}
Here is a new TableAdapter method for the Product table. The first step in the method is to create the products DataSet. This DataSet holds an empty DataTable for all the tables in the DataSet, and also includes all the relationship among the tables - we just need to add the data. This is done by getting TableAdapters for the other tables, and then using the Fill methods to fill the existing DataTables. Then we return just the Product DataTable as normal, but this table now has access to the other tables in the schema.
Okay, I skipped over the connection part. While it's not required, if I did nothing each TableAdapter would create, open and close it's own connection (or at least suck up a connection from the pool, then replace it). Since I know I'm about to make three database calls, it would make baby Jesus cry to allow the TableAdapters to act this way. TableAdapters are smart though, if you invoke a method with an already open connection it will not create a new connection, and leave the connection open when done.
By the way, if you need to alter a connection outside of a TableAdapter, then in the DataSet Designer, click on each of your TableAdapters and change the ConnectionModifer from "Assembly" to "Public".
Now all we need to do is connect up our web controls:
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
SelectMethod="GetAllWithDataSet" TypeName="ProductsTableAdapters.ProductTableAdapter">
</asp:ObjectDataSource>
<asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True" AutoGenerateRows="False"
DataKeyNames="ProductID" DataSourceID="ObjectDataSource1">
<FieldHeaderStyle Font-Bold="true" />
<Fields>
<asp:BoundField DataField="ProductNumber" HeaderText="Product Number"/>
<asp:TemplateField HeaderText="Model">
<ItemTemplate>
<asp:Label ID="Label1" runat="server"
Text='<%# ((System.Data.DataRowView)Container.DataItem).Row.GetParentRow("Product_ProductModel")["Name"] %>' />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
<asp:BoundField DataField="ListPrice" DataFormatString="{0:c}" HeaderText="List Price"
HtmlEncode="False" />
<asp:BoundField DataField="StandardCost" DataFormatString="{0:c}" HeaderText="Standard Cost"
HtmlEncode="False" />
<asp:BoundField DataField="Color" HeaderText="Color"/>
<asp:TemplateField ShowHeader="False">
<ItemTemplate>
<b>Inventory Status</b>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" Width="100%"
DataSource='<%# ((System.Data.DataRowView)Container.DataItem).Row.GetChildRows("ProductInventory_Product") %>'>
<Columns>
<asp:BoundField DataField="Name" HeaderText="Name" />
<asp:BoundField DataField="Shelf" HeaderText="Shelf" />
<asp:BoundField DataField="Bin" HeaderText="Bin" />
<asp:BoundField DataField="Quantity" HeaderText="Quantity" />
</Columns>
</asp:GridView>
</ItemTemplate>
</asp:TemplateField>
</Fields>
</asp:DetailsView>
For the most part, nothing special here. In the Model field, we need to walk the relationship to a parent record. The syntax it a bit long, but it gets the job done:
((System.Data.DataRowView)Container.DataItem).Row.GetParentRow("Product_ProductModel")["Name"]First part casts the record we are on as a DataRowView so we can call GetParentRow which takes the name of the relationship. Since we don't want the whole row, just a field, we call the index of the field. To get the child records of the ProductInventory table, the syntax is very similar:
((System.Data.DataRowView)Container.DataItem).Row.GetChildRows("ProductInventory_Product")
Note we assign this to the DataSource not DataSourceID attribute of the GridView. The output will look something like this:
Now that you know how to connect a DataSet easily to your ASP.NET view controls, it's worth the looking deeper into the DataSet. Then on your next project, let the framework do the plumbing while you go out and play.
Posted By Mike On Friday, April 20, 2007
Filed under datasets |
Comments (3)
Dan
-
Wednesday, November 28, 2007
4:55:04 AM
You can shorten the long databinding syntax in the aspx page by creating a protected method in the codebehind, such that:
Text='<%# ((System.Data.DataRowView)Container.DataItem).Row.GetParentRow("Product_ProductModel")["Name"] %>'
and
DataSource='<%# ((System.Data.DataRowView)Container.DataItem).Row.GetChildRows("ProductInventory_Product") %>'
turns into
Text='<%# GetModelName(Container.DataItem) %>'
DataSource='<%# GetInventoryStatus(Container.DataItem) %>'
and
private DataRow GetDataRow(object dataItem)
{
return ((System.Data.DataRowView)dataItem).Row;
}
protected string GetModelName(object dataItem)
{
return GetDataRow(dataItem).GetParentRow("Product_ProductModel")["Name"];
}
protected object GetModelName(object dataItem)
{
return GetDataRow(dataItem).GetParentRow("Product_ProductModel")["Name"];
}
protected string GetInventoryStatus(object dataItem) {
return GetDataRow(dataItem).GetChildRows("ProductInventory_Product");
}
Mike
-
Wednesday, November 28, 2007
2:37:24 PM
Great tip Dan! Sometimes... okay many times I miss the obvious.
Steveo
-
Friday, March 07, 2008
11:08:44 PM
Thank you so much! This is just what I was trying to figure out how to do.
