I have two database tables, one for Users of a web site, containing the fields "UserID", "Name" and the foreign key "PageID". And the other with the fields "PageID" (here the primary key), and "Url".
I want
to be able
to show the data in a gridview with data from both tables, and I'd like
to do it with databinding in the aspx page.
I'm not sure how
to do this, though, and I can't find any good examples of this particular situation. Here's what I have so far:
<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
CodeBehind="Default.aspx.cs" Inherits="LinqBinding._Default" %>
<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
<h2>
Testing
LINQ
</h2>
<asp:GridView ID="GridView1" runat="server" DataSourceID="LinqDataSourceUsers" AutoGenerateColumns="false">
<Columns>
<asp:CommandField ShowSelectButton="True" />
<asp:BoundField DataField="UserID" HeaderText="UserID" />
<asp:BoundField DataField="Name" HeaderText="Name" />
<asp:BoundField DataField="PageID" HeaderText="PageID" />
<asp:TemplateField HeaderText="Pages">
<ItemTemplate
<asp:DropDownList ID="DropDownList1"
DataSourceID="LinqDataSourcePages"
SelectedValue='<%#Bind("PageID") %>'
DataTextField="Url"
DataValueField="PageID"
runat="server">
</asp:DropDownList>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:LinqDataSource ID="LinqDataSourcePages" runat="server"
ContextTypeName="LinqBinding.UserDataContext" EntityTypeName=""
TableName="Pages">
</asp:LinqDataSource>
<asp:LinqDataSource ID="LinqDataSourceUsers" runat="server"
ContextTypeName="LinqBinding.UserDataContext" EntityTypeName=""
TableName="Users">
</asp:LinqDataSource>
</asp:Content>
But this only works in so far as it gets the user table into the gridview (that's not a problem), and I get the page data into the dropdown, but here's the problem: I of course get ALL the page data in there, not just the pages for each user on each row. So how do I put some sort of "where" constraint on dropdown for each row
to only show the pages for the user in that row? (Also,
to be honest I'm not sure I'm getting the foreign key relationship right, because I'm not too used
to working with relationships).
EDIT:
I think I have set up the relationship incorrectly. I keep getting the message that "Pages" doesn't exist as a property on the User object. And I guess it can't since the relationship right now is one way. So I tried
to create a many-to-many relationship. Again, my database knowledge is a bit limited, but I added a so called "junction table" with the fields UserID and PageID, same as the other tables' primary keys. I wasn't able
to make both of these primary keys in the junction table though (which it looked like some people had in examples I've seen...but since it wasn't possible I guessed they shouldn't be). Anyway, I created a relationship from each table and created new
LINQ classes from that.
But then what do I do? I set the junction table as the
Linq data source, since I guessed I had
to do this
to access both tables, but that doesn't work. Then it complains there is no Name property on that object. So how do I access the related tables? Here's what I have now with the many-to-many relationship:
<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
CodeBehind="Default.aspx.cs" Inherits="ManyToMany._Default" %>
<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
<h2>
Many
to many
LINQ
</h2>
<asp:GridView ID="GridView1" runat="server" DataSourceID="LinqDataSource1" AutoGenerateColumns="false">
<Columns>
<asp:CommandField ShowSelectButton="True" />
<asp:BoundField DataField="UserID" HeaderText="UserID" />
<asp:BoundField DataField="Name" HeaderText="Name" />
<asp:BoundField DataField="PageID" HeaderText="PageID" />
<asp:TemplateField HeaderText="Pages">
<ItemTemplate>
<asp:DropDownList ID="DropDownList1"
DataSource='<%#Eval("Pages") %>'
SelectedValue='<%#Bind("PageID") %>'
DataTextField="Url"
DataValueField="PageID"
runat="server">
</asp:DropDownList>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:LinqDataSource ID="LinqDataSource1" runat="server" ContextTypeName="ManyToMany.UserPageDataContext"
EntityTypeName="" TableName="UserPages">
</asp:LinqDataSource>
</asp:Content>
