.Net Development | Random Dev Notes

December 15, 2013

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

Filed under: .Net Development — Tags: VFP EF Provider — 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.
Run the Reverse Engineer Code First command.
Change the data source to use the VFP Entity Framework Provider (EF4 & EF5) option.
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.
Add Attribute the DbConfigurationTypeAttribute to your DbContext class using the VfpDbConfiguration type.

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

Comments (15)

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

Filed under: .Net Development — Tags: VFP EF Provider — 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.
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.

Another requirement that the provider needs to be included in the config file (this is handled by the NuGet package).
Install the VfpEntityFrameworkProvider.msi which can be found in the packages directory.
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.”

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

Comments (16)

June 2, 2013

Query large number of Ids without getting VFP Error 1812 – Statement too long

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

I actually wrote something about this issue awhile back in regards to LINQ to VFP. In this post I’d like to show a simpler example.

   1:  // Simulate getting a large number Ids

   2:  var orderIds = Enumerable.Range(10000, 1000).ToArray();

3:

   4:  // ToXmlToCursorFormattedXml is an Array extension method in the VfpClient namespace.

   5:  var xml = orderIds.ToXmlToCursorFormattedXml();

6:

   7:  using (var connection = new VfpConnection(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString)) {

   8:      using (var command = connection.CreateCommand()) {

   9:          command.CommandText = @"

  10:  select o.OrderId, c.CustomerId, c.CompanyName

  11:      from Orders o

  12:      inner join Customers c on upper(allt(o.CustomerId)) == upper(allt(c.CustomerId))

  13:      where o.OrderId in (select Id from (iif(XmlToCursor(@OrderIdsXml, 'curTempIdList') > 0, 'curTempIdList', '')))";

14:

  15:          command.Parameters.AddWithValue("@OrderIdsXml", xml);

16:

  17:          var dataAdapter = new VfpDataAdapter(command);

  18:          var dataTable = new DataTable();

19:

  20:          dataAdapter.Fill(dataTable);

  21:      }

  22:  }

* Example code can be found in Visual FoxPro .Net Examples.

Leave a Comment

Creating Visual FoxPro Tables and database container from DataTables

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

While working on the Visual FoxPro Entity Framework Provider I found myself in a position where I needed to convert schema DataTables into dbfs so that they could be queried. So I created a class that would encapsulate the logic needed to convert DataTables into dbfs with a database container. This blog entry shows how to use DataTableDbcCreator to create VFP tables and database container.

Here is an example:

   1:  using (var connection = new VfpConnection(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString)) {

   2:      connection.Open();

3:

   4:      var tables = connection.GetSchema(VfpConnection.SchemaNames.Tables);

   5:      var fields = connection.GetSchema(VfpConnection.SchemaNames.TableFields);

6:

   7:      connection.Close();

8:

   9:      var dbc = GetNewDbcFullPath();

  10:      var dbcCreator = new DataTableDbcCreator(dbc);

11:

  12:      dbcCreator.Add(tables);

  13:      dbcCreator.Add(fields);

  14:  }

This is what was created:

* Example code can be found in Visual FoxPro .Net Examples.

Leave a Comment

Bulk insert into a visual foxPro table from a DataTable

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

In one of the earlier versions of the Visual FoxPro Entity Framework Provider I ran into a nasty performance problem when generating the schema information for the Entity Framework Designer. The process that was used to get the schema information involved using the connection’s GetSchema method and then dumping that into a VFP table – for re-querying later. In my first attempt I was creating the tables and then inserting each record. What I found was inserting each record was very time consuming. I later realized that I could pass all the data to VFP as xml and convert that to a cursor so that the cursor could be used for inserting the data. This made a huge difference in performance. This blog entry shows how you can do a bulk insert.

Here is an example on doing a Bulk Insert:

   1:  // Get the Northwind OrderDetails data.

   2:  var orderDetails = GetDataTable("OrderDetails");

3:

   4:  // Convert the DataTable to an XmlToCursor formatted xml string.

   5:  // ToXmlToCursorFormattedXml is an DataTable extension method in the VfpClient namespace.

   6:  var xml = orderDetails.ToXmlToCursorFormattedXml();

7:

   8:  using (var connection = new VfpConnection(ConfigurationManager.ConnectionStrings["FreeTables"].ConnectionString)) {

   9:      connection.Open();

10:

  11:      // Create cursor using XmlToCursor with the DataTable xml.

  12:      using (var command = connection.CreateCommand()) {

  13:          command.CommandType = CommandType.StoredProcedure;

  14:          command.CommandText = "XmlToCursor";

  15:          command.Parameters.Add(new VfpParameter("xml", xml));

  16:          command.Parameters.Add(new VfpParameter("cursor", "curXmlTemp"));

  17:          command.ExecuteNonQuery();

  18:      }

19:

  20:      // Use the cursor to insert records into the destination table.

  21:      using (var command = connection.CreateCommand()) {

  22:          command.CommandText = "INSERT INTO 'OrderDetailsArchive' SELECT * FROM curXmlTemp";

  23:          command.ExecuteNonQuery();

  24:      }

25:

  26:      connection.Close();

  27:  }

* Example code can be found in Visual FoxPro .Net Examples.

Leave a Comment

Identify Production Issues using VfpCommand Tracing

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

This blog entry show how to configure VfpClient to write trace information to a file and includes a couple example on how to identify issues.

Configuration

Add the following diagnostics setting to have all VfpCommand command text written to a file.

   1:  <?xml version="1.0" encoding="utf-8"?>

   2:  <configuration>

   3:    <system.diagnostics>

   4:      <sources>

   5:        <!-- Adding the VfpClient source will allow you to see all VFP commands in the Output panel. -->

   6:        <source name="VfpClient" switchValue="Information" >

   7:          <!-- This listener will trace VFP commands to a text file and is intended for use in production when troubleshooting an issue. -->

   8:          <listeners>

   9:            <add name="VfpClientTextWriterTraceListener"

  10:                 type="System.Diagnostics.TextWriterTraceListener"

  11:                 initializeData="c:\VfpClient.txt" />

  12:          </listeners>

  13:        </source>

  14:      </sources>

  15:      <!-- Need to include the following when using VfpClientTextWriterTraceListener -->

  16:      <trace autoflush="true"/>

  17:    </system.diagnostics>

  18:  </configuration>

* This diagnostics setting works for LINQ to VFP and the VFP Entity Framework Provider.

Find Performance Issues

Situation: A support ticket states that the application has become very slow. (yep, a very vague ticket… surprised?)

Troubleshoot: Review the log file looking for long running queries. These can be identified by searching for the “Finished” details where the duration exceeds an acceptable time span. Copy the sql statement from the log file and execute it in the VFP IDE to experiment with improving performance.

Find Errors

Situation: A support ticket identifies an issue with the Customer search screen. The user is searching for a Customer name that contains “d’a” which should result in two Customers but a “No Data Found” message is displayed instead.

Troubleshoot: At this point all you really know is that there is a Customer query that isn’t returning the correct data. So you could search the log for all Customer queries that include the search criteria specified in the support ticket. In this example, it is clear that there was a problem with the query syntax as identified by the VfpException message. However, if it wasn’t so obvious you could copy the sql statement from the log file and execute it in the VFP IDE to identify the problem.

Here is an example of what the problematic code could could look like. (Sadly to say, I have seen this type of code in the production code)

Leave a Comment

VfpDataReader

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

The VfpDataReader is a wrapper class for the OleDbDataReader with the added value of:

Automatic Type Conversions
GetString Enhancements
GetSchemaTables Fixes

Automatic Type Conversions

One thing that has always annoyed me when working with the OleDbDataReader is that it is very exact. It expects that you know how the database types should be converted to .Net types and doesn’t allow for any flexibility (well not counting using GetValue with an explicit convert). This usually surfaces as a problem for me when trying to read a n(x, 0) field into .Net. My first instinct is to call GetInt32 on the data reader but that results in an error that reminds me that .Net sees this type of field as a decimal.

