Random Dev Notes

December 15, 2013

Visual FoxPro Entity Framework Provider v2-Getting Started with Code First

Filed under: .Net Development — Tags: — Tom Brothers @ 4:31 pm

A new version of the Visual FoxPro Entity Framework Provider that targets EF6 is available.  The purpose of this blog post is to outline what you need to get started using Code First with the EF Power Tools Beta 4. 

Getting Started:

  • Install the VFP EF Provider Version 1.  The Version 1 build is required because the EF Power Tools doesn’t work with the new EF6 classes.
  • Install the VFPEntityFrameworkProvider2 NuGet package. 
    image
  • Run the Reverse Engineer Code First command.
    image
  • Change the data source to use the VFP Entity Framework Provider (EF4 & EF5) option.
    image
  • A connection string is added to your config file as part of the code generation process.  The connection string was generated to target the VFP EF Provider Version 1.  You will need to change providerName in the connection string to use VfpEntityFrameworkProvider2 to get Entity Framework to work with the VFP EF Provider Version 2.
    image
  • Add Attribute the DbConfigurationTypeAttribute to your DbContext class using the VfpDbConfiguration type.
    image

* Here is a video showing everything mentioned in this post.

Visual FoxPro Entity Framework Provider v2-Getting Started Using the EF Designer

Filed under: .Net Development — Tags: — Tom Brothers @ 3:41 pm

A new version of the Visual FoxPro Entity Framework Provider that targets EF6 is available.  The purpose of this blog post is to outline what you need to get started using the EF Designer with the new provider.  With the previous VFP EF Provider you could simply run the VfpEntityFrameworkProvider.msi installer and then have the provider available to you when you added a new “ADO.NET Entity Data Model” to your project.  But EF6 requires a little more setup to get started.  Most of this setup is handled by the VFPEntityFrameworkProvider2 NuGet Package.  So lets get started with the NuGet package and look at the details of what needs to be done.

 

Getting Started:

  • Install the VFPEntityFrameworkProvider2 NuGet package. 
    image
  • Make sure the VfpClient.dll and the VfpEntityFrameworkProvider.dll are set to copy local.  Normally I would just suggest this as a deployment option but now this is required to get the designer to work
    image

    Another requirement that the provider needs to be included in the config file (this is handled by the NuGet package). 

    image
  • Install the VfpEntityFrameworkProvider.msi which can be found in the packages directory. 
    image
  • Restart Visual Studio
  • If you try to add a new “ADO.NET Entity Data Model” you will find that the provider is not available.  The reason for this is that the EF6 Designer is looking for the EF Provider in the current project and is unable to find it until you build the project.  After building the project you will find that the provider is available when you try to add a new “ADO.NET Entity Data Model.” 
     
    image

* Here is a video showing everything mentioned in this post.

August 31, 2012

VFP Entity Framework Provider (v0.7.0)

Filed under: .Net Development — Tags: — Tom Brothers @ 9:48 am

It has been awhile since I blogged about an update to the VFP Entity Framework Provider.  Here is a list of changes since my last post (which was for v0.6.1).

Changes:

  • (v0.6.2) – Modified the schema retrieval code to treat a free table candidate key as a primary key.
  • (v0.6.2) – Fixed bug related to using character field as a primary key.
  • (v0.6.3) – Added support for binary char, binary varchar, binary memo, date, and Guid.
  • (v0.6.3) – Fixed issue with Inserting/Updating DateTime values.
  • (v0.6.4) – Changed how columns are aliased in a query.
  • (v0.6.5) – Changed the way Decimal, Double, and Single values are written as part of DML commands to deal with localization issues.
  • (v0.6.5) – Made various changes to shorten the SQL command text length to help prevent "VFP Error 1812 – Statement too long" errors.
  • (v0.6.6) – Modified the SELECT statement to include a CAST(<fieldname> AS N(20, <scale>)) for Numeric values that include a decimal. To address this type of issue.
  • (v0.6.7) – Changed how Single, Double, Decimal, and DateTime constants are written out for the sql statements.
  • (v0.6.8) – Changed data manipulation statements to parameterize some types to fix localization issues and to prevent invalid statements due to carriage returns in strings.
  • (v0.6.9) – Modified installer to include setup for VS2012.
  • (v0.7.0) – Improved performance of generating the edmx
  • (v0.7.0) – Refactored ADO.Net related code to use VfpClient.

