I am using the following code to retrieve data from DBF file...
dbfConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"DBASE IV\";Data Source=" + appPath + "test\\sales\\" + DateTime.Now.ToString("yyyyMM") + "\\";
OleDbConnection odconnection = new OleDbConnection(dbfConnectionString);
odconnection.Open();
OleDbCommand oCmdTest = new OleDbCommand("select * from payment", odconnection);
oCmdTest.ExecuteNonQuery();
oledbDataAdapter = new OleDbDataAdapter(oCmdTest);
oledbDataAdapter.Fill(dataSet);
dataTable = dataSet.Tables[0];
This works fine except that it is not retrieving the records that are marked as deleted. I was able to successfully retrieve the deleted records with VFPOLEDB.1 as provider and executing some additional code (FoxPro which is excellent but gives me some issues regarding the field formats :-Error reading certain numeric values with VFPOLEDB driver and this can be fixed by casting these fields but the problem is that my tables are too big so I have to figure out and cast many fields )
OleDbCommand oCmdTest1 = new OleDbCommand("SET DELETED OFF", odconnection);
oCmdTest1.ExecuteNonQuery();
But this additional code wont work with Microsoft.Jet.OLEDB.4.0. How can I retrieve deleted records with Microsoft.Jet.OLEDB.4.0 as provider from a dbf file ? Any help is greatly appreciated..
Well, I couldn't find a way to read deleted records with Microsoft.Jet.OLEDB.4.0 or Microsoft.ACE.OLEDB.12.0 from DBF files. But things worked like a charm when I used a different provider, Advantage OLE DB Provider. Installed it and referenced the 32 bit .dll (Since my solution targets on Any Cpu). The connection string is modified in to..
dbfConnectionString = @"Provider=Advantage.OLEDB.1;User ID=adssys;Data Source=" + appPath + "test\\sales\\" + DateTime.Now.ToString("yyyyMM") + "\\;TableType=ADS_CDX;ShowDeleted=TRUE;Advantage Server Type=ADS_LOCAL_SERVER;";
Here ShowDeleted=TRUE is used to include deleted records.