Here is an example of the issue:

   1:  using (var connection = CreateConnection()) {

   2:      connection.Open();

3:

   4:      using (var command = connection.CreateCommand()) {

   5:          command.CommandText = "select cast(1 as n(10)) Column1 from SingleColumnSingleRow";

   6:          Console.WriteLine(command.CommandText);

   7:          var oleDbReader = command.ExecuteReader();

   8:          oleDbReader.Read();

9:

  10:          // Result type is Decimal.

  11:          var result = oleDbReader.GetValue(0);

  12:          Console.WriteLine("OleDb Result Type is:  " + result.GetType().FullName);

13:

  14:          try {

  15:              // The OleDbDataReader will not let me retrieve the value as an integer.

  16:              var value = oleDbReader.GetInt32(0);

  17:              Console.WriteLine("Value from OleDbDataReader:  " + value);

  18:          }

  19:          catch (InvalidCastException ex) {

  20:              DisplayException(ex);

  21:          }

22:

  23:          var vfpDataReader = new VfpDataReader(oleDbReader);

  24:          // Result type is Decimal.

  25:          var result2 = vfpDataReader.GetValue(0);

  26:          Console.WriteLine("VfpClient Result Type is:  " + result2.GetType().FullName);

27:

  28:          var value2 = vfpDataReader.GetInt32(0);

  29:          Console.WriteLine("Value from VfpDataReader:  " + value2);

  30:      }

31:

  32:      connection.Close();

  33:  }

Console output:

As you can see, the OleDbDataReader won’t give me the type that I want using GetInt32. To get the value as an integer I would have to use GetDecimal or GetValue and then manually convert that value to an integer. Personally, I’d rather the data reader handle this conversion for me.

GetString Enhancements

When using the OleDbReader to read character field values you tend to end up with trailing spaces in your string variable. I find this rather annoying because I consider those trailing spaces to be nothing more than a database storage detail. So I modified the GetString method so that it includes a call to TrimEnd before returning the string value.

Here is an example of the issue:

   1:  using (var connection = CreateConnection()) {

   2:      connection.Open();

3:

   4:      using (var command = connection.CreateCommand()) {

   5:          command.CommandText = "select 'Test' + space(10) Column1 from SingleColumnSingleRow";

   6:          Console.WriteLine(command.CommandText);

   7:          var reader = command.ExecuteReader();

   8:          reader.Read();

9:

  10:          Console.WriteLine("Value from OleDbDataReader:  '" + reader.GetString(0) + "'");

11:

  12:          var vfpDataReader = new VfpDataReader(reader);

  13:          Console.WriteLine("Value from VfpDataReader:  '" + vfpDataReader.GetString(0) + "'");

  14:      }

15:

  16:      connection.Close();

  17:  }

Console output:

Another change made to the GetString method is to do an Automatic Type Conversion for VFP fields that are created with “NOCPTRANS” (binary character, binary varchar, and binary memo fields). The OleDbDataReader will return these fields as a byte array. I would rather access these types as strings.

Here is an example of the issue:

   1:  using (var connection = CreateConnection()) {

   2:      connection.Open();

3:

   4:      using (var command = connection.CreateCommand()) {

   5:          command.CommandText = "select Name from BinaryVarchar";

   6:          Console.WriteLine(command.CommandText);

   7:          var oleDbReader = command.ExecuteReader();

   8:          oleDbReader.Read();

9:

  10:          var result = oleDbReader.GetValue(0);

  11:          Console.WriteLine("OleDb Result Type is:  " + result.GetType().FullName);

12:

  13:          try {

  14:              var value = oleDbReader.GetString(0);

  15:              Console.WriteLine("Value from OleDbDataReader:  '" + value + "'");

  16:          }

  17:          catch (InvalidCastException ex) {

  18:              DisplayException(ex);

  19:          }

20:

  21:          var vfpDataReader = new VfpDataReader(oleDbReader);

  22:          Console.WriteLine("Value from VfpDataReader:  '" + vfpDataReader.GetString(0) + "'");

  23:      }

24:

  25:      connection.Close();

  26:  }

Console output:

GetSchemaTable Fixes

The OleDbDataReader just doesn’t get it right when it comes to identifying primary keys and auto incrementing columns. I’ll use the Northwind Customers and Orders tables to prove this point and show how the VfpDataReader fixes this issue.

Here is an example comparing the data reader schema for the Customers table.

Here is the VFP structure to show that the IsKey should indeed be true.

Here is an example comparing the data reader schema for the Orders table. In this example, not only did the OleDbDataReader not get the IsKey right… it also failed on getting the correct IsAutoIncrement value.

Here is the VFP structure to show that the IsKey should indeed be true.

Here is the VFP structure to show that the IsAutoIncrement should indeed be true.

