Free!  FoxPro to WPF screen converter
   Almost free!  One day of FREE VFP to WPF evaluation of your FoxPro app ONSITE
   Free!  Read the foreword to Les' new book (available here)...
Skip Navigation Links

Top Ten FoxPro to .NET Conversion Tips

Some of the pitfalls of rewriting a FoxPro app


Les Pinter

Login or register to download source code

Introduction

If you have software that was written in FoxPro (or even FoxBASE), you're probably contemplating rewriting it. You have no choice. You can't find programmers to support it. You can't sell your company if FoxPro is part of your IT framework. And when the next version of Windows comes out, your software probably won't run. Converting your software to .NET is no longer a choice; it's just a matter of when. But it's not simple.

I once had a client who was convinced that FoxPro could be "translated" to C# line-for-line. It took him three months to understand that what he had asked for was not possible. So the first thing I want to make clear is this: FoxPro can't be translated to C#, or to any other language, line by line.

There are many paradigms used in FoxPro that are artefacts of FoxPro's table-centric design. Support for DBFs is the fabric of Visual FoxPro. Dozens of commands and functions provide for the manipulation and display of table data, often using just a single word: SCATTER/GATHER; BROWSE; EDIT; DELETE; APPEND; GO TOP; REPLACE; SEEK; SEEK(); LOOP; CONTINUE; and many, many others. Many of the eccentricities of the FoxPro language are due to the peculiarities of its tables.

FoxPro's data handling features are awesome. Tables can be traversed easily (DO WHILE NOT EOF(), SCAN...ENDSCAN), and EXIT ends the loop in either case, while SKIP/LOOP or simply LOOP moves to the next record and continues. LOCATE/CONTINUE provides one of several filtering mechanisms that looks just plain weird to C# or VB programmers. And LOCATE in a table with an active index moves the record pointer (another FoxPro oddity) to the first record in the indexed data, a fact that I learned only two years ago, after nearly 25 years of using xBASE languages. SEEK(Expr,Table) moves the pointer in the record in the referenced table that contains the specified key value of the currently selected index (exhale) in a single straightforward function call. Why don't C# and VB have these features? Because they're not table-based languages; they're general-purpose languages. Good for them, bad for us.

Some FoxPro coding patterns are workarounds for the lack of a SQL SELECT statement early in the language's life. The lack of a Structured Query Language in dBASE II/III and FoxBASE led to a style of coding that made even simple things look complicated. DO WHILE NOT EOF() and SCAN/ENDSCAN were often used to simulate a SELECT statement, in ways that can be hard to interpret. Coverting a hundred lines of procedural code into a SELECT statement, and isolating the code that will then operate on the data returned from that SELECT, has occupied countless hours of my FoxPro conversion career.

Moving data from text files to data tables and back is trivial in FoxPro, but more complicated in .NET. And there are many more peculiarities typical of "xBASE" languages. In fact, almost any operation related to data will lead to frustration on the part of a Foxpro developer. "Why does it have to be so damned hard?"

FoxPro's approach to data isn't the only feature that results in a different approach to coding. Like JavaScript, FoxPro doesn't require prior declaration of data types. I can be an integer one moment, and a string the next. That's an extreme case, but it can happen. And older programs (or even those written more recently) use global variables whose types are often arcane. But in the object-oriented world, there is no place for global variables. And when you encounter a statement like this:

Name = FirstName

you don't know whether FirstName is a column in the currently-selected table, or a memvar (m.FirstName). If both exist, FirstName will refer to the field in the current table - not the memvar. Try looking at that for a few hours wondering why the hell the field isn't being filled in with your memvar, as I've done, I don't know, hundreds of times.

.NET has built-in controls that permit the construction of very complex user interfaces, far beyond what's possible in FoxPro; so the design of a FoxPro form may be a collection of kludges to work around the paucity of features in FoxPro's control set. And never mind creating your own controls in FoxPro. But it's relatively easy to add controls to the robust set that ships with .NET. And the third-party marketplace is huge - Telerik is my personal favorite, but several others are widely used.

And let's not even talk about macro expansion, which has no equivalent in any language - at least, not the freewheeling form that it takes in FoxPro. The first thing I do when asked to estimate the cost of a conversion is to document the use of macro expansion. If it's a major factor, it's not good news. Generating IL (called reflection emit in .NET) is a far cry from constructing a FoxPro statement (or even an entire program) in a memvar and executing it on the fly.

