CODE Pathway Gateway
   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

Data access in FoxPro and .NET

Translating FoxPro data access to .NET and SQL isn't too hard, providing you know where to look for the equivalents

Les Pinter


Nothing does data like FoxPro. .NET certainly doesn't - not even close.

FoxPro uses a sort of default data environment, consisting of select areas and a group of commands and functions that, unless you specify otherwise, operate only on the current select area. Some of them are so subtly implemented that you don't even realize they're there. and that's the problem. Take the most basic operations related to records: Move forward and backward; add or delete a record; retrieve and display the value of a column in the current record. These are deceptively simple in FoxPro:

  • SKIP
  • SKIP -1
  • ? CompanyName

The equivalents in .NET require that you explicitly operate directly on the physical table, or instantiate objects whose methods are analogous to FoxPro's simple commands. Nothing about data handling is subtle in .NET. We simply refuse to believe that it really takes all of this. It does. Hopefully, this article will explain why, and provide you with the simplest equivalents.

Also, you'll probably be moving your data to SQL. While you can use FoxPro tables via the OleDB components that come with .NET, I don't know anyone who does. There are actually several problems with DBFs, and SQL fixes them, albeit not for free. The least expensive SQL Server license that I know of is about two grand, and it can go up pretty fast from there. But if you lose two or three days of programming trying to work around DBF challenges (record locking contention, trashed indexes and slow updating of CDX files on each active workstation), it doesn't take long to spend a few grand. We'll go with SQL Server. (In our article on Moving DBF Data to SQL, described below, you'll see how to do it.

Getting your data into SQL Server

You can use SQL Server Integration Services (SSIS - formerly known as DTS, or Data Transformation Services) to migrate your tables; or you can download the Fox2SQL program described in the first article in this series, which will do it for you. And with the source code, you can change if you don't like our default approaches. For example, we convert CHAR fields to VarChar in SQL, principally because string comparisons that only consider the number of characters in the right-side expression will work the expected way if the right-hand expression is a SQL VarChar column. If you prefer CHAR fields, go for it. Also, in our CREATE TABLE statement, we add "NOT NULL" and a default of '' for strings and dates and zero for numerics, so that you don't have to deal with Nulls - truly one of the most irritating mistakes in the design of SQL. Well, they call it a feature...

Connection strings

The first and in some ways most irritating characteristic of SQL Server is that it requires a Connection String, which either looks like this:

Integrated Security=SSPI;
Persist Security Info=False;
Initial Catalog=NORTHWIND;
Data Source=FAST_HP

or this:


The first is called a Trusted Connection; the second requires a userid and password. I generally use a trusted connection while I'm developing, then switch to a userid and password when we go into production. To create one on your own computer, create a file called, say, x.udl on the desktop and double-click on it. It will open the Data Link Properties dialog. On the first (Provider) tab, select Microsoft OLEDB Provider for Microsoft SQL Server (Fig, 1). On the second (Connection) tab, select your computer name, make sure the Integrated Security radio button is selected, and pick the name of the database you want to use from the combobox. If you've associated a userid and password with the database, select the Userid and Password radio button and enter them (Fig. 2). Click on the Test Connection button to verify that it works. Then save it, open it with Notepad, and cut and paste the connection string into wherever you need it. Actually, there are several ways to create a connection string, and in all of them, .NET will create one for you using the same Data Link dialog, but if you're into the nuts and bolts, that's how you do it.

Fig. 1 - Selecting the provider

Fig. 2 - Selecting the database

In either case, remove the first part of the string, "Provider=SQLOLEDB.1;", from your connection string, because you'll be passing it to a SQLConnection, and it already knows what the provider is.

Return data to where?

There are several mechanisms for getting data from a table. A SQLDataAdapter puts the requested records into a datatable inside a dataset, which is something like a FoxPro screen's Data Environment. A SQLDataReader reads one record at a time, forward-only, and is very, very fast if that's what you need. A SQLCommand can also use a DataReader to return individual rows, but it can't return a DataTable. So you can use the data, but perhaps not in the way you needed to. We'll use the SQLDataAdapter, because a DataTable is in some ways an in-memory DBF.

First, you have to instantiate a SQLDataAdapter object. There are several overloads. Allow me to explain. You can define as many versions of a function (method) as you please in .NET, as long as they have different signatures (i.e. different parameters.) You just include the word Overloads in each subsequent method declaration. Within .NET, it's common to define one overload for each possible combination of parameters. You can create your SQLDataAdapter with no parameters, with just the SQL statement, or with the SQL Statement and the connection string - or alternately with the SQL Statement and a SQLConnection object. You can assign any values not passed as parameters to the corresponding object properties;

It's not just a matter of style; there are substantive differences. If use the last overload and pass a SQLConnection that already exists to the constructor (that's what the INIT is called in .NET), it will re-use the connection. If you pass a SQL Connection string as the second parameter, it will create a new connection. If I just need one SQLDataAdapter and hence one SQLConnection, pass it a SQL statement and a connection string, thusly:

Dim da as New SQLDataAdapter ( _
 "Integrated Security=SSPI;Persist Security Info=False;
  Initial Catalog=NORTHWIND;Data Source=FAST_HP")

However, in .NET, we don't like to go around cutting and pasting these connection strings; so, there is a place to store yours: An Application Configuration file. You can add one to the project by right-clicking on the project and selecting Add, New item, then from the General group select Application Configuration File.

(You can either add it as an AppSetting or a ConnectionString. Either one can be read in code; either can be created in several ways; and each has some different characteristics. This is typical of .NET; the choices are simply bewildering. I'll show you one way, and someone who wants to look clever can tell you there's a better way. If I include everything, we'll never get done.)

Add this just after the <Configuration> tag at the beginning of the app.config file:

    <add key="ConnStr"
         value="Integrated Security=SSPI;
                Persist Security Info=False;
                Initial Catalog=NORTHWIND;
                Data Source=FAST_HP"/>

Then, add a reference to System.Configuration to your project, and "Include System.Configuration" ("using System.Configuration;" in C#) at the top of each file that reads a SQL table, and you can retrieve the connection string using one line of code:

Dim cs as string = ConfigurationManager.AppSettings("ConnStr")


string cs = ConfigurationManager.AppSettings("ConnStr")

I could write five more pages about connection strings, but that's not what this article is about. I told you it would be irritating...


FoxPro's principal data commands - INDEX, SKIP, GO, FIND, APPEND and DELETE are part of the environment, and are associated with the select area in use. In .NET, you can load the entire table into memory, then move around it in ways that resemble FoxPro's SKIP, GO and FIND/SEEK/LOCATE commands; you can also apply filters and set the sort (index) order. You can also use "For Each r as datarow in ds.tables(0).rows" to pass through the table (or the currently filtered rows) one at a time.

DataTables live inside Datasets, and can be created using a SQLDataAdapter, like this:

Imports System.Configuration, System.Data, System.Data.SqlClient

Dim ds As New Dataset
Dim da As SQLDataAdapter
(Load event of form)
Dim cs as string = ConfigurationManager.AppSettings("ConnString")
da = New SQLDataAdapter("SELECT * FROM CUSTOMERS, cs )
da.fill(ds,"Customers")  ' the second parameter changes the table name from "Table1" to "Customers"
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

Dataset ds;
SQLDataAdapter da;
(Load event of form)
string cs = ConfigurationManager.AppSettings("ConnString")
SQLDataAdapter da = New SQLDataAdapter("SELECT * FROM CUSTOMERS, cs );
da.fill(ds,"Customers");  // the second parameter changes the table name from "Table1" to "Customers"

(NB: The Include or using statements allow you to forego lengthy prefixes for the data access components. You still need a reference to System.Data and System.Data.SQLClient in your project.)

The dataset now contains one datatable, designated, of course, Tables(0) (Tables[0] in C#). The table contains a Rows collection, The first row is, of course, Rows(0) (Rows[0] in C#). And the first column in the table is referenced using either Tables(0).Rows(0)(0) or Tables(0).Rows(0)("ClientID") (Tables[0].Rows[0][0] or Tables[0].Rows[0]["ClientID"] in C#).

Let's stop and take a deep breath here. The preceding page has been the equivalent of USE CUSTOMERS in FoxPro, and still doesn't allow us to write "? Customers.ClientID". (There is a way to get to the point where you can use Customers.ClientID, but we're not there yet.) There is also a way to drag components from the IDE and generate much of this code, but it paints you into corners that you can't get out of if you don't know what the generated code does. It's frustrating and disappointing, but so far, it's the best Microsoft can do. We'll explore automatic generation of this code in a subsequent article.

Without an index, in FoxPro the first record is always record 1. With an index, it's the first record based on the current index order. RECNO() returns the position of that record relative to the top of the physical file, i.e. if your table has an index opened, the first record may be 315 instead of 1. RECCOUNT() returns the number of records in the table. SKIP, SKIP -1, GO TOP and GO BOTTOM move the FoxPro record pointer to the next, previous, first and last record respectively, selecting a different current record. ? Customers.ClientID (or simply ? ClientID) displays the contents of the ClientID in the current ID.

The .NET equivlent of RECNO() isn't located in the data table; it's the Pointer property of the BindingManager. If you want to traverse a datatable, you add a BindingManager to the form and set its DataSource property to the datatable. This BindingManager contains the MoveFirst, MovePrevious, MoveNext and MoveLast methods that you need, as well as a few others. The form shown in Fig. 3 demonstrates how this interaction works:

Fig. 3 - A form to demonstrate the .NET equivalents for GO TOP, GO BOTTOM, SKIP and SKIP -1

Here's the code:

Imports System.Configuration, System.Data, System.Data.SqlClient

Public Class Form1

  Dim ds As New DataSet
  Dim da As SqlDataAdapter
  Dim bs As New BindingSource

  Private Sub Form1_Load( _
   ByVal sender As System.Object, ByVal e As System.EventArgs) _
   Handles MyBase.Load

    Dim cs As String = ConfigurationManager.AppSettings("ConnStr")
    da = New SqlDataAdapter("SELECT * FROM CUSTOMERS", cs)
    da.Fill(ds, "Customers")
    DataGridView1.DataSource = ds.Tables(0).DefaultView
    bs.DataSource = ds.Tables("CUSTOMERS").DefaultView
    DataGridView1.Rows(bs.Position).Selected = True

  End Sub

  Private Sub Button1_Click( _
   ByVal sender As System.Object, ByVal e As System.EventArgs) _
   Handles Button1.Click
  End Sub

  Private Sub Button2_Click( _
   ByVal sender As System.Object, ByVal e As System.EventArgs) _
   Handles Button2.Click
  End Sub

  Private Sub Button3_Click( _
   ByVal sender As System.Object, ByVal e As System.EventArgs) _
   Handles Button3.Click
  End Sub

  Private Sub Button4_Click( _
   ByVal sender As System.Object, ByVal e As System.EventArgs) _
   Handles Button4.Click
  End Sub

  Sub ShowMove(ByVal Direction As String)
    DataGridView1.Rows(bs.Position).Selected = False
    Select Case Direction
      Case "Top"
      Case "Previous"
      Case "Next"
      Case "Bottom"
    End Select
    DataGridView1.Rows(bs.Position).Selected = True
    Text = "Row " & bs.Position.ToString + ": " + ds.Tables(0).Rows(bs.Position)(0)
    DataGridView1.CurrentCell = DataGridView1.Rows(bs.Position).Cells(0)
  End Sub

End Class

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Diagnostics;
using System.Windows.Forms;
using System.Linq;
using System.Xml.Linq;

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace DataAccess
  public partial class Form1

    internal Form1()
    private DataSet ds = new DataSet();
    private SqlDataAdapter da;
    private BindingSource bs = new BindingSource();

    private void Form1_Load(object sender, System.EventArgs e)

    string cs = ConfigurationManager.AppSettings["ConnStr"];
    da = new SqlDataAdapter("SELECT * FROM CUSTOMERS", cs);
    da.Fill(ds, "Customers");
    DataGridView1.DataSource = ds.Tables[0].DefaultView;
    bs.DataSource = ds.Tables["CUSTOMERS"].DefaultView;
    DataGridView1.Rows[bs.Position].Selected = true;


    private void Button1_Click(object sender, System.EventArgs e)

    private void Button2_Click(object sender, System.EventArgs e)

    private void Button3_Click(object sender, System.EventArgs e)

    private void Button4_Click(object sender, System.EventArgs e)

    public void ShowMove(string Direction)
    DataGridView1.Rows[bs.Position].Selected = false;
    switch (Direction)
      case "Top":
      case "Previous":
      case "Next":
      case "Bottom":
    DataGridView1.Rows[bs.Position].Selected = true;
    Text = "Row " + bs.Position.ToString() + ": " + ds.Tables[0].Rows[bs.Position][0];
    DataGridView1.CurrentCell = DataGridView1.Rows[bs.Position].Cells[0];

The BindingSource object bs is where the equivalents of GO TOP, SKIP-1, SKIP, GO BOTTOM and RECNO() are located; they are, respectively, MoveFirst(), MovePrevious(), MoveNext(), MoveLast() and Position. (Note that the first record is at Position 0.)

I had to do a few more things to make this demo behave properly: The highlighting of the selected row is handled by setting Selected = True; the previously selected row has to be unselected before you move; and the DataGridView's row pointer at the left of the grid is unaffected by the Selected setting, and must be moved by setting the CurrentCell. That's a little more involved than SKIP, isn't it?

However, there are actually some improvements. Due to the disconnected nature of data in .NET, a dozen users can run this form simultaneously without contention or locking problems. There is no equivalent for USE ... EXCLUSIVE in .NET, so any part of your legacy application that depends on exclusive use will have to be approached differently, period. And if you click on any column heading, the data will be indexed by that column, and will toggle between ASCENDING and DESCENDING, with no coding, and the button commands will still all work. I must say that I lust after this feature in FoxPro, but to no avail; Randy and Calvin have left the building.

Caveat Browser

This looks a lot like BROWSE, but it isn't. In FoxPro, you can change the data in a table while browsing it. You can do so as well in .NET, but it's not automatic. In order to do so, you have to call the Update() method of the DataAdapter. This method is only created if your SQL table has a Primary Key; and it must be called in the DataGridView's CellLeave() or RowLeave() method. I haven't implemented it here, but it opens up a whole other can of worms. And there's no equivalent to the BROWSE command's features for calling a VALID() method when exiting a column, or for specifying a column header or width in the BROWSE command. We'll do an article on BROWSE later on, and it will be HUGE (and I mean that technically.)

Filtering and sorting

If you're using SQL to filter results, you use the WHERE, HAVING or JOIN clauses. To sort the data, you use the ORDER BY clause. Just like in FoxPro, you can create multiple indexes within SQL, and the corresponding Rushmore-related functions will be real, real fast. They should be; SQL incorporated the FoxPro Rushmore technology in SQL 2005. You're welcome.

Form2 (Fig. 4) has a few added features;

Fig. 4 - Adding sorting and filtering to a dataview

If you're using a DataView from a DataTable in a Dataset, you can easily filter and sort your data. Here's the code for the three new buttons. I've hard-coded the filtering function to show records containing the string the user enters anywhere within the CompanyName field of the Northwind Customers table. The Filter and Sort methods are associated with the DataView, by the way, not with the DataTable. That's why I added the DataView above, and that's the only reason. I can't for the life of me imagine why Microsoft felt compelled to draw this distinction, but there it is.

  Private Sub Button5_Click( _
   ByVal sender As System.Object, ByVal e As System.EventArgs) _
   Handles Button5.Click
    dv.Sort = "CompanyName"
    dv.RowFilter = "CompanyName LIKE '%" + TextBox1.Text.TrimEnd & "%'"
    DataGridView1.Rows(bs.Position).Selected = True
  End Sub

  Private Sub Button6_Click( _
   ByVal sender As System.Object, ByVal e As System.EventArgs) _
   Handles Button6.Click
    dv.RowFilter = ""
    TextBox1.Text = ""
    DataGridView1.Rows(bs.Position).Selected = True
  End Sub

  Private Sub ComboBox1_SelectedIndexChanged( _
   ByVal sender As System.Object, ByVal e As System.EventArgs) _
   Handles ComboBox1.SelectedIndexChanged
    dv.Sort = ComboBox1.Text.TrimEnd
  End Sub

Note: To make this form the one you see when the project is launched, open the Project Properties Sheet and select Form2 as the Startup form (Fig. 5):

Fig. 5 - Making Form2 the startup form


In FoxPro, APPEND BLANK | FROM ARRAY and INSERT BLANK | FROM ARRAY can be used to add a record; DELETE marks a record as deleted, which makes it disappear if SET DELETED ON is in effect, or when you PACK the table; and REPLACE, UPDATE or just typing into a BROWSE grid and then moving to a different record, closing the table, or calling TableUpdate() will all change the contents of the current record. In the disconnected data world, it doesn't work that way.

The datatable equivalent of APPEND is <DataTable>.Rows.Add(<DataRow>). You have to build the DataRow before you add it. Also, after changing the DataTable, you have to call EndEdit on the BindingManager and SaveChanges on the DataAdapter in order to save your changes. You'll see this shortly.

Deleting a record is done like this: <BindingSource>..Delete(<BindingSource>.CurrentRow), and

Note that marking a record for deletion is unique to FoxPro, until you get to SQL 2008.

Microsoft's code generator as a solution

There's a way to get the IDE to write code that performs all of these operations. Create a new application called WizardGenerated App. Select Data from the IDE menu and add a connection string to the Northwind dataset. When asked, select the Customers table, checking only the first half-dozen columns. This will open the Data Sources window (Figure 6).

Fig. 6 - The Data Sources window

Click on Details; this sets the stage for what happens next. Drag and drop from the Customers table in the Data Sources treeview onto the form. After a few seconds, you'll have what you see in Fig. 7:

Fig. 7 - The generated Add/Edit/Delete form

You have to resize a few textboxes, but it's essentially complete. We're going to save a detailed analysis of this application, and a few suggestions for improving it, for the next article. (You'll notice that a Binding Navigator has been added to manage record-pointer movement and adding and deleting records and saving changes. It also introduces databinding using a typed dataset, another idea that begs for improvement. The Property sheet at the bottom of Fig. 7 shows the Text property data binding, which is actually reflected in the CodeBehind. Here's the relevant code for Load and Save:

Code to load data and to save changes:


Private Sub Form1_Load( _
 ByVal sender As System.Object, ByVal e As System.EventArgs) _
 Handles MyBase.Load


End Sub

Private Sub CustomersBindingNavigatorSaveItem_Click( _
 ByVal sender As System.Object, ByVal e As System.EventArgs) _
 Handles CustomersBindingNavigatorSaveItem.Click


End Sub


private void customersBindingNavigatorSaveItem_Click_1(object sender, EventArgs e)

private void Form1_Load_1(object sender, EventArgs e)

If you open up the generated dataset code, you'll find that SQL INSERT, UPDATE and DELETE statements have been generated and assigned to the DataTable object's InsertCommand, UpdateCommand and DeleteCommand. We'll look at these in the next article in this series, Add/Edit/Delete forms in .NET However, they're not identical. For example, if you delete a record but don't click on the little diskette to save your changes, the record isn't deleted; similarly, add a record without clicking on Save before closing the screen, and it's not saved. The default behaviors that we rely on as FoxPro developers aren't there. You have to write code to implement them.

As we've noted, in .NET you work with a copy of your data. Ultimately, saving changes means issuing an INSERT, UPDATE or DELETE statement against SQL Server. (This is also true of OLEDB and/or ODBC data sources, but this article would be an encyclopedia if we dealt with all of them; besides, at least the DataAdapter abstraction reduces our need to know the precise syntax of the data store's commandset to a call to an Insertcommand, UpdateCommand or DeleteCommand.)


A common requirement in FoxPro is to find the first of a group of records, then pass through all of the subsequent records that apply while doing some other condition testing and processing. The FoxPro way to do this looks like this:

SCAN WHILE STATE = "CA"  '&& Can also just use SCAN FOR STATE = "CA"
	 IF Balance < 100
	 && Do something here

The .NET equivalent would be this:


Dim ds as new dataset
Dim cs as string = ConfigurationManager.AppSettings("ConnStr")
Dim da as New SQLDataAdapter ( "SELECT * FROM Customers" )
Dim dv as DataView = ds.Tables(0).DefaultView
dv.RowFilter = "CompanyName Like 'C%'"
For Each dr As DataRow In dv.ToTable.Rows


DataSet ds = new DataSet();
string cs = ConfigurationManager.AppSettings["ConnStr"];
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Customers", cs);
DataView dv = ds.Tables[0].DefaultView;
dv.RowFilter = "CompanyName Like 'C%'";
foreach (DataRow dr in dv.ToTable().Rows)

Note that a simple SELECT ... WHERE would generally have done a better job in the Fox source. There are many, many places in the legacy code that we see where DO WHILE NOT EOF() or SCAN loops were used because the original program was written in FoxBASE, and SELECT wasn't yet part of the language. It's not considered cheating to use your brain. If it needs to be translated before it can be migrated, by all means, do so.

When a dataset is not the right approach

The approaches that we've seen so far all rely on returning all of the records in a table into a datatable in a dataset. That's not always desirable, or practical, or even possible.

If you run FoxPro's Upsizing Wizard, it will convert your USE <TableName> statements into SQL equivealents that return all of the records in the corresponding SQL table into a cursor. Let a hundred users do this on a network, and you'll bring your application to its knees.

If you have a table with a lot of records in it - like say, a million - this won't do. Loading a million records over the network is just too slow for any practical use. If you have lots of records, you'll have to do things differently - a LOT differently.

You might not have ever thought about it, but when you USE a table in FoxPro, you get one record - the first one, based on your ORDER or INDEX choice. If you BROWSE a table, you get as many records as you can see on the grid. It's deceptively simple. The solutions aren't.

Since SQL 2000, the TOP n clause has been available to provide what is at least a workable solution.

This is how you get the first record from a table:


That's also how you GO TOP. GO BOTTOM is just


To get the next record, you use this:


And to get to the previous record, use this:


If a grid displays a dozen records, initial and subsequent SELECTS should contain the TOP 12 clause. Note that when moving forward or backward, if the number of records returned is zero, drop back to the GO TOP and GO BOTTOM equivalents respectively.

These operations require an ORDER BY clause in the select statement passed to the DataAdapter, and you must issue a ds.Clear() call before loading the record; otherwise, it will just be appended.


Clearly, data management in FoxPro is as simple as can be, while data management in .NET is as complex as it can be, and unfortunately, we've barely scratched the surface. To compound the problem, some of the data managament solutions in the Microsoft pipeline, in my judgment, will ultimately be judged as defective, and will be abandoned. They follow from the same architectural philosophy that brought us Vista - an endless proliferation of layer upon layer of scaffolding, each layer of which is given one essential task that requires that it be mastered and implemented with at least a few obscure attribute decorations and method calls. It can't be this hard. However, until Redmond decides to fix it, this is what we have to work with.

During the First American Depression (70 years before this one), there was a joke that my father loved: "If we had some ham, we could have some ham and eggs, if we had some eggs." Microsoft's handling of data is something like that. You need a dataset, a data adapter, and data binding; but data binding needs typed datasets, so you need those as well. In addition, the data adapter uses commands created within the typed dataset. There's not a single, simple set of commands that just do the job. It's a whirling complex of ever-more-complex solutions to a problem that we, spoiled as we were, didn't even know existed. But this is a journey that you must make. That's why I'm writing this series of articles. And that's why I've assembled a team of programmers who do this all the time. There is no single solution, and there is no simple solution, but there's always a best solution.

In the next article we'll look more closely at Typed Datasets and their implications for data management. You'll see how code generation gives you much of what you need.


Copyright(C) Pinter Consulting, 2018Tel: +1 (650) 464-6924