Random Dev Notes

April 23, 2011

Accessing VFP data using LINQPad

Filed under: Development — Tags: , — Tom Brothers @ 10:52 am

A LINQ to VFP user recently suggested that I write a LINQPad provider that could be used to access VFP data.  This sounded like an interesting idea so I gave it a shot…

This blog entry provides a simple overview on how to use the driver.  The first thing you need to do is get the driver – which can be found in the LINQ to VFP download or as part of the NuGet download

Install the driver (must be x86 LINQPad build):

  1. Click “Add connection” link

  2. Click the “View more drivers…” button

  3. Click the “Browse…” button

  4. Open the LinqToVfpLinqPadDriver.lpx file

Connect to your database container (or free tables directory).

  1. If you just added the LinqToVfpLinqPadDriver you will continue to the “Choose Data Context” screen (otherwise you need to hit the “Add connection” link)
  2. Select the LINQ to VFP driver and then click the “Next >” button

  3. Enter a full connection string or click the “Locate Database Container…” link which will build a connection string for you. 


After installing the driver and setting up your connection you can start querying and manipulating your data.

Here is an example of a query.

You can also see the generated sql command.

Here is an example of manipulating the data.

* Cetin Basoz posted a video showing how to install the LINQPad driver.

February 20, 2011

LINQ to VFP’s usage of XmlToCursor

Filed under: Development — Tags: — Tom Brothers @ 8:55 pm

About a year ago I wrote a blog entry explaining a change that was made to LINQ to VFP to help prevent the VFP error “1812 – Statement too long.”  To be honest, I was never really satisfied with the solution… but at the time it seemed to be the only way I could deal with this annoying VFP error.  Fortunately though, a much more knowledgeable VFP developer, Cetin Basoz, stumbled upon my blog entry and offered a more elegant solution to this problem.  He pointed me to a link, that pre-dated my blog entry by a couple years, that suggested using the VFP’s XmlToCursor function instead of creating temp tables.  Needless to say, I took his advice and modified LINQ to VFP accordingly.

In this blog entry, I will provide more details on a scenario that resulted in the VFP error, how it can be dealt with using ADO.Net, and the changes made to LINQ to VFP to avoid this error.

First off, let me say that what you are about to see here is a dramatization of actual events.  The database names and table names have been changed to protect the innocent.  🙂

As a VFP developer, I’ve picked up a project that has been in production for many years.  Prior to my involvement, a senior developer convinced the project owner that the company needed to move away from DBFs and start using SQL Server.  The convincing argument had something to do with preventing corrupted memo files and index files.  The product owner was tired of hearing from the support team that the customer had to lose some data due to these issues.  In an effort to move forward, it was decided that new tables could only be created in SQL Server.  Existing table would be move to SQL Server at a later time to be determined when it is more convenient.  Unfortunately, the project owners never found a convenient time for converting the core tables.  This ultimately resulted in a fractured data system which consequently produced less than optimal query performance. 

This chain of events provides a very general idea of what led to the VFP error.  Now lets dig into this idea a little more with a concrete example using the Northwind database.

Setting up the scenario…

Assume that at one time an Order could be for only one Product and all the information was stored in the Orders table.  This data model seemed to satisfy the business needs for awhile but as time went on various pain points were attributed to this design.  Consequently, it was decided that it was time to create an Orders Details table so that multiple Products could be part of a single Order.  The new Order Details table would be created in SQL Server while the Orders and Products tables remained DBFs.

… with a basic scenario explained… time to move onto a work item

The product owner decided that what the application could really use is a report that provides a list of Customers that have received discounts on any Products.  This would have been a trivial work item had the data been in one data system but now that it spread across two data systems this will require several steps.

The basic steps are:

  1. Query SQL data for a list of Order Details records that have a discount
  2. Get a distinct list of Order Ids from the discounted Order Details
  3. Query VFP data for the Customer Information

Here is a code example of the steps listed above:

SqlData sqlData = new SqlData();
VfpData vfpData = new VfpData();
 
// Step 1:  get the OrderDetails that have a discount
DataTable orderDetails = sqlData.CreateDataTable("SELECT * FROM [Order Details] WHERE discount > 0");
 
// Step 2:  get a distinct list of order ids
string[] orderIds = orderDetails.AsEnumerable()
                             .Select(row => row.Field<int>("OrderId").ToString())
                             .Distinct()
                             .ToArray();
 
