Writing a DataTable As DBF (Long Post)

[Mark Sanders]

This will be a long post but any insight will be greatly appreciated. Process Description: I have to write a C# method which will take as its parameter a System.Data.DataTable and then write this DataTable to a new dBASE IV table on disk. Current Solution: (not a good one) Here is my current solution in C# which is very very very slow (It takes 5+ minutes to output a 10MB .DBF on a dual 2.4 Zeon machine with 2GB of RAM running a RAID 5). :((

public void WriteTable(DataTable table)
{
  //First, I create a column string to use in the CREATE TABLE sql statement.
  string columnString="";
 
  foreach(DataColumn column in table.Columns)
  {
    string type = "";

    switch(column.DataType.ToString())
    {
      case "System.String":
        type = "varchar(" + MaxLength(column) + ")";
        break;
      case "System.Int32":
        type = "int";
        break;
      case "System.Double":
        type = "double";
        break;
      default:
        throw new ArgumentException("Data type not found.", column.DataType.ToString());
    }

    columnString += column.ColumnName + " " + type + ", ";
  }


  columnString = columnString.Substring(0, columnString.Length-2);
  //Done creating column string.
  //////////////////////////////////////////////////////////////////////
  
  //Second, I create the new table on disk.
  connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + directory + ";Extended Properties=dBase IV";
  sqlString = "CREATE TABLE " + name + " (" + columnString + ")";
  connection = new OleDbConnection(connectionString);
  command = new OleDbCommand(sqlString, connection);
			
  command.Connection.Open();
  command.ExecuteNonQuery();
  command.Connection.Close();
  //Done creating table.
  //////////////////////////////////////////////////////////////////////
  
  //Third, I insert each record into the new table.
  command.Connection.Open();

  int columnCount = table.Columns.Count;
  foreach(DataRow row in table.Rows)
  {
    string valueString="";
    for(int i=0; i < columnCount; i++)
    {
      switch(row[i].GetType().ToString())
      {
        case "System.String":
          valueString += "'" + ((string)row[i]).Replace("'", "''") + "', ";
          break;
        case "System.Int32":
          valueString += System.Convert.ToString((int)row[i]) + ", ";
          break;
        case "System.Double":
          valueString += Sys

[Daniel Turini]

Mark Sanders wrote: Do you think it would be possible/better to try and see if I could use an OleDbDataSet/OleDbDataAdapter solution? No, probably this will be slower. Mark Sanders wrote: Can anyone give me some insight as to why this solution is incredibly slow other than the fact that there is a very large amount of records to write out? Use parametrized queries: this will give you a huge performance boost. See the OleDbParameter class for examples. Notice that using parametrized queries you won't need anymore to replace "dangerous" chars like the quotes. This probably will eliminate the need for the switch on the type and all the string conversions, too. For another tip, do not make massive string concatenations using the String class. This is slow, and will kill the performance of the garbage collector. Actually, this can lead to hard-to-find performance problems. Use the System.Text.StringBuilder instead. My latest article: GBVB - Converting VB.NET code to C#

[Mark Sanders]

I have been trying to find some examples of setting up an INSERT into a .dbf using OleDbParameter class. I have been unsuccessful. Can you point me to some examples? I understand how to use parameters with SQL Server stored procedures but I seem to be missing something when it comes to using parameters for and Ole connection to a .dbf. Here is what I have so far but it is not working.

string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\;Extended Properties=dBase IV";
string sqlString = "INSERT INTO TestTabl (Column) VALUES (?)";
OleDbConnection connection = new OleDbConnection(connectionString);
OleDbCommand command = new OleDbCommand(sqlString, connection);


command.Parameters.Add("@Column", OleDbType.VarChar);
command.Parameters["@Column"].Value = "It Worked";


command.Connection.Open();
command.ExecuteNonQuery();
command.Connection.Close();

Thanks Mark Sanders

[Mark Sanders]

I adjusted my code to use parameterized queries but have not gained a significant enough performance boost. After some performance tests I have found that my OleDbCommand.ExecuteNonQuery() takes an average of 0.004 seconds to execute (with the max occurance being 0.015 seconds). With my current mid-range test I do this for 145,160 records for a total of 9.85 minutes! After some more research it appears the general concensus is that ADO.NET is unable to do this type of operation any faster. So, my new question is... I have no choice in the requirements. I must take a DataTable and write it to disk as a dBASE IV table. Does anyone have any ideas on how to do this in the fastest possible way? Mark Sanders

[Lost User]

When I work with DBF files I always use microsoft's ODBC data provider classes, ODBC Adapters etc. So far it works fast and effective. You can dowload it somewhere from the MSDN Good luck. "I only Play for Sport" Lara H. Croft