* Example code can be found in Visual FoxPro .Net Examples.

Leave a Comment

Using Named Parameters when Accessing Visual FoxPro Data from .Net

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

One thing that has always annoyed me is the way passing parameters in a sql statement work when working with Visual FoxPro data from .Net using OleDbCommand.

Here are a few things in particular that annoy me:

Use of question marks place holders instead of named parameters.
Need to add parameters to the command in the order that they appear in the sql statement.
Need to add a parameter for each question make place holder even if a parameter has already been added with the same value.

Problem

In this example of the problem, I have a simple query that spans multiple tables looking for records that match a name or id. Ideally such a query would only require two parameters to be added to the command. But as you can see there were 12 parameters added to satisfy the ordinal place holder requirements. Admittedly, adding multiple parameters with the same values isn’t a huge problem nor is making sure the question marks line up with the correct parameter value. But I do find that dealing with this annoying mundane task causes a pause in my development as I try to work things out mentally.

   1:  using (var connection = new OleDbConnection(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString)) {

   2:      using (var command = connection.CreateCommand()) {

3:

   4:          command.CommandText = @"

   5:  SELECT CAST('Supplier' as v(254)) Type, CompanyName Name

   6:      FROM Suppliers WHERE UPPER(ALLTRIM(CompanyName)) like ? OR SupplierId = ?

   7:  UNION SELECT CAST('Shipper' as v(254)), CompanyName

   8:      FROM Shippers WHERE UPPER(ALLTRIM(CompanyName)) like ? OR ShipperId = ?

   9:  UNION SELECT CAST('Customer' as v(254)), CompanyName

  10:      FROM Customers WHERE UPPER(ALLTRIM(CompanyName)) like ?

  11:  UNION SELECT CAST('Product' as v(254)), ProductName

  12:      FROM Products WHERE UPPER(ALLTRIM(ProductName)) like ? OR ProductId = ?

  13:  UNION SELECT CAST('Category' as v(254)), CategoryName

  14:      FROM Categories WHERE UPPER(ALLTRIM(CategoryName)) like ? OR CategoryId = ?

  15:  UNION SELECT CAST('Employee' as v(254)), ALLTRIM(FirstName) + ' ' + ALLTRIM(LastName)

  16:      FROM Employees WHERE UPPER(ALLTRIM(FirstName)) like ? OR UPPER(ALLTRIM(LastName)) like ? OR EmployeeId = ?

  17:  Order by 2";

18:

  19:          command.Parameters.AddWithValue("@Name1", "%AB%");

  20:          command.Parameters.AddWithValue("@Id1", 1);

  21:          command.Parameters.AddWithValue("@Name2", "%AB%");

  22:          command.Parameters.AddWithValue("@Id2", 1);

  23:          command.Parameters.AddWithValue("@Name3", "%AB%");

  24:          command.Parameters.AddWithValue("@Name4", "%AB%");

  25:          command.Parameters.AddWithValue("@Id3", 1);

  26:          command.Parameters.AddWithValue("@Name5", "%AB%");

  27:          command.Parameters.AddWithValue("@Id4", 1);

  28:          command.Parameters.AddWithValue("@Name6", "%AB%");

  29:          command.Parameters.AddWithValue("@Name7", "%AB%");

  30:          command.Parameters.AddWithValue("@Id5", 1);

31:

  32:          var dataAdapter = new OleDbDataAdapter(command);

  33:          var dataTable = new DataTable();

34:

  35:          dataAdapter.Fill(dataTable);

  36:      }

  37:  }

Solution

