Random Dev Notes

August 25, 2009

LINQ to VFP – Example #2

Filed under: Development — Tags: — Tom Brothers @ 12:28 am

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:

  1. Create a new WebSite.
  2. Add references to IQToolkit.dll, LinqToVfp.dll, IQToolkitContrib.dll, and IQToolkitContrib.Web.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 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:

  1. Add a new class: Supplier.cs
    public class Supplier {
        public int SupplierID { get; set; } 
        public string CompanyName { get; set; }
    }
  2. Add a new Class: Category.cs
    public class Category {
        public int CategoryID { get; set; }
        public string CategoryName { get; set; }
    }
  3. 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");
            }
        }
    }
  4. 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;
            }
        }
    }
  5. 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. 

  1. Example2.aspx
  2. <%@ 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" 
                                TableName="Suppliers" />    
            </div>
        </form>
    </body>
    </html>
  3. Example2.cs
  4. 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.

    Leave a Comment »

    No comments yet.

    RSS feed for comments on this post. TrackBack URI

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

    Blog at WordPress.com.

    %d bloggers like this: