# Wednesday, September 09, 2009
LINQ to VFP - Example #3

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.
    image
           
  • Associations are determined by matching properties.
    image
  • 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.

image

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

image

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.

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

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]  | 
 # Monday, August 24, 2009
LINQ to VFP – Example #2

My first example was extremely limited in showing what could be accomplished using LINQ to VFP.  For this example, I would like to work though a LINQ to SQL example using LINQ to VFP as the data context.

Basic Setup:

  • Create a new Website.
  • Add references to IQToolkit.dll, LinqToVfp.dll, IQToolkitContrib.dll, and IQToolkitContrib.Web.dll
  • Add a Northwind connection string setting to the web.config 
    <connectionStrings>
        <add name="northwind"          
             providerName="System.Data.OleDb"          
             connectionString="Provider=VFPOLEDB.1;Data Source=**Your Path**\Northwind.dbc;"/>
    </connectionStrings>
  • Add a page control reference in the web.config to use IQToolkitContrib.Web.DataSource 
    <pages>
        <controls>
            <add tagPrefix="asp" namespace="System.Web.UI" assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
            <add tagPrefix="asp" namespace="System.Web.UI.WebControls" assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
            <add tagPrefix="iqw" namespace="IQToolkitContrib.Web" assembly="IQToolkitContrib.Web" />
        </controls>
    </pages>

Create Data Classes:

  • Add a new class:  Supplier.cs 
    public class Supplier {
        public int SupplierID { get; set; }
        public string CompanyName { get; set; }
    }
  • Add a new Class: Category.cs 
    public class Category {
        public int CategoryID { get; set; }
        public string CategoryName { get; set; }
    }
  • Add a new Class: Product.cs 
    using System;
    using IQToolkitContrib;
     
    public class Product : IValidate {
        public int ProductID { get; set; }
        public string ProductName { get; set; }
        public int SupplierID { get; set; }
        public Supplier Supplier { get; set; }
        public int CategoryID { get; set; }
        public Category Category { get; set; }
        public string QuantityPerUnit { get; set; }
        public decimal UnitPrice { get; set; }
        public int UnitsInStock { get; set; }
        public int UnitsOnOrder { get; set; }
        public int ReOrderlevel { get; set; }
        public bool Discontinued { get; set; }
     
        public void Validate() {
            if (this.Discontinued && this.UnitsOnOrder > 0) {
                throw new ArgumentException("Reorder level can't be greater than 0 if Discontinued");
            }
        }
    }
  • Add a new Class: NorthwindPolicy.cs 
    using System.Reflection;
    using LinqToVfp;
     
    internal class NorthwindQueryPolicy : VfpQueryPolicy {
        public override bool IsIncluded(MemberInfo member) {
            // this will ensure that the Product.Supplier and Product.Category properties will be populated
            switch (member.Name) {
                case "Supplier":
                case "Category":
                    return true;
     
                default:
                    return false;
            }
        }
    }
  • Add a new Class: Northwind.cs 
    using System.Configuration;
    using IQToolkit;
    using IQToolkitContrib;
    using LinqToVfp;
     
    public class Northwind : AVfpDatabaseContainer {
        public Northwind()
            : base(ConfigurationManager.ConnectionStrings["northwind"].ConnectionString, null) {
     
            // update the provider with some loading options
            this.Provider = this.Provider.New(new NorthwindQueryPolicy());
     
            // this will make it so that all command will be logged to the Output window
            this.Provider.Log = new DebuggerWriter();
        }
     
        public IEntityTable<Product> Products {
            get { return this.Provider.GetTable<Product>("Products"); }
        }
     
        public IEntityTable<Supplier> Suppliers {
            get { return this.Provider.GetTable<Supplier>("Suppliers"); }
        }
     
        public IEntityTable<Category> Categories {
            get { return this.Provider.GetTable<Category>("Categories"); }
        }
    }

