I thought my days of dealing with DBFs as a "production data" source were over, but HA (no such luck).
I recently had to retrieve, modify and replace some data that needed to be delivered in a DBF file.
Everything was fine until I realized / remembered the DBF driver does not ACTUALLY delete records from the data source -- it only marks them for deletion. You are responsible for handling the "chaff" either by using a utility to remove deleted records or by simply ignoring them. If imported into Excel, the marked-deleted records are ignored, but the file size will reflect the extra content. After several rounds of testing CRUD, the output DBF was
huge.
So, I went hunting for a method to "Pack" the records (removing deleted ones and resizing the DBF file) and eventually ran across the FOXPRO driver at ( http://msdn.microsoft.com/en-us/vfoxpro/bb190233.aspx ). Once installed, I changed the DSN in the code to the new one I created in the ODBC Administrator and ran some tests.
Using MSQuery, I simply tested the raw SQL command Pack {tablename} and it WORKED!
One really neat thing is the PACK command is used like regular SQL instructions; "Pack {tablename}" is all that is needed.
It is necessary, however, to close all connections to the database (and re-open) before issuing the PACK command or you will get the "File is in use" error.
Here is some C# code for a Pack method.
/// <summary>
/// Pack the DBF removing all deleted records
/// </summary>
/// <param name="strTableName">The table to pack</param>
/// <param name="strError">output of any errors</param>
/// <returns>bool (true if no errors)</returns>
public static bool Pack(string strTableName, ref string strError)
{
bool blnRetVal = true;
try
{
OdbcConnectionStringBuilder csbOdbc = new OdbcConnectionStringBuilder()
{
Dsn = "PSAP_FOX_DBF"
};
string strSQL = "pack " + strTableName;
using (OdbcConnection connOdbc = new OdbcConnection(csbOdbc.ToString()))
{
connOdbc.Open();
OdbcCommand cmdOdbc = new OdbcCommand(strSQL, connOdbc);
cmdOdbc.ExecuteNonQuery();
connOdbc.Close();
}
}
catch (Exception exc)
{
blnRetVal = false;
strError = exc.Message;
}
return blnRetVal;
}
