Connecting a gridview to multiple tables linqdatasource

Question

Hi folks,

I'm just playing with some linq and asp.net - absolute beginner so I'm not even sure exactly how to ask my question.

Anyway, I have a MSSQL database with asp membership info in it. For various reasons (mainly to store profile info in clear columns instead of all in one) I'm using a custom profile provider so my profile information is spread across a few tables in my database.

I have the normal aspnet_membership and aspnet_profle, but then I also have a tblUserProfile table, in which I store a bunch of user profile information like first name, phone number etc. The tblUserProfile also has a companyID in it, referring to a seperate table with a list of companies in it.

All tables have a GUID UserId as their key. I created a datamodel diagram that contains all the tables I'm using and it shows the keys linking up etc properly.

So now I have a gridview that uses a LinqDataSource that is connected to the aspnet_membership table. This bit so far works well, I'm able to display all the info in the aspnet_membership table. I also figured out how to show the company a user is in like this:

                    <asp:TemplateField HeaderText="Company" SortExpression="tblUserProfile.Company.CompanyName">
                        <ItemTemplate>
                            <%#Eval("tblUserProfile.Company.CompanyName") %>
                        </ItemTemplate>

What I can't figure out is how to make changes to this save to the database. If I change direct fields in aspnet_membership table, they update properly.

I created a dropdown showing all available companies, you can select the company to change directly in the grid, but when I try to update it reverts back to the original value.

                    <asp:TemplateField HeaderText="Company" SortExpression="tblUserProfile.Company.CompanyName">
                        <ItemTemplate>
                            <%#Eval("tblUserProfile.Company.CompanyName") %>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:DropDownList ID="CompanyDropDownList"
                                                DataSourceID="CompanyDataSource"
                                                DataValueField="Id"
                                                DataTextField="CompanyName"
                                                SelectedValue='<%#Bind("tblUserProfile.CompanyID") %>'
                                                runat="server">
                                                </asp:DropDownList>

                        </EditItemTemplate>
                    </asp:TemplateField>

I'm not sure if it's because my datasource is connecting to one table (aspnet_membership) but the value I'm trying to change is in tblUserProfile. It seems that I can retrieve/display values from the other tables connected via foreign keys, but can I also update values in those tables?

Sorry for a long winded question, but I'm pretty new to this so aren't sure exactly where the problems are otherwise I'd be more specific.

Thanks for any pointers

Answer 1

It sounds like you are trying to use the automatic updating provided by the LinqDataSource but as you point out, that doesn't work properly with joined data. That is a limitation of the automatic updating. You can however subscribe to one of the OnUpdating events and write some code to handle the additional updating. How exactly that should be written depends on the names of your key values, etc. but there should be some information about it on this site and elsewhere.

Answer 2

You can create a custom databound control to edit the sub objects

    public class CompositeBoundField : BoundField
{
    protected override object GetValue(Control controlContainer)
    {
        object item = DataBinder.GetDataItem(controlContainer);
        return DataBinder.Eval(item, this.DataField);
    }
}

And then use this in a page like so:

         <cc:CompositeBoundField DataField="aspnet_Membership.Email" HeaderText="Email"/>

Then add this to the web.config:

<pages>
        <controls>
            <add assembly="App_Code" namespace="CustomControls" tagPrefix="cc"/>
        </controls>

Let me know if this helps by giving me an up-check. It is possible to create other bound fields for checkbox or other types. This may have been the original author/source for this method:

http://iridescence.no/post/FixingBoundFieldSupportforCompositeObjects.aspx