There have been several updates along the way which really didn’t warrant a blog post which I why I haven’t written one.  This release isn’t much different as far as blog worthy content.  However, there were a few minor design time changes that should make things a little nicer (at least that is what I think) so I’ll go ahead and point them out.

Before digging into the changes I’d like to mention the refactoring effort that was made for this release.  The VFP Entity Framework Provider was originally developed using the System.Data.OleDb.* classes to connect to the VFP data – which is probably what you’d expect.  These classes required wrapper classes to handle some Entity Framework expectations.  What expectations?  Well lets just state the obvious which is the Entity Framework was original targeted for SQL Server.  So what does that mean as far as expectations?  In short I would say that if the System.Data.SqlClient.* classes “do it”… then the classes that the VFP Entity Framework Provider uses needs to “do it” too.  One good example would be that the SqlCommand class uses named parameters.  So the SqlCommand equivalent that VFP Entity Framework Provider uses must support named parameters.  In this case I needed to create a wrapper class around the OleDbCommand that could handle the concept of named parameters.  Doing this in the VFP Entity Framework project was actually the second time that I needed to write this wrapper class.  The first time was in the LINQ to VFP project.  Needless to say that having similar code in two different projects just didn’t seem right.  So I decided to pull the OleDb wrapper classes from both projects and place them in a new project.  VfpClient is the new project that was created with the wrapper classes but the development didn’t end there.  I also pulled in the Sedna DDEXProvider project (Microsoft.Data.FoxClient) which started me down the path of customizing the design time experience.  So coming back from what must seem like a tangent…  the VFP Entity Framework Provider was refactored to use VfpClient which includes a couple design time changes.

Even though this isn’t my first blog post for the provider I thought that it would be nice to take a step back and show how to install the provider.  I also thought it would be nice to show an example of it in use so that I can point to this post as a “Getting Started with” post since a couple screens have changed.

 

Install

To install the provider you just need to download and run the VfpEntityFrameworkProvider.msi from http://vfpefprovider.codeplex.com/.

Example

Here is a quick video showing the provider in use.

Changes

For those familiar with the provider… here is an overview of the changes:

(Old screen is on the left the new is on the right)

  • Added the "Visual FoxPro Data Client" data source so that you didn’t have to dig through the "<other>" selection.
    image image
  • Simplified the Connection Properties so that you just need to specify a path.
    image image
  • Moved the Advanced Properties to a separate dialog.  (only included a few of the properties… will add more as needed)
    image image
  • The Connection Name, in Server Explorer – Data Connections, is set to the Data Source instead of being hardcoded to “Visual FoxPro Data.”
    image image
  • The Schema properties for Table, Fields, Views, and Stored Procedures are VPF specific values (using DBGETPROP).
    image image
    image image

May 6, 2012

Using VFP Entity Framework Provider with Free Tables using Code First

Filed under: .Net Development — Tags: — Tom Brothers @ 3:37 pm

Introduction

In my previous post, titled “Using VFP Entity Framework Provider with Free Tables”, I outlined a way of connecting the VFP Entity Framework Provider with Free Tables.  The post was really geared towards an “out of the box” experience – said another way it was more specifically an Entity Framework 4.0 way.  Although I feel the post is relevant, it really didn’t sit well with me because it is not the way I would have done things.  This post will outline another way of connecting Free Table DBFs with the VFP Entity Framework Provider using Code First (or Entity Framework 4.1+).

(This post assumes you’ve already installed the VFP Entity Framework Provider)

Getting Started

Create a console application.

image

Create the Entities.

  1. Install the Entity Framework Power Tools
    image
  2. Use the Power Tools to generate the code first entities.
    image
  3. You’ll be prompted for the connection information.
    image 
    image
  4. A new Models folder has been added to the project.
    image

