Single web page, LinqDataSource, GridView to edit multiple data tables_问答_开发者

Problem is - new application, customer wants Linq, .NET 4.0, many data tables that are "categories" and "types" - simple data tables that are usually two fields: ID and Name (or description)

Instead of generating a web form to edit each of these data tables - wouldnt it be nice to only need one web page to edit all of them and you simply select the data table you want to edit. (BTW - where are the "pattern junkies". Isnt this a pattern? Where is the pattern .NET code template for this?)

I am playing with the Northwinds database. I added a table called DropDownConfiguration:

USE [Northwind]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DropDownConfiguration](
[DropDownConfigurationID] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[TableName] [nvarchar](50) NOT NULL,
[PrimaryKeyName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_DropDownConfiguration] PRIMARY KEY CLUSTERED 
(
[DropDownConfigurationID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,     ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Then I populated this data table with the following data:

DropDownConfigurationID   Name         TableName    PrimaryKeyName
1                       Categories     Categories   CategoryID
2                       Regions         Regions RegionID

asp.net web page looks like:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="EditMultiTable.aspx.vb"
Inherits="EditMultiTable" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
    <asp:LinqDataSource ID="LinqDataSourceMultiTable" runat="server" ContextTypeName="DataClassesDataContext"
        EnableInsert="True" EnableUpdate="True" EntityTypeName="" TableName="Categories">
    </asp:LinqDataSource>
    <asp:DropDownList ID="ddlDropDownConfigurations" runat="server" AutoPostBack="True">
    </asp:DropDownList>
    <asp:GridView ID="GridViewMultiTable" runat="server" DataKeyNames="CategoryID" DataSourceID="LinqDataSourceMultiTable">
        <Columns>
            <asp:CommandField ShowEditButton="True" />
        </Columns>
    </asp:GridView>
</div>
</form>
</body>
</html>

Code behind looks like:

Option Explicit On
Option Strict On

Partial Class EditMultiTable
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Dim myDropDownConfigurationList As List(Of DropDownConfiguration)

    If Not Page.IsPostBack Then
        ' Tell the drop down what data tables we want to be able to edit
        myDropDownConfigurationList = GetDropDownConfigurations()
        ddlDropDownConfigurations.DataSource = myDropDownConfigurationList
        ddlDropDownConfigurations.DataTextField = "Name"
        ddlDropDownConfigurations.DataValueField = "DropDownConfigurationID"
        ddlDropDownConfigurations.DataBind()
    End If
End Sub

Protected Sub ddlDropDownConfigurations_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlDropDownConfigurations.SelectedIndexChanged
    Dim myDropDownConfiguration As DropDownConfiguration
    Dim myDropDownConfigurationList As List(Of DropDownConfiguration)

    If ddlDropDownConfigurations.SelectedIndex > -1 Then
        ' clear out the old data bindings between the LinqDataSource and the GridView
        GridViewMultiTable.DataSourceID = Nothing
        GridViewMultiTable.DataSource = Nothing
        G开发者_JS百科ridViewMultiTable.DataKeyNames = Nothing
        GridViewMultiTable.DataMember = Nothing
        GridViewMultiTable.AutoGenerateColumns = False
        GridViewMultiTable.AutoGenerateEditButton = False
        GridViewMultiTable.DataBind()
        GridViewMultiTable.Columns.Clear()


        ' Set up the LinqDataSource for the new table
        myDropDownConfigurationList = GetDropDownConfigurations()
        myDropDownConfiguration = (From ddc In myDropDownConfigurationList Where ddc.DropDownConfigurationID = Long.Parse(ddlDropDownConfigurations.SelectedValue) Select ddc).FirstOrDefault()
        LinqDataSourceMultiTable.TableName = myDropDownConfiguration.TableName
        LinqDataSourceMultiTable.EntityTypeName = String.Empty
        LinqDataSourceMultiTable.EnableInsert = True
        LinqDataSourceMultiTable.EnableUpdate = True
        LinqDataSourceMultiTable.DataBind()

        ' bind the GridView to the LinqDataSource with the new data table
        GridViewMultiTable.DataSourceID = "LinqDataSourceMultiTable"
        GridViewMultiTable.DataKeyNames = New String() {myDropDownConfiguration.PrimaryKeyName}
        GridViewMultiTable.AutoGenerateColumns = True
        GridViewMultiTable.AutoGenerateEditButton = True
        GridViewMultiTable.DataBind()
    End If

End Sub

' Get my data table that lists the data tables that I want to configure
Function GetDropDownConfigurations() As List(Of DropDownConfiguration)
    Dim myDropDownConfigurationList As List(Of DropDownConfiguration)

    Using myDataClassesDataContext As New DataClassesDataContext
        myDropDownConfigurationList = (From ddc In myDataClassesDataContext.DropDownConfigurations Select ddc).ToList()
    End Using
    Return myDropDownConfigurationList
End Function
End Class

my problem is - either the LinqDataSource or the GridView is not being cleared. Its kind of using half of one data table and half of another. Which is why in my code behind - I have tried clearing the LinqDataSource and GridView in every way imaginable. If I run the program, select "Regions" in the drop down list box and then try to edit the first row of "regions" I got the following error:

DataBinding: 'Category' does not contain a property with the name 'RegionID'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.HttpException: DataBinding: 'Category' does not contain a property with the name 'RegionID'.

When you click "Edit" in gridview the Page_Load event will trigger and you will see that the LinqDataSourceMultiTable.TableName is "Categories" because this is what you last binded. You should rebind again your datasource if you want to perform actions on data displayed in gridview.I believe that is not necessary to do all that clearing section.

Dang it. The problem was so obvious - I am embarassed to admit it. My problem was that I did not do the standard gridview event handlers - on edit, on updating, on edit cancel. Will post the new code for anyone wanting to use this code:

Option Explicit On
Option Strict On

Partial Class EditMultiTable
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Dim myDropDownConfigurationList As List(Of DropDownConfiguration)

    If Not Page.IsPostBack Then
        ' Tell the drop down what data tables we want to be able to edit
        myDropDownConfigurationList = GetDropDownConfigurations()
        ddlDropDownConfigurations.DataSource = myDropDownConfigurationList
        ddlDropDownConfigurations.DataTextField = "Name"
        ddlDropDownConfigurations.DataValueField = "DropDownConfigurationID"
        ddlDropDownConfigurations.DataBind()
    End If
End Sub

Protected Sub ddlDropDownConfigurations_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlDropDownConfigurations.SelectedIndexChanged
    If ddlDropDownConfigurations.SelectedIndex > -1 Then
        BindData()
    End If
End Sub

Sub BindData()
    Dim myDropDownConfiguration As DropDownConfiguration
    Dim myDropDownConfigurationList As List(Of DropDownConfiguration)

    ' Set up the LinqDataSource for the new table
    myDropDownConfigurationList = GetDropDownConfigurations()
    myDropDownConfiguration = (From ddc In myDropDownConfigurationList Where ddc.DropDownConfigurationID = Long.Parse(ddlDropDownConfigurations.SelectedValue) Select ddc).FirstOrDefault()
    LinqDataSourceMultiTable.TableName = myDropDownConfiguration.TableName

    ' bind the GridView to the LinqDataSource with the new data table
    GridViewMultiTable.DataKeyNames = New String() {myDropDownConfiguration.PrimaryKeyName}
    GridViewMultiTable.DataBind()
End Sub

' Get my data table that lists the data tables that I want to configure
Function GetDropDownConfigurations() As List(Of DropDownConfiguration)
    Dim myDropDownConfigurationList As List(Of DropDownConfiguration)

    Using myDataClassesDataContext As New DataClassesDataContext
        myDropDownConfigurationList = (From ddc In myDataClassesDataContext.DropDownConfigurations Select ddc).ToList()
    End Using
    Return myDropDownConfigurationList
End Function

Protected Sub GridViewMultiTable_RowEditing(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs) Handles GridViewMultiTable.RowEditing
    GridViewMultiTable.EditIndex = e.NewEditIndex
    BindData()
End Sub

Protected Sub GridViewMultiTable_RowCancelingEdit(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCancelEditEventArgs) Handles GridViewMultiTable.RowCancelingEdit
    GridViewMultiTable.EditIndex = -1
    BindData()
End Sub

Protected Sub GridViewMultiTable_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles GridViewMultiTable.RowUpdating
    GridViewMultiTable.EditIndex = -1
    BindData()
End Sub

End Class