// Step 3:  
//          build select statement that looks for all orders specified in the orderIds array
string sql = string.Format(@"
                select o.OrderId, c.CustomerId, c.CompanyName
                    from Orders o
                    inner join Customers c on upper(allt(o.CustomerId)) == upper(allt(c.CustomerId))
                    where o.OrderId in ({0})", string.Join(",", orderIds));
 
//          execute select statement... results in vfp error
DataTable orderCustomerInfo = vfpData.CreateDataTable(sql);

Here is the select statement VFP couldn’t handle:

select o.OrderId, c.CustomerId, c.CompanyName
    from Orders o
    inner join Customers c on upper(allt(o.CustomerId)) == upper(allt(c.CustomerId))
    where o.OrderId in (10250,10251,10252,10254,10258,10260,10262,10263,10264,10266,10267,10269,10273,10275,10279,10284,10285,10287,10288,10291,10298,10303,10305,10314,10324,10327,10329,10330,10332,10333,10335,10336,10339,10340,10341,10342,10343,10344,10346,10347,10348,10350,10351,10352,10353,10357,10358,10359,10361,10368,10369,10370,10371,10372,10373,10376,10377,10379,10380,10385,10388,10390,10393,10395,10397,10398,10403,10404,10406,10411,10412,10414,10417,10419,10420,10421,10424,10425,10429,10430,10431,10434,10436,10438,10440,10443,10446,10450,10451,10452,10453,10454,10456,10459,10460,10461,10464,10465,10469,10472,10475,10476,10477,10478,10483,10485,10487,10488,10489,10491,10492,10493,10496,10500,10506,10507,10510,10511,10512,10513,10515,10516,10519,10522,10523,10525,10526,10527,10528,10533,10534,10535,10536,10541,10542,10543,10547,10548,10549,10550,10551,10554,10555,10559,10560,10562,10564,10565,10566,10567,10569,10570,10571,10572,10580,10581,10583,10584,10586,10588,10590,10592,10593,10595,10596,10597,10602,10603,10604,10605,10606,10610,10613,10616,10617,10622,10623,10627,10630,10631,10632,10633,10635,10637,10640,10642,10643,10644,10646,10648,10650,10651,10652,10653,10654,10655,10656,10658,10659,10661,10663,10664,10667,10668,10672,10677,10680,10681,10686,10687,10688,10689,10690,10693,10697,10698,10700,10701,10707,10712,10714,10717,10719,10721,10727,10730,10731,10735,10740,10741,10743,10744,10750,10751,10755,10756,10760,10761,10764,10765,10769,10770,10773,10774,10776,10777,10781,10784,10786,10787,10788,10790,10791,10794,10795,10796,10799,10800,10801,10802,10803,10804,10806,10808,10812,10813,10814,10816,10817,10823,10832,10833,10834,10835,10837,10838,10839,10840,10843,10845,10847,10849,10850,10851,10854,10855,10857,10859,10863,10865,10866,10868,10871,10872,10875,10877,10878,10880,10882,10884,10891,10892,10894,10899,10900,10902,10905,10908,10912,10913,10918,10923,10924,10925,10930,10931,10932,10935,10936,10938,10939,10941,10944,10951,10952,10953,10954,10955,10959,10960,10961,10963,10966,10970,10978,10980,10983,10985,10988,10990,10991,10993,10994,10997,10999,11000,11002,11006,11008,11009,11011,11012,11014,11020,11021,11025,11027,11030,11033,11034,11038,11041,11046,11047,11049,11050,11051,11052,11053,11062,11063,11064,11065,11068,11070,11071,11074,11075,11076,11077)

Finally, after a long winded setup of the scenario I’ve gotten back to the decision point in my last post where I decided I needed to use temp tables to get passed this issue.

Here is the code example for the same 3 basic steps only this time using XmlToCursor:

SqlData sqlData = new SqlData();
VfpData vfpData = new VfpData();
 
// Step 1:  get the OrderDetails that have a discount
DataTable orderDetails = sqlData.CreateDataTable("SELECT * FROM [Order Details] WHERE discount > 0");
 
// Step 2:  get a distinct list of order ids
int[] orderIds = orderDetails.AsEnumerable()
                             .Select(row => row.Field<int>("OrderId"))
                             .Distinct()
                             .ToArray();
 
// Step 3:  
//          build xml
StringBuilder sb = new StringBuilder();
sb.Append("<VFPData>");
 
foreach (int orderId in orderIds) {
    sb.Append("<curTempIdList Id='");
    sb.Append(orderId);
    sb.Append("' />");
}
 
sb.Append("</VFPData>");
string xml = sb.ToString();
 
DataTable orderCustomerInfo;
 
using (OleDbCommand command = vfpData.CreateCommand()) {
    //      build select statement that looks for all orders specified in the orderIds array
    //      using XmlToCursor
    command.CommandText = @"
        select o.OrderId, c.CustomerId, c.CompanyName
            from Orders o
            inner join Customers c on upper(allt(o.CustomerId)) == upper(allt(c.CustomerId))
            where o.OrderId in (select Id from (iif(XmlToCursor(?, 'curTempIdList') > 0, 'curTempIdList', '')))";
 
    command.Parameters.Add("Xml", xml);
 
    //          execute select statement... executes properly without a VFP error
    orderCustomerInfo = vfpData.CreateDataTable(command);
}

Here is the selected statement:

select o.OrderId, c.CustomerId, c.CompanyName
    from Orders o
    inner join Customers c on upper(allt(o.CustomerId)) == upper(allt(c.CustomerId))
    where o.OrderId in (select Id from (iif(XmlToCursor(?, 'curTempIdList') > 0, 'curTempIdList', '')))

There is one main consideration that you would want to keep in mind when using XmlToCursor as I have in this example.  The generated xml didn’t include any schema information which resulted in VFP making an assumption as to the expected data type.  VFP’s assumption worked out for this example.  But if the OrderId column had been a string this query would have failed due to a data type mismatch.  To correct this issue, the xml would have to include a schema that explicitly identified the data type.

Here is an example querying the Customers table which has the CustomerId column define as a string.  In this example the XmlToCursor function creates a cursor with the Id column being an int instead of a string which results in an “Operator/operant type mismatch” exception:

VfpData vfpData = new VfpData();
 
// notice that this string array contains only numbers
string[] customerIds = { "1", "2" };
 
StringBuilder sb = new StringBuilder();
sb.Append("<VFPData>");
 
foreach (string customerId in customerIds) {
    sb.Append("<curTempIdList Id='");
    sb.Append(customerId);
    sb.Append("' />");
}
 
sb.Append("</VFPData>");
string xml = sb.ToString();
 
DataTable customers;
 
using (OleDbCommand command = vfpData.CreateCommand()) {
    command.CommandText = @"
        select *
            from Customers 
            where CustomerId in (select Id from (iif(XmlToCursor(?, 'curTempIdList') > 0, 'curTempIdList', '')))";
 
    command.Parameters.Add("Xml", xml);
 
    // causes "Operator/operand type mismatch." exception
    customers = vfpData.CreateDataTable(command);
}

To help deal with this issue, I wrote a class that would convert an array to XmlToCursor formatted xml which would include the schema when necessary.

class XmlToCursor {
    private readonly XNamespace xsd = "http://www.w3.org/2001/XMLSchema";
    private readonly XNamespace msdata = "urn:schemas-microsoft-com:xml-msdata";
    private const string ROOTNAME = "VFPData";
    public readonly string CursorName = "curTempIdList";
    public readonly string ColumnName = "Id";
    private Type arrayType;
    
    public string GetXml(Array array) {
        this.arrayType = this.GetArrayType(array);
 
        XDocument xdoc = new XDocument();
        XElement root = new XElement(ROOTNAME);
        xdoc.Add(root);
 
        XElement schema = this.GetSchemaElement();
 
        if (schema != null) {
            root.Add(schema);
        }
 
        foreach (var item in array) {
            if (item == null) {
                root.Add(new XElement(CursorName));
            }
            else {
                root.Add(new XElement(CursorName,
                            new XAttribute(ColumnName, item)));
            }
        }
 
        return xdoc.ToString();
    }
 
    private Type GetArrayType(Array array) {
        Type type = array.GetType();
 
        // Try to get the actual array type incase it requires schema information in the xml.
        if (type.FullName == "System.Object[]" && array.Length > 0) {
            type = Type.GetType(array.GetValue(0).GetType().FullName + "[]");
        }
 
        return type;
    }
 
    private XElement GetSchemaElement() {
        XElement attribute = this.GetAttributeElement();
 
        if (attribute == null) {
            return null;
        }
 
        return new XElement(xsd + "schema",
                new XAttribute("id", ROOTNAME),
                new XAttribute(XNamespace.Xmlns + "xsd", xsd.NamespaceName),
                new XAttribute(XNamespace.Xmlns + "msdata", msdata.NamespaceName),
                new XElement(xsd + "element",
                    new XAttribute("name", ROOTNAME),
                    new XAttribute(msdata + "IsDataSet", true),
                    new XElement(xsd + "complexType",
                        new XElement(xsd + "choice",
                            new XAttribute("maxOccurs", "unbounded"),
                            new XElement(xsd + "element",
                                new XAttribute("name", CursorName),
                                new XAttribute("minOccurs", 0),
                                new XAttribute("maxOccurs", "unbounded"),
                                new XElement(xsd + "complexType",
                                    attribute,
                                    new XElement(xsd + "anyAttribute",
                                        new XAttribute("namespace", "http://www.w3.org/XML/1998/namespace"),
                                        new XAttribute("processContext", "lax"))))))));
    }
 
    private XElement GetAttributeElement() {
        XElement complexType = null;
 
        switch (this.arrayType.FullName) {
            case "System.String[]":
                complexType = this.GetVarCharAttributeElement();
                break;
            default:
                return null;
        }
 
        return complexType;
    }
 
    private XElement GetVarCharAttributeElement() {
        const int MAX_VARCHAR_LENGTH = 254;
 
        return new XElement(xsd + "attribute",
                new XAttribute("name", this.ColumnName),
                new XAttribute("use", "required"),
                new XElement(xsd + "simpleType",
                    new XElement(xsd + "restriction",
                    new XAttribute("base", "xsd:string"),
                    new XElement(xsd + "maxLength",
                        new XAttribute("value", MAX_VARCHAR_LENGTH)))));
    }
}

Here is an example of the Customer query rewritten to use the XmlToCursor class:

VfpData vfpData = new VfpData();
 
// notice that this string array contains only numbers
string[] customerIds = { "1", "2" };
XmlToCursor xmlToCursor = new XmlToCursor();
string xml = xmlToCursor.GetXml(customerIds);
 
DataTable customers;
 
using (OleDbCommand command = vfpData.CreateCommand()) {
    command.CommandText = @"
        select *
            from Customers 
            where CustomerId in (select Id from (iif(XmlToCursor(?, 'curTempIdList') > 0, 'curTempIdList', '')))";
 
    command.Parameters.Add("Xml", xml);
 
    // the query succeeds
    customers = vfpData.CreateDataTable(command);
}

At this point, I’ve explained a basic scenario that has required the XmlToCursor function.  I’ve also shown how the function can be used in ADO.net.  So how does this XmlToCursor stuff affect LINQ to VFP…?

Before becoming aware of the potential usage of XmlToCursor I had included some temp table helper classes in LINQ to VFP.  These classes got the job done but resulted in some very messy code because it required branching the code to handle queries that contained too many array items (see code below).  In addition to less than desirable code, the LINQ to VFP developer had to evaluate each query to determine if it had potential of causing the “statement too long” error. 

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;

What I think would have been much better is if LINQ to VFP evaluated the query to determine the array length so that it could modify the select statement as needed.  In retrospect, I probably could have made the select statement rewrite happen with the temp tables but it just didn’t seem like the right thing to do at the time.  Nevertheless, this essentially describes the process that was included when LINQ to VFP was changed to use XmlToCursor.  In addition to this new process I have added a new setting that is used to opt-in to the whole XmlToCursor rewrite process.

Here is the previous code example rewritten so that the sql select statement includes the XmlToCursor function.

// can be set once in the application startup 
VfpQueryProvider.XmlToCursorMinimumItemCount = 100;
 
NorthwindDataContext context = new NorthwindDataContext(connectionString);
 
int[] ids = context.List<Order>().OrderBy(o => o.OrderID).Take(160).Select(o => o.OrderID).ToArray();
 
List<Order> list = (from o in context.List<Order>()
            where ids.Contains(o.OrderID)
            select o).ToList();
 
return list;

February 25, 2010

WCF Data Service mangled my select statement…

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

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:

image

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:

image

__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

January 23, 2010

VFP Error 1812 – Statement too long (.Net/LINQ to VFP perspective)

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

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

Ok so I have a simple work around for this error in VFP.  Now what about .Net…?

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.

image

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.

image

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.

image


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();

image

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();
}