Page Setup:  I had the resulting Example2.aspx and Example2.cs after working though the LINQ to SQL example with the only change being the replacement of <asp:LinqDataSource with <iqw:DataSource.

  • Example2.aspx 
    <%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Example2.aspx.cs" Inherits="Example2" %>
     
    <!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>
                    Pick Category:
                    <asp:DropDownList ID="CategoryList" 
                                      DataSourceID="CategoryDataSource" 
                                      DataTextField="CategoryName" 
                                      DataValueField="CategoryId" 
                                      AutoPostBack="true" 
                                      runat="server" />
                </div>
                <div style="color:Red;">
                    <asp:Literal ID="ErrorMessage" runat="server" EnableViewState="false" />
                </div>
     
                <asp:GridView ID="ProductGrid" 
                              runat="server" 
                              AllowPaging="True" 
                              AllowSorting="True" 
                              AutoGenerateColumns="False" 
                              DataSourceID="ProductDataSource" 
                              DataKeyNames="ProductId"
                              OnRowUpdated="ProductGrid_RowUpdated"
                              OnRowDeleted="ProductGrid_RowDeleted"
                              >
                    <Columns>
                        <asp:CommandField ShowEditButton="True" />
                        <asp:CommandField ShowDeleteButton="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" 
                                        SortExpression="UnitPrice" />
                        <asp:BoundField DataField="UnitsInStock" 
                                        HeaderText="UnitsInStock" 
                                        SortExpression="UnitsInStock" />
                        <asp:BoundField DataField="UnitsOnOrder" 
                                        HeaderText="UnitsOnOrder" 
                                        SortExpression="UnitsOnOrder" />
                        <asp:CheckBoxField DataField="Discontinued" 
                                           HeaderText="Discontinued" 
                                           SortExpression="Discontinued" />
                    </Columns>
                </asp:GridView>
                <iqw:DataSource ID="ProductDataSource" 
                                runat="server" 
                                ContextTypeName="Northwind" 
                                TableName="Products" 
                                Where="CategoryId == @CategoryId"
                                EnableDelete="true"
                                EnableUpdate="true"
                                EnableInsert="true">
                    <WhereParameters>
                        <asp:ControlParameter ControlID="CategoryList" 
                                              Name="CategoryId" 
                                              PropertyName="SelectedValue" 
                                              Type="Int32" />
                    </WhereParameters>
                </iqw:DataSource>
                <iqw:DataSource ID="CategoryDataSource" 
                                runat="server" 
                                ContextTypeName="Northwind" 
                                TableName="Categories" />
                <iqw:DataSource ID="SupplierDataSource" 
                                runat="server" 
                                ContextTypeName="Northwind" 
                                TableName="Suppliers" />    
            </div>
        </form>
    </body>
    </html>
  • Example2.cs 
    using System;
    using System.Web.UI.WebControls;
     
    public partial class Example2 : System.Web.UI.Page {
        protected void ProductGrid_RowUpdated(object sender, GridViewUpdatedEventArgs e) {
            if (e.Exception != null) {
                if (e.Exception is ArgumentException) {
                    this.ErrorMessage.Text = e.Exception.Message;
                }
                else {
                    this.ErrorMessage.Text = "An error occurred while trying to update this product.";
                }
     
                e.ExceptionHandled = true;
                e.KeepInEditMode = true;
            }        
        }
     
        protected void ProductGrid_RowDeleted(object sender, GridViewDeletedEventArgs e) {
            if (e.Exception != null) {
                this.ErrorMessage.Text = e.Exception.Message;
                e.ExceptionHandled = true;
            }
        }
    }

 

* Note:  When attempting to delete a record, you will get a trigger error due to referential integrity.  You can create a new record in the Product table for testing the delete link.

LINQ to VFP
8/24/2009 6:32 AM Eastern Daylight Time  #    Disclaimer  |  Comments [0]  | 
 # Saturday, August 22, 2009
Custom LinqDataSource for IQToolkit

I was trying to work though a LINQ to SQL example using IQToolkit as the data context. I found that the LinqDataSource would work fine for reading the data.  But when attempting to update the data an exception was raised indicating the data context did not extent System.Data.Linq.DataContext.  So to finish up the example, I needed to create a custom LinqDataSource for IQToolkit.  After a little inspection using Reflector, I found that I just needed to create two sub-classes.

The first class that needed to be created was a sub-class of LinqDataSourceView:

using System;
using System.Globalization;
using System.Linq;
using System.Web;
using System.Web.UI.WebControls;
using IQToolkit;
 
namespace IQToolkitContrib.Web {
    public class DataSourceView : LinqDataSourceView {
        private LinqDataSource owner;
 
        public DataSourceView(LinqDataSource owner, string name, HttpContext context)
            : base(owner, name, context) {
            this.owner = owner;
        }
 
        /// <summary>
        /// Make sure that the data context has a property that implements IEntityTable
        /// </summary>
        protected override void ValidateContextType(Type contextType, bool selecting) {
            if (!selecting && contextType.GetProperties().Where(p => p.PropertyType.GetInterface("IEntityTable") != null).Count() == 0) {
                throw new InvalidOperationException(string.Format(CultureInfo.InvariantCulture, "The data context used by IQToolkit-DataSourceView '{0}' must have an IEntityTable Property when the Delete, Insert or Update operations are enabled.", this.owner.ID));
            }
        }
 
        /// <summary>
        /// Make sure that the table implementes IEntityTable
        /// </summary>
        protected override void ValidateTableType(Type tableType, bool selecting) {
            if (!selecting && (!tableType.IsGenericType || tableType.GetInterface("IEntityTable") == null)) {
                throw new InvalidOperationException(string.Format(CultureInfo.InvariantCulture, "The table property used by IQToolkit-DataSourceView '{0}' must extend IEntityTable when the Delete, Insert or Update operations are enabled.", this.owner.ID));
            }
        }
 
        protected override void DeleteDataObject(object dataContext, object table, object oldDataObject) {
            ((IEntityTable)table).Delete(oldDataObject);
        }
 
        protected override void UpdateDataObject(object dataContext, object table, object oldDataObject, object newDataObject) {
            ((IEntityTable)table).Update(newDataObject);
        }
 
        protected override void InsertDataObject(object dataContext, object table, object newDataObject) {
            ((IEntityTable)table).Insert(newDataObject);
        }
    }
}

The second class that needed to be created was a sub-class of LinqDataSource:

using System.Web.UI.WebControls;
 
namespace IQToolkitContrib.Web {
    public class DataSource : LinqDataSource {
        protected override LinqDataSourceView CreateView() {
            return new DataSourceView(this, "DefaultView", this.Context);
        }
    }
}

After building these two classes in a separate assembly, I was able to use the DataSource as I would any other custom server control.

IQToolkit | IQToolkit Contrib
8/22/2009 12:02 PM Eastern Daylight Time  #    Disclaimer  |  Comments [0]  | 
 # Wednesday, August 19, 2009
LINQ to VFP – Example #1

The following example is a quick proof of concept simply showing how to view the Customer table from the Northwind.dbc.

  1. Create a new WebSite.
  2. Add references to IQToolkit.dll and LinqToVfp.dll
  3. Add a Northwind connection string setting to the web.config 
    <connectionStrings>
        <add name="northwind" 
             providerName="System.Data.OleDb" 
             connectionString="Provider=VFPOLEDB.1;Data Source=**Your Path**\Northwind.dbc;"/>
    </connectionStrings>
  4. Add a new class:  Customer.cs 
    public class Customer {
        public string CustomerId { get; set; }
        public string CompanyName { get; set; }
        public string ContactName { get; set; }
        public string Address { get; set; }
        public string City { get; set; }
        public string Region { get; set; }
        public string PostalCode { get; set; }
        public string Country { get; set; }
        public string Phone { get; set; }
        public string Fax { get; set; }
    }
  5. Add a new class:  Northwind.cs 
    using System.Configuration;
    using IQToolkit;
    using LinqToVfp;
     
    public class Northwind : AVfpDatabaseContainer {
        public Northwind()
            : base(ConfigurationManager.ConnectionStrings["northwind"].ConnectionString, null) {
     
            // this will make it so that all command will be logged to the Output windoww
            this.Provider.Log = VfpQueryProvider.CreateDebuggerWriter();
        }
     
        public IEntityTable<Customer> Customers {
            get { return this.Provider.GetTable<Customer>("Customers"); }
        }
    }
  6. Modify Default.aspx to include to following in the div tag: 
    <asp:GridView ID="mainGrid" 
                  runat="server" 
                  DataSourceID="LinqDataSource1" 
                  AllowPaging="True"
                  AllowSorting="True" />
     
    <asp:LinqDataSource ID="LinqDataSource1" 
                        runat="server" 
                        ContextTypeName="Northwind"  
                        TableName="Customers" />
LINQ to VFP
8/19/2009 7:07 AM Eastern Daylight Time  #    Disclaimer  |  Comments [0]  |