Deleting all records from a DBF file

May 8, 2012 at 1:59 PM

I would like to delete a record completely from the DBF file. Actually I would like to clear all records from the DBF file. However records deleted using the "Delete" statement are not really deleted (e.g. you can undelete them using a 3rd party DBF explorer). There is an option in a DBF Editor application written by Teklynx International (dated from year 2000) which has an option to do this (Record -> Delete All Records).

Coordinator
May 9, 2012 at 1:14 PM
Edited May 9, 2012 at 1:14 PM

That is correct… VFP only marks a record for deletion when you issue the delete command. You need to issue a PACK command to instruct VFP to remove the deleted records (which actually recreates the table).  To delete all records and PACK the table at once you can use the ZAP command.  I've add a method for each of these commands on the provider.  Below is an example of how you can use them.

 

 

using System;
using IQToolkit;
using IQToolkit.Data.Mapping;
using IQToolkitContrib;
using LinqToVfp;

namespace PackZapTest
{
    static class Program
    {
        [STAThread]
        static void Main()
        {
            var path = @"c:\NorthwindData\Northwind.dbc";
            var connectionString = "provider=vfpoledb;data source=" + path;

            var context = new DataContext(connectionString);

            // You could PACK the table manually.
            using (var command = context.Provider.Connection.CreateCommand())
            {
                var tableName = "customers";
                
                command.CommandText = "EXECSCRIPT([USE " + tableName + " IN SELECT (0) EXCLUSIVE] + CHR(13) + [PACK] + CHR(13) + [CLOSE TABLES ALL])";
                
                context.Provider.DoConnected(() => command.ExecuteNonQuery());
            }

            // Added a Pack method to the provider so that you don't need to use the manual method shown above.
            context.Provider.Pack(context.Customers);

            // Also added a method for Zap.
            context.Provider.Zap(context.Customers);
            
        }
    }

    public class DataContext : DbEntityContextBase
    {
        public new VfpQueryProvider Provider
        {
            get
            {
                return (VfpQueryProvider)base.Provider;
            }
        }

        public DataContext(string connectionString)
            : this(connectionString, typeof(DataMapping).FullName)
        {
        }

        public DataContext(string connectionString, string mappingId)
            : this(VfpQueryProvider.Create(connectionString, mappingId))
        {
        }

        public DataContext(VfpQueryProvider provider)
            : base(provider)
        {
        }

        public virtual IEntityTable<Customer> Customers
        {
            get { return this.GetTable<Customer>(); }
        }
    }

    public class DataMapping : DataContext
    {
        public DataMapping(string connectionString)
            : base(connectionString)
        {
        }

        [Table(Name = "Customers")]
        [Column(Member = "CompanyName")]
        public override IEntityTable<Customer> Customers
        {
            get
            {
                return base.Customers;
            }
        }
    }

    public class Customer
    {
        public string CustomerId { get; set; }
        public string CompanyName { get; set; }
    }
}

 

 

* Note:  Both of these commands require exclusive access to the table so an exception will be thrown if they cannot get exclusive access.

May 9, 2012 at 2:35 PM

Hey, great, very quick response. Thanks!