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"
  ENDIF
  
  IF !USED("__IDTABLE")
     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
     ENDIF
  ENDIF
  SELECT __IDTABLE
  
  IF !USED("__IDTABLE")
     ERROR "Unable to open Id table"
     RETURN -1
  ENDIF
  
  IF !USED(lcTableName)
     USE (lcTableName) IN 0
  ENDIF
  SELECT (lcTableName)
  
  *** Assume failure
  lnId = -1
  
  SET REPROCESS TO 2 SECONDS
  
  SELECT __IDTABLE
  LOCATE FOR UPPER(Tablename) = UPPER(lcTableName)
  IF !FOUND()
     *** Insert a new id
     APPEND BLANK
     REPLACE ID WITH 0,Tablename WITH (lcTableName)
  ENDIF
  
  FOR lnX=1 TO 10000    && Only allow 10000 loops to handle potential endless loops
     SELECT __IDTABLE
     IF RLOCK()
        IF ID < 2147483645
           REPLACE ID WITH ID + 1
        ELSE
           *** Allow for rollover -
           *** this code checks for existing ids and skips
           REPLACE ID WITH 1
        ENDIF
  
        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
           LOOP
        ENDIF
  
        *** Reset the record point
        IF lnRecno # 0 AND lnRecno < RECCOUNT()
           GOTO lnRecno
        ENDIF
  
        SELECT __IDTABLE
        lnId = ID
     ENDIF
     UNLOCK IN __IDTABLE
     EXIT
  ENDFOR

  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.

  CREATE TABLE wws_id (TABLENAME C(40),ID I)

  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;" +
   		 "Exclusive=false;Nulls=false;";
  	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.