In this article, I'll reveal my ten favorite tips to use when converting FoxPro programs to .NET. And of course, of course, you can do anything in C# or VB that you can do in FoxPro. But there's really no alternative. We've been painted into a corner, and the only way out is .NET. So let's get started.

(1) DATA

For FoxPro developers looking to move to .NET, the elephant in the living room is data. Here's how you open a table in FoxPro:

USE Customers

As you have undoubtedly already discovered, it's much more complicated in .NET. For one thing, there is no native data storage. SQL Server is the default database for .NET applications. There's a miniature version installed with Visual Studio; it's name is ".\SQLExpress". The "." actually means "on this computer"; you can also use "(local)\SQLExpress" or "localhost\SQLExpress".) If you have SQL Server installed either on your computer or on another computer on your network, and have the right to create and update tables on it, you can use that.

I sometimes feel like I'm an unpaid salesman for Microsoft. You can use other data sources, but the results probably won't be worth the trouble. And SQL Server isn't free; the basic license is a couple of grand a year. It's a little like the Gillete model: .NET is the razor; SQL Server is the blades. but whaddaya gonna do? The path of least resistance is SQL Server.

Loading some data

Open the Data Sources window, right-click and select Create New SQL Server database, and call it TestData. Then, create and populate a People database:

USE TestData
GO
CREATE TABLE PEOPLE ( ID Int IDENTITY(1,1) PRIMARY KEY, FirstName VarChar(20), LastName VarChar(20), Address VarChar(50), City VarChar(20), State CHAR(2), ZIP CHAR(10) )
INSERT INTO PEOPLE (FirstName, LastName, Address, City, State, ZIP ) VALUES
 ( 'Les',  'Pinter',   '34616 Highway 190',  'Springville', 'CA', '93265' ),
 ( 'Sam',  'Spade',    '204 Topanga Canyon', 'Malibu',      'CA', '90215' ),
 ( 'Mary', 'Carvajal', '1424 Columbia',      'Houston',     'TX', '77005' ),
 ( 'Saul', 'Bellow',   '214 West 53rd',      'New York',    'NY', '10012' )

Loading a DataTable

There are two ways to get to your data: DataAdapters-DataSets-DataTables, and Entity-Framework. DataTables are easier to understand, coming from the FoxPro world, but Entity Framework is how we'll be connecting to data for the foreseeable future, so you should learn both.

In order to use a DataAdapter, you're going to need a connection string. The easiest way to get one is to create a text file called 'data.udl' on your desktop, double-click on it, and select "Use Windows NT Integrated Security". For Server Name, use ".", "(local)" or "localhost". When you close the dialog, data.udl will contain a string that starts with "Provider=SQLOLEDB.1;". Remove that first bit and copy the rest of the string into the paste buffer using Ctrl+C.

Now, create a Windows Forms project in either VB or C#, and drop a button and a DataGridView onto the form so that it looks like Fig. 1, below:

screen
Fig. 1: A form to test dataset loading

Double-click on the first button and add the following code, starting with the connection string that's in your paste buffer:

string cs = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TestData;Data Source=.";
string cmd = "SELECT * FROM PEOPLE";
SqlDataAdapter da = new SqlDataAdapter(cmd, cs);
DataTable dt = new DataTable();
da.Fill(dt);
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
dataGridView1.DataSource = dt;

Run the form, click the first button, and voilĂ , you've got data! And although not as fast as FoxPro would be, it wasn't bad.

If you wanted to make changes in the table and save them back to the database, you'd have to add another step above to generate the INSERT, DELETE and UPDATE commands and add them to the DataAdapter. The following line of code should be added right before the DataTable declaration.

SqlCommandBuilder builder = new SqlCommandBuilder(da);

The missing commands are created and added to the DataAdapter object da in the CommandBuilder's constructor.

You would also have to double-click on the Save button at the upper right of the first datagridview and add the following Click event handler code:

da.Update(dt);MessageBox.Show("Saved");  // call the Update command created by the builder's constructor

However, in order to be able to reference da (the DataAdapter) and dt (the DataTable), you need to move the declarations of both to the area just above the constructor ("public Form1"). Otherwise, da and dt are created and destroyed in the Click event. Here's the restructured code:

using System;
using System.Data;                     // to get a DataTable
using System.Windows.Forms;
using System.Data.SqlClient;           // to get a SQLDataAdapter and a SQLCommandBuilder
using System.Collections.ObjectModel;  // to get an ObservableCollection

public partial class Form1 : Form
{
   SqlDataAdapter da;
   DataTable dt;

   public Form1()
   {  InitializeComponent(); }

   private void Load_DataTable_Click(object sender, EventArgs e)
   {  string cs = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TestData;Data Source=.";
      string cmd = "SELECT * FROM People";
      da = new SqlDataAdapter(cmd, cs);
      SqlCommandBuilder builder = new SqlCommandBuilder(da);
      dt = new DataTable();
      da.Fill(dt);
      dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
      dataGridView1.DataSource = dt;
   }

   private void Save_Data_Click(object sender, EventArgs e)
   {  da.Update(dt); MessageBox.Show("Saved");}

}

Loading Data using Entity-Framework

Entity-framework uses classes that the IDE generates for you. If you haven't already done so, from inside Visual Studio, click on Tools, Library Package Manager, Package Manager Console, and type

   install-package EntityFramework

That gets the latest version and adds a reference to your project.

Now, right-click on your project, select Add, New Item, and enter "ADO" in the upper-right-hand corner to filter available templates. Pick ADO.NET Entity Data Model (the only available selection) and click Next:

Figure
Fig. 2: Select the ADO.NET template

Select "Generate from Database" and again click Next:

Figure
Fig. 3: Generate from the DataBase

If you had already created a connection to the TestData database, you could select it from the dropdown below. However, entity connection strings don't look like ADO connection strings, create a new connection. Down at the bottom is a very important name - the name of the DBContextManager that will be created for it. It's a lot of code, but you don't even need to read it. However, you do need to know it's name. TestDataEntities is the default name in this case, and that's fine. Click Next to continue.

Figure
Fig. 4: Name the DBContext manager

There are several versions of Entity-Framework. Currently, version 6 is the latest and greatest. Unless you have a compelling reason to do so, leave it selected and click Next.

Figure
Fig. 5: Select the Entity-Framework version

In the next panel, you can select the name(s) of the table(s) for which you want "entity" classes to be generated. An entity is like a FoxPro table, in the sense that People.FirstName will be the way you refer to the firstName column in your table. But it also contains the skeleton of a query, which is how you'll use LINQ to SELECT data from your table. You can rename the Model NameSpace from Model1, the default, to something more meaningful, but it isn't really important. Note that if you had views and stored procedures, you could also include them.

Figure
Fig. 6: Select the table(s) for your model

The muscle of Entity-Framework is that little DBContext class that it created. It has to be declared globally in the form, so that it's available to both get and save data. Back near the top, before the call to the Form1() constructor, just below the SQLDataAdapter and DataTable declarations, add this:

   TestDataEntities context;

Now we're ready for some code. Double-click the Load Using Entity-Framework button and enter this code:

   context = new TestDataEntities();
   ObservableCollection peeps = new ObservableCollection();
   foreach (Person P in context.People) { peeps.Add(P); }
   dataGridView2.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
   dataGridView2.DataSource = peeps;

Finally, for the second Save button's Click event code, add this:

   context.SaveChanges(); MessageBox.Show("Saved");

The code that you need to accomplish this basic operation is, to say the least, not obvious. But then, what would be as straightforward as USE? In particular, the need to manually load an ObservableCollection in a LINQ expression is hard to understand, especially given that pretty much all WPF apps will use LINQ to manipulate data, and ObservableCollections to store said data in their ViewModels. So we'll just wait for EF 7.0...

I hope this section has demystified data access in Visual Studio to some degree. It won't ever be like FoxPro, but we can live with it.

(2) Ready, SET...

Consider one of FoxPro's most distinctive mechanisms: The SET commands. There are about 110 of them. How many are there in C#? NONE. In VB? Three, if you count the OPTION STRICT, EXPLICIT and INFER statements. None of the SET commands have system-wide counterparts in .NET.

In a typical application, I only use perhaps a dozen SET commands, but the defaults of the rest of them are always in effect, and they might be defeating some of your intentions. You should definitely find and study the implications of any and all SET statements in your FoxPro code.

There are mechanisms within FoxPro that provide a context for the SET commands; that context doesn't exist in .NET. For example, the notion of a "current select area" allows you to SEEK without saying which table you want to search - it's just the "current" one. SET DELETED applies to ALL open tables. The behavior of SEEK and FIND is determined by the EXACT, NEAR, DELETED and FILTER settings. GO TOP moves the top of the "current" table; SCAN...ENDSCAN traverses it; SET FILTER TO &expr filters it. This simplifies coding, but it creates a maintenance nightmare: You have to walk back, sometimes through several method calls, to determine the name of the current table. If it's a cursor derived from a SELECT of another table, or even worse, of another cursor, it can be challenging.

In SQL Server and in it's cousin LINQ, SET EXACT ON is the default behavior of a SELECT; you get exactly what you ask for. For SET EXACT OFF behavior (which is usually what you want), adding a wildcard specification (e.g. "SELECT * WHERE Name LIKE 'FR%'" will match only on the first few characters.

The behavior of comboboxes and listboxes is regulated by the MULTILOCKS setting; number display is determined by the CURRENCY, DECIMALS, SEPARATOR and FIXED settings. Your application's display of different currencies is controlled by these, a mechanism that can be quite complicated for multicurrency apps. In .NET, the functionality of many SET commands in FoxPro is implemented (generally) in parameters of the corresponding methods.

In FoxPro, SET TALK OFF turns off automatic echoing of the results of certain data transformations - COUNT, SELECT, SUM, and several others. There is an equivalent - sort of - in SQL: SET NOCOUNT ON|OFF. But unless you're in a T-SQL Query window, it's irrelevant. SET SAFETY ON|OFF determines whether overwriting a file produces a dialog if the file already exists, but each write method of each object that writes to disk has an optional parameter that controls that. SET CONFIRM ON|OFF determines whether pressing ENTER is required to move from one form field to another; in .NET apps, pressing TAB is how you move from one control to another. So there are equivalences, but there isn't a central setting to control how they work.

A comprehensive treatment of the 110 FoxPro SET commands and their C#/VB equivalents would be a book in itself. We'll deal with them a few at a time in a forthcoming series of articles.

(3) Subclassing the form controls

Subclassing your controls gives you one central point to set the look and feel of each type of control: 11-point green Tahoma text for labels, 9-point Courier New for textboxes, 14-point shadowed Arial for panel headings, and so forth. You can also have textboxes that turn white on red when they have the focus. This is what gives your app that distinctive look and feel that makes your UI memorable.

In FoxPro, you start by creatig a classlib with a .vcx extension, and then adding one of each control that you'll want to use. This is all it takes:

Listing n - Subclassing the WinForms controls in VFP

CREATE CLASSLIB MyControls
CREATE CLASS MyTextBox       OF MyControls AS TextBox (Ctrl-W to close the designer)
CREATE CLASS MyLabel         OF MyControls AS Label                 ""
CREATE CLASS MyCommandButton OF MyControls AS CommandButton         ""
CREATE CLASS MyRadioButton   OF MyControls AS RadioButton           ""
CREATE CLASS MyContainer     OF MyControls AS Container             ""
...etc...

Then, use MODIFY CLASS <Name> OF Mycontrols to open the designer, and add property settings and code just as you would in the Screen Designer. Couldn't be easier. Here's how to change the backgrond color of textboxes as you tab through them:

Listing n - Change background color of textboxes when focus changes

FontName = "Courier New"
FontSize = 9

PROCEDURE GotFocus:
THIS.BackColor = RGB ( 255,   0,   0 )
THIS.ForeColor = RGB (   0,   0,   0 )

PROCEDURE LostFocus:
THIS.BackColor = RGB (   0,   0,   0 )
THIS.ForeColor = RGB ( 255, 255, 255 )

That's all it takes in VFP. You can set each subclassed control's font, color and other properties, and add event-handling code as needed, and it will ripple through your entire app.

Subclassing the form controls in .NET is another matter. In either C# or VB, you add a new Class to your project, and create one class for each control. Then you add code to respond to any events.

Listing n - Subclassing form controls

C#

using System;
using System.Drawing;
using System.Windows.Forms;

public class MyTextBox: TextBox
{
   public MyTextBox(): base()
   { InitializeComponent(); }

   private void InitializeComponent()
   { this.Enter += new System.EventHandler(this.myTextBox_Enter);
     this.Leave += new System.EventHandler(this.myTextBox_Leave);
     this.Font = new Font("Courier New", 14);
     this.BackColor = Color.White;
   }

   private void myTextBox_Enter(object sender, EventArgs e)
   { this.BackColor = Color.Red;
     this.ForeColor = Color.White;
   }

   private void myTextBox_Leave(object sender, EventArgs e)
   { this.BackColor = Color.White;
     this.ForeColor = Color.Black;
   }
}

