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.

    August 23, 2009

    Custom LinqDataSource for IQToolkit

    Filed under: Development — Tags: , — Tom Brothers @ 1:34 am

    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.

    August 19, 2009

    LINQ to VFP – Example #1

    Filed under: Development — Tags: — Tom Brothers @ 9:13 pm

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

    Blog at WordPress.com.