image

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


Creating a temporary table is going to add a little extra processing time so this should only be used if needed.  Here is an example that of how I’m using this in my production code.

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;

January 7, 2010

IQToolkitCodeGen (alpha version)

Filed under: Development — Tags: , , — Tom Brothers @ 11:40 am

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. 

image


Settings Overview:

image

As expected, you can use a full connection string to access VFP or SQL Server.  There are also a few unconventional connection options.  You can specify the full path to a dbc file or a specify a directory for free tables.  Both of these options will be used to create a connection string for VFP.  You can also provide a modified version of a SQL Server connection string that includes a pipe delimited list of databases (Example:  Data Source=.;Initial Catalog=Northwind|AspNet;Integrated Security=True). 

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

image
The Entity Settings allow you to specify the file extension, namespace, output path, and template.  There is only one Entity template available. 
 
image
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.

image

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.

image

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

image

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.

This is a diagram shows the Template classes along with classes that they reference.

image

September 9, 2009

LINQ to VFP – Example #3

Filed under: Development — Tags: — Tom Brothers @ 11:14 am

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.
    image
  • Associations are determined by matching properties.
    image
  • 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.

image

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

image

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.
    image
  2. Add a new class (NorthwindAttributes.cs) that includes all the attributes.
    image
  3. Modify the Northwind class to include the NorthwindAttribute class as the second parameter to the base constructor.
    image

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

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