public class MyButton : Button {}

public class MyLabel : Label {}

public class MyRadioButton: RadioButton {}

public class MyListBox : ListBox {}

public class MyPanel : Panel { }


VB

Imports System
Imports System.Drawing
Imports System.Windows.Forms

Public Class MyTextBox
   Inherits TextBox
   Public Sub New()
      MyBase.New()
   InitializeComponent()
   End Sub

   Private Sub InitializeComponent()
     Me.Enter += New System.EventHandler(Me.myTextBox_Enter)
     Me.Leave += New System.EventHandler(Me.myTextBox_Leave)
     Me.Font = New Font("Courier New", 14)
     Me.BackColor = Color.White
   End Sub

   Private Sub myTextBox_Enter(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Enter
     Me.BackColor = Color.Red
     Me.ForeColor = Color.White
   End Sub

   Private Sub myTextBox_Leave(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Leave
     Me.BackColor = Color.White
     Me.ForeColor = Color.Black
   End Sub

End Class

Public Class MyButton
   Inherits Button
End Class

Public Class MyLabel
   Inherits Label
End Class

Public Class MyRadioButton
   Inherits RadioButton
End Class

Public Class MyListBox
   Inherits ListBox
End Class

Public Class MyPanel
   Inherits Panel
End Class

Don't look for a visual designer to build these controls; it's just code. Feels like a giant step backward, doesn't it?

(4) SCAN...ENDSCAN

Much of a typical FoxPro program consists of traversing tables, often using SCAN...ENDSCAN. For Example, consider the following code fragment:

Listing n - SCAN...ENDSCAN

* Create the test data
CREATE TABLE CUSTOMERS ( CUSTCODE Int, OverDueBal Logical )
INSERT INTO CUSTOMERS VALUES ( 1, .F. )
INSERT INTO CUSTOMERS VALUES ( 2, .T. )
INSERT INTO CUSTOMERS VALUES ( 3, .T. )
INSERT INTO CUSTOMERS VALUES ( 4, .F. )

*Create a target table to hold selected records
CREATE CURSOR LISTONE ( CustCode Int )

SELECT CUSTOMERS
SCAN
   IF CUSTOMERS.OverDueBal
      INSERT INTO LISTONE ( CustCode ) VALUES CUSTOMERS.CustCode )
   ENDIF
ENDSCAN

FoxPro only has one way to store tabular data: Tables and their cousins Cursors (temporary tables - usually in-memory). In .NET, you have a choice of data structures. In .NET, you have DataSets, which can contain one or more DataTables. Each DataTable has a DataView, which in many ways is more similar to a FoxPro table, since it supports filtering and change notification, and makes your code a little more readable. The DataTable and DataView are in the System.Data namespace. They're indexed by row number; to traverse either, you use a for or foreach loop:

In my example, I create some test data on the fly in order to illustrate the example.

Listing n - iterating over a collection

C#

DataTable MyTable = new DataTable("Customers");

DataColumn dc1 = new DataColumn("CustCode", typeof(Int32));
MyTable.Columns.Add(dc1);

DataColumn dc2 = new DataColumn("OverDueBal", typeof(Boolean));
MyTable.Columns.Add(dc2);

DataRow dr = MyTable.NewRow();
dr[0] = 1; dr[1] = false; MyTable.Rows.Add(dr);
dr = MyTable.NewRow();
dr[0] = 2; dr[1] = true; MyTable.Rows.Add(dr);
dr = MyTable.NewRow();
dr[0] = 3; dr[1] = true; MyTable.Rows.Add(dr);
dr = MyTable.NewRow();
dr[0] = 4; dr[1] = false; MyTable.Rows.Add(dr);

DataTable ListOne = new DataTable();
ListOne.Clear();
DataColumn dc = new DataColumn("CustCode", typeof(Int32));
ListOne.Columns.Add(dc);

DataView dv = MyTable.DefaultView;
int NumRows = dv.Count;
for (int I = 0; I < NumRows; I++)
{
   if (Convert.ToInt32(dv[I]["OverDueBal"]) > 0) { ListOne.Rows.Add(dv[I]["CustCode"]); }
}
MessageBox.Show(String.Format("{0} total customers; {1} have overdue balances", dv.Count, ListOne.Rows.Count));

VB

Dim MyTable As New DataTable("Customers")

Dim dc1 As New DataColumn("CustCode", GetType(Int32))
MyTable.Columns.Add(dc1)

Dim dc2 As New DataColumn("OverDueBal", GetType(Boolean))
MyTable.Columns.Add(dc2)

Dim dr As DataRow _
   = MyTable.NewRow(): dr(0) = 1: dr(1) = False: MyTable.Rows.Add(dr)
dr = MyTable.NewRow(): dr(0) = 2: dr(1) = True:  MyTable.Rows.Add(dr)
dr = MyTable.NewRow(): dr(0) = 3: dr(1) = True:  MyTable.Rows.Add(dr)
dr = MyTable.NewRow(): dr(0) = 4: dr(1) = False: MyTable.Rows.Add(dr)

Dim ListOne As New DataTable()
ListOne.Clear()
Dim dc As New DataColumn("CustCode", GetType(Int32))
ListOne.Columns.Add(dc)

Dim dv As DataView = MyTable.DefaultView
Dim NumRows As Integer = dv.Count
For I As Integer = 0 To NumRows - 1
    If Convert.ToInt32(dv(I)("OverDueBal")) > 0 Then
       ListOne.Rows.Add(dv(I)("CustCode"))
    End If
Next I
MessageBox.Show(String.Format("{0} total customers; {1} have overdue balances", dv.Count, ListOne.Rows.Count))

If you're using WPF (and even if you're not), you may prefer using an ObservableCollection, which you generally build using the ADO Entity-Framework ORM tool. ObservableCollections can also be traversed using either an index (rowname[I]) or more commonly a foreach loop. By default, a single object and a collection of such objects are defined in the generated class, so that "foreach (customer in customers)" means what you would expect it to mean. See almost any Entity-Framework code on the Net for examples.

