Random Dev Notes

I’ve been working on a project that uses WCF Data Services for data access on a DMZ server.  The application was designed to work directly with LINQ to VFP on the intranet but use WCF Data Services on the extranet/DMZ server.  During development I noticed that in some cases the queries over WCF Data Services didn’t exactly match the direct LINQ to VFP queries.  What I found was the Expression Tree for the queries that required an upper(alltrim()) (to utilize indexes) had included some extra null checking Expressions. 

Here is an example of the issue.

Example Query:

var list = (from d in context.List<Customer>()

            where d.CustomerId.Trim().ToUpper() == "ALFKI"

            select d).ToList();

When executing this query directly with LINQ to VFP you would get the following Expression:

This would result in the following query:

__Param__0__ = [ALFKI]

SELECT t0.City, t0.CompanyName, t0.ContactName, t0.Country, t0.CustomerId, t0.Phone ;

FROM Customers AS t0 ;

WHERE (UPPER(ALLTRIM(t0.CustomerId)) = __Param__0__)

The same query would result in the following when executed through WCF Data Services:

__Param__0__ = [ALFKI]

SELECT t0.City, t0.CompanyName, t0.ContactName, t0.Country, t0.CustomerId, t0.Phone ;

FROM Customers AS t0 ;

WHERE (ICASE((ICASE((t0.CustomerId IS NULL), NULL, ALLTRIM(t0.CustomerId)) IS NULL), NULL, UPPER(ICASE((t0.CustomerId IS NULL), NULL, ALLTRIM(t0.CustomerId)))) = __Param__0__)

The extra null checking makes sense given the fact that WCF Data Services can be used with Data Sources other than ORMs.  However, this really made a mess of the Select Statement created by LINQ to VFP.  More importantly the query was unable to utilize the index. 

Correcting this issue wasn’t that difficult once it had been identified.  I added the following Visitor class to remove the unnecessary null checking Expressions.

using System.Linq.Expressions;

using IQToolkit.Data.Common;

namespace LinqToVfp {

    public class ConditionalImmediateIfNullRemover : DbExpressionVisitor {

        public static Expression Remove(Expression expression) {

            return new ConditionalImmediateIfNullRemover().Visit(expression);

        }

        protected override Expression VisitConditional(ConditionalExpression c) {

            if (c.Test.NodeType == ExpressionType.Equal && c.IfTrue.NodeType == ExpressionType.Constant && ((ConstantExpression)c.IfTrue).Value == null) {

                MethodCallExpression methodCallExpression = c.IfFalse as MethodCallExpression;

                if (methodCallExpression != null) {

                    if (methodCallExpression.Object.NodeType == ExpressionType.Conditional) {

                        return this.Visit(base.VisitConditional(c));

                    }

                    BinaryExpression be = (BinaryExpression)c.Test;

                    ColumnExpression columnExpression = be.Left as ColumnExpression;

                    if (columnExpression == null) {

                        return c.IfFalse;

                    }

                    else {

                        return this.Visit(c.IfFalse);

                    }

                }

            }

            return base.VisitConditional(c);

        }

    }

}

* This change was made in v1.0.17.1

In the last 10 years or so that I’ve been working with VFP I’ve only encountered the “Statement too long” error a handful of times.  Within VFP, I found that this error could be handled in two ways.  One way to handle this error is to simply rewrite the SQL Statement.  Another way, which I only recently discovered, was to use a sys() command.

Here is a quick VFP example using the Northwind database.  I have a function that accepts a parameter which is a comma delimited list of Order Ids.  The parameter is passed into a simple SQL Select statement.

FUNCTION CreateOrdersCursorByOrderIds(tcOrderIds) 

LOCAL lcSql 

lcSql = " SELECT * " + ;

            " FROM Orders " + ;

            " WHERE OrderId in (" + tcOrderIds + ")" + ;

            " into cursor curOrders " 

&lcSql

This method works fine when there are a limited amount of Order Ids but when there are a couple hundred Order Ids the statement fails.  This statement can easily be rewritten using a join statement to prevent the “Statement too long” error.

FUNCTION CreateOrdersCursorByOrderIds(tcOrderIds) 

LOCAL lnIndex, laOrders[1]

CREATE CURSOR curOrderIds(OrderId i)

FOR lnIndex = 1 TO ALINES(laOrders, tcOrderIds, 1, ",")

    INSERT INTO curOrderIds value(VAL(laOrders[lnIndex]))

NEXT 

SELECT o.* ;

    FROM Orders o ;

    INNER JOIN curOrderIds i ON o.OrderId = i.OrderId ;

    into cursor curOrders

USE IN curOrderIds

An alternative option to re-writing the statement is to use the SYS(3055) command.

FUNCTION CreateOrdersCursorByOrderIds(tcOrderIds) 

LOCAL lcSql 

lcSql = " SELECT * " + ;

            " FROM Orders " + ;

            " WHERE OrderId in (" + tcOrderIds + ")" + ;

            " into cursor curOrders " 

SYS(3055, 2040)

&lcSql

Here is the same example re-written in C#.

using (OleDbConnection conn = new OleDbConnection(connectionString)) {

    conn.Open();

    string[] orderIds = new string[] {

        "10248", "10249", "10250", "10251", "10252", "10253", "10254", "10255", "10256", "10257", "10258", "10259", "10260", "10261", "10262", 

            "10263", "10264", "10265", "10266", "10267", "10268", "10269", "10270", "10271", "10272", "10273", "10274", "10275", "10276", "10277", 

            "10278", "10279", "10280", "10281", "10282", "10283", "10284", "10285", "10286", "10287", "10288", "10289", "10290", "10291", "10292", 

            "10293", "10294", "10295", "10296", "10297", "10298", "10299", "10300", "10301", "10302", "10303", "10304", "10305", "10306", "10307", 

            "10308", "10309", "10310", "10311", "10312", "10313", "10314", "10315", "10316", "10317", "10318", "10319", "10320", "10321", "10322", 

            "10323", "10324", "10325", "10326", "10327", "10328", "10329", "10330", "10331", "10332", "10333", "10334", "10335", "10336", "10337", 

            "10338", "10339", "10340", "10341", "10342", "10343", "10344", "10345", "10346", "10347", "10348", "10349", "10350", "10351", "10352", 

            "10353", "10354", "10355", "10356", "10357", "10358", "10359", "10360", "10361", "10362", "10363", "10364", "10365", "10366", "10367", 

            "10368", "10369", "10370", "10371", "10372", "10373", "10374", "10375", "10376", "10377", "10378", "10379", "10380", "10381", "10382", 

            "10383", "10384", "10385", "10386", "10387", "10388", "10389", "10390", "10391", "10392", "10393", "10394", "10395", "10396", "10397", 

            "10398", "10399", "10400", "10401", "10402", "10403", "10404", "10405", "10406", "10407"

    };

    DataTable dt = new DataTable();

    string sql = @"SELECT * FROM Orders WHERE OrderId in (" + string.Join(",", orderIds) + ")";

    OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);

    da.Fill(dt);

    da.Dispose();

}

When I run this code I get the same “Statement too long” error.

Out of the two ways that I know of to correct this issue… Using the SYS(3055) command would be the easiest to implement.  So I modified the code to include the SYS(3055) command.

using (OleDbConnection conn = new OleDbConnection(connectionString)) {

    conn.Open();

    string[] orderIds = new string[] {

        "10248", "10249", "10250", "10251", "10252", "10253", "10254", "10255", "10256", "10257", "10258", "10259", "10260", "10261", "10262", 

            "10263", "10264", "10265", "10266", "10267", "10268", "10269", "10270", "10271", "10272", "10273", "10274", "10275", "10276", "10277", 

            "10278", "10279", "10280", "10281", "10282", "10283", "10284", "10285", "10286", "10287", "10288", "10289", "10290", "10291", "10292", 

            "10293", "10294", "10295", "10296", "10297", "10298", "10299", "10300", "10301", "10302", "10303", "10304", "10305", "10306", "10307", 

            "10308", "10309", "10310", "10311", "10312", "10313", "10314", "10315", "10316", "10317", "10318", "10319", "10320", "10321", "10322", 

            "10323", "10324", "10325", "10326", "10327", "10328", "10329", "10330", "10331", "10332", "10333", "10334", "10335", "10336", "10337", 

            "10338", "10339", "10340", "10341", "10342", "10343", "10344", "10345", "10346", "10347", "10348", "10349", "10350", "10351", "10352", 

            "10353", "10354", "10355", "10356", "10357", "10358", "10359", "10360", "10361", "10362", "10363", "10364", "10365", "10366", "10367", 

            "10368", "10369", "10370", "10371", "10372", "10373", "10374", "10375", "10376", "10377", "10378", "10379", "10380", "10381", "10382", 

            "10383", "10384", "10385", "10386", "10387", "10388", "10389", "10390", "10391", "10392", "10393", "10394", "10395", "10396", "10397", 

            "10398", "10399", "10400", "10401", "10402", "10403", "10404", "10405", "10406", "10407"

    };

    DataTable dt = new DataTable();

    string sql = @"SELECT * FROM Orders WHERE OrderId in (" + string.Join(",", orderIds) + ")";

    using (OleDbCommand cmd = conn.CreateCommand()) {

        cmd.CommandText = "SYS(3055, 2040)";

        cmd.ExecuteNonQuery();

        cmd.CommandText = "SYS(3055)";

        var sys3055 = cmd.ExecuteScalar();

        System.Diagnostics.Debug.WriteLine("SYS(3055) = " + sys3055.ToString());

    }

    OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);

    da.Fill(dt);

    da.Dispose();

}

No luck… the simple solution didn’t work.

Adding the SYS(3055) command just changed the error message to “Insufficient stack space.”  Researching this error led me back to SYS(3055) which clearly isn’t a fix for .Net.

I still have two options at this point.  I can pull all the Orders into a DataTable and then filter the results in .Net.  Using LINQ to DataSets with this option would have a nice feel to it… it would seem similar to processing the data in VFP but it would come at a heavy performance cost (assuming the data was a larger data set than the Northwind.dbc).  Needless to say I’d like to avoid this option if possible so I’ll move on to my last idea.  I need to rewrite the query to use an inner join as I did in the VFP code.  To pull this off I will need to create a temporary table to hold the Order Ids.

using (OleDbConnection conn = new OleDbConnection(connectionString)) {

    conn.Open();

    string[] orderIds = new string[] {

        "10248", "10249", "10250", "10251", "10252", "10253", "10254", "10255", "10256", "10257", "10258", "10259", "10260", "10261", "10262", 

        "10263", "10264", "10265", "10266", "10267", "10268", "10269", "10270", "10271", "10272", "10273", "10274", "10275", "10276", "10277", 

        "10278", "10279", "10280", "10281", "10282", "10283", "10284", "10285", "10286", "10287", "10288", "10289", "10290", "10291", "10292", 

        "10293", "10294", "10295", "10296", "10297", "10298", "10299", "10300", "10301", "10302", "10303", "10304", "10305", "10306", "10307", 

        "10308", "10309", "10310", "10311", "10312", "10313", "10314", "10315", "10316", "10317", "10318", "10319", "10320", "10321", "10322", 

        "10323", "10324", "10325", "10326", "10327", "10328", "10329", "10330", "10331", "10332", "10333", "10334", "10335", "10336", "10337", 

        "10338", "10339", "10340", "10341", "10342", "10343", "10344", "10345", "10346", "10347", "10348", "10349", "10350", "10351", "10352", 

        "10353", "10354", "10355", "10356", "10357", "10358", "10359", "10360", "10361", "10362", "10363", "10364", "10365", "10366", "10367", 

        "10368", "10369", "10370", "10371", "10372", "10373", "10374", "10375", "10376", "10377", "10378", "10379", "10380", "10381", "10382", 

        "10383", "10384", "10385", "10386", "10387", "10388", "10389", "10390", "10391", "10392", "10393", "10394", "10395", "10396", "10397", 

        "10398", "10399", "10400", "10401", "10402", "10403", "10404", "10405", "10406", "10407"

};

    DataTable dt = new DataTable();

    string sql = @"

select * 

    from Orders o 

    inner join {0} t on o.OrderId = t.OrderId

";

    string tempTableFullPath = Path.ChangeExtension(Path.GetTempFileName(), "dbf");

    using (OleDbCommand cmd = conn.CreateCommand()) {

        cmd.CommandText = string.Format("create table \"{0}\" free (OrderId I)", tempTableFullPath);

        cmd.ExecuteNonQuery();

        for (int index = 0, total = orderIds.Length; index < total; index++) {

            cmd.CommandText = string.Format("insert into \"{0}\" values({1})", tempTableFullPath, orderIds[index]);

            cmd.ExecuteNonQuery();

        }

    }

    OleDbDataAdapter da = new OleDbDataAdapter(string.Format(sql, tempTableFullPath), conn);

    da.Fill(dt);

    da.Dispose();

}

This worked perfectly.

Great, I got a proof of concept to work using standard ADO.Net.  Now I’d like to get this to work with LINQ to VFP. 

Here is the example rewritten with LINQ to VFP.

NorthwindDataContext context = new NorthwindDataContext(connectionString);

int[] ids = context.List<Order>().OrderBy(o => o.OrderID).Take(160).Select(o => o.OrderID).ToArray();

var list = context.List<Order>().Where(o => ids.Contains(o.OrderID)).ToList();

