How can I save a DataTable to a .DBF?

Question

I've been working on a program to read a dbf file, mess around with the data, and save it back to dbf. The problem that I am having is specifically to do with the writing portion.

    private const string constring = "Driver={Microsoft dBASE Driver (*.dbf)};"
                                   + "SourceType=DBF;"
                                   + "DriverID=277;"
                                   + "Data Source=¿;"
                                   + "Extended Properties=dBASE IV;";
    private const string qrystring = "SELECT * FROM [¿]";
    public static DataTable loadDBF(string location)
    {
        string filename = ConvertLongPathToShort(Path.GetFileName(location));
        DataTable table = new DataTable();
        using(OdbcConnection conn = new OdbcConnection(RTN(constring, filename)))
        {
            conn.Open();
            table.Load(new OdbcCommand(RTN(qrystring, filename), conn).ExecuteReader());
            conn.Close();
        }
        return table;
    }

    private static string RTN(string stmt, string tablename)
    { return stmt.Replace("¿", tablename); }

    [DllImport("Kernel32", CharSet = CharSet.Auto)]
    static extern Int32 GetShortPathName(
    String path,                // input string
    StringBuilder shortPath,    // output string
    Int32 shortPathLength);     // StringBuilder.Capacity

    public static string ConvertLongPathToShort(string longPathName)
    {
        StringBuilder shortNameBuffer;
        int size;

        shortNameBuffer = new StringBuilder();

        size = GetShortPathName(longPathName, shortNameBuffer, shortNameBuffer.Capacity);
        if (size >= shortNameBuffer.Capacity)
        {
            shortNameBuffer.Capacity = size + 1;
            GetShortPathName(longPathName, shortNameBuffer, shortNameBuffer.Capacity);
        }

        return shortNameBuffer.ToString();
    }

This is what I'm working with. I've tried a number of methods to write a new file, none of them productive. To be honest, while normally I would be an advocate of form and function, I just want the damn thing to work, this app is supposed to do one very specific thing, it's not going to simulate weather.

-=# Edit #=-

I've since discontinued the app due to time pressure, but before I scrapped it I realised that the particular format of dbf I was working with had no primary key information. This of course meant that I had to essentially read the data out to DataTable, mess with it, then wipe all the records in the dbf and insert everything from scratch. Screw that for a lark.

Cosher

Answer 1

For people coming here in the future: I wrote this today and it works well. The filename is without the extension (.dbf). The path (used for connection) is the directory path only (no file). You can add your datatable to a dataset and pass it in. Also, some of my datatypes are foxpro data types and may not be compatible with all DBF files. Hope this helps.

    public static void DataSetIntoDBF(string fileName, DataSet dataSet)
    {
        ArrayList list = new ArrayList();

        if (File.Exists(Path + fileName + ".dbf"))
        {
            File.Delete(Path + fileName + ".dbf");
        }

        string createSql = "create table " + fileName + " (";

        foreach (DataColumn dc in dataSet.Tables[0].Columns)
        {
            string fieldName = dc.ColumnName;

            string type = dc.DataType.ToString();

            switch (type)
            {
                case "System.String":
                    type = "varchar(100)";
                    break;

                case "System.Boolean":
                    type = "varchar(10)";
                    break;

                case "System.Int32":
                    type = "int";
                    break;

                case "System.Double":
                    type = "Double";
                    break;

                case "System.DateTime":
                    type = "TimeStamp";
                    break;
            }

            createSql = createSql + "[" + fieldName + "]" + " " + type + ",";

            list.Add(fieldName);
        }

        createSql = createSql.Substring(0, createSql.Length - 1) + ")";

        OleDbConnection con = new OleDbConnection(GetConnection(Path));

        OleDbCommand cmd = new OleDbCommand();

        cmd.Connection = con;

        con.Open();

        cmd.CommandText = createSql;

        cmd.ExecuteNonQuery();

        foreach (DataRow row in dataSet.Tables[0].Rows)
        {
            string insertSql = "insert into " + fileName + " values(";

            for (int i = 0; i < list.Count; i++)
            {
                insertSql = insertSql + "'" + ReplaceEscape(row[list[i].ToString()].ToString()) + "',";
            }

            insertSql = insertSql.Substring(0, insertSql.Length - 1) + ")";

            cmd.CommandText = insertSql;

            cmd.ExecuteNonQuery();
        }

        con.Close();
    }

    private static string GetConnection(string path)
    {
        return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=dBASE IV;";
    }

    public static string ReplaceEscape(string str)
    {
        str = str.Replace("'", "''");
        return str;
    }

Uprise

I added case with System.Decimal, but it was helping me with your codes Thanks – Haslett

I'm getting an exception Data type mismatch in criteria expression. when the insert statement runs. Any idea what would cause this? – Tonicity

I figured it out. Your code isn't checking for DBNull values in the DataSet and is inserting an empty string '' in columns that should be inserting NULL. – Tonicity

Answer 2

What kind of dbf file are you working with? (There are several, e.g. dBase, FoxPro etc that are not 100% compatible.) I have gotten this to work with the Microsoft Visual FoxPro OleDB Provider from C#, you might give that a shot instead of using the dBase ODBC driver.

Polysyllabic

Answer 3

Using ADO.Net to read and write dbf files turns out to be really slow so I would suggest you use an alternative approach.

One option would be to use the old DAO 3.6 library. This is much faster and just as compatible, but depends on a com object to work.

A better approach would be to use the open source DBFExporter component. It may require some code to set up (you need a class with properties that describe your recordset and the properties must have certain attributes set) but after that it works really well. It is fast to use but it doesn't read dbf files. The component is licences under the LGPL so you should be able to use it in commercial code.

Jemine