(5) Embedded SQL WHERE clauses disguised as IF statements

Have you ever seen code like this in a legacy FoxPro app?

Of course you have.
SELECT WorkFile
SCAN
   IF MONTH > 3   && first quarter sales
      LOOP
   ENDIF
   IF NOT SEEK ( LIVEORDERS, CustCode )   && Skip if no outstanding orders
      LOOP
   ENDIF
   BalPct = LIVEORDERS.Amount / WorkFile.CreditLimit
   IF BalPct <= .3
      LOOP
   ENDIF
   ? CustName, Amount, DueDate
ENDSCAN

Have you written code like this? Of course you have. If you've been in this business long enough, as I have, you've made every dumb mistake in the book. That's how you learn what not to do. Good judgment comes from experience; experience comes from bad judgment.

Should you convert this code to similar code in C# or VB? Please don't. Long ago, FoxPro didn't have SQL, so this was the only way to filter data before SQL came along. In fact, adding SQL was how Dave Fulton convinced Microsoft to buy FoxPro and kill it, because it turned FoxPro into a true competitor of their (much more expensive) SQL Server product. But that would be a monopolistic practice, wouldn't it? And isn't monopoly against the law in the United States?

Microsoft hired a guy named Milind Lele, a complete unknown from outside of the FoxPro community, to oversee the shutdown of the FoxPro division. So I read his doctoral dissertation. It was on "How to run a monopoly company and get away with it." He has since left Microsoft and runs a consulting business specializing in how to run a monopoly company and get away with it, based on his book, "Monopoly Rules: How to Find, Capture, and Control the Most Lucrative Markets in Any Business," No kidding. I was fired as Contributing Editor of FoxTalk for suggesting that this was happening. Microsoft was FoxTalk's biggest advertiser. If you're not outraged, you're not paying attention...

FoxPro's SQL is very similar to Microsoft T-SQL, and a reasonable rendering of this code might be this:

Listing n - Procedural code converted to SQL

SELECT CustName, CreditLimit, DueDate
  FROM WorkFile, LiveOrders
 WHERE MONTH(duedate) <= 3
   AND WorkFile.CustCode IN (SELECT CustCode FROM LIVEORDERS)
   AND (WorkFile.CreditLimit / LiveOrders.Amount) > .3

I've seen SCAN/ENDSCAN loops that took hours to convert into one or two SELECT statements. FoxPro developers were devilishly clever in getting the results that they needed. You might have to write a couple of SELECT statements, perhaps a SQL function or two, and perhaps a stored procedure to boot, to reproduce the behavior of a few dozen lines of FoxPro code to filter a table as it scans through it.

(6) Returning value(s) from a form

In FoxPro, you can create a modal form and then use something like the following to pass it parameters and return a value:

    DO FORM <name> WITH <parm1,...parmn> TO <varName> 