Switching from using OleDbCommand to using VfpCommand (found in VfpClient) will allow you to use named parameters.

   1:  using (var connection = new VfpConnection(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString)) {

   2:      using (var command = connection.CreateCommand()) {

3:

   4:          command.CommandText = @"

   5:  SELECT CAST('Supplier' as v(254)) Type, CompanyName Name

   6:      FROM Suppliers WHERE UPPER(ALLTRIM(CompanyName)) like @Name OR SupplierId = @Id

   7:  UNION SELECT CAST('Shipper' as v(254)), CompanyName

   8:      FROM Shippers WHERE UPPER(ALLTRIM(CompanyName)) like @Name OR ShipperId = @Id

   9:  UNION SELECT CAST('Customer' as v(254)), CompanyName

  10:      FROM Customers WHERE UPPER(ALLTRIM(CompanyName)) like @Name

  11:  UNION SELECT CAST('Product' as v(254)), ProductName

  12:      FROM Products WHERE UPPER(ALLTRIM(ProductName)) like @Name OR ProductId = @Id

  13:  UNION SELECT CAST('Category' as v(254)), CategoryName

  14:      FROM Categories WHERE UPPER(ALLTRIM(CategoryName)) like @Name OR CategoryId = @Id

  15:  UNION SELECT CAST('Employee' as v(254)), ALLTRIM(FirstName) + ' ' + ALLTRIM(LastName)

  16:      FROM Employees WHERE UPPER(ALLTRIM(FirstName)) like @Name OR UPPER(ALLTRIM(LastName)) like @Name OR EmployeeId = @Id

  17:  Order by 2

  18:  ";

19:

  20:          command.Parameters.AddWithValue("@Name", "%AB%");

  21:          command.Parameters.AddWithValue("@Id", 1);

22:

  23:          var dataAdapter = new VfpDataAdapter(command);

  24:          var dataTable = new DataTable();

25:

  26:          dataAdapter.Fill(dataTable);

  27:      }

  28:  }

* I personally find the example using VfpCommand much easier to understand and work with compared to the example using OleDbCommand.

* Example code can be found in Visual FoxPro .Net Examples.

Leave a Comment

Getting started with VFP Client for ADO.Net

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

VfpClient is simply a wrapper around the System.Data.OleDb classes with the goal of reducing pain points when accessing VFP data from .Net. Here is a list of some features included in VfpClient:

Getting Started

Make sure your project targets x86. (Requirement when using VfpOleDb provider)
Install VfpClient using NuGet

Add the VfpClient diagnostics source for tracing command text.

   1:  <?xml version="1.0" encoding="utf-8"?>

   2:  <configuration>

   3:    <system.diagnostics>

   4:      <sources>

   5:        <!-- This source will allow you to see all VFP commands in the Output panel. -->

   6:        <source name="VfpClient" switchValue="Information" >

   7:          <!-- This listener will trace VFP commands to a text file and is intended for use in production when troubleshooting an issue. -->

   8:          <!--<listeners>

   9:            <add name="VfpClientTextWriterTraceListener"

  10:                 type="System.Diagnostics.TextWriterTraceListener"

  11:                 initializeData="c:\VfpClient.txt" />

  12:          </listeners>-->

  13:        </source>

  14:      </sources>

  15:    </system.diagnostics>

  16:    <!-- Need to include the following when using VfpClientTextWriterTraceListener -->

  17:    <!--<trace autoflush="true"/>-->

  18:  </configuration>

VfpClient uses the VfpOleDb provider so the VfpOleDb installer is included in the NuGet package in case you don’t already have it installed. Additionally, the NuGet package includes an installer for VfpClient. This installer will install the VfpOleDb provider and modify system settings to setup Visual Studio Integration with VfpClient.

* Note: The installer needs to be run as an Admin. If you have problems running the install from Windows Explorer then try using the command prompt using “msiexec /i VfpClient.msi”

In Use

All you really need to know to use the VfpClient is that it has equivalent classes that can be found in the System.Data.OleDb namespace the only difference is that the VfpClient classes have a prefix of “Vfp” instead of “OleDb.”

Here is a simple example using VfpClient:

Visual Studio Integration

The Visual Studio Integration includes some minor changes to simplify the connection process and changed property pages to include VFP specific information.

Here is a view of some of the screens. The 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.
Simplified the Connection Properties so that you just need to specify a path.
The Schema properties for Table, Fields, Views, and Stored Procedures are VPF specific values (using DBGETPROP).

Leave a Comment

August 31, 2012

VFP Entity Framework Provider (v0.7.0)

Filed under: .Net Development — Tags: VFP EF Provider — 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.
Simplified the Connection Properties so that you just need to specify a path.
Moved the Advanced Properties to a separate dialog. (only included a few of the properties… will add more as needed)
The Connection Name, in Server Explorer – Data Connections, is set to the Data Source instead of being hardcoded to “Visual FoxPro Data.”
The Schema properties for Table, Fields, Views, and Stored Procedures are VPF specific values (using DBGETPROP).