Random Dev Notes - LINQ to VFP

Wednesday, September 09, 2009

For this example, I’ll modify the project created in LINQ to VFP – Example #2. I will add a new page that will use the Details View control. This new page will include the ability to insert a Product.

Page Setup: Add a new page Example3 to the project:

Example3.aspx

         <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Example3.aspx.cs" Inherits="Example3" %>

        <!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>

                    <div style="color:Red;">

                        <asp:Literal ID="ErrorMessage" runat="server" EnableViewState="false" />

                    </div>

                    <asp:DetailsView ID="ProductDetailsView"

                                     runat="server"

                                     DataSourceID="ProductDataSource" 

                                     DataKeyNames="ProductId" 

                                     AutoGenerateRows="False" 

                                     OnItemUpdated="ProductDetailsView_ItemUpdated"

                                     OnItemDeleted="ProductDetailsView_ItemDeleted"

                                     OnItemInserted="ProductDetailsView_ItemInserted">

                        <Fields>

                            <asp:BoundField DataField="ProductID" HeaderText="Product Id" ReadOnly="True" />

                            <asp:BoundField DataField="ProductName" 

                                            HeaderText="ProductName" 

                                            SortExpression="ProductName" />

                            <asp:TemplateField HeaderText="Supplier" SortExpression="Supplier.CompanyName">

                                <ItemTemplate>

                                    <%# Eval("Supplier.CompanyName")%>

                                </ItemTemplate>

                                <EditItemTemplate>

                                    <asp:DropDownList ID="DropDownList1"

                                                      DataSourceID="SupplierDataSource" 

                                                      DataValueField="SupplierId" 

                                                      DataTextField="CompanyName" 

                                                      SelectedValue='<%# Bind("SupplierId") %>' 

                                                      runat="server" />

                                </EditItemTemplate>

                            </asp:TemplateField>

                            <asp:TemplateField HeaderText="Category" SortExpression="Category.CategoryName">

                                <ItemTemplate>

                                    <%# Eval("Category.CategoryName")%>

                                </ItemTemplate>

                                <EditItemTemplate>

                                    <asp:DropDownList ID="DropDownList2" 

                                                      DataSourceID="CategoryDataSource" 

                                                      DataValueField="CategoryId" 

                                                      DataTextField="CategoryName" 

                                                      SelectedValue='<%# Bind("CategoryId") %>' 

                                                      runat="server" />

                                </EditItemTemplate>

                            </asp:TemplateField>

                            <asp:BoundField DataField="UnitPrice" 

                                            HeaderText="UnitPrice" />

                            <asp:BoundField DataField="UnitsInStock" 

                                            HeaderText="UnitsInStock" />

                            <asp:BoundField DataField="UnitsOnOrder" 

                                            HeaderText="UnitsOnOrder" />

                            <asp:CheckBoxField DataField="Discontinued" 

                                               HeaderText="Discontinued" />

                            <asp:CommandField ShowEditButton="True" />

                            <asp:CommandField ShowDeleteButton="True" />

                            <asp:CommandField ShowInsertButton="True" />

                        </Fields>

                    </asp:DetailsView>

                    <iqw:DataSource ID="ProductDataSource" 

                                    runat="server" 

                                    ContextTypeName="WebExample.Model.Northwind" 

                                    TableName="Products" 

                                    RetrieveGeneratedId="True"

                                    EnableDelete="true"

                                    EnableInsert="true"

                                    EnableUpdate="true"

                                    OnInserted="ProductDataSource_Inserted">

                    </iqw:DataSource>    

                    <iqw:DataSource ID="CategoryDataSource" 

                                    runat="server" 

                                    ContextTypeName="WebExample.Model.Northwind" 

                                    TableName="Categories" />

                    <iqw:DataSource ID="SupplierDataSource" 

                                    runat="server" 

                                    ContextTypeName="WebExample.Model.Northwind" 

                                    TableName="Suppliers" />          

                </div>

            </form>

        </body>

        </html>

Example3.cs

        using System;

        using System.Web.UI.WebControls;

        using WebExample.Model;

        public partial class Example3 : System.Web.UI.Page {

            protected void Page_Load(object sender, EventArgs e) {

                if (!this.IsPostBack) {

                    this.ProductDetailsView.ChangeMode(DetailsViewMode.Insert);

                }

            }

            protected void ProductDataSource_Inserted(object sender, LinqDataSourceStatusEventArgs e) {

                Product p = e.Result as Product;

                this.ProductDataSource.Where = "ProductId = " + p.ProductID;

            }

            protected void ProductDetailsView_ItemUpdated(object sender, DetailsViewUpdatedEventArgs e) {

                if (e.Exception != null) {

                    this.ErrorMessage.Text = e.Exception.Message;

                    e.ExceptionHandled = true;

                    e.KeepInEditMode = true;

                }

            }

            protected void ProductDetailsView_ItemInserted(object sender, DetailsViewInsertedEventArgs e) {

                if (e.Exception != null) {

                    this.ErrorMessage.Text = e.Exception.Message;

                    e.ExceptionHandled = true;

                }

            }

            protected void ProductDetailsView_ItemDeleted(object sender, DetailsViewDeletedEventArgs e) {

                if (e.Exception != null) {

                    this.ErrorMessage.Text = e.Exception.Message;

                    e.ExceptionHandled = true;

                }

            }

        }

With the new page created, it is time to test the insert feature. Start by adding new Product information and then click the Insert link. At this point an exception has been thrown indicating that the “Field ProductId is read-only.” Now what does that mean? It means that I finally need to do a little explaining about how Mapping works in my examples.

Implicit Mapping: Up until this point I’ve been able to use Implicit Mapping. Implicit mapping allowed me to simple create data classes and let IQToolkit connect the classes to the FoxPro Tables.

Here are a couple key points about Implicit Mapping:

The Primary Key field must end with “ID” (upper case required).
Can handle singular and plural naming issues. Notice in the image below that the class name is singular and the table name is plural.
Associations are determined by matching properties.
Cannot determine if a Primary Key an auto generated value.

After my brief explanation of Implicit Mapping and with knowing about the Products table structure it should obvious why we cannot insert the new Product. The Implicit Mapping is trying to insert a value into the auto generated primary key field – ProductId. You can find the insert statement in the Output Window when in debug mode.

If you copy the insert statement and run it in VFP you will see that you get the same error.

So how do we fix this error…? It is time to stop using Implicit Mapping and start using a more explicit type of mapping. The IQToolkit includes two other type of mappings. I will use Attribute Mapping to finish up this example.

Three changes are required to setup the Attribute mapping.

The IEntityTable<T> properties of the Northwind class need to be set as virtual.
Add a new class (NorthwindAttributes.cs) that includes all the attributes.
Modify the Northwind class to include the NorthwindAttribute class as the second parameter to the base constructor.

After making these changes you should see that the insert is working as expected.

LINQ to VFP

9/9/2009 6:32 AM Eastern Daylight Time

Disclaimer | Comments [0] |

On this page....

LINQ to VFP - Example #3