In .NET, a modal form can't return a value. But it can contain as many public properties as you wish, and when you call Hide(), control returns to the calling program, and the form is still out there. So this is how you return one or more values:

    Payments pmt = new Payments(); pmt.ShowDialog();

In the form, declare one or more public properties:

    public string ReturnCode { get; set; }
    public decimal Balance   { get; set; }

And in the Click() event code for the button that closes the form:

    this.Hide();

Now we can show the complete code that calls the form and returns the values from the now-hidden form:

    Payments pmt = new Payments(); pmt.ShowDialog();
    // the form is now hidden, but still active. Retrieve the two public property values:
    string  SavedCode = pmt.ReturnCode;
    decimal Bal       = pmt.Balance;
    pmt.Close();      // Now you can close the form.

And that's how it's done.

(7) Reports

REPORT FORM <FRXName>, which has a half-dozen useful keywords that can follow the basic command, is one of the best features in FoxPro. You can open the data sources in its DataEnvironment code, SELECT and JOIN various tables, convert and format data, and then use the simple report layout designer's stored instructions to produce useful reports.

Initially, Microsoft licensed Crystal Reports as a free add-in of Visual Studio 2005. Ultimately, they dumped it in favor of SQL Server Reporting Services (SSRS), when it was eventually brought up to snuff. I use Telerik Reporting, and I wouldn't use it if SSRS were more user-friendly. In either case, you're responsible for loading the data source before the reporter starts, and you'll probably do it using SQL.

You might not even be dealing with REPORT FORM. I recently had to convert a large number of reports from FoxBASE, and I had forgotten how primitive reporting was in the old days. ou may find some of these gems in your project:

Listing n - earlier FoxBASE and FoxPro reporting programs

* FoxBASE reporting using the "?" command
USE ACCOUNTS
LineNum = 66
DO WHILE NOT EOF()
    IF LineNum > 65
	   ? 'Account balances:'
  	   ? '-----------------'
  	   ?
  	   LineNum = 3
  	ENDIF
	? Account, Balance
	LineNum = LineNum + 1
	IF LineNum > 65
	   ? CHR(12)
	ENDIF
ENDDO


* FoxPro reporting using TextMerge
SET TEXTMERGE ON TO Account.rpt NOSHOW
LineNum = 66
USE Accounts
SCAN
	IF LineNum > 65
	   Heading()
	ENDIF
	\<>   <>
	LineNum = LineNum + 1
	IF LineNum > 65
	   \<>
	ENDIF
ENDSCAN

USE
SET TEXTMERGE TO
SET TEXTMERGE OFF
MODI FILE Account.rpt NOMO
!copy/n Account.rpt prn:

PROCEDURE Heading
\Accounting Report for <>
\-----------------------------------
LineNum = 3
ENDPROC

It goes without saying that rendering this code in C# or VB would not be a solution. Use the reporting tools, figure out whether you want to use a DataTable, a DataView, an ObservableCollection or some other structure, populate the data source, and run the report that you've designed. The only use you have for the code is to design a more professional-looking report, and to construct the SQL statement(s) to populate the data source.

(8) APPEND

Adding a blank record to a table is actually just about as easy in .NET as it is in FoxPro. If you want to populate some fields, do so before adding the row to the table.

DataRow dr = MyTable.NewRow();
dr[0] = "Les";
dr["Amount"] = 123.98;
MyTable.Rows.Add(dr);

To add a row from another table, don't use Add: use ImportRow():

DataRow dr = Table1.Rows[n];
Table2.ImportRow(dr);

To import an entire table, you just iterate over all of the rows in the source table:

foreach (DataRow dr in Table1)
 { Table2.ImportRow(dr); }

And to append conditionally (e.g. APPEND FROM TEMP FOR State = "CA"):

foreach (DataRow dr in Table1)
 { if ( dr["State"] == "CA" ) {  Table2.ImportRow(dr); } }

(9) SCATTER/GATHER

This pair of FoxPro commands creates a bunch of memvars, and then REPLACEs fields of the same name in the source table with their contents - like MOVE CORRESPONDING in COBOL, if you've been around a while. Here's an example:

SELECT Orders
SCAN
    IF Balance > 0
       SCATTER MEMVAR
       SELECT OrdTemp  && Contains only OrderNum, Balance, and ShipDate
       GATHER  MEMVAR
    ENDIF
