How to create a DBF file from scratch in C#?

Question

I am trying to write a DBF file from scratch in my program. I want to create it, add some columns, and then add data to the columns X amount of times. My program will not need to read it in again, but other programs will.

I've looked around for a solution to this, but all seem to assume an existing DBF file, whereas I want to make a new one.

The purpose of this is to make the DBF part of an ESRI ShapeFile.

Does anyone know how to do this?

Byrann

The only option I see would be to issue a create table command directly using the vfpoledb provider. It's cumbersome, but it might work. – Journalese

I have just found out you need the DBF file in a dBase IV format so I have edited my answer. Try it, it should work with the GIS (not sure if you open it with MS Access, though). – Frampton

Answer 1

Download Microsoft OLE DB Provider for Visual FoxPro 9.0 and use:

string connectionString = @"Provider=VFPOLEDB.1;Data Source=D:\temp";
using (OleDbConnection connection = new OleDbConnection(connectionString))
using (OleDbCommand command = connection.CreateCommand())
{
    connection.Open();

    OleDbParameter script = new OleDbParameter("script", @"CREATE TABLE Test (Id I, Changed D, Name C(100))");

    command.CommandType = CommandType.StoredProcedure;
    command.CommandText = "ExecScript";
    command.Parameters.Add(script);
    command.ExecuteNonQuery();
}

Edit: The OP does not want a FoxPro DBF format but dBase IV format:

string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\temp;Extended Properties=dBase IV";

using (OleDbConnection connection = new OleDbConnection(connectionString))
using (OleDbCommand command = connection.CreateCommand())
{
    connection.Open();

    command.CommandText = "CREATE TABLE Test (Id Integer, Changed Double, Name Text)";
    command.ExecuteNonQuery();
}

Frampton

How do you figure that "Microsoft OLE DB Provider for Visual FoxPro 9.0" is built in to the .NET Framework? That's no more built-in than my suggestion to use Microsoft Excel to do the heavy lifting. (But yes, +1, still a better suggestion than mine.) – Dinh

Note: The ExecScript command can be used to copy the table file to other formats as well. @Cody: Thank you. Well, the interface is built-in, the provider may not be. We had it pre-installed, I added the download link anyway. – Frampton

Thanks. I have tried this but when I try and open it in Access it says "External table is not in the expected format.". Any ideas? – Byrann

Remove the @ escape character and add \r\n COPY TO TestInOldFormat TYPE fox2x to the script string - it will copy the FoxPro DBF to the old format that also opens in Excel. I am not sure it will open in Access, though. See msdn.microsoft.com/en-us/library/aa977446.aspx for other formats. – Frampton

Thanks again. I tried that and it did work in Excel, but like you thought not in Access. The other ShapeFile DBFs do work in Access, so if mine cannot then it will not be recognised within the GIS program I am using. I don't think any of the other formats on the link you sent will change this either. I've made a start on creating the DBF myself with a binary writer, though it looks like it is going to be tricky beyond the header. – Byrann

@Greg: Yeah, you should have provided the exact format the GIS requires. M$ Access doesn't support DBF databases anyway and only barely importing them, it uses its own MDB format, doesn't it? Did you want to create MDB or ACCDB instead (you can do that using CatalogClass)? – Frampton

@Jaroslav: Yes, Access converts files to MDB format, but as it wasn't doing it with mine originally I thought something was wrong, but it is the DBF format I want. I've done what you said, and it now seems to be starting to work. I still have some things to iron out, like it seems to limit the filename length it makes to 8 characters, and also it is creating a DBT file which I don't need. Do you know how to fix either of those things? Anyway though, it's on the right track, so thanks a lot for your help! – Byrann

@Greg: it only supports 8.3 (name.extension) filenames. But you can easily rename or delete any files after the creation using the File.Move and File.Delete methods respectively. DBT is a storage for content of dBASE memo fields - you probably won't need memo fields. – Frampton

Answer 2

If you want to completely eliminate external dependencies, you will have to resort to creating the file manually. And in order to do that, you'll need to spend some quality time with the whitepaper describing the file format's specifications to understand the fields you need to implement and what they should contain. You can find that online here: http://www.esri.com/library/whitepapers/pdfs/shapefile.pdf

Of course, this isn't really an undertaking for the faint of heart. Make sure that you understand the work that is entailed here before embarking on the journey.

Dinh

@Jaroslav: We anxiously await your impending answer to the contrary. – Dinh

It's not hard to create the DBF part of a shapefile. I agree the SHP and SHX parts call for determination. – Roxanneroxburgh

Answer 3

I don't know anything about ESRI ShapeFile... but you can create a dbf using OleDb.

Here is an example using the VFP OleDb provider:

    string dbfDirectory = @"c:\";
    string connectionString = "Provider=VFPOLEDB;Data Source=" + dbfDirectory;
    using (OleDbConnection connection = new OleDbConnection(connectionString)) {
        connection.Open();
        OleDbCommand command = connection.CreateCommand();

        command.CommandText = "create table Customer(CustId int, CustName v(250))";
        command.ExecuteNonQuery();
        connection.Close();
    }

Citation