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.

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;
    }