Alter the Model

I’ll alter the Blog_Lookups table just list in the last blog post.

Here is the code that was generated:

image

Using Visual Studio’s refactoring I renamed the class name and some of the properties.

image

One other thing that needs to be done which I couldn’t do in the last example is set the primary key to the correct column.

image

 

* One benefit of the using the Code First approach makes it easier to set the primary key when your table doesn’t include a candidate key.

February 5, 2012

Using VFP Entity Framework Provider with Free Tables

Filed under: Development — Tags: — Tom Brothers @ 10:42 pm

Introduction

To my surprise it seems that the majority of people interested in using the VFP Entity Framework Provider are targeting Free Tables. Fortunately, I took special care during development of the provider to ensure that it would work as best as it could with Free Tables. In this blog entry I’ll walk through an example using the provider with Free Tables.

For this example I will “borrow” the blog Free Table DBFs from the Web Connection shareware download.

(This post assumes you’ve already installed the VFP Entity Framework Provider)

Getting Started

Create a simple console application.

image

Create the Entity Framework Data Model.

  1. Add a new item.
    image
  2. Select “ADO.NET Entity Data Model” and enter BlogModel as the name.
    image
  3. Select “Generate from database.”
    image
  4. Create a New Connection.
    image
  5. Enter the Data Source and Provider. (Notice that the Data Source is the directory that contains the DBFs)
    image
  6. Don’t need to change anything on this screen. Just click next.
    image
  7. Select all tables.
    image

Here is the Entity Framework Model that was created.

image

Now look at the Error List panel. Wonder what those Warnings and Messages mean or how to resolve them? This issue will be discussed later

image

Query Some Data

The Model has been created and we are ready to query the data. For this example, I want to query the Blog_Lookups table.

Here is the Model. Notice the key icon on all the properties? Add this to the list of things that will be discussed later.

image

Here is an example of querying the data sending it to the console.

image

Console Output

image

 

Lets take a second to recap what we’ve done. We create a project that included an Entity Framework Model that was created from Free Tables and we were able to query these tables. All this basically happened in the matter of minutes.

I know, I know… you could’ve done this quicker in VFP but still getting this done in minutes in .Net is pretty cool… huh?

(now that I’m done patting my self on the back… lets get back to business :))

Alter the Model

Did you notice that when I wrote to the console I conceptualized some of the Model property names? I wrote out a label of “Abbreviation” instead of “Cdata”, “Name” instead of “Cdata1”, and “Description” instead of “Cdata2.” Well the cool thing with ORMs, such as Entity Framework, is that you can make such changes to the Model. More specifically what I’m trying to point out is that the Model property names do not need to match the table column names. I personally find the ability to change the property names useful when having to deal with the Free Table’s 10 character column name limitation. Additionally, you can change change the Entity name without needing to change the table name.

Here is an example of modifying the Model (just the Model was changed… not the underlying table).

image
image

Update (failure)

Ok, we have a Model and we even tweaked it so that our code is a little more readable (by changing the Entity name and property names)… so what is next? Well, we’d like to modify the data right?

Here is code to find an Entity, change the Name, and then save the change.

image

It didn’t work!!!

image

Looking at the exception message, what does it mean that the “Name” is part of the object’s key information mean…?

One Step Forward Two Steps Back…

Remember the issues that I pushed off for a “discussion later?” Well now is the time. The update didn’t work due to these two issues (which is really one issue).

The first issue was the Warnings and Messages in the Error List. Here is the Error List that relates to the Blog_Lookups.

image

The second issue is the key indicator on all the properties. Entity Framework, by default, uses all the properties as a composite primary key when it cannot identify an actual primary key for the table. When it does this it actually places the Model in a read-only state which is why the update fails.

image

So how do we fix these issues (errr… this issue)? Well the Error List clearly states that we are missing a primary key on the table doesn’t it?

Candidate Key=Primary Key

Free Tables don’t have Primary Keys so does that mean we can’t update data using the VFP Entity Framework Provider? Of course not. The provider is aware of the fact that Free Tables can’t have primary keys so it treats Candidate Keys as Primary Keys. So in this example, if we add a Candidate Key to the Blog_Lookups table prior to creating the Model…

image

… the Model will identify the Candidate Key as a Primary Key as seen in the following Model. (Notice that only the Pk property has a key icon)

image

 

* There is another way to modify the model without changing the table structure. If you are interested in this option I’d suggest reading Julia Lerman’s Programming Entity Framework, Second Edition book to learn how to modify the xml that defines the Models.

November 14, 2011

VFP Entity Framework Provider (v0.6.1)

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

I posted an update to the VFP EF Provider

Changes:

Bug:  Fixed an issue with getting the schema information when the connecting to free tables. 

Installer:  Modified the installer so that it will update the machine.config… so there is no need to manually change that machine.config anymore.

Installer:  Added a registry setting so that the provider is available in the Visual Studio “Add Reference” dialog box.

November 7, 2011

VFP Entity Framework Provider (v0.6)

Filed under: Development — Tags: — Tom Brothers @ 1:06 am

Introduction

Another VFP EF Provider build is ready for testing.  The focus of this build was to get the provider to work with “Code First” scenarios.

The remainder of this blog entry will walk you though a simple example of using Code First.  This example was derived from the ADO.NET team blog – EF 4.2 Code First Walkthrough.


Create the Application

  • Open Visual Studio 2010
  • File –> New –> Project..
  • Select “Windows” from the left menu and “Console Application”
  • Enter “CodeFirstSample” as the name
  • Select “OK”

Add References

  • Add EntityFramework.dll
    image
  • Add VfpEntityFrameworkProvider.dll

Add Code

Replace the Project.cs code with the following:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity;
using System.Linq;
 
namespace CodeFirstSample {
    class Program {
        static void Main(string[] args) {
            using (var db = new ProductContext()) {
                // Use Find to locate the Food category 
                var food = db.Categories.Find("FOOD");
                if (food == null) {
                    food = new Category { CategoryId = "FOOD", Name = "Foods" };
                    db.Categories.Add(food);
                }
                // Create a new Food product 
                Console.Write("Please enter a name for a new food: ");
                var productName = Console.ReadLine();
                var product = new Product { Name = productName, Category = food };
                db.Products.Add(product);
                int recordsAffected = db.SaveChanges();
                Console.WriteLine("Saved {0} entities to the database.", recordsAffected);
                // Query for all Food products using LINQ 
                var allFoods = from p in db.Products
                               where p.CategoryId == "FOOD"
                               orderby p.Name
                               select p;
                Console.WriteLine("All foods in database:");
                foreach (var item in allFoods) {
                    Console.WriteLine(" - {0}", item.Name);
                }
                Console.WriteLine("Press any key to exit.");
                Console.ReadKey();
            }
        }
    }
 
    public class ProductContext : DbContext {
        public DbSet<Category> Categories { get; set; }
        public DbSet<Product> Products { get; set; }
    }
 
    public class Category {
        [MaxLength(10)]
        public string CategoryId { get; set; }
 
        [MaxLength(50)]
        public string Name { get; set; }
 
        public virtual ICollection<Product> Products { get; set; }
    }
 
    public class Product {
        public int ProductId { get; set; }
 
        [MaxLength(50)]
        public string Name { get; set; }
 
        [MaxLength(10)]
        public string CategoryId { get; set; }
        public virtual Category Category { get; set; }
    }
}

This example deviates from the ADO.NET team blog – EF 4.2 Code First Walkthrough when defining the model classes (Category and Product).  The MaxLength attribute was added to let the provider know that the table field should be varchar instead of a memo.


Change App.Config

