我怎么能保存一个数据表到一个.dbf？ [英] How can I save a DataTable to a .DBF?

问题描述

我已经工作程序与数据读取周围一个DBF文件，乱七八糟的，保存回DBF。 ，我遇到的问题是具体的写作部分做的。

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 #=-

我已经停产，因为该应用程序由于时间的压力，但在此之前我报废它，我意识到， DBF我与工作的特殊格式没有主键信息。当然，这意味着我不得不从根本上读取数据到数据表，惹它，然后抹在DBF中的所有记录，然后插入一切从头开始。
螺纹，对于云雀

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.

推荐答案

有关的人来这里的未来：我这今天撰文称，它的工作原理好。文件名是没有扩展名（DBF）。路径（用于连接）是目录路径（不使用文件）。您可以将数据表添加到数据集，并通过它。另外，我的一些数据类型是FoxPro数据类型，可能无法与所有DBF文件兼容。希望这有助于。

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

这篇关于我怎么能保存一个数据表到一个.dbf？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！