ENDSCAN

My advice is to eliminate the middleman. If a similarly named column exists in the target row, assign it the value of the column of the same name in the source row:

DataRow drFrom = Table1.Rows[n1];
DataRow drTo = Table2.Rows[n2];
for (    int i = 0; i < drFrom.Columns.Count; i++ )
 { for ( int j = 0; j < drTo.Columns.Count;   j++  )
   { if ( drFrom.Column[i].Name == drTo.Column[j].Name ) { drTo[j] = drFrom[i]; } } }

Not that this works because drTo isn't a copy of Table2.Rows[n2]; it's a pointer to the original row.

(10) Arrays containing different data types

To create an array of strings in C#, do this:

   string[] names = { "Les", "Bob", "Sharon" };

To create an array of integers, do this:

   int[] vals = { 3, 12, 192, 21 };

But you can't mix and match. Array elements all have to be of the same type.

In FoxPro, arrays can contain any combination of data types. In fact, you can USE a table and SCATTER TO ARRAY laMyArray, and laMyArray will contain exactly the same data as the record that it came from: strings, numbers, dates, whatever.

USE Customers
SCATTER TO ARRAY laCust

You can't do that in .NET. But you can use collections to do what FoxPro does with arrays.

The List is a good candidate. It's declared using the generics syntax:

   List custs

The list can then be loaded by any number of mechanisms, including

<query>.ToList()
, as was shown previously. But Lists are read-only - okay if you don't need to change the data, but we need to change the data. So the ObservableCollection is the collection of choice for database guys. It's especially needed for WPF, which require that each column value be a public property with Property Change Notification built in. That's exactly what an ObservableCollection is.

When you declare an ObservableCollection, you invariably specify what type of records it will hold. This is called it's type, denoted by a capital T in the literature of generics. For example, if you have a Customer table, your declaration of the ObservableCollection to hold it in memory will be this:

   public ObservableCollection custs { get; set; }

It's always done exactly this way, right at the top of your code file, before the constructor. That's so it can be found from other code using reflection, which can scan for public properties. The get and set are needed to allow code to run when you either set a value or request a value, since any code in the "getter" and "setter" runs when those two events occur.

Curiously, Entity Framework can return your data in a number of collection types (e.g. ToList()), but not as an ObservableCollection. I use a third-party Object-Relational Management (ORM) tool called IdeaBlade that corrects a number of E-F deficiencies, including this one. Here's the code to return your data directly into an ObservableCollection of the desired type:

public ObservableCollection custs { get; set; }
...
custs = new ObservableCollection<Customer>();
var query = dbContext.Customer.Where(c => c.State == "CA");
query.Execute().ForEach(custs.Add);

NOTE: dbContext is often called mgr in IdeaBlade documentation.

So if you use IdeaBlade DevForce 2010 instead of native Entity-Framework, all of your data will be stored in ObservableCollections.

When you install DevForce, it replaces the ADO Data Entity Model template (don't worry; you can swap them out in seconds.) Then,

  1. Right-click on your project and select Add, Class;
  2. Type "ADO" in the Search textbox at the upper right to filter selections; and
  3. Select "ADO Data Model"

All of your tables will be represented by classes that hold your data, populate themselves using LINQ, and update their table based on additions, changes or deletions made to the ObservableCollections where your data is stored. And since PropertyChangeNotification is built in, a change anywhere in your data is reflected instantly anywhere else that the data is displayed - even on another screen.

Note: Make sure that the table's primary key ends with "ID" (or consists simply of "ID"); that's how the Template Generator knows that it's a key. There's a way to designate another column as the key, so that you don't have to change any of your primary key names. You can also declare compound keys. But that's a topic for another article.

Conclusion

The bad news is that you're probably looking at converting your FoxPro apps at some point in the not-too-distant future. It's expensive, but as many grunts vacationing in beautiful Southeast Asia discovered some years ago, there's a point where it's more dangerous to stay put than to get moving. If we can help, give us a call.

The Visual FoxPro Toolkit for .NET by Kamal Patel and Cathy Gero is an excellent starting point for your first forays into .NET. It's available with source code in both C# and VB. I wouldn't use it, but the source code is an excellent tool for understanding the issues that are involved in conversion from FoxPro to .NET.

qqq


Copyright(C) Pinter Consulting, 2012Tel: +1 (650) 464-6924
Automated conversion between C# and VB by Visible C#/Visible VB from Tangible Software Solutions