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>
