C# 如何将 DataTable 保存到 .DBF？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/322792/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I save a DataTable to a .DBF?
提问by tzrlk
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.
我一直在开发一个程序来读取 dbf 文件，处理数据，然后将其保存回 dbf。我遇到的问题特别与写作部分有关。
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.
由于时间压力，我已经停止使用该应用程序，但在我废弃它之前，我意识到我正在使用的特定格式的 dbf 没有主键信息。这当然意味着我必须基本上将数据读取到 DataTable 中，弄乱它，然后擦除 dbf 中的所有记录并从头开始插入所有内容。把它搞砸了。
采纳答案by HeyThereLameMan
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.
对于将来来这里的人：我今天写了这篇文章，效果很好。文件名没有扩展名 (.dbf)。路径（用于连接）仅为目录路径（无文件）。您可以将数据表添加到数据集并将其传入。此外，我的某些数据类型是 foxpro 数据类型，可能与所有 DBF 文件不兼容。希望这可以帮助。
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;
}
回答by Eyvind
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.
您使用的是哪种 dbf 文件？（有几个，例如 dBase、FoxPro 等，它们不是 100% 兼容的。）我已经让它与来自 C# 的 Microsoft Visual FoxPro OleDB Provider 一起工作，您可以尝试一下，而不是使用 dBase ODBC 驱动程序。
回答by Rune Grimstad
Using ADO.Net to read and write dbf files turns out to be really slow so I would suggest you use an alternative approach.
使用 ADO.Net 读取和写入 dbf 文件的速度非常慢，因此我建议您使用另一种方法。
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.
一种选择是使用旧的 DAO 3.6 库。这要快得多并且同样兼容，但取决于 com 对象的工作。
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.
更好的方法是使用开源DBFExporter 组件。它可能需要一些代码来设置（您需要一个具有描述记录集的属性的类，并且这些属性必须设置某些属性）但之后它工作得非常好。它使用起来很快，但它不读取 dbf 文件。该组件是 LGPL 下的许可证，因此您应该能够在商业代码中使用它。