Add the provider information and connection string to app.config.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <system.data>
    <DbProviderFactories>
      <clear />
      <add name="Vfp Entity Framework Provider"
                 invariant="VfpEntityFrameworkProvider"
                 description="Vfp Entity Framework Provider"
                 type="VfpEntityFrameworkProvider.VfpProviderFactory, VfpEntityFrameworkProvider, Version=0.6.0.0, Culture=neutral, PublicKeyToken=feace53afe38fe48" />
    </DbProviderFactories>
  </system.data>
  <connectionStrings>
    <add name="ProductContext"
             connectionString="provider=vfpoledb;data source=DataCodeFirst.dbc"
             providerName="VfpEntityFrameworkProvider" />
  </connectionStrings>
</configuration>

Run & Review

Run the application and then check the database container directory to see that the data files have been created.

October 23, 2011

VFP Entity Framework Provider (v0.5)

Filed under: Development — Tags: — Tom Brothers @ 7:46 pm

Introduction

I’m back it again… working on another LINQ provider for Visual FoxPro data.

Introduced back in 2009, LINQ to VFP was my first attempt at writing a LINQ provider that accessed Visual FoxPro data.  That provider worked well but had a few drawbacks that I was willing to live with at the time due to the lack of options.     The most notable drawbacks include not having a designer/code generator and requiring special code to get it to work with other Microsoft offerings such as WCF Data Services.  Additionally, there were some usability limitations due the framework that LINQ to VFP was build upon (IQToolkit).  I was able to code my way around most of these issues but I still felt that the whole process wasn’t all that developer friendly.  What I really wanted was a more integrated development process that could easily hook into new Microsoft offerings (such as LightSwitch).  So I started to look into what it would take to create an Entity Framework 4 Provider.  Fortunately I found that there was a starter project available.  I modified this project to work with Visual FoxPro data and now have what I’d like to consider a proof-of-concept build ready for testing.

* Note:  This build only works for “Database first” scenarios.

See In Action

A sample application (VfpEFQuerySamples) is available to see the provider in action.  This application is a copy of the ADO.NET Entity Framework Query Samples configured to work with Visual FoxPro data.

image2
* Notes:
  • Installing the Visual FoxPro Entity Framework Provider is not required to run this application
  • The VfpOleDb driver must be install otherwise you will see the following when you try to run the samples
    image5

Install Steps

  • Download and run the VfpEntityFrameworkProvider.msi from the codeplex site.

* UPDATE:  Starting with version 0.6.1, the installer will update the machine.config so no need to manually make this change.

  • Add an entry for the provider in C:WindowsMicrosoft.NETFrameworkv4.0.30319configmachine.config. 
    image8
    <add name="Vfp Entity Framework Provider" 
         invariant="VfpEntityFrameworkProvider" 
         description="Vfp Entity Framework Provider" 
         type="VfpEntityFrameworkProvider.VfpProviderFactory, VfpEntityFrameworkProvider, Version=0.5.0.0, Culture=neutral, PublicKeyToken=feace53afe38fe48"/>


How to add to a Project

  • Add a new ADO.NET Entity Data Model
    image11
  • Select “Generate from database”
    image14
  • Click on the “New Connection…” button and select the VFP Provider
    image17
  • Enter the connection information
    image20
  • Continue with the following wizard screens

    imageimage image image

  • After completing all the wizard screens you will see the EF designer and depending on your data you may see messages similar to the following.  This is expected behavior from the Entity Framework and is not specific to the Visual FoxPro Entity Framework Provider.
    image38

Deployment

There are two options for deploying an application using this provider. Both options required the provider information to be added to the machine.config or the project’s app.config/web.config.

image47
  1. Install the VfpEntityFrameworkProvider.msi. This installer includes the VfpOleDb driver.
  2. Include the VfpEntityFrameworkProvider.dll in the project. You will also need to ensure that the VfpOleDb driver is installed.

    image44


Known Issues

  • The generated sql statements could be improved to use more concise syntax
  • I haven’t figured out how to hook into “Generate Database” screen so it currently shows commands for Sql Server instead of Vfp.

    image50
  • The provider only woks in the non-Express versions of Visual Studio.  This is not because I’m intentionally restricting the Express Products but instead because I haven’t figured out the registry settings yet.

Blog at WordPress.com.