Random Dev Notes

June 2, 2013

Creating Visual FoxPro Tables and database container from DataTables

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

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

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

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

Identify Production Issues using VfpCommand Tracing

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

image

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.

image

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)

image

Bulk insert into a visual foxPro table from a DataTable

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

Using Named Parameters when Accessing Visual FoxPro Data from .Net

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

VfpDataReader

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

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

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

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.

image

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

image

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.

image[12]

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

image

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

image

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

Getting started with VFP Client for ADO.Net

Filed under: .Net Development — Tags: — 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)
    image
  • Install VfpClient using NuGet
    image
  • 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.
    image

* 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:
image

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

Blog at WordPress.com.