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