January 2006 Archives

The other day at work I figured out a clever way to nest Gridview controls without doing any work with Datasets in the code-behind, which is the the only other method I've ever found. Since I haven't seen any instances of my method on the 'net, I thought I'd write up a quick blog entry so other folks could benefit. If you are using standard paging, the most data source connections you will have will be eleven; hardly cause for worrying about server utilization.

1. First off, create a standard .aspx web forms page and drag a Gridview and a SqlDataSource (my example will use the AdventureWorks Sample DB).
2. Configure the SqlDataSource to connect to the Production.Products table. The code should look like:
   

   <asp:SqlDataSource ID="SqlDataSource1" runat="server"

   ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString %>"

   SelectCommand="SELECT * FROM Production.Product">

   </asp:SqlDataSource>

3. Next, configure the GridView to use the SqlDataSource and add a few basic columns, including ProductID as the last column as a TemplateField. This is where we will embed the nested GridView, so make sure you use the ItemTemplate and delete the EditItemTemplate. Here is my example:
   

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

   DataSourceID="SqlDataSource1"

   AllowPaging="True"

   AllowSorting="True" AutoGenerateColumns="False">

   <Columns>

   <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />

   <asp:BoundField DataField="ProductNumber" HeaderText="Product Number" SortExpression="ProductNumber" />

   <asp:BoundField DataField="Color" HeaderText="Color" SortExpression="Color" />

   <asp:BoundField DataField="ListPrice" DataFormatString="{0:c}" HeaderText="List Price"

   HtmlEncode="False" SortExpression="ListPrice" />

   <asp:TemplateField HeaderText="Product List Price History" InsertVisible="False" SortExpression="ProductID">

   <ItemTemplate>

   </ItemTemplate>

   </asp:TemplateField>

   </Columns>

   </asp:GridView

4. Next, drag another GridView and a SqlDataSource into the ItemTemplate section of the TemplateField in the source code editor. See below:
   

   <asp:TemplateField HeaderText="Product List Price History" InsertVisible="False" SortExpression="ProductID">

   <ItemTemplate>

   <asp:GridView ID="GridView2" runat="server">

   </asp:GridView>

   <asp:SqlDataSource ID="SqlDataSource2" runat="server"></asp:SqlDataSource>

   </ItemTemplate>

   </asp:TemplateField>

5. Now switch to Design view and click Edit Templates on the SmartButton for GridView1. Now point the SqlDataSource at the ProductListPriceHistory table and then bind GridView2 to SqlDataSource2. In source view, the ItemTemplate section should now look like this:
   

   <ItemTemplate>

   <asp:GridView ID="GridView2" runat="server"

   AutoGenerateColumns="False"

   DataKeyNames="ProductID,StartDate"

   DataSourceID="SqlDataSource2">

   <Columns>

   <asp:BoundField DataField="ListPrice"

   DataFormatString="{0:c}"

   HeaderText="List Price"

   HtmlEncode="False" SortExpression="ListPrice" />

   <asp:BoundField DataField="ModifiedDate"

   DataFormatString="{0:d}"

   HeaderText="Modified Date"

   HtmlEncode="False" SortExpression="ModifiedDate" />

   </Columns>

   </asp:GridView>

   <asp:SqlDataSource ID="SqlDataSource2" runat="server"

   ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString %>"

   SelectCommand="SELECT * FROM Production.ProductListPriceHistory">

   </asp:SqlDataSource>

   </ItemTemplate>

6. Now comes the sneaky part. Were are going to use the String.Format functionality of the Eval one-way databinding syntax to dynamically change the SelectCommand for SqlDataSource2 for each parent row in GridView1. Here's what it looks like:
   

   <asp:SqlDataSource ID="SqlDataSource2" runat="server"

   ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString %>"

   SelectCommand='<%# Eval("ProductID", "SELECT * FROM Production.ProductListPriceHistory WHERE ProductID = {0} ")%>' >

   </asp:SqlDataSource>

   
   Notice that we have to use single quotes to delimit the SelectCommand property so that the Eval engine can then use double quotes.
7. If so set up everything right (and I copied it into this listing right) you should see output like this on Page 31 of the GridView1 control:
   

Spokane Community College has been enrolled in the Oxfam America Collegiate Click Drive on the Poverty Fighters website.  The donations go around the world to fund micro-loans to help families in dire poverty start small businesses.  Every time you click, the sponsors donate 25 cents.  Click here to go to the website and "donate".  Scroll down and select Spokane Community College and help my school rank higher than last year.  Each computer can do two clicks per day.  There are a lot of good stories to read.  It really reminded me that despite the difficulties of the last year or so, we are truly blessed. 

 In other school news, I got back my first test score in Organic Chemistry and I aced it!  WOO HOO!  I was also invited to join Phi Theta Kappa, the international honor society of the two-year college.  I qualified because I have kept my gpa above 3.6 for three quarters, with a full load.  I'm very excited about it; I'll get to graduate with honor stoles! 

 

 

For the first time in my life, and for the first time in franchise history, the Seattle Seahawks are going to the Superbowl.  That's football for all you foreign-types :)  In a sound 34-14 trouncing of the Carolina Panthers yesterday afternoon, the 'Hawks won the NFC Championships and earned the the right to face the Pittsburgh Steelers in Detroit for Superbowl 40.  Waahoo!

Browsers: dump your caches! My blog has a new look showing off my mad Photoshop skillz. Carbon fibre, shiny red plastic buttons and see-thru lozenges. It's all new from the ground up (I actually started over from the default templates and style sheets in MT). Not much going on this weekend, as you probably could have guessed :) Jakki went to a hockey game last night and I stayed up working the tutorials on www.tutorialized.com. Emelie is here now, demo-ing her first attempt at Jakki-mole.

In other, less geeky, news: congrats to the Seahawks for getting into the NFC division championships and winning a playoff game for the first time since 1984.

Update 1/16/06 - I fixed the text color on the textboxes so a normal human can see it. Before only people wearing nightvision goggles or mutants with infravision could read it.

It's my birthday. Aside from the obvious, I know it because I felt about a thousand years old when I got up this morning. Before I got up, the cats (include the new Cleo, more on her later) had dumped the trash over in search of chicken bones and commenced to running around at 4:30am. Then, while I was in the shower, one of them discovered a packet of catnip and they proceeded to spread it over the hall runner and get stoned out of their teeny tiny furry minds. Tigger was just rolling around on the pile of scattered catnip, his eyes as big as saucers.

Eventually Jakki had to get up as well and we both stomped around herding cats and righting upset trash bins. If you know what's good for you, you really don't want to get Jakki up in those wee small hours of the morning. Believe me, at 5:30am I could have SOOOO gone back to bed. Then 6:00am arrived with my sister's traditional Beatle's Birthday call. It was nice, and it's good to hear happy tidings after a monring like that.

So here I sit at my computer at work at 3:15pm daydreaming of a soft, warm bed. Today on this eleventh day of January, two thousand and six, I'm chronologically 31 years old-- but I feel just as young as the hills around me. *snore*