Visual FoxPro Databases

Visual FoxPro is supported via OleDb connections to DBC based databases. Free tables are not by default supported although if you don't have IDs generated through the default behavior and don't use Transactions you can use free tables.

In order to use wwBusiness with VFP databases:

  • Download the Visual FoxPro 8.0 OleDb Provider

  • Make sure you use a Database (DBC) file
    Because some of the features will require a stored procedure you will need to make sure that you use Database Container for your data storage. This requirement is necessary only if you want to automatically generate Integer PKs (otherwise you have to override the CreateNewId() methods of your business objects).

  • Every table used for a business object must have a primary or canidate key
    Because wwBusiness relies on default generic update mechanisms every update requires primary key information in order to make those updates. Make sure every table has at least a canidate key. Keys should be of consistent type.

  • Add the CreateNewId into the Database's Stored Procedures
    This method is used to generate new integer primary keys for the business objects. Again, if you don't use Integer Pks (Strings or GUIDs) you can skip this step. Otherwise cut and paste the following function into your Database's Stored Procedure file.

    * CreateNewId
    ***  Function: Creates a new ID for a given table.
    ***            if the table doesn't exist it is created
    ***            if the table name doesn't exist in the table it's added
    ***    Assume: Table must be part of the database
    ***      Pass:  ID table name  (usually wws_id)
    ***               Name of the table we're generating a PK value for
    ***               Field of the PK
    ***    Return: New PK value or -1
    PROCEDURE CreateNewId(lcIDTable,lcTableName,lcPkField)
    LOCAL lnX, lnId, lnRecno,llError
    IF VARTYPE(lcType) # "C"
       lcType = "I"
       llError = .F.
       ON ERROR llError = .T.
       USE (lcIDTable) IN 0 ALIAS __IDTABLE
       ON ERROR
       IF llError
          SELECT 0
          CREATE TABLE (lcIDTable) (Tablename C(40),ID I)
          USE (lcIDTable) IN 0 ALIAS __IDTABLE
       ERROR "Unable to open Id table"
       RETURN -1
    IF !USED(lcTableName)
       USE (lcTableName) IN 0
    SELECT (lcTableName)
    *** Assume failure
    lnId = -1
    LOCATE FOR UPPER(Tablename) = UPPER(lcTableName)
    IF !FOUND()
       *** Insert a new id
       REPLACE ID WITH 0,Tablename WITH (lcTableName)
    FOR lnX=1 TO 10000    && Only allow 10000 loops to handle potential endless loops
       IF RLOCK()
          IF ID < 2147483645
             REPLACE ID WITH ID + 1
             *** Allow for rollover -
             *** this code checks for existing ids and skips
             REPLACE ID WITH 1
          lnId2 = ID
          SELE (lcTableName)
          lnRecno = RECNO()  && Save record no
          *** Make sure value doesn't exist
          SELECT pk FROM (lcTableName) WHERE EVALUATE(lcPkField) = lnId2
          IF FOUND()
             UNLOCK IN (lcIDAlias)
             GOTO lnRecno
          *** Reset the record point
          IF lnRecno # 0 AND lnRecno < RECCOUNT()
             GOTO lnRecno
          SELECT __IDTABLE
          lnId = ID

    Note this method is called by default from the wwDataOleDbVfp.CreateNewId() method.

  • Create the wws_id table
    Although the table can be autocreated by the stored procedure it's better that you manually create the file and add it to the database.


    This table will eventually contain an entry for each table in the application that is accessed through the business object.

  • Configure the constructor of your business object subclass

    Set the business object's ServerType to OleDbVfp in the constructor:

    public busCustomer()
    	this.Tablename = "wws_customers";
    	this.ConnectType = ServerTypes.OleDbVfp;
    	this.ConnectionString = 
    		@"Provider=vfpoledb;Data Source=D:\projects\webstoredbfdata\webstore.dbc;" +
    	this.PkType = PkFieldTypes.intType;
    	this.NewRowBlankValues = true;

  • Set permissions if using ASP.Net
    If you are using ASP.Net with a VFP datasource you will need to make sure the Web user account for your application has full access to the data. This means ASPNET/NETWORK SERVICE by default (or whatever other account you have configured to run the Web application) require that these rights are set in the directory where the data files exist.

Once you've performed these simple tasks your VFP database should work as any other provider.

Important Note:
Visual FoxPro tables require maintenance in order to clean up Memo bloat and bloat caused by deleted records. Usually this means occasionally PACKING and recreation of the index files. This functionality is not possible through OleDb and you will need to perform these tasks externally either through the Visual FoxPro IDE or another application that can gain exclusive access to the data.

© West Wind Technologies, 1996-2018 • Updated: 07/14/04
Comment or report problem with topic