Random Dev Notes

June 2, 2013

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.

Leave a Comment »

No comments yet.

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: