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.

29 Comments »

  1. Hi Tom.
    I saw this one by accident, while working with LinqToVFP in LinqPad today and wondered if there were any newer version – using v1.17.11:) I am going to create a thread in Foxite and point this thread (that would help to those people to whom I already suggested LinqToVFP).
    Thanks for doing it. It is much appreciated.

    Comment by Cetin Basoz — April 28, 2011 @ 2:12 pm

    • Glad to hear that you like this provider. I look forward to hearing some feedback on how it works for you…

      This first release of the LINQPad provider works with the current version of LINQtoVFP – v1.17.12.

      Comment by Tom Brothers — April 28, 2011 @ 2:55 pm

  2. Great stuff. I used to program in VFP so this is a chance to reminisce 🙂

    Send me your e-mail address and I’ll throw you a promotional autocompletion license.

    Cheers
    Joe

    Comment by Joe Albahari — April 29, 2011 @ 10:45 am

  3. This is great stuff Tom, thanks!

    Comment by Eric Selje — September 15, 2011 @ 3:23 am

  4. Hi Tom,
    Could you please let me know how can I query the contents of a .dbf file using the linqpad vfp driver?

    Thanks,
    Gokul

    Comment by Gokul — October 31, 2011 @ 6:40 pm

    • You will need to provide a full connection string if you are not using a DBC. For example, here is a full connection string where I’m pointing to the “free tables directory” where the foxcodes.dbf file is located.
      Data Source=C:Program Files (x86)Microsoft Visual FoxPro 9;Provider=vfpoledb

      * Note – VfpOleDb will disregard any specific DBF file in the connection string… so you can’t actually specify a single DBF.

      Comment by Tom Brothers — October 31, 2011 @ 8:40 pm

      • Bros –

        “free tables directory” connections throw object reference exception.

        My connect string is as shown above and is pointing to the folder containing various dbf files. Should I be able to reference the dbf files in queries if the connection has been specified as above? If so, how?

        Thanks.

        Joe

        Comment by Joe S. — December 8, 2011 @ 1:44 pm

      • Here is a video showing how to connect to free table directory. Hopefully this will help you out. If not, could you create a little video to show me what you are doing?

        Comment by Tom Brothers — December 8, 2011 @ 6:47 pm

  5. Tom –

    I have added a “free tables directory” connection and it throws an object reference exception.

    My connect string is as shown above… points to the folder containing various dbf files. Should I be able to reference the dbf files in queries if the connection has been specified as above? If so, how? From you example above… I would expect it might look something like

    (from fc in foxcodes select fc).Dump();

    Thanks.

    Joe

    Comment by Joe S. — December 8, 2011 @ 2:39 pm

  6. Tom –

    You did precisely what I expected and it looks identical to what I am doing. The only thing that is different is my folder location. The only files my folder is DBF files and accompanying cdx files. Perhaps there is a minimum version requirement for the dbf files?

    Thanks.

    Joe

    Comment by Joe S. — December 8, 2011 @ 11:14 pm

    • Tom –

      Solved the puzzle. I was using Joe’s latest beta to test drive some of his enhancements.

      You driver shows this exception behavior with LinqPad version 4.37.2.0.

      When I ran LP v4.33.4.0…. your driver behaved as expected.

      Thanks for your help… and good stuff.

      Joe

      Comment by Joe S. — December 9, 2011 @ 11:22 am

      • Tom –

        While I am happy I can access VFP from Linqpad…. My ultimate goal when I started this quest was to do some cross database joining of tables where one table was provided via an MSSQL Linq to SQL connection and another table was provided by your VFP driver. No joy at this point.

        My guess is that LP currently provides for cross connection joins only between like connection types.

        If you can shed some light… even if only to confirm or comment on my guess… please do.

        Thanks again.

        Joe

        Comment by Joe S. — December 9, 2011 @ 11:58 am

      • I downloaded the latest version (v4.37.5) of LINQPad and I now see the Object reference error. I’ll have to debug this issue. Thanks for pointing this out.

        As to your ultimate goal of querying between VFP and SQL Server data…
        The LINQPad query window is tied to a single data context so you cannot do the “cross connection joins.” However, the new “My Extensions” feature is flexible enough to allow you to define a data context that could be used in any Query tab.

        Here is an example of creating a LINQ to VFP data context in “My Extensions.”

        void Main()
        {
        var context = new NorthwindDbc.NorthwindContext(@"D:LINQPadTestNorthwindDataNorthwind.dbc");
        context.Categories.Dump();
        }

        public static class MyExtensions
        {
        }

        // Using the NorthwindDbc class to provide a namespace
        public class NorthwindDbc {
        public class Category {
        public int CategoryId { get; set; }
        public string CategoryName { get; set; }
        }

        public class NorthwindContext : DbEntityContextBase {
        public NorthwindContext(string connectionString)
        : this(connectionString, typeof(NorthwindAttributes).FullName) {
        }

        public NorthwindContext(string connectionString, string mappingId)
        : base(VfpQueryProvider.Create(connectionString, mappingId)) {
        }

        public virtual IEntityTable Categories {
        get { return this.Provider.GetTable("Categories"); }
        }
        }

        public class NorthwindAttributes : NorthwindContext {
        public NorthwindAttributes(string connectionString)
        : base(connectionString) {
        }

        [IQToolkit.Data.Mapping.TableAttribute(Name = "Categories")]
        [IQToolkit.Data.Mapping.ColumnAttribute(Member = "CategoryId", IsPrimaryKey = true, IsGenerated = true)]
        [IQToolkit.Data.Mapping.ColumnAttribute(Member = "CategoryName")]
        public override IEntityTable Categories {
        get { return base.Categories; }
        }
        }
        }

        Here is an example of using the VFP data context in a Query tab that has a SQL Server connection.

        void Main()
        {
        var vfpContext = new NorthwindDbc.NorthwindContext(@"D:LINQPadTestNorthwindDataNorthwind.dbc");

        var categoryIds = vfpContext.Categories
        .OrderByDescending (x => x.CategoryId)
        .Take(2)
        .Select (x => (int?) x.CategoryId)
        .ToList(); // Make sure to Execute the query using ToList!

        Products.Where (p => categoryIds.Contains(p.CategoryID)).Dump();
        }

        Comment by Tom Brothers — December 9, 2011 @ 1:49 pm

  7. Hi Joe,

    Is there a way to query on the content of the memo field in standalone VFP table?

    Thanks,
    Boris

    Comment by Boris — December 13, 2011 @ 1:49 pm

    • You should be able to query all field types. Are you having a problem querying a memo field?

      Comment by Tom Brothers — December 13, 2011 @ 2:28 pm

  8. Really interesting your contribution to world of LinQPad users.

    VFP seems to disappears in 2015 and in consequence, migration of data process will be required. Additionally, many applications are currently using VFP databases and some of this customers, can request new features to be used in a modern application written in C# with access to this databases.

    Regards
    I will to try your driver.

    Thank you.
    César F. Qüeb Montejo

    Comment by cesarq — January 18, 2012 @ 4:39 pm

  9. Today, working with the latest beta release, the VFP driver does not work. The “object reference not set to an instance of an object” exception is displayed.

    Comment by cesarq — January 19, 2012 @ 11:48 am

    • I’m aware of that issue. I’ve been waiting for the next (non-beta) release of LINQPad before I posted a fix. But I guess there is no reason that I couldn’t just provide a beta fix… here is the link http://download.codeplex.com/Download?ProjectName=linqtovfp&DownloadId=330689.

      Comment by Tom Brothers — January 19, 2012 @ 9:07 pm

      • Hi Tom,

        Thank you for the beta fix. I will to try it.
        Sorry for the delay.. busy with work’s projects.

        Regards

        Comment by cesarq — January 24, 2012 @ 2:51 pm

      • Using the Take() method thrown following VFPException: SQL: TOP requires an ORDER BY.

        LinQ source:
        from x in Detfics.Take(20)
        where x.Cveemp == 13713
        select x

        Exception:
        at VfpClient.VfpCommand.ExecuteDbDataReader(CommandBehavior behavior)
        at System.Data.Common.DbCommand.ExecuteReader()
        at LinqToVfp.VfpQueryProvider.Executor.ExecuteReader(DbCommand command)
        at IQToolkit.Data.DbEntityProvider.Executor.Execute[T](QueryCommand command, Func`2 fnProjector, MappingEntity entity, Object[] paramValues)
        at lambda_method(Closure )
        at IQToolkit.Data.EntityProvider.Execute(Expression expression)
        at LinqToVfp.VfpQueryProvider.Execute(Expression expression)
        at IQToolkit.Query`1.GetEnumerator()
        at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
        at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)

        Regards

        Comment by cesarq — January 25, 2012 @ 2:22 pm

      • Hi, Please ignore my previous post… I was missing the orderby clause.. My apologies for this.

        BTW: The beta driver is working fine with latest beta of LinQPad 4.

        Thank you.

        Comment by cesarq — January 25, 2012 @ 2:48 pm

  10. Hello,
    I am trying to check whether a record exists, if yes update it or else insert it in to a foxpro table. Do you have any ideas on how to implement this?

    Thanks,
    Gokul

    Comment by Gokul — March 11, 2012 @ 5:25 am

    • There is an InsertOrUpdate method that you can use. Here is an example of how it works using the Northwind.dbc.


      // this will be an update
      var category = new Categories{ Categoryid = 1, Categoryname= "Beverages", Description = "New Description" };

      Categories.InsertOrUpdate(category);

      // this will be an insert because the Categoryid doesn't exist in the table
      category = new Categories{ Categoryid = -1, Categoryname= "Beverages", Description = "New Description" };

      Categories.InsertOrUpdate(category);

      While testing this example I found a bug with the InsertOrUpdate method. I updated the LINQtoVFP zip with a fix for this issue.

      Comment by Tom Brothers — March 12, 2012 @ 6:29 am

  11. Thank You, Tom.

    Comment by Gokul — March 12, 2012 @ 3:18 pm

  12. For free table, I do exactly as you show in your video for foxcodes.
    Linqpad 4 gives me “The name ‘Foxcodes’ does not exist in the current context” in query window.
    In the left panel ( tree view ) both tables ( foxcodes,labels ) were identified and shown.

    Comment by Mikhail — May 18, 2016 @ 4:14 am

  13. I forgot to mention in my previous comment , the same error when connection is added against dbc Northwind taken from examples.
    CS0103 The name ‘Categories’ does not exist in the current context

    Comment by Mikhail — May 18, 2016 @ 11:48 pm

  14. More clarification LINQPad.exe included into linqtovfp-102194.zip with the respective dlls ( its verstion 4.43.6.0 ) does work with the Foxpro driver. Looks like some breaking change was introduced in later LINQPad versions.

    Comment by Mikhail — May 19, 2016 @ 2:35 am

    • For anyone who would be interested to have it work in the latest versions of LinqPad
      The example of amended method below eliminates the error. Note: the original linq statement was commented out as for some reason stopped returning tables in a list ( empty list with Count = 0 is returned ) .

      public static ReadOnlyCollection GetTableAndViews(List schema) {
      //var tables = schema.Where(x => x.Tag is Table)
      // .Select(x => x.Tag as SchemaObject)
      // .ToList();

      List tables = new List();

      var categories = schema.Where(x => x.Kind == ExplorerItemKind.Category);

      foreach (var category in categories) {
      tables.AddRange(category.Children.Where(x => x.Tag is Table || x.Tag is View).Select(x => x.Tag as SchemaObject).ToList());
      }

      return tables.AsReadOnly();
      }

      Comment by Mikhail — May 30, 2016 @ 11:37 pm

  15. I confirm Mikhails. There’s problem with connect to free tables. Additionally, when LinqPad connects to free tables DbContext defined in application, returns error: “Database Container is requered”.

    Comment by Vikong — June 6, 2016 @ 3:54 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: