Optimal way to handle .dbf from C#

Question

What data-provider can I use to update .dbf file from C#?

I tried a few different .dbf providers to create a DataSource but I get a message like this: "Error Message: ERROR HYC00 Microsoft ODBC dBase Driver Optional feature not implemented."

Or when I generated a dataset and a dataadapter with an update function I got: "Update requires a valid UpdateCommand when passed DataRow collection with modified rows."

If anyone knows some way to work on .dbf from C# with lots of updates please help. When I try to update rows one by one it is too slow because the provider will lose too much time on searching a big .dbf file. Maybe there is a way to automatically build an index and that the data-source knows to use it?

Another way is to load all into something like dataset and update after all changes are done, but the update part is not working for now.

Help please!

Answer 1

The vanilla OleDbConnection handles DBFs pretty well, if you stick with very basic SQL operations.

Here where I work, we build & maintain applications that interact with DBFs using exclusively OleDb classes. We do not, however, use Adapters or DataSources -- everything is done "directly" through OleDbCommands, OleDbDataReaders, and so on.

Perhaps DataAdapters rely on features that may not be present when interacting with basic or legacy data sources such as xBase. Have you tried using an UPDATE OleDbCommand?

Answer 2

Usually,the FoxPro driver works with .DBF files. The file format is similar enough that this works well for reading. Writing is a bit more tricky. Unfortunately, since the DBASE is such an old technology, .NET doesn't play nice with it, so you're pretty much stuck with your slow option. Believe, me, I feel your pain as I have to work with these regularly for a POS system we support.

http://www.aspcode.net/Reading-DBF-files-in-C.aspx

http://stackoverflow.com/questions/2065820/net-connection-to-dbase-dbf-file

http://stackoverflow.com/questions/75705/how-to-read-write-dbase-iii-files-using-c-net-odbc-or-ole

and finally, my favorite source for Connection Strings:

http://www.carlprothman.net/Default.aspx?tabid=81

Answer 3

From your coment about ~1GB database, I too work with VFP Databases (.dbf) file formats, and SQL-Updates work no problem via OleDbCommand creation / execution, and can work with whatever native commands the VFP OleDbProvider runs.

For trying to remove some characters, I typically use the function CHRTRAN() (that is, if you DO use Visual Foxpro Ole DB Provider), where you can literally strip out many characters (such as invalid) something like...

Update YourTable
   set SomeField = chrtran( SomeField, "!@#$%^*(", "" )

will go through ALL records and strip any from the field (first parameter), any instance of individual character (2st parameter), and change it to the corresponding character found in the 3rd parameter... in this case, no value, just an empty string, so the characters would be stripped out. Quite fast in itself, and you don't have to keep scanning through all records being downloaded, tested, and then pushed back.

Again, not positive which native .DBF file system you are working with, but VFP is extremely fast with such manipulation.