Rewriting the LINQ to VFP statement isn’t as simple as it was in VFP or using standard ADO.Net.  The temporary table needs an Entity class and this Entity class will need to be mapped to the temporary table.  Plus I will need to be able to pass the full temporary file path as the Table Id.  After a few attempts I found that I could not pull this off without modifying the LINQ to VFP code.  So I modified the VfpQueryProvider to include a new method that would create and map the temporary table.  Here is an example of the rewritten LINQ to VFP statement using the temporary table.

NorthwindDataContext context = new NorthwindDataContext(connectionString);

int[] ids = context.List<Order>().OrderBy(o => o.OrderID).Take(160).Select(o => o.OrderID).ToArray();

using (TempTable<TempTableIntId> tempTable = context.Provider.CreateTempTable(ids)) {

    var list = (from o in context.List<Order>()

                join t in tempTable.EntityTable on o.OrderID equals t.Id

                select o).ToList();

}

* One thing to note about the TempTable class is that it implements IDisposable.  The dispose method will delete the temporary file so keep this in mind if you are using the TempTable when passing around IQueryable.  In this case you need to make sure the Dispose method isn’t called before executing the query.

NorthwindDataContext context = new NorthwindDataContext(connectionString);

int[] ids = context.List<Order>().OrderBy(o => o.OrderID).Take(160).Select(o => o.OrderID).ToArray();

List<Order> list;

if (ids.Length > 100) {

    using (TempTable<TempTableIntId> tempTable = context.Provider.CreateTempTable(ids)) {

        list = (from o in context.List<Order>()

                join t in tempTable.EntityTable on o.OrderID equals t.Id

                select o).ToList();

    }

}

else {

    list = (from o in context.List<Order>()

            where ids.Contains(o.OrderID)

            select o).ToList();

}

return list;

When I first started using LINQ to VFP I didn’t mind manually writing Entity classes and mapping the data but after awhile I found it to become a tedious task.  So I decided to write a code generation application that would work with LINQ to VFP and the IQToolkit SQL Server Provider.  This application has really turned out to be a play thing for me so that I can get some hands on learning with WPF and Spark View Engine.  But before I’d allow myself to get to far into playing around I wanted to make sure I got the core functionality done.  At this point I have an alpha build available for anyone interested. 

Settings Overview:

The Data Context Settings were designed with two different Data Contexts types in mind.  The first Data Context is an Entity Provider which is basically a class that has a property for each Entity.  This type of Data Context is what the IQToolkit Tests use.  The other type of Data Context is a based on a Repository Pattern which uses Generic methods instead of accessing an Entity property. 

The Data Context Settings allow you to specify the class name, output file, and namespace for both types of these Data Contexts.  The Base Class setting is for the Repository Data Context.

The Entity Settings allow you to specify the file extension, namespace, output path, and template.  There is only one Entity template available. 

The Mapping Settings can be used to create an Attribute Mapping class or an Xml Mapping file.

---

By default, the Entity Provider Template was designed to work with the Attribute Mapping Template and the Repository Provider Template was designed to work with the Xml Mapping Template.

---

Getting Started:

Enter all the setting values and then click the Load Data button.  Then the grids will be populated with the Tables, Columns, and Associations information.

The Tables grid is pretty simple to use.  Just check the checkbox in the Include column if you want an Entity class created.  The Entity class name can be specified using the grid’s Entity Name column.

The Columns grid shows the Columns of the selected row in the Tables grid.  This grid has a few more available options than the Table grid but is just as easy to use.  Check the checkbox in the Include column if you want the Entity class to include the property for the selected column.  In this grid, you can also change the name and type of the property as well as identify the primary key and indicate if it is an auto generated key.

The last grid shows the Associations.  This grid has an Include column and a Property Name column that can be set.

Click the Generate Files button after all the mapping information has been entered to complete the process.

Don’t like my templates?

The template files are distributed with this application so feel free to modify the templates as needed. Additionally, if you feel that you need to add a new template just add it to the appropriate folder and it will show up in the combo box after resetting the application.

For this example, I’ll modify the project created in LINQ to VFP – Example #2.  I will add a new page that will use the Details View control.  This new page will include the ability to insert a Product.

Page Setup:  Add a new page Example3 to the project:

