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:

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