Random Dev Notes

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;

           }

       }

   }

6. 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

<%@ 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>

2. Example2.cs

3. 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.

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" />