• Example3.aspx

  <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Example3.aspx.cs" Inherits="Example3" %>

  <!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 style="color:Red;">

                  <asp:Literal ID="ErrorMessage" runat="server" EnableViewState="false" />

              </div>

              <asp:DetailsView ID="ProductDetailsView"

                               runat="server"

                               DataSourceID="ProductDataSource" 

                               DataKeyNames="ProductId" 

                               AutoGenerateRows="False" 

                               OnItemUpdated="ProductDetailsView_ItemUpdated"

                               OnItemDeleted="ProductDetailsView_ItemDeleted"

                               OnItemInserted="ProductDetailsView_ItemInserted">

                  <Fields>

                      <asp:BoundField DataField="ProductID" HeaderText="Product Id" ReadOnly="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" />

                      <asp:BoundField DataField="UnitsInStock" 

                                      HeaderText="UnitsInStock" />

                      <asp:BoundField DataField="UnitsOnOrder" 

                                      HeaderText="UnitsOnOrder" />

                      <asp:CheckBoxField DataField="Discontinued" 

                                         HeaderText="Discontinued" />

                      <asp:CommandField ShowEditButton="True" />

                      <asp:CommandField ShowDeleteButton="True" />

                      <asp:CommandField ShowInsertButton="True" />

                  </Fields>

              </asp:DetailsView>

              <iqw:DataSource ID="ProductDataSource" 

                              runat="server" 

                              ContextTypeName="WebExample.Model.Northwind" 

                              TableName="Products" 

                              RetrieveGeneratedId="True"

                              EnableDelete="true"

                              EnableInsert="true"

                              EnableUpdate="true"

                              OnInserted="ProductDataSource_Inserted">

              </iqw:DataSource>    

              <iqw:DataSource ID="CategoryDataSource" 

                              runat="server" 

                              ContextTypeName="WebExample.Model.Northwind" 

                              TableName="Categories" />

              <iqw:DataSource ID="SupplierDataSource" 

                              runat="server" 

                              ContextTypeName="WebExample.Model.Northwind" 

                              TableName="Suppliers" />          

          </div>

      </form>

  </body>

  </html>

• Example3.cs

  using System;

  using System.Web.UI.WebControls;

  using WebExample.Model;

  public partial class Example3 : System.Web.UI.Page {

      protected void Page_Load(object sender, EventArgs e) {

          if (!this.IsPostBack) {

              this.ProductDetailsView.ChangeMode(DetailsViewMode.Insert);

          }

      }

      protected void ProductDataSource_Inserted(object sender, LinqDataSourceStatusEventArgs e) {

          Product p = e.Result as Product;

          this.ProductDataSource.Where = "ProductId = " + p.ProductID;

      }

      protected void ProductDetailsView_ItemUpdated(object sender, DetailsViewUpdatedEventArgs e) {

          if (e.Exception != null) {

              this.ErrorMessage.Text = e.Exception.Message;

              e.ExceptionHandled = true;

              e.KeepInEditMode = true;

          }

      }

      protected void ProductDetailsView_ItemInserted(object sender, DetailsViewInsertedEventArgs e) {

          if (e.Exception != null) {

              this.ErrorMessage.Text = e.Exception.Message;

              e.ExceptionHandled = true;

          }

      }

      protected void ProductDetailsView_ItemDeleted(object sender, DetailsViewDeletedEventArgs e) {

          if (e.Exception != null) {

              this.ErrorMessage.Text = e.Exception.Message;

              e.ExceptionHandled = true;

          }

      }

  }

With the new page created, it is time to test the insert feature.  Start by adding new Product information and then click the Insert link.  At this point an exception has been thrown indicating that the “Field ProductId is read-only.”  Now what does that mean?  It means that I finally need to do a little explaining about how Mapping works in my examples.

Implicit Mapping:  Up until this point I’ve been able to use Implicit Mapping.  Implicit mapping allowed me to simple create data classes and let IQToolkit connect the classes to the FoxPro Tables. 

Here are a couple key points about Implicit Mapping:

• The Primary Key field must end with “ID” (upper case required).
• Can handle singular and plural naming issues.  Notice in the image below that the class name is singular and the table name is plural.
  
         
• Associations are determined by matching properties.
  
• Cannot determine if a Primary Key an auto generated value.

After my brief explanation of Implicit Mapping and with knowing about the Products table structure it should obvious why we cannot insert the new Product.  The Implicit Mapping is trying to insert a value into the auto generated primary key field – ProductId.  You can find the insert statement in the Output Window when in debug mode.

If you copy the insert statement and run it in VFP you will see that you get the same error.

So how do we fix this error…?  It is time to stop using Implicit Mapping and start using a more explicit type of mapping.  The IQToolkit includes two other type of mappings.  I will use Attribute Mapping to finish up this example.

Three changes are required to setup the Attribute mapping.

1. The IEntityTable<T> properties of the Northwind class need to be set as virtual.
   
2. Add a new class (NorthwindAttributes.cs) that includes all the attributes.
   
3. Modify the Northwind class to include the NorthwindAttribute class as the second parameter to the base constructor.
   

After making these changes you should see that the insert is working as expected.

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.

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